SQLSpecialColumns function (CLI) - Get special (row identifier) columns

Returns unique row identifier information (for example, the primary key or unique index) 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:

Db2® Call Level Interface 2.1
ODBC 1.0

Unicode equivalent: You can also use this function with the Unicode character set. The corresponding Unicode function is SQLSpecialColumnsW(). Refer to Unicode functions (CLI) for information about ANSI to Unicode function mappings.

Syntax

SQLRETURN   SQLSpecialColumns(
               SQLHSTMT          StatementHandle,   /* hstmt */
               SQLUSMALLINT      IdentifierType,    /* fColType */
               SQLCHAR           *CatalogName,      /* szCatalogName */
               SQLSMALLINT       NameLength1,       /* cbCatalogName */
               SQLCHAR           *SchemaName,       /* szSchemaName */
               SQLSMALLINT       NameLength2,       /* cbSchemaName */
               SQLCHAR           *TableName,        /* szTableName */
               SQLSMALLINT       NameLength3,       /* cbTableName */
               SQLUSMALLINT      Scope,             /* fScope */
               SQLUSMALLINT      Nullable);         /* fNullable */

Function arguments

Table 1. SQLSpecialColumns arguments
Data type	Argument	Use	Description
SQLHSTMT	StatementHandle	Input	Statement handle
SQLUSMALLINT	IdentifierType	Input	Type of unique row identifier to return. Only the listed types are supported: SQL_BEST_ROWID Returns the optimal set of columns, 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 is returned.
SQLCHAR *	CatalogName	Input	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 support 3-part naming.
SQLSMALLINT	NameLength1	Input	Number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) needed to store CatalogName, or SQL_NTS if CatalogName is null-terminated.
SQLCHAR *	SchemaName	Input	Schema qualifier of the specified table.
SQLSMALLINT	NameLength2	Input	Number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) needed to store SchemaName, or SQL_NTS if SchemaName is null-terminated.
SQLCHAR *	TableName	Input	Table name.
SQLSMALLINT	NameLength3	Input	Number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) needed to store TableName, or SQL_NTS if TableName is null-terminated.
SQLUSMALLINT	Scope	Input	Minimum required duration for which the unique row identifier is valid. Scope must be one of the SQL_SCOPE_CURROW, SQL_SCOPE_TRANSACTION, or SQL_SCOPE_SESSION: 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 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.
SQLUSMALLINT	Nullable	Input	Determines whether to return special columns that can have a NULL value. Must be SQL_NO_NULL or SQL_NULLABLE: SQL_NO_NULLS - The row identifier column set returned cannot have any NULL values. SQL_NULLABLE - The row identifier column set returned might 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 Call Level Interface returns the best set of row identifier column set based on its internal criterion.

If the schema qualifier argument associated with a table name is not specified, then the schema name defaults to the one currently in effect for the current connection.

If there is no column set which 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. Columns returned by SQLSpecialColumns 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.

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 might be added and the names of the columns changed in future releases, the position of the current columns will not change.

Note: The Informix® database server has a virtual column named ROWID for every non-fragmented table. The SQLSpecialColumns() function returns information about the ROWID column when accessing an Informix database server.

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 listed values:

SQL_SCOPE_CURROW
SQL_SCOPE_TRANSACTION
SQL_SCOPE_SESSION

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

Column 2 COLUMN_NAME (VARCHAR(128) not NULL)

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

Column 3 DATA_TYPE (SMALLINT not NULL)

SQL data type of the column.

Column 4 TYPE_NAME (VARCHAR(128) not NULL)

DBMS character string representation 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, and timestamp data types, this is the total number of SQLCHAR or SQLWCHAR elements required to display the value when converted to character.

For the columns that are defined with the CODEUNITS32 unit, the number of code units for the column is returned.

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.

Refer to the table of data type precision.

Column 6 BUFFER_LENGTH (INTEGER)

The maximum number of bytes for the associated C buffer to store data from the specified column if SQL_C_DEFAULT were specified on the SQLBindCol(), SQLGetData(), and SQLBindParameter() calls. The BUFFER_LENGTH value does not include any null-terminator. For exact numeric data types, the length accounts for the decimal and the sign.

Refer to the table of data type length.

Column 7 DECIMAL_DIGITS (SMALLINT)

The scale of the column. NULL is returned for data types where scale is not applicable. Refer to the table of data type scale.

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 might receive the listed values from RDBMS servers that are not from IBM:

SQL_PC_UNKNOWN
SQL_PC_PSEUDO

Return codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_STILL_EXECUTING
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 2. 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. 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.
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. The function was called before a statement was prepared on the statement handle.
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 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.
HYT00	Timeout expired.	The timeout period expired before the data source returned the result set. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for `SQLSetStmtAttr()`. Note: This SQLSTATE applies only to .Net applications.

Restrictions

None.

Example

The following code example gets column information for a table.

  /* get special columns */
  cliRC = SQLSpecialColumns(hstmt,
                            SQL_BEST_ROWID,
                            NULL,
                            0,
                            tbSchema,
                            SQL_NTS,
                            tbName,
                            SQL_NTS,
                            SQL_SCOPE_CURROW,
                            SQL_NULLABLE);