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.

Unicode (UTF-16) equivalent: This function can also be used with the Unicode (UTF-16) character set. The corresponding Unicode function is SQLPrimaryKeysW(). Refer to Unicode in DB2 for iCLI for more information about Unicode support for DB2 CLI.

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 KEY_SEQ	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.
Note: The column names used by DB2 for i 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_ERROR
SQL_INVALID_HANDLE

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 for i 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 for i 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 for i CLI does not support catalog as a qualifier for table name.
HYT00	Timeout expired

Restrictions

None.