SQLSpecialColumns - Get special (row identifier) columns

SQLSpecialColumns() returns unique row identifier information (primary key or unique index) for a table. For example, unique index or primary key information. The information is returned in an SQL result set, which can be retrieved using the same functions that are used to fetch a result set generated by a SELECT statement.

Unicode (UTF-16) equivalent: This function can also be used with the Unicode (UTF-16) character set. The corresponding Unicode function is SQLSpecialColumnsW(). Refer to Unicode in DB2 for iCLI for more information about Unicode support for DB2 CLI.

Syntax

SQLRETURN SQLSpecialColumns  (SQLHSTMT       hstmt,
                              SQLSMALLINT    fColType,
                              SQLCHAR        *szCatalogName,
                              SQLSMALLINT    cbCatalogName,
                              SQLCHAR        *szSchemaName,
                              SQLSMALLINT    cbSchemaName,
                              SQLCHAR        *szTableName,
                              SQLSMALLINT    cbTableName,
                              SQLSMALLINT    fScope,
                              SQLSMALLINT    fNullable);

Function arguments

Table 1. SQLSpecialColumns arguments
Data type Argument Use Description
SQLHSTMT hstmt Input Statement handle.
SQLSMALLINT fColType Input Reserved for future use to support additional types of special columns.

This data type is currently ignored.
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 Length of szCatalogName. This must be a set to 0.
SQLCHAR * szSchemaName Input Schema qualifier of the specified table.
SQLSMALLINT cbSchemaName Input Length of szSchemaName.
SQLCHAR * szTableName Input Table name.
SQLSMALLINT cbTableName Input Length of cbTableName.
SQLSMALLINT fScope Input Minimum required duration for which the unique row identifier is valid.
fScope must be one of the following values:
  • SQL_SCOPE_CURROW - The row identifier is guaranteed to be valid only while positioned on that row. A later reselect using the same row identifier values might not return a row if the row is 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.
SQLSMALLINT fNullable Input This determines whether to return special columns that can have a NULL value.
Must be one of the following values:
  • SQL_NO_NULLS

    The row identifier column set returned cannot have any NULL values.

  • SQL_NULLABLE

    The row identifier column set returned can include columns where NULL values are permitted.

Usage

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

If there is no column set that allows 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. The result set returned by SQLSpecialColumns() has the following columns in the following order:
Table 2. Columns returned by SQLSpecialColumns
Column number/name Data type Description
1 SCOPE SMALLINT not NULL Actual scope of the rowid. This contains one of the following values:
  • SQL_SCOPE_CURROW
  • SQL_SCOPE_TRANSACTION
  • SQL_SCOPE_SESSION

Refer to fScope in Table 1 for a description of each value.
2 COLUMN_NAME VARCHAR(128) not NULL Name of the row identifier column.
3 DATA_TYPE SMALLINT not NULL SQL data type of the column.
4 TYPE_NAME VARCHAR(128) not NULL Database Management System (DBMS) character string represented of the name associated with DATA_TYPE column value.
5 COLUMN_SIZE INTEGER The precision of the column. NULL is returned for data types where precision is not applicable.
6 BUFFER_LENGTH INTEGER The length, in bytes, of the data returned in the default C type. For CHAR data types, this is the same as the value in the LENGTH_PRECISION column.
7 DECIMAL_DIGITS SMALLINT The scale of the column. NULL is returned for data types where scale is not applicable.
8 PSEUDO_COLUMN SMALLINT This indicates whether the column is a pseudo-column; DB2 for i CLI only returns:
  • SQL_PC_NOT_PSEUDO

Return codes

  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO
  • SQL_ERROR
  • SQL_INVALID_HANDLE

Diagnostics

Table 3. SQLSpecialColumns SQLSTATEs
SQLSTATE Description Explanation
24000 Cursor state that is not valid Cursor related information is requested, but no cursor is open.
40003 * Statement completion unknown The communication link between the CLI and the data source fails before the function completes processing.
HY001 Memory allocation failure The driver is unable to allocate memory required to support the processing or completion of the function.
HY009 Argument length that is not valid The value of one of the length arguments is less than 0, but not equal to SQL_NTS.
HY021 Internal descriptor that is not valid The internal descriptor cannot be addressed or allocated, or it contains a value that is not valid.
HYC00 Driver not capable The data source does not support the catalog portion (first part) of a three-part table name.
Parent topic: DB2 for i CLI functions