Microsoft KB Archive/243056

= INFO: Using SchemaName and the SchemaSeparator in ODBC =

Article ID: 243056

Article Last Modified on 5/18/2000

-

APPLIES TO


 * Microsoft Open Database Connectivity 3.0
 * Microsoft Open Database Connectivity 3.5
 * Microsoft Open Database Connectivity 3.51
 * Microsoft Open Database Connectivity 3.6
 * Microsoft Open Database Connectivity 3.7

-



This article was previously published under Q243056



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

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

Additional query words: Catalog Separator, Schema, SQLGetInfo

Keywords: kbinfo kbdocfix KB243056

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.