[fpc-devel] TField.FieldName: how about database and table names?

[Bram Kuijvenhoven]

Hi,

Thank you for your reply, Michael, and also Leonardo.

I will try to explain the background to my question a little more; I will need to adapt TODBCConnection anyway, but I want to know how this could be done best.

Michael Van Canneyt wrote:
> 
> On Wed, 18 Apr 2007, Bram Kuijvenhoven wrote:
> 
>> Hi!
>>
>> The TField class from the db unit defines a FieldName. When using SQL queries,
>> columns in the result set can be fully qualified using the form
>> 'database.table.field'. Apparently FieldName only constitus the latter part.
>>
>> When using an SQL query like
>>
>>  SELECT * FROM myTable AS t1, myTable AS t2
>>
>> how should one distinguish between t1.aField and t2.aField when using TField?
> 
> That depends on the engine. The engine assigns a unique name to each field
> in the result set. This differs between engines, and can differ between
> versions of the engine.
> 
> Run a query with an aggregate field on Interbase 6, run the same query on
> Firebird 1. You'll see that the aggregate field is named differently.

In ODBC, the columns in the result set have a number of 'descriptor fields' that are connected to their 'name':
- SQL_DESC_BASE_COLUMN_NAME
- SQL_DESC_BASE_TABLE_NAME
- SQL_DESC_CATALOG_NAME
- SQL_DESC_NAME (<--- currently used in TODBCConnection as FieldNames)
- SQL_DESC_SCHEMA_NAME
- SQL_DESC_TABLE_NAME
(Source: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlsetdescfield.asp, under 'FieldIdentifier Argument')

There is no statement in the ODBC specification claiming that any of these field names should be unique. It appears that I must implement a procedure in TODBCConnection to ensure the FieldNames are unique.

To create unique FieldNames I can indeed use the technique proposed by Leonardo (age, age1, age2, etc.); this will ensure uniqueness of FieldNames.

For the purposes of my application, however, it would also be nice if I could tell the 'components' catalog/database, table/schema and column/field of a field, as given by the ODBC driver.
  
>> Is there currently a feature for this in the data model, or should we perhaps
>> add two additional properties -- DatabaseName and TableName -- to a TSQLField
>> (and TSQLFieldDef) descendant? (which then could be used by SQLDB etc.)
> 
> No, because:
> 
> 1. DatabaseName you can get from the connection component. We don't support
>    multi-connection queries, and AFAIK most engines don't support it
>    either...

I did not refer to multi-connection queries, but multi-catalog queries; e.g. in MySQL one can perfectly do a query like

  SELECT dbFoo.tableBar.aField, dbBar.tableFoor.anotherField
    FROM dbFoo.tableBar, dbBar.tblFoo

Here dbFoo and dbBar are two catalogs (or: databases).

In TODBCConnection the DatabaseName has a different meaning than with e.g. TMySQLConnectionXX; it actually refers to a DSN, which I thought to be more appropriate in the setting of ODBC. Another reason I used DatabaseName for a DSN, is that there is no ODBC-defined connection parameter defining the 'database' to connect to, as with for example MySQL. The MySQL ODBC driver defines the DATABASE parameter for this, but this is not part of the ODBC specification. (Note: use TODBCConnection.Params to specify custom parameters such as DATABASE=myDB and SERVER=127.0.0.1) Some database driver operate on files, e.g. the MS Access driver, which uses the DBQ parameter to specify a file that holds the database.

> 2. Tablename makes no sense if you execute a stored procedure.

For ODBC, if a tablename makes no sense, the corresponding descriptor field is simply an empty string. We can copy this behaviour.
 
> Also, it would take a lot of extra queries to be able to retrieve these
> values, and this would seriously impact performance. If there is anything
> we should not compromise on, it's performance.

For ODBC, the information does not require additional queries, only some API calls, which could also be delayed until the properties are actually read.

> The only thing we can do is add the 'Origin' property such as Delphi has it.
> And even then, this is of limited use.

What is this 'Origin' property? I'm curious.

Regards,

Bram