Call Level Interface Guide and Reference

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.

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 138. 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 listed in Columns Returned By SQLPrimaryKeys, ordered by TABLE_CAT, TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.

Since 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 to be consistent with SQL92 limits. Since DB2 names are less than 128, the application can choose to always set aside 128 characters (plus the null-terminator) for the output buffer, or alternatively, 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 respectively the actual lengths of the TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME columns supported by the connected DBMS.

Although new columns may 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))
This is always null.

Column 2  TABLE_SCHEM (VARCHAR(128))
The name of the schema containing TABLE_NAME.

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

Column 6  PK_NAME (VARCHAR(128))
Primary key identifier. NULL if not applicable to the data source.

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

Diagnostics


Table 139. 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.
HY008 Operation canceled.

Asynchronous processing was enabled for the StatementHandle. The function was called and before it completed execution, SQLCancel() was called on the StatementHandle. Then the function was called again on the StatementHandle.

The function was called and, before it completed execution, SQLCancel() was called on the StatementHandle from a different thread in a multithread application.

HY010 Function sequence error. The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation.

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

HY014 No more handles. DB2 CLI was unable to allocate a handle due to internal resources.
HY090 Invalid string or buffer length. The value of one of the name length arguments was less than 0, but not equal SQL_NTS.
HYC00 Driver not capable. DB2 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. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetConnectAttr().

Restrictions

None.

CLI Sample tbconstr.c

(The complete sample tbconstr.c is also available here .)

 
/* From the CLI sample tbconstr.c */
/* ... */
 
    /* call SQLPrimaryKeys */
    printf("\n    Call SQLPrimaryKeys for the table %s.%s\n", 
            tbSchema, tbName); 
    sqlrc = SQLPrimaryKeys(hstmt, NULL, 0,
                        tbSchema, SQL_NTS, tbName, SQL_NTS);
    STMT_HANDLE_CHECK( hstmt, sqlrc ) ;
    
 

References


[ Top of Page | Previous Page | Next Page ]