Call Level Interface Guide and Reference

SQLColumns - Get Column Information for a Table

Purpose

Specification: DB2 CLI 2.1 ODBC 1.0

SQLColumns() returns a list of columns in the specified tables. The information is returned in an SQL result set, which can be retrieved using the same functions that are used to fetch a result set generated by a query.

Syntax

SQLRETURN   SQLColumns       (
                SQLHSTMT          StatementHandle,   /* hstmt */
                SQLCHAR      FAR  *CatalogName,      /* szCatalogName */
                SQLSMALLINT       NameLength1,       /* cbCatalogName */
                SQLCHAR      FAR  *SchemaName,       /* szSchemaName */
                SQLSMALLINT       NameLength2,       /* cbSchemaName */
                SQLCHAR      FAR  *TableName,        /* szTableName */
                SQLSMALLINT       NameLength3,       /* cbTableName */
                SQLCHAR      FAR  *ColumnName,       /* szColumnName */
                SQLSMALLINT       NameLength4);      /* cbColumnName */

Function Arguments

Table 38. SQLColumns Arguments

Data Type Argument Use Description
SQLHSTMT StatementHandle input Statement handle.
SQLCHAR * CatalogName input Buffer that may contain a pattern-value to qualify the result set. Catalog is the first part of a 3 part table name.
This must be a NULL pointer or a zero length string.
SQLSMALLINT NameLength1 input Length of CatalogName. This must be set to 0.
SQLCHAR * SchemaName input Buffer that may contain a pattern-value to qualify the result set by schema name.
SQLSMALLINT NameLength2 input Length of SchemaName
SQLCHAR * TableName input Buffer that may contain a pattern-value to qualify the result set by table name.
SQLSMALLINT NameLength3 input Length of TableName
SQLCHAR * ColumnName input Buffer that may contain a pattern-value to qualify the result set by column name.
SQLSMALLINT NameLength4 input Length of ColumnName

Data Type	Argument	Use	Description
SQLHSTMT	StatementHandle	input	Statement handle.
SQLCHAR *	CatalogName	input	Buffer that may contain a `pattern-value` to qualify the result set. `Catalog` is the first part of a 3 part table name. This must be a NULL pointer or a zero length string.
SQLSMALLINT	NameLength1	input	Length of `CatalogName`. This must be set to 0.
SQLCHAR *	SchemaName	input	Buffer that may contain a `pattern-value` to qualify the result set by schema name.
SQLSMALLINT	NameLength2	input	Length of `SchemaName`
SQLCHAR *	TableName	input	Buffer that may contain a `pattern-value` to qualify the result set by table name.
SQLSMALLINT	NameLength3	input	Length of `TableName`
SQLCHAR *	ColumnName	input	Buffer that may contain a `pattern-value` to qualify the result set by column name.
SQLSMALLINT	NameLength4	input	Length of `ColumnName`

Usage

This function is called to retrieve information about the columns of either a table or a set of tables. A typical application may wish to call this function after a call to SQLTables() to determine the columns of a table. The application should use the character strings returned in the TABLE_SCHEMA and TABLE_NAME columns of the SQLTables() result set as input to this function.

SQLColumns() returns a standard result set, ordered by TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and ORDINAL_POSITION. Columns Returned by SQLColumns() lists the columns in the result set.

The SchemaName, TableName, and ColumnName arguments accept search patterns. For more information about valid search patterns, see Input Arguments on Catalog Functions.

This function does not return information on the columns in a result set, SQLDescribeCol() or SQLColAttribute() should be used instead.

If the SQL_ATTR_LONGDATA_COMPAT attribute is set to SQL_LD_COMPAT_YES via either a call to SQLSetConnectAttr() or by setting the LONGDATACOMPAT keyword in the DB2 CLI initialization file, then the LOB data types are reported as SQL_LONGVARCHAR, SQL_LONGVARBINARY or SQL_LONGVARGRAPHIC.

Since calls to SQLColumns() in many cases map to a complex and thus expensive query against the system catalog, they should be used sparingly, and the results saved rather than repeating calls.

The VARCHAR columns of the catalog functions result set have been declared with a maximum length attribute of 128 to be consistent with SQL92 limits. Since DB2 names are less than 128, the application can choose to always set aside 128 characters (plus the null-terminator) for the output buffer, or alternatively, call SQLGetInfo() with the SQL_MAX_CATALOG_NAME_LEN, SQL_MAX_OWNER_SCHEMA_LEN, SQL_MAX_TABLE_NAME_LEN, and SQL_MAX_COLUMN_NAME_LEN to determine respectively the actual lengths of the TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME columns supported by the connected DBMS.

Although new columns may be added and the names of the existing columns changed in future releases, the position of the current columns will not change. There were changes to these columns between version 2 and version 5. See Changes to SQLColumns() Return Values for more information if you are running a version 2 DB2 CLI application (that uses SQLColumns()) against a version 5 or later server.

Optimize SQL Columns Keyword and Attribute

It is possible to set up the DB2 CLI/ODBC Driver to optimize calls to SQLColumns() using either:

OPTIMIZESQLCOLUMNS DB2 CLI/ODBC configuration keyword (see Configuring db2cli.ini for more information)
SQL_ATTR_OPTIMIZESQLCOLUMNS connection attribute (see SQLSetConnectAttr - Set Connection Attributes for more information)

If either of these values are set, then the following columns will not return any information:

12 REMARKS
13 COLUMN_DEF

Columns Returned by SQLColumns

Column 1 TABLE_CAT (VARCHAR(128))

This is always NULL.

Column 2 TABLE_SCHEM (VARCHAR(128))

The name of the schema containing TABLE_NAME.

Column 3 TABLE_NAME (VARCHAR(128) not NULL)

Name of the table, view, alias, or synonym.

Column 4 COLUMN_NAME (VARCHAR(128) not NULL)

Column identifier. Name of the column of the specified table, view, alias, or synonym.

Column 5 DATA_TYPE (SMALLINT not NULL)

SQL data type of column identified by COLUMN_NAME. This is one of the values in the Symbolic SQL Data Type column in Table 2.

Column 6 TYPE_NAME (VARCHAR(128) not NULL)

Character string representing the name of the data type corresponding to DATA_TYPE.

Column 7 COLUMN_SIZE (INTEGER)

If the DATA_TYPE column value denotes a character or binary string, then this column contains the maximum length in characters for the column.

For date, time, timestamp data types, this is the total number of characters required to display the value when converted to character.

For numeric data types, this is either the total number of digits, or the total number of bits allowed in the column, depending on the value in the NUM_PREC_RADIX column in the result set.

See also, Table 195.

Column 10 NUM_PREC_RADIX (SMALLINT)

Either 10 or 2 or NULL. If DATA_TYPE is an approximate numeric data type, this column contains the value 2, then the COLUMN_SIZE column contains the number of bits allowed in the column.

If DATA_TYPE is an exact numeric data type, this column contains the value 10 and the COLUMN_SIZE contains the number of decimal digits allowed for the column.

For numeric data types, the DBMS can return a NUM_PREC_RADIX of either 10 or 2.

NULL is returned for data types where radix is not applicable.

Column 11 NULLABLE (SMALLINT not NULL)

SQL_NO_NULLS if the column does not accept NULL values.

SQL_NULLABLE if the column accepts NULL values.

Column 12 REMARKS (VARCHAR(254))

May contain descriptive information about the column. It is possible that no information is returned in this column; see Optimize SQL Columns Keyword and Attribute for more details.

Column 13 COLUMN_DEF (VARCHAR(254))

The column's default value. If the default value is a numeric literal, then this column contains the character representation of the numeric literal with no enclosing single quotes. If the default value is a character string, then this column is that string enclosed in single quotes. If the default value a pseudo-literal, such as for DATE, TIME, and TIMESTAMP columns, then this column contains the keyword of the pseudo-literal (e.g. CURRENT DATE) with no enclosing quotes.

If NULL was specified as the default value, then this column returns the word NULL, not enclosed in quotes. If the default value cannot be represented without truncation, then this column contains TRUNCATED with no enclosing single quotes. If no default value was specified, then this column is NULL.

It is possible that no information is returned in this column; see Optimize SQL Columns Keyword and Attribute for more details.

Column 14 SQL_DATA_TYPE (SMALLINT not NULL)

SQL data type, as it appears in the SQL_DESC_TYPE record field in the IRD. This column is the same as the DATA_TYPE column.

Column 15 SQL_DATETIME_SUB (SMALLINT)

The subtype code for datetime data types:

SQL_CODE_DATE
SQL_CODE_TIME
SQL_CODE_TIMESTAMP

For all other data types this column returns NULL.

Column 16 CHAR_OCTET_LENGTH (INTEGER)

Contains the maximum length in octets for a character data type column. For Single Byte character sets, this is the same as COLUMN_SIZE. For all other data types it is NULL.

Column 17 ORDINAL_POSITION (INTEGER not NULL)

The ordinal position of the column in the table. The first column in the table is number 1.

Column 18 IS_NULLABLE (VARCHAR(254))

Contains the string 'NO' if the column is known to be not nullable; and 'YES' otherwise.

Note: This result set is identical to the X/Open CLI Columns() result set specification, which is an extended version of the SQLColumns() result set specified in ODBC V2. The ODBC SQLColumns() result set includes every column in the same position.

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_STILL_EXECUTING
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 39. SQLColumns SQLSTATEs

SQLSTATE Description Explanation
24000 Invalid cursor state. A cursor was already opened on the statement handle.
40003 08S01 Communication link failure. The communication link between the application and data source failed before the function completed.
HY001 Memory allocation failure. DB2 CLI is unable to allocate memory required to support execution or completion of the function.
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.
HY010 Function sequence error.
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.
An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called.
HY014 No more handles. DB2 CLI was unable to allocate a handle due to internal resources.
HY090 Invalid string or buffer length. The value of one of the name length arguments was less than 0, but not equal SQL_NTS.
HYC00 Driver not capable. DB2 CLI does not support catalog as a qualifier for table name.
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
24000	Invalid cursor state.	A cursor was already opened on the statement handle.
40003 08S01	Communication link failure.	The communication link between the application and data source failed before the function completed.
HY001	Memory allocation failure.	DB2 CLI is unable to allocate memory required to support execution or completion of the function.
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.
HY010	Function sequence error.	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. An asynchronously executing function (not this one) was called for the `StatementHandle` and was still executing when this function was called.
HY014	No more handles.	DB2 CLI was unable to allocate a handle due to internal resources.
HY090	Invalid string or buffer length.	The value of one of the name length arguments was less than 0, but not equal SQL_NTS.
HYC00	Driver not capable.	DB2 CLI does not support `catalog` as a qualifier for table name.
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

None.

CLI Sample tbinfo.c

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

 
/* From the CLI sample TBINFO.C */
/* ... */
 
    /* call SQLColumns */ 
    printf("\n    Call SQLColumns for:\n");
    printf("        tbSchemaPattern = %s\n", tbSchemaPattern);    
    printf("        tbNamePattern = %s\n", tbNamePattern); 
    printf("        colNamePattern = %s\n", colNamePattern);    
    sqlrc = SQLColumns( hstmt, NULL, 0,
                        tbSchemaPattern, SQL_NTS,
                        tbNamePattern, SQL_NTS,
                        colNamePattern, SQL_NTS);

References

[ Top of Page | Previous Page | Next Page ]