Call Level Interface Guide and Reference

SQLSpecialColumns - Get Special (Row Identifier) Columns

Purpose

Specification: DB2 CLI 2.1 ODBC 1.0

SQLSpecialColumns() returns unique row identifier information (primary key or unique index) 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   SQLSpecialColumns(
                SQLHSTMT          StatementHandle,   /* hstmt */
                SQLUSMALLINT      IdentifierType,    /* fColType */
                SQLCHAR      FAR  *CatalogName,      /* szCatalogName */
                SQLSMALLINT       NameLength1,       /* cbCatalogName */
                SQLCHAR      FAR  *SchemaName,       /* szSchemaName */
                SQLSMALLINT       NameLength2,       /* cbSchemaName */
                SQLCHAR      FAR  *TableName,        /* szTableName */
                SQLSMALLINT       NameLength3,       /* cbTableName */
                SQLUSMALLINT      Scope,             /* fScope */
                SQLUSMALLINT      Nullable);         /* fNullable */

Function Arguments

Table 172. SQLSpecialColumns Arguments

Data Type Argument Use Description
SQLHSTMT StatementHandle Input Statement handle
SQLUSMALLINT IdentifierType Input Type of unique row identifier to return. Only the following type is supported:

SQL_BEST_ROWID
Returns the optimal set of column(s) which can uniquely identify any row in the specified table.

Note: For compatibility with ODBC applications, SQL_ROWVER is also recognized, but not supported; therefore, if SQL_ROWVER is specified, an empty result will be returned.

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. This must be a set to 0.
SQLCHAR * SchemaName Input Schema qualifier of the specified table.
SQLSMALLINT NameLength2 Input Length of SchemaName.
SQLCHAR * TableName Input Table name.
SQLSMALLINT NameLength3 Input Length of NameLength3.
SQLUSMALLINT Scope Input Minimum required duration for which the unique row identifier will be valid.
Scope must be one of the following:

SQL_SCOPE_CURROW: The row identifier is guaranteed to be valid only while positioned on that row. A later re-select using the same row identifier values may not return a row if the row was updated or deleted by another transaction.
SQL_SCOPE_TRANSACTION: The row identifier is guaranteed to be valid for the duration of the current transaction.
SQL_SCOPE_SESSION: The row identifier is guaranteed to be valid for the duration of the connection.

The duration over which a row identifier value is guaranteed to be valid depends on the current transaction isolation level. For information and scenarios involving isolation levels, refer to the IBM DB2 SQL Reference.
SQLUSMALLINT Nullable Input Determines whether to return special columns that can have a NULL value.
Must be one of the following:

SQL_NO_NULLS - The row identifier column set returned cannot have any NULL values.
SQL_NULLABLE - The row identifier column set returned may include columns where NULL values are permitted.

Usage

If multiple ways exist to uniquely identify any row in a table (i.e. if there are multiple unique indexes on the specified table), then DB2 CLI will return the best set of row identifier column set based on its internal criterion.

If there is no column set which allow any row in the table to be uniquely identified, an empty result set is returned.

The unique row identifier information is returned in the form of a result set where each column of the row identifier is represented by one row in the result set. Columns Returned By SQLSpecialColumns shows the order of the columns in the result set returned by SQLSpecialColumns(), sorted by SCOPE.

Since calls to SQLSpecialColumns() 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_COLUMN_NAME_LEN to determine the actual length of the COLUMN_NAME column supported by the connected DBMS.

Although new columns may be added and the names of the columns changed in future releases, the position of the current columns will not change.

Columns Returned By SQLSpecialColumns

Column 1 SCOPE (SMALLINT)

The duration for which the name in COLUMN_NAME is guaranteed to point to the same row. Valid values are the same as for the Scope argument: Actual scope of the row identifier. Contains one of the following values:

SQL_SCOPE_CURROW
SQL_SCOPE_TRANSACTION
SQL_SCOPE_SESSION

Refer to Scope in Table 172 for a description of each value.

Column 2 COLUMN_NAME (VARCHAR(128) not NULL)

Name of the column that is (or part of) the table's primary key.

Column 3 DATA_TYPE (SMALLINT not NULL)

SQL data type of the column. One of the values in the Symbolic SQL Data Type column in Table 2.

Column 4 TYPE_NAME (VARCHAR(128) not NULL)

DBMS character string represented of the name associated with DATA_TYPE column value.

Column 5 COLUMN_SIZE (INTEGER)

If the DATA_TYPE column value denotes a character or binary string, then this column contains the maximum length in bytes; if it is a graphic (DBCS) string, this is the number of double byte characters for the parameter.

For date, time, timestamp data types, this is the total number of bytes required to display the value when converted to character.

For numeric data types, this is either the total number of digits, or the total number of bits allowed in the column, depending on the value in the NUM_PREC_RADIX column in the result set.

See also Table 196.

Column 7 DECIMAL_DIGITS (SMALLINT)

The scale of the column. NULL is returned for data types where scale is not applicable. See also Table 195.

Column 8 PSEUDO_COLUMN (SMALLINT)

Indicates whether or not the column is a pseudo-column DB2 Call Level Interface will only return:

SQL_PC_NOT_PSEUDO

DB2 DBMSs do not support pseudo columns. ODBC applications may receive the following values from other non-IBM RDBMS servers:

SQL_PC_UNKNOWN
SQL_PC_PSEUDO

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_STILL_EXECUTING
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 173. SQLSpecialColumns 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.
HY009 Invalid argument value. TableName is null.
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.
An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called.
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 length arguments was less than 0, but not equal to SQL_NTS.
The value of one of the length arguments exceeded the maximum length supported by the DBMS for that qualifier or name.
HY097 Column type out of range. An invalid IdentifierType value was specified.
HY098 Scope type out of range. An invalid Scope value was specified.
HY099 Nullable type out of range. An invalid Nullable values was specified.
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().

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.
HY009	Invalid argument value.	`TableName` is null.
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. An asynchronously executing function (not this one) was called for the `StatementHandle` and was still executing when this function was called.
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 length arguments was less than 0, but not equal to SQL_NTS. The value of one of the length arguments exceeded the maximum length supported by the DBMS for that qualifier or name.
HY097	Column type out of range.	An invalid `IdentifierType` value was specified.
HY098	Scope type out of range.	An invalid `Scope` value was specified.
HY099	Nullable type out of range.	An invalid `Nullable` values was specified.
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 SQLSpecialColumns */ 
    printf("\n    Call SQLSpecialColumns for the table %s.%s\n",
           tbSchema, tbName); 
    sqlrc = SQLSpecialColumns(hstmt, SQL_BEST_ROWID, NULL, 0,
                              tbSchema, SQL_NTS, tbName, SQL_NTS,
                              SQL_SCOPE_CURROW, SQL_NULLABLE);

References

[ Top of Page | Previous Page | Next Page ]