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

[Michael Van Canneyt]

On Thu, 19 Apr 2007, Bram Kuijvenhoven wrote:

> 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.

The SQL standard specifies this, as far as I know ?

> 
> 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.

Yes, but this behaviour would be unique to ODBC. Not something to generalize.

>  
> > > 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).

I didn't know you can connect to 2 databases at once in mysql ? Is this new
in MySQL 5 ?

> 
> 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.

Yes, and all this is why I think such thing should not be part of TDataset/TField.
It's very ODBC specific.

> 
> > 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.

Yes, but this would not be so for Firebird or - as far as I know - MySQL.
There you would need additional queries to retrieve such information.
Therefore I don't think that having such information available should
be part of the standard set of SQLDB or TDataset.

> 
> > 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.

It's equivalent to TableName.FieldName. It's mainly used for the
DatasetProvider resolver, I think.

Michael.