Call Level Interface Guide and Reference

SQLDescribeCol - Return a Set of Attributes for a Column

Purpose

Specification: DB2 CLI 1.1 ODBC 1.0 ISO CLI

SQLDescribeCol() returns a set of commonly used descriptor information (column name, type, precision, scale, nullability) for the indicated column in the result set generated by a query.

This information is also available in the fields of the IRD.

If the application needs only one attribute of the descriptor information, or needs an attribute not returned by SQLDescribeCol(), the SQLColAttribute() function can be used in place of SQLDescribeCol(). See SQLColAttribute - Return a Column Attribute for more information.

Either SQLPrepare() or SQLExecDirect() must be called before calling this function.

This function (or SQLColAttribute()) is usually called before a bind column function (SQLBindCol(), SQLBindFileToCol()) to determine the attributes of a column before binding it to an application variable.

Syntax

SQLRETURN   SQLDescribeCol   (
                SQLHSTMT          StatementHandle,   /* hstmt */
                SQLUSMALLINT      ColumnNumber,      /* icol */
                SQLCHAR      *FAR ColumnName,        /* szColName */
                SQLSMALLINT       BufferLength,      /* cbColNameMax */
                SQLSMALLINT  *FAR NameLengthPtr,     /* pcbColName */
                SQLSMALLINT  *FAR DataTypePtr,       /* pfSqlType */
                SQLUINTEGER  *FAR ColumnSizePtr,     /* pcbColDef */
                SQLSMALLINT  *FAR DecimalDigitsPtr,  /* pibScale */
                SQLSMALLINT  *FAR NullablePtr);      /* pfNullable */

Function Arguments

Table 46. SQLDescribeCol Arguments

Data Type Argument Use Description
SQLHSTMT StatementHandle input Statement handle
SQLUSMALLINT ColumnNumber input Column number to be described. Columns are numbered sequentially from left to right, starting at 1. This can also be set to 0 to describe the bookmark column.
SQLCHAR * ColumnName output Pointer to column name buffer. This value is read from the SQL_DESC_NAME field of the IRD. This is set to NULL if the column name cannot be determined.
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.
SQLSMALLINT BufferLength input Size of ColumnName buffer.
SQLSMALLINT * NameLengthPtr output Bytes available to return for ColumnName argument. Truncation of column name (ColumnName) to BufferLength - 1 bytes occurs if NameLengthPtr is greater than or equal to BufferLength.
SQLSMALLINT * DataTypePtr output Base SQL data type of column. To determine if there is a User Defined Type associated with the column, call SQLColAttribute() with fDescType set to SQL_COLUMN_DISTINCT_TYPE. Refer to the Symbolic SQL Data Type column of Table 2 for the data types that are supported.
SQLUINTEGER * ColumnSizePtr output Precision of column as defined in the database.
If fSqlType denotes a graphic or DBCLOB SQL data type, then this variable indicates the maximum number of double-byte characters the column can hold.
SQLSMALLINT * DecimalDigitsPtr output Scale of column as defined in the database (only applies to SQL_DECIMAL, SQL_NUMERIC, SQL_TIMESTAMP). Refer to Table 195 for the scale of each of the SQL data types.
SQLSMALLINT * NullablePtr output Indicates whether NULLS are allowed for this column

SQL_NO_NULLS
SQL_NULLABLE

Data Type	Argument	Use	Description
SQLHSTMT	`StatementHandle`	input	Statement handle
SQLUSMALLINT	`ColumnNumber`	input	Column number to be described. Columns are numbered sequentially from left to right, starting at 1. This can also be set to 0 to describe the bookmark column.
SQLCHAR *	`ColumnName`	output	Pointer to column name buffer. This value is read from the SQL_DESC_NAME field of the IRD. This is set to NULL if the column name cannot be determined. 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.
SQLSMALLINT	`BufferLength`	input	Size of `ColumnName` buffer.
SQLSMALLINT *	`NameLengthPtr`	output	Bytes available to return for `ColumnName` argument. Truncation of column name (`ColumnName`) to `BufferLength - 1` bytes occurs if `NameLengthPtr` is greater than or equal to `BufferLength`.
SQLSMALLINT *	`DataTypePtr`	output	Base SQL data type of column. To determine if there is a User Defined Type associated with the column, call `SQLColAttribute()` with `fDescType` set to SQL_COLUMN_DISTINCT_TYPE. Refer to the Symbolic SQL Data Type column of Table 2 for the data types that are supported.
SQLUINTEGER *	`ColumnSizePtr`	output	Precision of column as defined in the database. If `fSqlType` denotes a graphic or DBCLOB SQL data type, then this variable indicates the maximum number of double-byte `characters` the column can hold.
SQLSMALLINT *	`DecimalDigitsPtr`	output	Scale of column as defined in the database (only applies to SQL_DECIMAL, SQL_NUMERIC, SQL_TIMESTAMP). Refer to Table 195 for the scale of each of the SQL data types.
SQLSMALLINT *	`NullablePtr`	output	Indicates whether NULLS are allowed for this column SQL_NO_NULLS SQL_NULLABLE

Usage

Columns are identified by a number, are numbered sequentially from left to right, and may be described in any order.

Column numbers start at 1 if bookmarks are not used (SQL_ATTR_USE_BOOKMARKS statement attribute set to SQL_UB_OFF).
Column numbers start at 0 if bookmarks are used (the statement attribute set to SQL_UB_ON).

If a null pointer is specified for any of the pointer arguments, DB2 CLI assumes that the information is not needed by the application and nothing is returned.

If the column is a User Defined Type, SQLDescribeCol only returns the built-in type in DataTypePtr. Call SQLColAttribute() with fDescType set to SQL_COLUMN_DISTINCT_TYPE to obtain the User Defined Type.

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_STILL_EXECUTING
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

If SQLDescribeCol() returns either SQL_ERROR, or SQL_SUCCESS_WITH_INFO, one of the following SQLSTATEs may be obtained by calling the SQLError() function.

Table 47. SQLDescribeCol SQLSTATEs

SQLSTATE Description Explanation
01004 Data truncated. The column name returned in the argument ColumnName was longer than the value specified in the argument BufferLength. The argument NameLengthPtr contains the length of the full column name. (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. (Call SQLNumResultCols() first to determine if there are any rows in the result set.)
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.
40003 08S01 Communication link failure. The communication link between the application and data source failed before the function completed.
58004 Unexpected system failure. Unrecoverable system error.
HY001 Memory allocation failure. DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY002 Invalid column number. The value specified for the argument ColumnNumber was less than 1.
The value specified for the argument ColumnNumber was greater than the number of columns in the result set.
HY008 Operation canceled.
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.
HY090 Invalid string or buffer length. The length specified in argument BufferLength less than 1.
HY010 Function sequence error. The function was called prior to calling SQLPrepare() or SQLExecDirect() for the StatementHandle.
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.
HY013 Unexpected memory handling error. DB2 CLI was unable to access memory required to support execution or completion of the function.
HYC00 Driver not capable. The SQL data type of column ColumnNumber is not recognized by DB2 CLI.
HYT00 Timeout expired. The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetConnectAttr().

SQLSTATE	Description	Explanation
01004	Data truncated.	The column name returned in the argument `ColumnName` was longer than the value specified in the argument `BufferLength`. The argument `NameLengthPtr` contains the length of the full column name. (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. (Call `SQLNumResultCols()` first to determine if there are any rows in the result set.)
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.
40003 08S01	Communication link failure.	The communication link between the application and data source failed before the function completed.
58004	Unexpected system failure.	Unrecoverable system error.
HY001	Memory allocation failure.	DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY002	Invalid column number.	The value specified for the argument `ColumnNumber` was less than 1. The value specified for the argument `ColumnNumber` was greater than the number of columns in the result set.
HY008	Operation canceled.	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.
HY090	Invalid string or buffer length.	The length specified in argument `BufferLength` less than 1.
HY010	Function sequence error.	The function was called prior to calling `SQLPrepare()` or `SQLExecDirect()` for the `StatementHandle`. 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.
HY013	Unexpected memory handling error.	DB2 CLI was unable to access memory required to support execution or completion of the function.
HYC00	Driver not capable.	The SQL data type of column `ColumnNumber` is not recognized by DB2 CLI.
HYT00	Timeout expired.	The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for `SQLSetConnectAttr()`.

Restrictions

The following ODBC defined data types are not supported:

SQL_BIT
SQL_TINYINT

CLI Sample utilcli.c

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

 
/* From the CLI sample utilcli.c */
/* ... */
 
        sqlrc = SQLDescribeCol( hstmt,
                        ( SQLSMALLINT ) ( i + 1 ),
                        colName,
                        sizeof(colName),
                        &colNameLen,
                        &colType,
                        &colSize,
                        &colScale,
                        NULL ) ;

References

[ Top of Page | Previous Page | Next Page ]