INFO: Using SchemaName and the SchemaSeparator in ODBC

ID: Q243056

The information in this article applies to:

Microsoft Open Database Connectivity, versions 3.0, 3.5, 3.51, 3.6, 3.7

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_LOCATION
SQL_CATALOG_NAME
SQL_CATALOG_NAME_SEPARATOR
SQL_CATALOG_TERM
SQL_CATALOG_USAGE
SQL_SCHEMA_TERM
SQL_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>).

Additional query words: Catalog Separator, Schema, SQLGetInfo

Keywords : kbdocfix kbODBC
Version : WINDOWS:3.0,3.5,3.51,3.6,3.7
Platform : WINDOWS
Issue type : kbinfo