SQLColAttribute - Return a column attribute

SQLColAttribute() obtains an attribute for a column of the result set, and is also used to determine the number of columns. SQLColAttribute() 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.

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

Syntax

SQLRETURN SQLColAttribute  (SQLHSTMT       StatementHandle,
                            SQLSMALLINT    ColumnNumber,
                            SQLSMALLINT    FieldIdentifier,
                            SQLPOINTER     CharacterAttributePtr,
                            SQLSMALLINT    BufferLength,
                            SQLSMALLINT    *StringLengthPtr,
                            SQLPOINTER     NumericAttributePtr);

Function arguments

Table 1. SQLColAttribute arguments
Data type	Argument	Use	Description
SQLHSTMT	StatementHandle	Input	Statement handle.
SQLSMALLINT	ColumnNumber	Input	The number of the record in the IRD from which the field value is to be retrieved. This argument corresponds to the column number of result data, ordered sequentially from left to right, starting at 1. Columns can be described in any order. Column 0 can be specified in this argument, but all values except SQL_DESC_TYPE and SQL_DESC_OCTET_LENGTH will return undefined values.
SQLSMALLINT	FieldIdentifier	Input	The field in row ColumnNumber of the IRD that is to be returned Table 2.
SQLPOINTER	CharacterAttributePtr	Output	Pointer to a buffer in which to return the value in the FieldIdentifier field of the ColumnNumber row of the IRD, if the field is a character string. Otherwise, the field is unused.
SQLSMALLINT	BufferLength	Input	Number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) needed to store the *CharacterAttributePtr buffer, if the field is a character string. Otherwise, the field is ignored.
SQLSMALLINT *	StringLengthPtr	Output	Pointer to a buffer in which to return the total number of bytes (excluding the byte count of the null termination character for character data) available to return in CharacterAttributePtr. For character data, if the number of bytes available to return is greater than or equal to BufferLength, the descriptor information in CharacterAttributePtr is truncated to BufferLength minus the length of a null termination character and is null-terminated by DB2 CLI. For all other types of data, the value of BufferLength is ignored and DB2 CLI assumes the size of *CharacterAttributePtr is 32 bits.
SQLPOINTER	NumericAttributePtr	Output	Pointer to a buffer in which to return the value in the FieldIdentifier field of the ColumnNumber row of the IRD, if the field is a numeric descriptor type, such as SQL_DESC_COLUMN_LENGTH. Otherwise, the field is unused.

Table 2. Field Identifier 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_COLUMN_CCSID	INTEGER	The CCSID of the column identified in ColumnNumber is returned in NumericAttributePtr. This is the CCSID of the result set column data as it is known to the database before the column is bound out to the application, and may not contain the CCSID of the data returned for the column to the application. For instance, for a result set column consisting simply of a base table's column, this field will contain the CCSID of the column - the same CCSID value as shown in the CCSID column of the SYSCOLUMNS view. On the other hand, the CCSID for a derived result set column, such as one that contains an expression, will be set based on the expression and the job environment in which the statement is run. For data types where the CCSID is not applicable, a value of 0 is returned.
SQL_DESC_COUNT	SMALLINT	The number of columns in the result set is returned in NumericAttributePtr.
SQL_DESC_DISPLAY_SIZE	SMALLINT	The maximum number of bytes needed to display the data in character form is returned in NumericAttributePtr.
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 NumericAttributePtr. If the column identified in ColumnNumber 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 ColumnNumber is returned in CharacterAttributePtr. If the column is an expression, then the result returned is product specific.
SQL_DESC_NULLABLE	SMALLINT	If the column identified by ColumnNumber can contain nulls, then SQL_NULLABLE is returned in NumericAttributePtr. If the column is constrained not to accept nulls, then SQL_NO_NULLS is returned in NumericAttributePtr.
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 ColumnNumber. This is returned in CharacterAttributePtr. 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 ColumnNumber is returned in NumericAttributePtr. 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(), SQLColAttribute() 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 CharacterAttributePtr. If the required information is a number, it is returned in NumericAttributePtr.

Although SQLColAttribute() allows for future extensions, it requires more calls to receive the same information than SQLDescribeCol() for each column.

If a FieldIdentifier descriptor type does not apply to the database server, an empty string is returned in CharacterAttributePtr or zero is returned in NumericAttributePtr, 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 SQLColAttribute() with FieldIdentifier set to SQL_DESC_COUNT is an alternative to calling SQLNumResultCols() to determine whether any columns can be returned.

Call SQLNumResultCols() before calling SQLColAttribute() 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