`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. This result set can be retrieved by using the same functions that process a result set that is generated by a query.

ODBC specifications for `SQLPrimaryKeys()`

Table 1. `SQLPrimaryKeys()` specifications
ODBC specification level	In X/Open CLI CAE specification?	In ISO CLI specification?
1.0	No	No

Syntax

SQLRETURN   SQLPrimaryKeys   (SQLHSTMT          hstmt,
                              SQLCHAR      FAR  *szCatalogName,
                              SQLSMALLINT       cbCatalogName,
                              SQLCHAR      FAR  *szSchemaName,
                              SQLSMALLINT       cbSchemaName,
                              SQLCHAR      FAR  *szTableName,
                              SQLSMALLINT       cbTableName);

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. `SQLPrimaryKeys()` arguments
Data type	Argument	Use	Description
SQLHSTMT	hstmt	input	Statement handle.
SQLCHAR *	szCatalogName	input	Catalog qualifier of a three-part table name. This must be a null pointer or a zero length string.
SQLSMALLINT	cbCatalogName	input	The length, in bytes, of szCatalogName.
SQLCHAR *	szSchemaName	input	Schema qualifier of table name.
SQLSMALLINT	cbSchemaName	input	The length, in bytes, of szSchemaName.
SQLCHAR *	szTableName	input	Table name.
SQLSMALLINT	cbTableName	input	The length, in bytes, of szTableName.

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 listed in Table 3, 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.

The VARCHAR columns of the catalog functions result set have been declared with a maximum length attribute of 128 bytes to be consistent with ANSI/ISO SQL standard of 1992 limits. Because DB2® names are less than 128, you can always choose to set aside 128 characters (plus the nul-terminator) for the output buffer. Alternatively, you can call SQLGetInfo() with the InfoType argument set to each of the following values:

SQL_MAX_CATALOG_NAME_LEN, to determine the length of TABLE_CAT columns that the connected database management system supports
SQL_MAX_SCHEMA_NAME_LEN, to determine the length of TABLE_SCHEM columns that the connected database management system supports
SQL_MAX_TABLE_NAME_LEN, to determine the length of TABLE_NAME columns that the connected database management system supports
SQL_MAX_COLUMN_NAME_LEN, to determine the length of COLUMN_NAME columns that the connected database management system supports

Although new columns might be added and the names of the existing columns changed in future releases, the position of the current columns does not change. The following table lists each column in the result set this function generates.

Table 3. Columns returned by `SQLPrimaryKeys()`
Column number	Column name	Data type	Description
1	TABLE_CAT	VARCHAR(128)	This is always null.
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. Contains a null value if not applicable to the data source.

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

After you call SQLPrimaryKeys(), it returns one of the following values:

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 4. `SQLPrimaryKeys()` SQLSTATEs
SQLSTATE	Description	Explanation
24000	Invalid cursor state.	A cursor is already open on the statement handle.
40003 or 08S01	Communication link failure.	The communication link between the application and data source fails before the function completes.
HY001	Memory allocation failure.	DB2 ODBC is not able to allocate the required memory to support the execution or the completion of the function.
HY010	Function sequence error.	The function is called during a data-at-execute operation. (That is, the function is called during a procedure that uses the `SQLParamData()` or `SQLPutData()` functions.)
HY014	No more handles.	DB2 ODBC is not able to allocate a handle due to low 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.
HYC00	Driver not capable.	DB2 ODBC does not support catalog as a qualifier for table name.

Example

The following example shows an application that uses SQLPrimaryKeys() to locate a primary key for a table, and calls SQLColAttribute() to find the data type of the key.

Figure 1. An application that locates a table's primary key

/* ... */
#include <sqlcli1.h>
void main()
{
   SQLCHAR      rgbDesc_20];
   SQLCHAR      szTableName_20];
   SQLCHAR      szSchemaName_20];
   SQLCHAR      rgbValue_20];
   SQLINTEGER   pcbValue;
   SQLHENV      henv;
   SQLHDBC      hdbc;
   SQLHSTMT     hstmt;
   SQLSMALLINT  pscDesc;
   SQLINTEGER   pdDesc;
   SQLRETURN    rc;
   /*******************************************************************/
   /*   Initialization...                                             */
   /*******************************************************************/
   if( SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv)!= SQL_SUCCESS )
   {
       fprintf( stdout, "Error in SQLAllocHandle\n" );
       exit(1);
   }
   if( SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc)!= SQL_SUCCESS )
   {
       fprintf( stdout, "Error in SQLAllocHandle\n" );
       exit(1);
   }
   if( SQLConnect( hdbc,
                   NULL, SQL_NTS,
                   NULL, SQL_NTS,
                   NULL, SQL_NTS ) != SQL_SUCCESS )
   {
       fprintf( stdout, "Error in SQLConnect\n" );
       exit(1);
   }
   if( SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt)!= SQL_SUCCESS )
   {
       fprintf( stdout, "Error in SQLAllocHandle\n" );
       exit(1);
   }
 
   /*******************************************************************/
   /*  Get primary key for table 'myTable' by using SQLPrimaryKeys    */
   /*******************************************************************/
   rc = SQLPrimaryKeys( hstmt,
                        NULL, SQL_NTS,
                        (SQLCHAR*)szSchemaName, SQL_NTS,
                        (SQLCHAR*)szTableName, SQL_NTS );
   if( rc != SQL_SUCCESS )
   {
       goto exit;
   }
   /*
    *   Because all we need is the ordinal position, we'll bind column 5 from
    *   the result set.
    */
   rc = SQLBindCol( hstmt,
                    5,
                    SQL_C_CHAR,
                    (SQLPOINTER)rgbValue,
                    20,
                    &pcbValue );
   if( rc != SQL_SUCCESS )
   {
       goto exit;
   }
   /*
    *   Fetch data...
    */
   if( SQLFetch( hstmt ) != SQL_SUCCESS )
   {
        goto exit;
   }
   /*******************************************************************/
   /*  Get data type for that column by calling SQLColAttribute().    */
   /*******************************************************************/
   rc =  SQLColAttribute(  hstmt,
                           pcbValue,
                           SQL_COLUMN_TYPE,
                           rgbDesc,
                           20,
                           &pcbDesc,
                           &pfDesc );
   if( rc != SQL_SUCCESS )
   {
       goto exit;
   }
   /*
    *   Display the data type.
    */
   fprintf( stdout, "Data type ==> 
exit:
   /*******************************************************************/
   /* Clean up the environment...                                     */
   /*******************************************************************/
   SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK);
   SQLDisconnect( hdbc );
   SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
   SQLFreeHandle(SQL_HANDLE_ENV, henv);
}