SQLColAttributes - Obtain column attributes

SQLColAttributes - Obtain column attributes

SQLColAttributes() obtains an attribute for a column of the result set, and is also used to determine the number of columns. SQLColAttributes() is a more extensible alternative to the SQLDescribeCol() function.
Either SQLPrepare() or SQLExecDirect() must be called before calling this function.
This function (or SQLDescribeCol()) must be called before SQLBindCol(), if the application does not know the various attributes (such as data type and length) of the column.

Syntax
SQLRETURN SQLColAttributes (SQLHSTMT       hstmt,
                            SQLSMALLINT    icol,
                            SQLSMALLINT    fDescType,
                            SQLCHAR        *rgbDesc,
                            SQLINTEGER     cbDescMax,
                            SQLINTEGER     *pcbDesc,
                            SQLINTEGER     *pfDesc);
Function arguments

Table 1. SQLColAttributes arguments
Data type Argument Use Description

SQLHSTMT hstmt Input Statement handle.
SQLSMALLINT icol Input Column number in result set (must be between 1 and the number of columns in the results set inclusive). This argument is ignored when SQL_DESC_COUNT is specified.
SQLSMALLINT fDescType Input Supported values are described in Table 2.
SQLCHAR * rgbDesc Output Pointer to buffer for string column attributes.
SQLINTEGER cbDescMax Input Length of descriptor buffer (rgbDesc)
SQLINTEGER * pcbDesc Output
Actual number of bytes in the descriptor to return. If this argument contains a value equal to or higher than the length rgbDesc buffer, truncation has occurred. The descriptor is then truncated to cbDescMax - 1 bytes.
SQLINTEGER * pfDesc Output Pointer to integer which holds information regarding numeric column attributes.

Table 2. fDescType descriptor types
Descriptor Type Description

SQL_DESC_AUTO_INCREMENT INTEGER This is SQL_TRUE if the column can be incremented automatically upon insertion of a new row to the table. SQL_FALSE if the column cannot be incremented automatically.
SQL_DESC_BASE_COLUMN CHAR(128) The name of the actual column in the underlying table over which this column is built.
For this attribute to be retrieved, the attribute SQL_ATTR_EXTENDED_COL_INFO must have been set to SQL_TRUE for either the statement handle or the connection handle.
SQL_DESC_BASE_SCHEMA CHAR(128) The schema name of the underlying table over which this column is built.
For this attribute to be retrieved, the attribute SQL_ATTR_EXTENDED_COL_INFO must have been set to SQL_TRUE for either the statement handle or the connection handle.
SQL_DESC_BASE_TABLE CHAR(128) The name of the underlying table over which this column is built.
For this attribute to be retrieved, the attribute SQL_ATTR_EXTENDED_COL_INFO must have been set to SQL_TRUE for either the statement handle or the connection handle.
SQL_DESC_COUNT SMALLINT The number of columns in the result set is returned in pfDesc.

SQL_DESC_DISPLAY_SIZE

SMALLINT

The maximum number of bytes needed to display the data in character form is returned in pfDesc.
SQL_DESC_LABEL CHAR(128) The label for this column, if one exists. Otherwise, a zero-length string.
For this attribute to be retrieved, the attribute SQL_ATTR_EXTENDED_COL_INFO must have been set to SQL_TRUE for either the statement handle or the connection handle.
SQL_DESC_LENGTH INTEGER The number of bytes of data associated with the column is returned in pfDesc.
If the column identified in icol is character based, for example, SQL_CHAR, SQL_VARCHAR, or SQL_LONG_VARCHAR, the actual length or maximum length is returned.
If the column type is SQL_DECIMAL or SQL_NUMERIC, SQL_DESC_LENGTH is (precision * 256) + scale. This is returned so that the same value can be passed as input on SQLBindCol(). The precision and scale can also be obtained as separate values for these data types by using SQL_DESC_PRECISION and SQL_DESC_SCALE.
SQL_DESC_NAME CHAR(128) The name of the column icol is returned in rgbDesc. If the column is an expression, then the result returned is product specific.
SQL_DESC_NULLABLE SMALLINT If the column identified by icol can contain nulls, then SQL_NULLABLE is returned in pfDesc.
If the column is constrained not to accept nulls, then SQL_NO_NULLS is returned in pfDesc.
SQL_DESC_PRECISION SMALLINT The precision attribute of the column is returned.
SQL_DESC_SCALE SMALLINT The scale attribute of the column is returned.
SQL_DESC_SEARCHABLE INTEGER This is SQL_UNSEARCHABLE if the column cannot be used in a WHERE clause.
This is SQL_LIKE_ONLY if the column can be used in a WHERE clause only with the LIKE predicate.
This is SQL_ALL_EXCEPT_LIKE if the column can be used in a WHERE clause with all comparison operators except LIKE.
This is SQL_SEARCHABLE if the column can be used in a WHERE clause with any comparison operator.
For this attribute to be retrieved, the attribute SQL_ATTR_EXTENDED_COL_INFO must have been set to SQL_TRUE for either the statement handle or the connection handle.
SQL_DESC_TYPE_NAME CHAR(128) The character representation of the SQL data type of the column identified in icol. This is returned in rgbDesc. The possible values for the SQL data type are listed inTable 1. In addition, user-defined type (UDT) information is also returned. The format for the UDT is <schema name qualifier><job's current separator><UDT name>.
SQL_DESC_TYPE SMALLINT The SQL data type of the column identified in icol is returned in pfDesc. The possible values for pfSqlType are listed in Table 1.
SQL_DESC_UNNAMED SMALLINT This is SQL_NAMED if the NAME field is an actual name, or SQL_UNNAMED if the NAME field is an implementation-generated name.
SQL_DESC_UPDATABLE INTEGER Column is described by the values for the defined constants:
SQL_ATTR_READONLY
SQL_ATTR_WRITE
SQL_ATTR_READWRITE_UNKNOWN
SQL_COLUMN_UPDATABLE describes the updatability of the column in the result set. Whether a column can be updated can be based on the data type, user privileges, and the definition of the result set itself. If it is unclear whether a column can be updated, SQL_ATTR_READWRITE_UNKNOWN should be returned.
For this attribute to be retrieved, the attribute SQL_ATTR_EXTENDED_COL_INFO must have been set to SQL_TRUE for either the statement handle or the connection handle.

Usage

Instead of returning a specific set of arguments like SQLDescribeCol(), SQLColAttributes() can be used to specify which attribute you want to receive for a specific column. If the required information is a string, it is returned in rgbDesc. If the required information is a number, it is returned in pfDesc.
Although SQLColAttributes() allows for future extensions, it requires more calls to receive the same information than SQLDescribeCol() for each column.
If a fDescType descriptor type does not apply to the database server, an empty string is returned in rgbDesc or zero is returned in pfDesc, depending on the expected result of the descriptor.
Columns are identified by a number (numbered sequentially from left to right starting with 1) and can be described in any order.
Calling SQLColAttributes() with fDescType set to SQL_DESC_COUNT is an alternative to calling SQLNumResultCols() to determine whether any columns can be returned.
Call SQLNumResultCols() before calling SQLColAttributes() to determine whether a result set exists.

Return codes

SQL_SUCCESS

SQL_SUCCESS_WITH_INFO

SQL_ERROR

SQL_INVALID_HANDLE

SQL_NO_DATA_FOUND

Diagnostics

Table 3. SQLColAttributes SQLSTATEs
SQLSTATE Description Explanation

07009 Column number that is not valid The value specified for the argument icol is less than 1.
HY009 Argument value that is not valid The value specified for the argument fDescType is not equal to a value specified in Table 2.
The argument rgbDesc, pcbDesc, or pfDesc is a null pointer.
HY010 Function sequence error The function is called before calling SQLPrepare() or SQLExecDirect() for the hstmt.
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 SQL data type returned by the database server for column icol is not recognized by DB2^® UDB CLI.

References

SQLBindCol - Bind a column to an application variable

SQLDescribeCol - Describe column attributes

SQLExecDirect - Execute a statement directly

SQLExecute - Execute a statement

SQLPrepare - Prepare a statement

Parent topic:
DB2 UDB CLI functions

Data type	Argument	Use	Description
SQLHSTMT	hstmt	Input	Statement handle.
SQLSMALLINT	icol	Input	Column number in result set (must be between 1 and the number of columns in the results set inclusive). This argument is ignored when SQL_DESC_COUNT is specified.
SQLSMALLINT	fDescType	Input	Supported values are described in Table 2.
SQLCHAR *	rgbDesc	Output	Pointer to buffer for string column attributes.
SQLINTEGER	cbDescMax	Input	Length of descriptor buffer (rgbDesc)
SQLINTEGER *	pcbDesc	Output	Actual number of bytes in the descriptor to return. If this argument contains a value equal to or higher than the length rgbDesc buffer, truncation has occurred. The descriptor is then truncated to cbDescMax - 1 bytes.
SQLINTEGER *	pfDesc	Output	Pointer to integer which holds information regarding numeric column attributes.

Descriptor	Type	Description
SQL_DESC_AUTO_INCREMENT	INTEGER	This is SQL_TRUE if the column can be incremented automatically upon insertion of a new row to the table. SQL_FALSE if the column cannot be incremented automatically.
SQL_DESC_BASE_COLUMN	CHAR(128)	The name of the actual column in the underlying table over which this column is built. For this attribute to be retrieved, the attribute SQL_ATTR_EXTENDED_COL_INFO must have been set to SQL_TRUE for either the statement handle or the connection handle.
SQL_DESC_BASE_SCHEMA	CHAR(128)	The schema name of the underlying table over which this column is built. For this attribute to be retrieved, the attribute SQL_ATTR_EXTENDED_COL_INFO must have been set to SQL_TRUE for either the statement handle or the connection handle.
SQL_DESC_BASE_TABLE	CHAR(128)	The name of the underlying table over which this column is built. For this attribute to be retrieved, the attribute SQL_ATTR_EXTENDED_COL_INFO must have been set to SQL_TRUE for either the statement handle or the connection handle.
SQL_DESC_COUNT	SMALLINT	The number of columns in the result set is returned in pfDesc.
SQL_DESC_DISPLAY_SIZE	SMALLINT	The maximum number of bytes needed to display the data in character form is returned in pfDesc.
SQL_DESC_LABEL	CHAR(128)	The label for this column, if one exists. Otherwise, a zero-length string. For this attribute to be retrieved, the attribute SQL_ATTR_EXTENDED_COL_INFO must have been set to SQL_TRUE for either the statement handle or the connection handle.
SQL_DESC_LENGTH	INTEGER	The number of bytes of data associated with the column is returned in pfDesc. If the column identified in icol is character based, for example, SQL_CHAR, SQL_VARCHAR, or SQL_LONG_VARCHAR, the actual length or maximum length is returned. If the column type is SQL_DECIMAL or SQL_NUMERIC, SQL_DESC_LENGTH is (precision 256) + scale*. This is returned so that the same value can be passed as input on `SQLBindCol()`. The precision and scale can also be obtained as separate values for these data types by using SQL_DESC_PRECISION and SQL_DESC_SCALE.
SQL_DESC_NAME	CHAR(128)	The name of the column icol is returned in rgbDesc. If the column is an expression, then the result returned is product specific.
SQL_DESC_NULLABLE	SMALLINT	If the column identified by icol can contain nulls, then SQL_NULLABLE is returned in pfDesc. If the column is constrained not to accept nulls, then SQL_NO_NULLS is returned in pfDesc.
SQL_DESC_PRECISION	SMALLINT	The precision attribute of the column is returned.
SQL_DESC_SCALE	SMALLINT	The scale attribute of the column is returned.
SQL_DESC_SEARCHABLE	INTEGER	This is SQL_UNSEARCHABLE if the column cannot be used in a WHERE clause. This is SQL_LIKE_ONLY if the column can be used in a WHERE clause only with the LIKE predicate. This is SQL_ALL_EXCEPT_LIKE if the column can be used in a WHERE clause with all comparison operators except LIKE. This is SQL_SEARCHABLE if the column can be used in a WHERE clause with any comparison operator. For this attribute to be retrieved, the attribute SQL_ATTR_EXTENDED_COL_INFO must have been set to SQL_TRUE for either the statement handle or the connection handle.
SQL_DESC_TYPE_NAME	CHAR(128)	The character representation of the SQL data type of the column identified in icol. This is returned in rgbDesc. The possible values for the SQL data type are listed inTable 1. In addition, user-defined type (UDT) information is also returned. The format for the UDT is <schema name qualifier><job's current separator><UDT name>.
SQL_DESC_TYPE	SMALLINT	The SQL data type of the column identified in icol is returned in pfDesc. The possible values for pfSqlType are listed in Table 1.
SQL_DESC_UNNAMED	SMALLINT	This is SQL_NAMED if the NAME field is an actual name, or SQL_UNNAMED if the NAME field is an implementation-generated name.
SQL_DESC_UPDATABLE	INTEGER	Column is described by the values for the defined constants: SQL_ATTR_READONLY SQL_ATTR_WRITE SQL_ATTR_READWRITE_UNKNOWN SQL_COLUMN_UPDATABLE describes the updatability of the column in the result set. Whether a column can be updated can be based on the data type, user privileges, and the definition of the result set itself. If it is unclear whether a column can be updated, SQL_ATTR_READWRITE_UNKNOWN should be returned. For this attribute to be retrieved, the attribute SQL_ATTR_EXTENDED_COL_INFO must have been set to SQL_TRUE for either the statement handle or the connection handle.

SQLSTATE	Description	Explanation
07009	Column number that is not valid	The value specified for the argument icol is less than 1.
HY009	Argument value that is not valid	The value specified for the argument fDescType is not equal to a value specified in Table 2. The argument rgbDesc, pcbDesc, or pfDesc is a null pointer.
HY010	Function sequence error	The function is called before calling `SQLPrepare()` or `SQLExecDirect()` for the hstmt.
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 SQL data type returned by the database server for column icol is not recognized by DB2^® UDB CLI.