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);