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.
Syntax
SQLRETURN SQLColAttribute (SQLHSTMT StatementHandle,
SQLSMALLINT ColumnNumber,
SQLSMALLINT FieldIdentifier,
SQLPOINTER CharacterAttributePtr,
SQLSMALLINT BufferLength,
SQLSMALLINT *StringLengthPtr,
SQLPOINTER NumericAttributePtr);
Function 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. |
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 | INTEGER | 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 |
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
SQLSTATE | Description | Explanation |
---|---|---|
01004 | Data truncated | The requested information is returned as a null-terminated string and its length exceeded the length of the application buffer as specified in cbInfoValueMax. The argument pcbInfoValue contains the actual (not truncated) length of the requested information. |
07009 | Column number that is not valid | The value specified for the argument ColumnNumber is less than 1. |
HY009 | Argument value that is not valid | The value specified for the argument FieldIdentifier is
not equal to a value specified in Table 1. The argument CharacterAttributePtr, StringLengthPtr, or NumericAttributePtr is a null pointer. |
HY010 | Function sequence error | The function is called before calling SQLPrepare() or SQLExecDirect() for
the StatementHandle. |
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 ColumnNumber is not recognized by Db2 for i CLI. |