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?

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

Regards,

Bram

Bram, suppose myTable has two fields, "name" and "age", the query should return something like this:

name|age|name1|age1
----+---+-----+----
aa | 11| aa| 11

Each TField is related to one column in the resultset, not to each table involved in the query.

__________________________________________________ Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

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.

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

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

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.

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

Michael.

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.

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.

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

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

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

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

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

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.

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

Michael.

The sequence Schema.Table.Column could be unique, but this does not hold for e.g. SQL_DESC_NAME in ODBC or MYSQL_FIELD.name in the MySQL C API.

(As an experiment, try to run a query like 'SELECT * FROM db.table, db.table' in MySQL; it will complain: 'Not unique table/alias: 'table''. This can be fixed by 'SELECT * FROM db.table, db.table AS aTableAlias'. On the other hand it does accept 'SELECT * FROM db2006.table, db2007.table', here the table names are the same, but the schema names differ.)

The MySQL C API also defines this; see the MYSQL_FIELD record. I expect at least some other DB engines also support this, in particular because it is also in ODBC.

(NB: I need to make one correction to my terminology: a 'schema' appears to be the same as a 'database' (in MySQL), but 'catalog' refers to the database metadata.)

(NB: I currently have only a MySQL database running here, so that's why I will mostly refer to MySQL)

I think this has been supported in MySQL for as long as I know it :)

This can be very useful in applications; for example one might have one a (huge) postal code database (mapping addresses to postal codes) which is used in several other databases. (Of course it is also useful when transferring data from one database to another.)

If TDataset and TField are too general for this, then how about TSQLQuery? Anyway, we also have TField.Origin.

Because we use DESCRIBE queries instead of e.g. accessing MYSQL_FIELD?

I see TField.Origin is also in FPC's db unit. Perhaps we can use this one.

Should TField.Origin also contain the schema name? It might be required to guarantee uniqueness.

We should take care, however: at least some DBMSes support (yes, MySQL) . characters in field names, we could end up with 'schema.table.a.field.with.dots.in.its.name'. If an application wants to parse this into schema, table and field name, it should be really careful. When the schema name is omitted form Origin (for some reason), it could even fail parsing on e.g. 'table.field.withadot'.

This is why I prefer to have separate TableName and SchemaName properties added, at the level of TField or perhaps at the level of SQLDB.

Actually there is even more:
- SQL_DESC_BASE_COLUMN_NAME / MYSQL_FIELD.org_name
- SQL_DESC_BASE_TABLE_NAME / MYSQL_FIELD.org_table
- SQL_DESC_CATALOG_NAME / MYSQL_FIELD.catalog

The first two are for column and table aliases; the purpose and meaning of last one is still a little bit of a mystery to me.

From my colleagues I heard few DB layers support the schema.table.column distinction, and thereby give rise to the need to supply aliases for columns in queries doing multiple joins on the same table. I hope the FPC DB layer can be made more powerful than that!

Regards,

Bram