| 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 45. 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. |
| 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 3 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 198 for the scale of each of the SQL data types. |
| SQLSMALLINT * | NullablePtr | output | Indicates whether NULLS are allowed for this column
|
Usage
Columns are identified by a number, are numbered sequentially from left to right, and may be described in any order.
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
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 46. 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(). |
Restrictions
The following ODBC defined data types are not supported:
/* From CLI sample samputil.c */
/* ... */
/* print_results */
SQLRETURN print_results( SQLHANDLE hstmt ) {
SQLCHAR colname[32] ;
SQLSMALLINT coltype ;
SQLSMALLINT colnamelen ;
SQLSMALLINT nullable ;
SQLUINTEGER collen[MAXCOLS] ;
SQLSMALLINT scale ;
SQLINTEGER outlen[MAXCOLS] ;
SQLCHAR * data[MAXCOLS] ;
SQLCHAR errmsg[256] ;
SQLRETURN rc ;
SQLSMALLINT nresultcols, i ;
SQLINTEGER displaysize ;
rc = SQLNumResultCols( hstmt, &nresultcols ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
for ( i = 0; i < nresultcols; i++ ) {
SQLDescribeCol( hstmt,
( SQLSMALLINT ) ( i + 1 ),
colname,
sizeof(colname),
&colnamelen,
&coltype,
&collen[i],
&scale,
NULL
) ;
/* get display length for column */
SQLColAttribute( hstmt,
( SQLSMALLINT ) ( i + 1 ),
SQL_DESC_DISPLAY_SIZE,
NULL,
0,
NULL,
&displaysize
) ;
/*
Set column length to max of display length,
and column name length. Plus one byte for
null terminator.
*/
collen[i] = max( displaysize,
strlen( ( char * ) colname )
) + 1 ;
printf( "%-*.*s",
( int ) collen[i],
( int ) collen[i],
colname
) ;
/* allocate memory to bind column */
data[i] = ( SQLCHAR * ) malloc( ( int ) collen[i] ) ;
/* bind columns to program vars, converting all types to CHAR */
SQLBindCol( hstmt,
( SQLSMALLINT ) ( i + 1 ),
SQL_C_CHAR,
data[i],
collen[i],
&outlen[i]
) ;
}
printf( "\n" ) ;
/* display result rows */
while ( SQLFetch( hstmt ) != SQL_NO_DATA ) {
errmsg[0] = '\0' ;
for ( i = 0; i < nresultcols; i++ ) {
/* Check for NULL data */
if ( outlen[i] == SQL_NULL_DATA )
printf( "%-*.*s",
( int ) collen[i],
( int ) collen[i],
"NULL"
) ;
else { /* Build a truncation message for any columns truncated */
if ( outlen[i] >= collen[i] ) {
sprintf( ( char * ) errmsg + strlen( ( char * ) errmsg ),
"%d chars truncated, col %d\n",
( int ) outlen[i] - collen[i] + 1,
i + 1
) ;
}
/* Print column */
printf( "%-*.*s",
( int ) collen[i],
( int ) collen[i],
data[i]
) ;
}
} /* for all columns in this row */
printf( "\n%s", errmsg ) ; /* print any truncation messages */
} /* while rows to fetch */
/* free data buffers */
for ( i = 0; i < nresultcols; i++ ) {
free( data[i] ) ;
}
return( SQL_SUCCESS ) ;
} /* end print_results */
References