SAS/ACCESS Software for Relational Databases: Reference

Special ODBC Queries

The following special queries are supported by the SAS/ACCESS interface to ODBC. Many databases provide or use system tables that allow queries to return the list of available tables, columns, procedures, and other useful information. In ODBC, much of this functionality is provided through special APIs (application programming interfaces) in order to accommodate databases that do not follow the SQL table structure. You can use these special queries on non-SQL and on SQL databases. The general format of the special queries is:

ODBC::SQLAPI "parameter 1","parameter n"
where

ODBC::: is required to distinguish special queries from regular queries
SQLAPI: is the specific API that is being called. Both ODBC:: and SQLAPI are case sensitive.
"parameter n": is a quoted string that is delimited by commas.

Within the quoted string, two characters are universally recognized: the percent sign (%) and the underscore (_). The percent sign % matches any sequence of zero or more characters; the underscore represents any single character. Each driver also has an escape character that can be used to place characters within the string. Consult the driver's documentation to determine the valid escape character.

The values for the special query arguments are DBMS specific. For example, you supply the fully qualified table name for the "Qualifier" argument. In dBase, the value of "Qualifier" might be c:\dbase\tst.dbf and in SQL Server, the value might be test.customer. In addition, depending on the DBMS that you are using, valid values for "Owner" might be a user ID, a database name, or a library. All arguments are optional. If you specify some but not all parameters within an argument, use a comma to indicate the omitted parameters. If you do not specify any parameters, commas are not necessary. Note that these special queries may not be available for all ODBC drivers.

The following special queries are supported:

ODBC::SQLTables <"Qualifier", "Owner","Table-name", "Type">: returns a list of all the tables that match the specified arguments. If no arguments are specified, all accessible table names and information are returned.
ODBC::SQLColumns <"Qualifier", "Owner","Table-name", "Col-name">: returns a list of all the columns that match the specified arguments. If no arguments are specified, all accessible column names and information are returned.
ODBC::SQLColumnPrivileges <"Qualifier", "Owner","Table-name", "Col-name">: returns a list of all the column privileges that match the specified arguments. If no arguments are specified, all accessible column names and privilege information are returned.
ODBC::SQLForeignKeys <"PK-qualifier", "PK-owner", "PK-table-name", "FK-qualifier", "FKOwner","FKTableName">: returns a list of all the columns that comprise foreign keys that match the specified arguments. If no arguments are specified, all accessible foreign key columns and information are returned.
ODBC::SQLPrimaryKeys <"Qualifier", "Owner", "Table-name">: returns a list of all the columns that compose the primary key that matches the specified table. A primary key can be composed of one or more columns. If no table name is specified, this special query will fail.
ODBC::SQLProcedureColumns <"Qualifier", "Owner", "Proc-name", "Col-name">: returns a list of all the procedure columns that match the specified arguments. If no arguments are specified, all accessible procedure columns are returned.
ODBC::SQLProcedures <"Qualifier", "Owner", "Proc-name">: returns a list of all the procedures that match the specified arguments. If no arguments are specified, all accessible procedures are returned.
ODBC::SQLStatistics <"Qualifier", "Owner", "Table-name">: returns a list of the statistics for the specified table name, with options of SQL_INDEX_ALL and SQL_ENSURE set in the SQLStatistics API call. If the table name argument is not specified, this special query will fail.
ODBC::SQLTablePrivileges <"Qualifier", "Owner", "Table-name">: returns a list of all the tables and associated privileges that match the specified arguments. If no arguments are specified, all accessible table names and associated privileges are returned.
ODBC::SQLGetTypeInfo: returns information about the data types that are supported in the data source.

Chapter Contents
Previous
Next
Top of Page