Call Level Interface Guide and Reference

SQLPrimaryKeys - Get Primary Key Columns of A Table

Purpose

Specification: DB2 CLI 2.1 ODBC 1.0

SQLPrimaryKeys() returns a list of column names that comprise the primary key for a table. The information is returned in an SQL result set, which can be retrieved using the same functions that are used to process a result set generated by a query.

Syntax

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

Function Arguments

Table 138. SQLPrimaryKeys Arguments

Data Type Argument Use Description
SQLHSTMT StatementHandle input Statement handle.
SQLCHAR * CatalogName input Catalog qualifier of a 3 part table name.
This must be a NULL pointer or a zero length string.
SQLSMALLINT NameLength1 input Length of CatalogName
SQLCHAR * SchemaName input Schema qualifier of table name.
SQLSMALLINT NameLength2 input Length of SchemaName
SQLCHAR * TableName input Table name.
SQLSMALLINT NameLength3 input Length of TableName

Data Type	Argument	Use	Description
SQLHSTMT	StatementHandle	input	Statement handle.
SQLCHAR *	CatalogName	input	Catalog qualifier of a 3 part table name. This must be a NULL pointer or a zero length string.
SQLSMALLINT	NameLength1	input	Length of `CatalogName`
SQLCHAR *	SchemaName	input	Schema qualifier of table name.
SQLSMALLINT	NameLength2	input	Length of `SchemaName`
SQLCHAR *	TableName	input	Table name.
SQLSMALLINT	NameLength3	input	Length of `TableName`

Usage

SQLPrimaryKeys() returns the primary key columns from a single table, Search patterns cannot be used to specify the schema qualifier or the table name.

The result set contains the columns listed in Columns Returned By SQLPrimaryKeys, ordered by TABLE_CAT, TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.

Since calls to SQLPrimaryKeys() 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_SCHEMA_NAME_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.

Columns Returned By SQLPrimaryKeys

Column 1 TABLE_CAT (VARCHAR(128)): This is always null.
Column 2 TABLE_SCHEM (VARCHAR(128)): The name of the schema containing TABLE_NAME.
Column 3 TABLE_NAME (VARCHAR(128) not NULL): Name of the specified table.
Column 4 COLUMN_NAME (VARCHAR(128) not NULL): Primary Key column name.
Column 5 ORDINAL_POSITION (SMALLINT not NULL): Column sequence number in the primary key, starting with 1.
Column 6 PK_NAME (VARCHAR(128)): Primary key identifier. NULL if not applicable to the data source.

Note: The column names used by DB2 CLI follow the X/Open CLI CAE specification style. The column types, contents and order are identical to those defined for the SQLPrimaryKeys() result set in ODBC.

If the specified table does not contain a primary key, an empty result set is returned.

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_STILL_EXECUTING
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 139. SQLPrimaryKeys 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.
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.
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 SQL_NTS.
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.
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.
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 SQL_NTS.
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 tbconstr.c

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

 
/* From the CLI sample tbconstr.c */
/* ... */
 
    /* call SQLPrimaryKeys */
    printf("\n    Call SQLPrimaryKeys for the table %s.%s\n", 
            tbSchema, tbName); 
    sqlrc = SQLPrimaryKeys(hstmt, NULL, 0,
                        tbSchema, SQL_NTS, tbName, SQL_NTS);
    STMT_HANDLE_CHECK( hstmt, sqlrc ) ;

References

[ Top of Page | Previous Page | Next Page ]