Call Level Interface Guide and Reference

SQLColAttribute - Return a Column Attribute

Purpose

Specification: DB2 CLI 5.0 ODBC 3.0 ISO CLI

SQLColAttribute() returns descriptor information for a column in a result set. Descriptor information is returned as a character string, a 32-bit descriptor-dependent value, or an integer value.

Syntax

SQLRETURN   SQLColAttribute  (
                SQLHSTMT          StatementHandle,       /* hstmt */
                SQLSMALLINT       ColumnNumber,          /* icol */
                SQLSMALLINT       FieldIdentifier,       /* fDescType */
                SQLPOINTER        CharacterAttributePtr, /* rgbDesc */
                SQLSMALLINT       BufferLength,          /* cbDescMax */
                SQLSMALLINT       *StringLengthPtr,      /* pcbDesc */
                SQLPOINTER        NumericAttributePtr);  /* pfDesc */

Function Arguments

Table 33. SQLColAttribute Arguments

Data Type Argument Use Description
SQLHSTMT StatementHandle input Statement handle.
SQLUSMALLINT 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 may 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 (see Table 34).
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.
SQLINTEGER BufferLength input The length of the *CharacterAttributePtr buffer, if the field is a character string. Otherwise, this field is ignored.
SQLSMALLINT *StringLengthPtr output
Pointer to a buffer in which to return the total number of bytes (excluding the null termination byte 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 an integer 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.

Data Type	Argument	Use	Description
SQLHSTMT	StatementHandle	input	Statement handle.
SQLUSMALLINT	`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 may 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 (see Table 34).
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.
SQLINTEGER	BufferLength	input	The length of the *`CharacterAttributePtr` buffer, if the field is a character string. Otherwise, this field is ignored.
SQLSMALLINT	*StringLengthPtr	output	Pointer to a buffer in which to return the total number of bytes (excluding the null termination byte 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 an integer 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.

Usage

SQLColAttribute() returns information either in *NumericAttributePtr or in *CharacterAttributePtr. Integer information is returned in *NumericAttributePtr as a 32-bit, signed value; all other formats of information are returned in *CharacterAttributePtr. When information is returned in *NumericAttributePtr, DB2 CLI ignores CharacterAttributePtr, BufferLength, and StringLengthPtr When information is returned in *CharacterAttributePtr, DB2 CLI ignores NumericAttributePtr.

SQLColAttribute() returns values from the descriptor fields of the IRD. The function is called with a statement handle rather than a descriptor handle. The values returned by SQLColAttribute() for the FieldIdentifier values listed below can also be retrieved by calling SQLGetDescField() with the appropriate IRD handle.

The currently defined descriptor types, the version of DB2 CLI in which they were introduced (perhaps with different name), and the arguments in which information is returned for them are shown below; it is expected that more descriptor types will be defined to take advantage of different data sources.

DB2 CLI must return a value for each of the descriptor types. If a descriptor type does not apply to a data source, then, unless otherwise stated, DB2 CLI returns 0 in *StringLengthPtr or an empty string in *CharacterAttributePtr.

The following table lists the descriptor types returned by SQLColAttribute().

Table 34. SQLColAttribute Arguments

FieldIdentifier Information returned in Description
SQL_COLUMN_AUTO_INCREMENT Numeric AttributePtr Changed to SQL_DESC_AUTO_UNIQUE_VALUE. ^a
SQL_COLUMN_CASE_SENSITIVE Numeric AttributePtr Changed to SQL_DESC_CASE_SENSITIVE. ^a
SQL_COLUMN_CATALOG_NAME Character AttributePtr Changed to SQL_DESC_CATALOG_NAME. ^a
SQL_COLUMN_COUNT Numeric AttributePtr Changed to SQL_DESC_COUNT. ^a
SQL_COLUMN_DISPLAY_SIZE Numeric AttributePtr Changed to SQL_DESC_DISPLAY_SIZE. ^a
SQL_COLUMN_LABEL Character AttributePtr Changed to SQL_DESC_LABEL. ^a
SQL_COLUMN_DISTINCT_TYPE Character AttributePtr Changed to SQL_DESC_DISTINCT_TYPE. ^a
SQL_COLUMN_LENGTH Numeric AttributePtr Changed to SQL_DESC_OCTET_LENGTH. ^a
SQL_COLUMN_MONEY Numeric AttributePtr Changed to SQL_DESC_FIXED_PREC_SCALE. ^a
SQL_COLUMN_NAME Character AttributePtr Changed to SQL_DESC_NAME. ^a
SQL_COLUMN_NULLABLE Numeric AttributePtr Changed to SQL_DESC_NULLABLE. ^a
SQL_COLUMN_OWNER_NAME Character AttributePtr Changed to SQL_DESC_SCHEMA_NAME. ^a
SQL_COLUMN_PRECISION Numeric AttributePtr Changed to SQL_DESC_PRECISION. ^a
SQL_COLUMN_QUALIFIER_NAME Character AttributePtr Changed to SQL_DESC_CATALOG_NAME. ^a
SQL_COLUMN_SCALE Numeric AttributePtr Changed to SQL_DESC_SCALE. ^a
SQL_COLUMN_SEARCHABLE Numeric AttributePtr Changed to SQL_DESC_SEARCHABLE. ^a
SQL_COLUMN_TABLE_NAME Character AttributePtr Changed to SQL_DESC_TABLE_NAME. ^a
SQL_COLUMN_TYPE Numeric AttributePtr Changed to SQL_DESC_TYPE. ^a
SQL_COLUMN_TYPE_NAME Character AttributePtr Changed to SQL_DESC_TYPE_NAME. ^a
SQL_COLUMN_UNSIGNED Numeric AttributePtr Changed to SQL_DESC_UNSIGNED. ^a
SQL_COLUMN_UPDATABLE Numeric AttributePtr Changed to SQL_DESC_UPDATABLE. ^a
SQL_DESC_AUTO_UNIQUE_VALUE (DB2 CLI v2) Numeric AttributePtr Indicates if the column data type is an auto increment data type.
SQL_FALSE is returned in NumericAttributePtr for all DB2 SQL data types.
SQL_DESC_BASE_COLUMN_NAME (DB2 CLI v5) Character AttributePtr
The base column name for the set column. If a base column name does not exist (as in the case of columns that are expressions), then this variable contains an empty string.
This information is returned from the SQL_DESC_BASE_COLUMN_NAME record field of the IRD, which is a read-only field.
SQL_DESC_BASE_TABLE_NAME (DB2 CLI v5) Character AttributePtr The name of the base table that contains the column. If the base table name cannot be defined or is not applicable, then this variable contains an empty string.
SQL_DESC_CASE_SENSITIVE (DB2 CLI v2) Numeric AttributePtr Indicates if the column data type is a case sensitive data type.
Either SQL_TRUE or SQL_FALSE will be returned in NumericAttributePtr depending on the data type.
Case sensitivity does not apply to graphic data types, SQL_FALSE is returned.
SQL_FALSE is returned for non-character data types.
SQL_DESC_CATALOG_NAME (DB2 CLI v2) Character AttributePtr The catalog of the table that contains the column is returned in CharacterAttributePtr. An empty string is returned since DB2 CLI only supports two part naming for a table.
SQL_DESC_CONCISE_TYPE (DB2 CLI v5) Character AttributePtr
The concise data type.
For the datetime data types, this field returns the concise data type, e.g., SQL_TYPE_TIME.
This information is returned from the SQL_DESC_CONCISE_TYPE record field of the IRD.
SQL_DESC_COUNT (DB2 CLI v2) Numeric AttributePtr The number of columns in the result set is returned in NumericAttributePtr.
SQL_DESC_DISPLAY_SIZE (DB2 CLI v2) Numeric AttributePtr The maximum number of bytes needed to display the data in character form is returned in NumericAttributePtr.
Refer to Table 197 for the display size of each of the column types.
SQL_DESC_DISTINCT_TYPE (DB2 CLI v2) Character AttributePtr The user defined distinct type name of the column is returned in CharacterAttributePtr. If the column is a built-in SQL type and not a user defined distinct type, an empty string is returned.
Note: This is an IBM defined extension to the list of descriptor attributes defined by ODBC.

SQL_DESC_FIXED_PREC_SCALE (DB2 CLI v2) Numeric AttributePtr
SQL_TRUE if the column has a fixed precision and non-zero scale that are data-source-specific.
SQL_FALSE if the column does not have a fixed precision and non-zero scale that are data-source-specific.
SQL_FALSE is returned in NumericAttributePtr for all DB2 SQL data types.
SQL_DESC_LABEL (DB2 CLI v2) Character AttributePtr The column label is returned in CharacterAttributePtr. If the column does not have a label, the column name or the column expression is returned. If the column is unlabeled and unnamed, an empty string is returned.
SQL_DESC_LENGTH (DB2 CLI v2) Numeric AttributePtr
A numeric value that is either the maximum or actual character length of a character string or binary data type. It is the maximum character length for a fixed-length data type, or the actual character length for a variable-length data type. Its value always excludes the null termination byte that ends the character string.
This information is returned from the SQL_DESC_LENGTH record field of the IRD.
SQL_DESC_LITERAL_PREFIX (DB2 CLI v5) Character AttributePtr
This VARCHAR(128) record field contains the character or characters that DB2 CLI recognizes as a prefix for a literal of this data type. This field contains an empty string for a data type for which a literal prefix is not applicable.
SQL_DESC_LITERAL_SUFFIX (DB2 CLI v5) Character AttributePtr This VARCHAR(128) record field contains the character or characters that DB2 CLI recognizes as a suffix for a literal of this data type. This field contains an empty string for a data type for which a literal suffix is not applicable.
SQL_DESC_LOCAL_TYPE_NAME (DB2 CLI v5) Character AttributePtr This VARCHAR(128) record field contains any localized (native language) name for the data type that may be different from the regular name of the data type. If there is no localized name, then an empty string is returned. This field is for display purposes only. The character set of the string is locale-dependent and is typically the default character set of the server.
SQL_DESC_NAME (DB2 CLI v2) Character AttributePtr The name of the column ColumnNumber is returned in CharacterAttributePtr. If the column is an expression, then the column number is returned.
In either case, SQL_DESC_UNNAMED is set to SQL_NAMED. If there is no column name or a column alias, an empty string is returned and SQL_DESC_UNNAMED is set to SQL_UNNAMED.
This information is returned from the SQL_DESC_NAME record field of the IRD.
The column name value can be affected by the environment attribute SQL_ATTR_USE_LIGHT_OUTPUT_SQLDA. See SQLSetEnvAttr - Set Environment Attribute for more information.
SQL_DESC_NULLABLE (DB2 CLI v2) Numeric AttributePtr 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.
This information is returned from the SQL_DESC_NULLABLE record field of the IRD.
SQL_DESC_NUM_PREX_RADIX (DB2 CLI v5) Numeric AttributePtr

If the datatype in the SQL_DESC_TYPE field is an approximate data type, this SQLINTEGER field contains a value of 2 because the SQL_DESC_PRECISION field contains the number of bits.
If the datatype in the SQL_DESC_TYPE field is an exact numeric data type, this field contains a value of 10 because the SQL_DESC_PRECISION field contains the number of decimal digits.
This field is set to 0 for all non-numeric data types.

SQL_DESC_OCTET_LENGTH (DB2 CLI v2) Numeric AttributePtr The number of bytes of data associated with the column is returned in NumericAttributePtr. This is the length in bytes of data transferred on the fetch or SQLGetData() for this column if SQL_C_DEFAULT is specified as the C data type. Refer to Table 196 for the length of each of the SQL data types.
If the column identified in ColumnNumber is a fixed length character or binary string, (for example, SQL_CHAR or SQL_BINARY) the actual length is returned.
If the column identified in ColumnNumber is a variable length character or binary string, (for example, SQL_VARCHAR or SQL_BLOB) the maximum length is returned.
SQL_DESC_PRECISION (DB2 CLI v2) Numeric AttributePtr The precision in units of digits is returned in NumericAttributePtr if the column is SQL_DECIMAL, SQL_NUMERIC, SQL_DOUBLE, SQL_FLOAT, SQL_INTEGER, SQL_REAL or SQL_SMALLINT.
If the column is a character SQL data type, then the precision returned in NumericAttributePtr, indicates the maximum number of characters the column can hold.
If the column is a graphic SQL data type, then the precision returned in NumericAttributePtr, indicates the maximum number of double-byte characters the column can hold.
Refer to Table 194 for the precision of each of the SQL data types.
This information is returned from the SQL_DESC_PRECISION record field of the IRD.
SQL_DESC_SCALE (DB2 CLI v2) Numeric AttributePtr The scale attribute of the column is returned. Refer to Table 195 for the scale of each of the SQL data types.
This information is returned from the SCALE record field of the IRD.
SQL_DESC_SCHEMA_NAME (DB2 CLI v2) Character AttributePtr The schema of the table that contains the column is returned in CharacterAttributePtr. An empty string is returned as DB2 CLI is unable to determine this attribute.
SQL_DESC_SEARCHABLE (DB2 CLI v2) Numeric AttributePtr Indicates if the column data type is searchable:

SQL_PRED_NONE (SQL_UNSEARCHABLE in DB2 CLI v2) if the column cannot be used in a WHERE clause.
SQL_PRED_CHAR (SQL_LIKE_ONLY in DB2 CLI v2) if the column can be used in a WHERE clause only with the LIKE predicate.
SQL_PRED_BASIC (SQL_ALL_EXCEPT_LIKE in DB2 CLI v2) if the column can be used in a WHERE clause with all comparison operators except LIKE.
SQL_SEARCHABLE if the column can be used in a WHERE clause with any comparison operator.

SQL_DESC_TABLE_NAME (DB2 CLI v2) Character AttributePtr The name of the table that contains the column is returned in CharacterAttributePtr. An empty string is returned as DB2 CLI cannot determine this attribute.
SQL_DESC_TYPE (DB2 CLI v2) Numeric AttributePtr The SQL data type of the column identified in ColumnNumber is returned in NumericAttributePtr. The possible values returned are listed in Table 2.
When ColumnNumber is equal to 0, SQL_BINARY is returned for variable-length bookmarks, and SQL_INTEGER is returned for fixed-length bookmarks.
For the datetime data types, this field returns the verbose data type, i.e., SQL_DATETIME.
This information is returned from the SQL_DESC_TYPE record field of the IRD.
SQL_DESC_TYPE_NAME (DB2 CLI v2) Character AttributePtr The type of the column (as entered in an SQL statement) is returned in CharacterAttributePtr.
For information on each data type refer to the TYPE_NAME attribute found in Data Types and Data Conversion.
SQL_DESC_UNNAMED (DB2 CLI v5) Numeric AttributePtr
SQL_NAMED or SQL_UNNAMED. If the SQL_DESC_NAME field of the IRD contains a column alias, or a column name, SQL_NAMED is returned. If there is no column name or a column alias, SQL_UNNAMED is returned.
This information is returned from the SQL_DESC_UNNAMED record field of the IRD.
SQL_DESC_UNSIGNED (DB2 CLI v2) Numeric AttributePtr Indicates if the column data type is an unsigned type or not.
SQL_TRUE is returned in NumericAttributePtr for all non-numeric data types, SQL_FALSE is returned for all numeric data types.
SQL_DESC_UPDATABLE (DB2 CLI v2) Numeric AttributePtr Indicates if the column data type is an updateable data type:

SQL_ATTR_READWRITE_UNKNOWN is returned in NumericAttributePtr for all DB2 SQL data types.
SQL_ATTR_READONLY is returned if the column is obtained from a catalog function call.

Although DB2 CLI does not retrun them, ODBC also defines the following values:

SQL_DESC_UPDATABLE
SQL_UPDT_WRITE

Note:

^a
In DB2 Version 5, the version 2 FieldIdentifier values have been changed. Although the old version 2 FieldIdentifier is still supported in this version of DB2, we recommend that you begin using the new FieldIdentifier.

This function is an extensible alternative to SQLDescribeCol(). SQLDescribeCol() returns a fixed set of descriptor information based on ANSI-89 SQL. SQLColAttribute() allows access to the more extensive set of descriptor information available in ANSI SQL-92 and DBMS vendor extensions.

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_STILL_EXECUTING
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 35. SQLColAttribute SQLSTATEs

SQLSTATE Description Explanation
01000 Warning. Informational message. (Function returns SQL_SUCCESS_WITH_INFO.)
01004 Data truncated. The buffer *CharacterAttributePtr was not large enough to return the entire string value, so the string value was truncated. The length of the untruncated string value is returned in *StringLengthPtr. (Function returns SQL_SUCCESS_WITH_INFO.)
07005 The statement did not return a result set. The statement associated with the StatementHandle did not return a result set. There were no columns to describe.
07009 Invalid descriptor index. The value specified for ColumnNumber was equal to 0, and the SQL_ATTR_USE_BOOKMARKS statement attribute was SQL_UB_OFF. The value specified for ColumnNumber was less than 0. The value specified for the argument ColumnNumber was greater than the number of columns in the result set.
HY000 General error. An error occurred for which there was no specific SQLSTATE. The error message returned by SQLGetDiagRec() in the *MessageText buffer describes the error and its cause.
HY001 Memory allocation failure. DB2 CLI was unable to allocate memory required to support execution or completion of the function.
HY008 Operation was cancelled
Asynchronous processing was enabled for the StatementHandle. The function was called and before it completed execution, SQLCancel() was called on the StatementHandle. Then the function was called again on the StatementHandle.
The function was called and, before it completed execution, SQLCancel() was called on the StatementHandle from a different thread in a multithread application.
HY010 Function sequence error. The function was called prior to calling SQLPrepare() or SQLExecDirect() for the StatementHandle.
An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called.
SQLExecute() or SQLExecDirect() was called for the StatementHandle and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns.
HY090 Invalid string or buffer length. The value specified for the argument BufferLength was less than 0.
HY091 Descriptor type out of range. The value specified for the argument FieldIdentifier was not one of the defined values, and was not an implementation-defined value.
HYC00 Driver not capable. The value specified for the argument FieldIdentifier was not supported by DB2 CLI.

SQLSTATE	Description	Explanation
01000	Warning.	Informational message. (Function returns SQL_SUCCESS_WITH_INFO.)
01004	Data truncated.	The buffer `CharacterAttributePtr` was not large enough to return the entire string value, so the string value was truncated. The length of the untruncated string value is returned in `StringLengthPtr`. (Function returns SQL_SUCCESS_WITH_INFO.)
07005	The statement did not return a result set.	The statement associated with the StatementHandle did not return a result set. There were no columns to describe.
07009	Invalid descriptor index.	The value specified for `ColumnNumber` was equal to 0, and the SQL_ATTR_USE_BOOKMARKS statement attribute was SQL_UB_OFF. The value specified for `ColumnNumber` was less than 0. The value specified for the argument `ColumnNumber` was greater than the number of columns in the result set.
HY000	General error.	An error occurred for which there was no specific SQLSTATE. The error message returned by `SQLGetDiagRec()` in the `*MessageText` buffer describes the error and its cause.
HY001	Memory allocation failure.	DB2 CLI was unable to allocate memory required to support execution or completion of the function.
HY008	Operation was cancelled	Asynchronous processing was enabled for the `StatementHandle`. The function was called and before it completed execution, `SQLCancel()` was called on the `StatementHandle`. Then the function was called again on the `StatementHandle`. The function was called and, before it completed execution, `SQLCancel()` was called on the `StatementHandle` from a different thread in a multithread application.
HY010	Function sequence error.	The function was called prior to calling `SQLPrepare()` or `SQLExecDirect()` for the `StatementHandle.` An asynchronously executing function (not this one) was called for the `StatementHandle` and was still executing when this function was called. `SQLExecute()` or `SQLExecDirect()` was called for the `StatementHandle` and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns.
HY090	Invalid string or buffer length.	The value specified for the argument `BufferLength` was less than 0.
HY091	Descriptor type out of range.	The value specified for the argument `FieldIdentifier` was not one of the defined values, and was not an implementation-defined value.
HYC00	Driver not capable.	The value specified for the argument `FieldIdentifier` was not supported by DB2 CLI.

SQLColAttribute() can return any SQLSTATE that can be returned by SQLPrepare() or SQLExecute() when called after SQLPrepare() and before SQLExecute() depending on when the data source evaluates the SQL statement associated with the StatementHandle.

For performance reasons, an application should not call SQLColAttribute() before executing a statement.

Restrictions

None.

CLI Sample utilcli.c

(The complete sample utilcli.c is also available here .)

 
/* From the CLI sample utilcli.c */
/* ... */
 
        /* get display size for column */
        sqlrc = SQLColAttribute( hstmt,
                         ( SQLSMALLINT ) ( i + 1 ),
                         SQL_DESC_DISPLAY_SIZE,
                         NULL,
                         0,
                         NULL,
                         &colDataDisplaySize ) ;
        STMT_HANDLE_CHECK( hstmt, sqlrc);

References

[ Top of Page | Previous Page | Next Page ]