SQLPrimaryKeys - Get primary key columns of a table

 

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 that is generated by a query.

 

Syntax

SQLRETURN   SQLPrimaryKeys   (SQLHSTMT          StatementHandle,
                              SQLCHAR           *CatalogName,
                              SQLSMALLINT       NameLength1,
                              SQLCHAR           *SchemaName,
                              SQLSMALLINT       NameLength2,
                              SQLCHAR           *TableName,
                              SQLSMALLINT       NameLength3);

 

Function arguments

Table 1. 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.

 

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 that are listed in Table 2, ordered by TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and ORDINAL_POSITION.

Because 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.

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

Table 2. Columns returned by SQLPrimaryKeys
Column number/name Data type Description
1 TABLE_CAT VARCHAR (128) The current server.
2 TABLE_SCHEM VARCHAR (128) The name of the schema containing TABLE_NAME.
3 TABLE_NAME VARCHAR (128) not NULL Name of the specified table.
4 COLUMN_NAME VARCHAR (128) not NULL Primary Key column name.
5 ORDINAL_POSITION SMALLINT not NULL Column sequence number in the primary key, starting with 1.
6 PK_NAME VARCHAR(128) Primary key identifier. NULL if not applicable to the data source.

The column names used by DB2® UDB 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

 

Error conditions

Table 3. SQLPrimaryKeys SQLSTATEs
SQLSTATE Description Explanation
24000 Cursor state that is not valid A cursor is already opened on the statement handle.
40003 08S01 Communication link failure The communication link between the application and data source fails before the function is completed.
HY001 Memory allocation failure DB2 UDB CLI is unable to allocate memory required to support the processing or completion of the function.
HY008 Operation canceled  
HY010 Function sequence error The function is called while in a data-at-processing (SQLParamData(), SQLPutData()) operation.
HY014 No more handles DB2 UDB CLI is unable to allocate a handle due to internal resources.
HY021 Internal descriptor that is not valid The internal descriptor cannot be addressed or allocated, or it contains a value that is not valid .
HY090 String or buffer length that is not valid The value of one of the name length arguments is less than 0, but not equal to SQL_NTS.
HYC00 Driver not capable DB2 UDB CLI does not support catalog as a qualifier for table name.
HYT00 Timeout expired  

 

Restrictions

None.

 

References

 

Parent topic:

DB2 UDB CLI functions