Call Level Interface Guide and Reference

SQLTables - Get Table Information

Purpose

Specification: DB2 CLI 2.1 ODBC 1.0

SQLTables() returns a list of table names and associated information stored in the system catalog of the connected data source. The list of table names is returned as a result set, which can be retrieved using the same functions that are used to process a result set generated by a query.

Syntax

SQLRETURN   SQLTables        (
                SQLHSTMT          StatementHandle,   /* hstmt */
                SQLCHAR      FAR  *CatalogName,      /* szCatalogName */
                SQLSMALLINT       NameLength1,       /* cbCatalogName */
                SQLCHAR      FAR  *SchemaName,       /* szSchemaName */
                SQLSMALLINT       NameLength2,       /* cbSchemaName */
                SQLCHAR      FAR  *TableName,        /* szTableName */
                SQLSMALLINT       NameLength3,       /* cbTableName */
                SQLCHAR      FAR  *TableType,        /* szTableType */
                SQLSMALLINT       NameLength4);      /* cbTableType */

Function Arguments

Table 178. SQLTables Arguments

Data Type Argument Use Description
SQLHSTMT StatementHandle Input Statement handle.
SQLCHAR * CatalogName Input Buffer that may contain a pattern-value to qualify the result set. Catalog is the first part of a 3 part table name.
This must be a NULL pointer or a zero length string.
SQLSMALLINT NameLength1 Input Length of CatalogName. This must be set to 0.
SQLCHAR * SchemaName Input Buffer that may contain a pattern-value to qualify the result set by schema name.
SQLSMALLINT NameLength2 Input Length of SchemaName.
SQLCHAR * TableName Input Buffer that may contain a pattern-value to qualify the result set by table name.
SQLSMALLINT NameLength3 Input Length of TableName.
SQLCHAR * TableType Input Buffer that may contain a value list to qualify the result set by table type.
The value list is a list of upper-case comma-separated single quoted values for the table types of interest. Valid table type identifiers may include: TABLE, VIEW, SYSTEM TABLE, ALIAS, SYNONYM. If TableType argument is a NULL pointer or a zero length string, then this is equivalent to specifying all of the possibilities for the table type identifier.
If SYSTEM TABLE is specified, then both system tables and system views (if there are any) are returned.
SQLSMALLINT NameLength4 Input Size of NameLength4

Data Type	Argument	Use	Description
SQLHSTMT	StatementHandle	Input	Statement handle.
SQLCHAR *	CatalogName	Input	Buffer that may contain a `pattern-value` to qualify the result set. `Catalog` is the first part of a 3 part table name. This must be a NULL pointer or a zero length string.
SQLSMALLINT	NameLength1	Input	Length of `CatalogName`. This must be set to 0.
SQLCHAR *	SchemaName	Input	Buffer that may contain a `pattern-value` to qualify the result set by schema name.
SQLSMALLINT	NameLength2	Input	Length of `SchemaName`.
SQLCHAR *	TableName	Input	Buffer that may contain a `pattern-value` to qualify the result set by table name.
SQLSMALLINT	NameLength3	Input	Length of `TableName`.
SQLCHAR *	TableType	Input	Buffer that may contain a `value list` to qualify the result set by table type. The value list is a list of upper-case comma-separated single quoted values for the table types of interest. Valid table type identifiers may include: TABLE, VIEW, SYSTEM TABLE, ALIAS, SYNONYM. If `TableType` argument is a NULL pointer or a zero length string, then this is equivalent to specifying all of the possibilities for the table type identifier. If SYSTEM TABLE is specified, then both system tables and system views (if there are any) are returned.
SQLSMALLINT	NameLength4	Input	Size of `NameLength4`

Note that the CatalogName, SchemaName, and TableName arguments accept search patterns. For more information about valid search patterns, refer to Input Arguments on Catalog Functions.

Usage

Table information is returned in a result set where each table is represented by one row of the result set. To determine the type of access permitted on any given table in the list, the application can call SQLTablePrivileges(). Otherwise, the application must be able to handle a situation where the user selects a table for which SELECT privileges are not granted.

To support obtaining just a list of schemas, the following special semantics for the SchemaName argument can be applied: if SchemaName is a string containing a single percent (%) character, and CatalogName and TableName are empty strings, then the result set contains a list of valid schemas in the data source.

If TableType is a single percent character (%) and CatalogName, SchemaName, and TableName are empty strings, then the result set contains a list of valid table types for the data source. (All columns except the TABLE_TYPE column contain NULLs.)

If TableType is not an empty string, it must contain a list of upper-case, comma-separated values for the types of interest; each value may be enclosed in single quotes or unquoted. For example, "'TABLE','VIEW'" or "TABLE,VIEW". If the data source does not support or does not recognize a specified table type, nothing is returned for that type.

Sometimes, an application calls SQLTables() with null pointers for some or all of the SchemaName, TableName, and TableType arguments so that no attempt is made to restrict the result set returned. For some data sources that contain a large quantity of tables, views, aliases, etc., this scenario maps to an extremely large result set and very long retrieval times. Three mechanisms are introduced to help the end user reduce the long retrieval times: three keywords (SCHEMALIST, SYSCHEMA, TABLETYPE) can be specified in the CLI initialization file to help restrict the result set when the application has supplied null pointers for either or both of SchemaName and TableType. These keywords and their usage are discussed in detail in Configuration Keywords. If the application did not specify a null pointer for SchemaName or TableType then the associated keyword specification in the CLI initialization file is ignored.

The result set returned by SQLTables() contains the columns listed in Table 179 in the order given. The rows are ordered by TABLE_TYPE, TABLE_CAT, TABLE_SCHEM, and TABLE_NAME.

Since calls to SQLTables() in many cases map to a complex and thus expensive query against the system catalog, they should be used sparingly, and the results saved rather than repeating calls.

The VARCHAR columns of the catalog functions result set have been declared with a maximum length attribute of 128 to be consistent with SQL92 limits. Since DB2 names are less than 128, the application can choose to always set aside 128 characters (plus the null-terminator) for the output buffer, or alternatively, call SQLGetInfo() with the SQL_MAX_CATALOG_NAME_LEN, SQL_MAX_OWNER_SCHEMA_LEN, SQL_MAX_TABLE_NAME_LEN, and SQL_MAX_COLUMN_NAME_LEN to determine respectively the actual lengths of the TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME columns supported by the connected DBMS.

Although new columns may be added and the names of the existing columns changed in future releases, the position of the current columns will not change.

Table 179. Columns Returned By SQLTables

Column Name Data Type Description
TABLE_CAT VARCHAR(128) The name of the catalog containing TABLE_SCHEM. This column contains a NULL value.
TABLE_SCHEM VARCHAR(128) The name of the schema containing TABLE_NAME.
TABLE_NAME VARCHAR(128) The name of the table, or view, or alias, or synonym.
TABLE_TYPE VARCHAR(128) Identifies the type given by the name in the TABLE_NAME column. It can have the string values 'TABLE', 'VIEW', 'INOPERATIVE VIEW', 'SYSTEM TABLE', 'ALIAS', or 'SYNONYM'.
REMARKS VARCHAR(254) Contains the descriptive information about the table.

Column Name	Data Type	Description
TABLE_CAT	VARCHAR(128)	The name of the catalog containing TABLE_SCHEM. This column contains a NULL value.
TABLE_SCHEM	VARCHAR(128)	The name of the schema containing TABLE_NAME.
TABLE_NAME	VARCHAR(128)	The name of the table, or view, or alias, or synonym.
TABLE_TYPE	VARCHAR(128)	Identifies the type given by the name in the TABLE_NAME column. It can have the string values 'TABLE', 'VIEW', 'INOPERATIVE VIEW', 'SYSTEM TABLE', 'ALIAS', or 'SYNONYM'.
REMARKS	VARCHAR(254)	Contains the descriptive information about the table.

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_STILL_EXECUTING
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 180. SQLTables SQLSTATEs

SQLSTATE Description Explanation
24000 Invalid cursor state. A cursor was already opened on the statement handle.
40003 08S01 Communication link failure. The communication link between the application and data source failed before the function completed.
HY001 Memory allocation failure. DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY008 Operation canceled.
Asynchronous processing was enabled for the StatementHandle. The function was called and before it completed execution, SQLCancel() was called on the StatementHandle. Then the function was called again on the StatementHandle.
The function was called and, before it completed execution, SQLCancel() was called on the StatementHandle from a different thread in a multithread application.
HY009 Invalid argument value. TableName is null.
HY010 Function sequence error.
The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation.
The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation.
An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called.
HY014 No more handles. DB2 CLI was unable to allocate a handle due to internal resources.
HY090 Invalid string or buffer length. The value of one of the name length arguments was less than 0, but not equal to SQL_NTS.
The valid of one of the name length arguments exceeded the maximum value supported for that data source. The maximum supported value can be obtained by calling the SQLGetInfo() function.
HYC00 Driver not capable. DB2 CLI does not support catalog as a qualifier for table name.
HYT00 Timeout expired. The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetConnectAttr().

SQLSTATE	Description	Explanation
24000	Invalid cursor state.	A cursor was already opened on the statement handle.
40003 08S01	Communication link failure.	The communication link between the application and data source failed before the function completed.
HY001	Memory allocation failure.	DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY008	Operation canceled.	Asynchronous processing was enabled for the `StatementHandle`. The function was called and before it completed execution, `SQLCancel()` was called on the `StatementHandle`. Then the function was called again on the `StatementHandle`. The function was called and, before it completed execution, `SQLCancel()` was called on the `StatementHandle` from a different thread in a multithread application.
HY009	Invalid argument value.	`TableName` is null.
HY010	Function sequence error.	The function was called while in a data-at-execute (`SQLParamData()`, `SQLPutData()`) operation. The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation. An asynchronously executing function (not this one) was called for the `StatementHandle` and was still executing when this function was called.
HY014	No more handles.	DB2 CLI was unable to allocate a handle due to internal resources.
HY090	Invalid string or buffer length.	The value of one of the name length arguments was less than 0, but not equal to SQL_NTS. The valid of one of the name length arguments exceeded the maximum value supported for that data source. The maximum supported value can be obtained by calling the `SQLGetInfo()` function.
HYC00	Driver not capable.	DB2 CLI does not support `catalog` as a qualifier for table name.
HYT00	Timeout expired.	The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for `SQLSetConnectAttr()`.

Restrictions

None.

CLI Sample tbread.c

Also, refer to Querying Environment Information Example.

(The complete sample tbread.c is also available here .)

 
/* From the CLI sample TBREAD.C */
/* ... */
 
    /* call SQLTables */ 
    printf("\n    Call SQLTables.\n");
    sqlrc = SQLTables( hstmt, NULL, 0,
                       tbSchemaPattern, SQL_NTS,
                       tbNamePattern, SQL_NTS,
                       NULL, 0); 
    STMT_HANDLE_CHECK( hstmt, sqlrc);

References

[ Top of Page | Previous Page | Next Page ]