SUMMARY
The ODBC specification defines the following attributes, which an application can query through
SQLGetInfo to get information about the Catalog names and Schema names, in order to use the information to qualify the objects when they are used in different types of SQL statements:
SQL_CATALOG_LOCATIONSQL_CATALOG_NAMESQL_CATALOG_NAME_SEPARATORSQL_CATALOG_TERMSQL_CATALOG_USAGESQL_SCHEMA_TERMSQL_SCHEMA_USAGE
Although the documentation is clear about the usage of Catalog names, it is not clear about how the Schema names should be used.
Given that the driver supports usage of Catalog names and the Catalog separator, if the driver returns an empty string for
SQL_SCHEMA_TERM, then an application using the driver should qualify the tablename as follows:
select * from catalog_name.tablename
The driver and the database backend should be responsible for making sure that such a syntax is supported.
Any SQL92 compliant driver supports qualifying a tablename with a Schema name. The Schema name always precedes the tablename, and the separator is always a ".".
A driver that does not support Schema names returns an empty string as the Schema Term through
SQL_SCHEMA_TERM. Also, if it exposes
SQL_SCHEMA_USAGE it returns a zero.
If the driver returns an empty string for Schema Term, then the application should not use the schema separator (".") when constructing a qualified name (that is, it should qualify names as: <catalog><catalog separator><tablename>).
If the driver does support Schema names, but the application wants to qualify the tablename with the catalog and use the default schema, then the application must use the schema separator as well (that is, <catalog><catalog separator>.<tablename>).