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. CatalogName, NameLength1, SchemaName, and NameLength2 are ignored. Columns 1, 2, and 6 of the returned result set are always a zero length string.

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 76. SQLPrimaryKeys arguments

Data type Argument Use Description
SQLHSTMT StatementHandle input Statement handle.
SQLCHAR* CatalogName input Catalog qualifier of a three-part table name.

This field is ignored by DB2 Everyplace.

SQLSMALLINT NameLength1 input Length of CatalogName. This field is ignored by DB2 Everyplace.
SQLCHAR* SchemaName input Schema qualifier of table name. This field is ignored by DB2 Everyplace.
SQLSMALLINT NameLength2 input Length of SchemaName. This field is ignored by DB2 Everyplace.
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 table name.

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

Calls to SQLPrimaryKeys() in many cases map to complex and, thus, expensive queries against the system catalog.

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

The result set contains these columns, ordered by TABLE_NAME, and ORDINAL_POSITION

Column 1 TABLE_CAT (VARCHAR(128))
This is always a zero-length string.

Column 2 TABLE_SCHEM (VARCHAR(128))
This is always a zero-length string.

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

Column 6 PK_NAME (VARCHAR(128))
This is always a zero-length string.

The column names used by DB2 CLI/ODBC follow the X/Open CLI CAE specification style.

Return codes

Diagnostics

Table 77. SQLPrimaryKey SQLSTATEs

SQLSTATE Description Explanation
24000 Invalid cursor state. A cursor is 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.
HY010 Function sequence error. The function is called while in a data-at-execute (SQLPrepare() or SQLExecDirect()) operation.
HY014 No more handles. DB2 CLI is unable to allocate a handle due to internal resources.
HY090 Invalid string or buffer length. The value of one of the name length arguments is less than 0, but not equal SQL_NTS.

Restrictions

Use calls to SQLPrimaryKeys() sparingly, and save the results rather than repeating calls.

Related reference