SQLPrimaryKeys function (CLI) - Get primary key columns of a table

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

Specification:

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

Unicode equivalent: You can also use this function with the Unicode character set. The corresponding Unicode function is SQLPrimaryKeysW(). For information about ANSI to Unicode function mappings, seeUnicode functions (CLI).

Syntax

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

Function arguments

Table 1. SQLPrimaryKeys arguments
Data type Argument Use Description
SQLHSTMT StatementHandle Input The statement handle.
SQLCHAR * CatalogName Input A catalog qualifier of a 3-part table name. If the target DBMS does not support 3-part naming, and CatalogName is not a null pointer and does not point to a zero-length string, then an empty result set and SQL_SUCCESS is returned. Otherwise, this is a valid filter for DBMSs that supports 3-part naming.
SQLSMALLINT NameLength1 Input The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store CatalogName, or SQL_NTS if CatalogName is null-terminated.
SQLCHAR * SchemaName Input The schema qualifier of table name.
SQLSMALLINT NameLength2 Input The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store SchemaName, or SQL_NTS if SchemaName is null-terminated.
SQLCHAR * TableName Input The table name.
SQLSMALLINT NameLength3 Input The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store TableName, or SQL_NTS if TableName is null-terminated.

Usage

The SQLPrimaryKeys() function returns the primary key columns from a single table. You cannot use search patterns to specify any of the arguments.

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

In many cases, calls to the SQLPrimaryKeys() function map to a complex and thus expensive query against the system catalog, so you should use these calls sparingly, and save the results rather than repeating calls.

If the schema name is not provided, the schema name defaults to the name that is in effect for the current connection.

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 the actual lengths of the TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME columns that are supported by the connected DBMS.

You can specify *ALL as a value in the SchemaName to resolve unqualified stored procedure calls or to find libraries in catalog API calls. CLI searches on all existing schemas in the connected database. You are not required to specify *ALL, as this behavior is the default in CLI. Alternatively, you can set the SchemaFilter IBM® Data Server Driver configuration keyword or the Schema List CLI/ODBC configuration keyword to *ALL.

Although new columns might 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))
The primary key table catalog name. The value is NULL if this table does not have catalogs.
Column 2  TABLE_SCHEM (VARCHAR(128))
The name of the schema that contains TABLE_NAME.
Column 3  TABLE_NAME (VARCHAR(128) not NULL)
The name of the specified table.
Column 4  COLUMN_NAME (VARCHAR(128) not NULL)
The primary key column name.
Column 5  KEY_SEQ (SMALLINT not NULL)
The column sequence number in the primary key, starting with 1.
Column 6  PK_NAME (VARCHAR(128))
The primary key identifier. NULL if not applicable to the data source.
Note: The column names that are used by 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_ERROR
  • SQL_INVALID_HANDLE
  • SQL_STILL_EXECUTING
  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO

Diagnostics

Table 2. 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. It is likely that process-level memory has been exhausted for the application process. Consult the operating system configuration for information about process-level memory limitations.
HY008 Operation was Canceled. Asynchronous processing was enabled for StatementHandle. The function was called and before it completed execution, SQLCancel() was called on StatementHandle from a different thread in a multithreaded application. Then the function was called again on StatementHandle.
HY010 Function sequence error. The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation.

The function was called while in a BEGIN COMPOUND and END COMPOUND SQL operation.

HY014 No more handles. Db2 CLI was unable to allocate a handle due to resource limitations.
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.
HYC00 Driver not capable. 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. You can set the timeout period by using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetStmtAttr().
Note: This SQLSTATE applies only to .Net applications.

Restrictions

None.

Example

  /* get the primary key columns of a table */
  cliRC = SQLPrimaryKeys(hstmt, NULL, 0, tbSchema, SQL_NTS, tbName, SQL_NTS);