SQLSpecialColumns() - Get special (row identifier) columns

SQLSpecialColumns() returns unique row identifier information (primary key or unique index) for a table. The information is returned in an SQL result set. You can retrieve this result set with the same functions that process a result set that is generated by a query.

ODBC specifications for SQLSpecialColumns()

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

Syntax

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

Function arguments

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

Table 2. SQLSpecialColumns() arguments
Data type Argument Use Description
SQLHSTMT hstmt input Statement handle.
SQLUSMALLINT fColType input Type of unique row identifier to return. Only the following type is supported:
  • SQL_BEST_ROWID, which returns the optimal set of columns that can uniquely identify any row in the specified table.

Exception: For compatibility with ODBC applications, SQL_ROWVER is also recognized, but not supported; therefore, if SQL_ROWVER is specified, an empty result is returned.
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. This must be a set to 0.
SQLCHAR * szSchemaName input Schema qualifier of the specified table.
SQLSMALLINT cbSchemaName input The length, in bytes, of szSchemaName.
SQLCHAR * szTableName input Table name.
SQLSMALLINT cbTableName input The length, in bytes, of cbTableName.
SQLUSMALLINT fScope input Minimum required duration for which the unique row identifier is valid.
fScope 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 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. This attribute is only valid if SQL_TXN_SERIALIZABLE and SQL_TXN_REPEATABLE_READ isolation attributes are set.
  • SQL_SCOPE_SESSION: The row identifier is guaranteed to be valid for the duration of the connection.

    Important: This attribute is not supported by Db2 for z/OS®.

The duration over which a row identifier value is guaranteed to be valid depends on the current transaction isolation level.
SQLUSMALLINT fNullable 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 can include columns where null values are permitted.

Usage

If multiple ways exist to uniquely identify any row in a table (that is, if the specified table is indexed with multiple unique indexes), Db2 ODBC returns the best set of row identifier column sets based on its internal criterion.

If no column set 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. Table 3 shows the order of the columns in the result set returned by SQLSpecialColumns(), sorted by SCOPE.

Because 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 are 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 bytes, the application can choose to always set aside 128 bytes (plus the nul-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 database management system.

Although new columns might be added and the names of the columns changed in future releases, the position of the current columns does not change. The following table lists these columns.

Table 3. Columns returned by SQLSpecialColumns()
Column number Column name Data type Description
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 fScope argument: Actual scope of the row identifier. Contains one of the following values:
  • SQL_SCOPE_CURROW
  • SQL_SCOPE_TRANSACTION
  • SQL_SCOPE_SESSION
See fScope in Table 2 for a description of each value.
2 COLUMN_NAME VARCHAR(128) NOT NULL Name of the column that is (or part of) the table's primary key.
3 DATA_TYPE SMALLINT NOT NULL SQL data type of the column.
4 TYPE_NAME VARCHAR(128) NOT NULL database management system character string represented of the name associated with DATA_TYPE column value.
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.
6 BUFFER_LENGTH INTEGER The maximum number of bytes for the associated C buffer to store data from this column if SQL_C_DEFAULT is specified on the SQLBindCol(), SQLGetData() and SQLBindParameter() calls. This length does not include any nul-terminator. For exact numeric data types, the length accounts for the decimal and the sign.
7 DECIMAL_DIGITS SMALLINT The scale of the column. NULL is returned for data types where scale is not applicable.
8 PSEUDO_COLUMN SMALLINT Indicates whether the column is a pseudo-column. Db2 ODBC only returns:
  • SQL_PC_NOT_PSEUDO
Db2 database management systems do not support pseudo columns. ODBC applications can receive the following values from other non-IBM® relational database management system servers:
  • SQL_PC_UNKNOWN
  • SQL_PC_PSEUDO

Return codes

After you call SQLSpecialColumns(), 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. SQLSpecialColumns() SQLSTATEs
SQLSTATE Description Explanation
08S01 Communication link failure. The communication link between the application and data source fails before the function completes.
24000 Invalid cursor state. A cursor is opened on the statement handle.
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. This SQLSTATE is returned for one or more of the following reasons:
  • The value of one of the length arguments is less than 0, but not equal to SQL_NTS.
  • The value of one of the length arguments exceeded the maximum length supported by the database management system for that qualifier or name.
HY097 Column type out of range. An invalid fColType value is specified.
HY098 Scope type out of range. An invalid fScope value is specified.
HY099 Nullable type out of range. An invalid fNullable values is specified.
HYC00 Driver not capable. Db2 ODBC does not support catalog as a qualifier for table name.

Example

The following example shows an application that prints a list of columns that uniquely define rows in a table. This application uses SQLSpecialColumns() to find these columns.
Figure 1. An application that prints the column set for a unique index of a table
/* ... */
SQLRETURN
list_index_columns(SQLHDBC hdbc, SQLCHAR *schema, SQLCHAR *tablename )
{
/* ... */
    rc = SQLSpecialColumns(hstmt, SQL_BEST_ROWID, NULL, 0, schema, SQL_NTS,
                    tablename, SQL_NTS, SQL_SCOPE_CURROW, SQL_NULLABLE);
    rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, (SQLPOINTER) column_name.s, 129,
                    &column_name.ind);
    rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER) type_name.s, 129,
                    &type_name.ind);
    rc = SQLBindCol(hstmt, 5, SQL_C_LONG, (SQLPOINTER) & precision,
                    sizeof(precision), &precision_ind);
    rc = SQLBindCol(hstmt, 7, SQL_C_SHORT, (SQLPOINTER) & scale,
                    sizeof(scale), &scale_ind);
    printf("Primary key or unique index for 
    /* Fetch each row, and display */
    while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS) {
            printf("%-14.14s %-14.14s \n", deptname.s, location.s);
        }
        if (precision_ind != SQL_NULL_DATA) {
            printf(" (%ld", precision);
        } else {
            printf("(\n");
        }
        if (scale_ind != SQL_NULL_DATA) {
            printf(", %d)\n", scale); 
        } else {
            printf(")\n");
        }
    }
/* ... */