SQLPrimaryKeys function (CLI) - Get primary key columns of a table
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.
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
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.
- 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.
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
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);