SQLColumns function (CLI) - Get column information for a table

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

Specification:

CLI 2.1
ODBC 1.0

Unicode Equivalent: You can also use this function with the Unicode character set. The corresponding Unicode function is SQLColumnsW(). For details about ANSI to Unicode function mappings, see Unicode functions (CLI).

Syntax

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

Function arguments

Table 1. SQLColumns arguments
Data type	Argument	Use	Description
SQLHSTMT	StatementHandle	Input	The statement handle.
SQLCHAR *	CatalogName	Input	A catalog qualifier of a 3-part table name. If the target DBMS does not support 3-part naming, and CatalogName is not a null pointer and does not point to a zero-length string, then an empty result set and SQL_SUCCESS is returned. Otherwise, this is a valid filter for DBMSs that supports 3-part naming.
SQLSMALLINT	NameLength1	Input	The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that is required to store CatalogName, or SQL_NTS if CatalogName is null-terminated.
SQLCHAR *	SchemaName	Input	A buffer that might contain a pattern value to qualify the result set by schema name.
SQLSMALLINT	NameLength2	Input	The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store SchemaName, or SQL_NTS if SchemaName is null-terminated.
SQLCHAR *	TableName	Input	A buffer that might contain a pattern value to qualify the result set by table name.
SQLSMALLINT	NameLength3	Input	The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store TableName, or SQL_NTS if TableName is null-terminated.
SQLCHAR *	ColumnName	Input	A buffer that might contain a pattern value to qualify the result set by column name.
SQLSMALLINT	NameLength4	Input	The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store ColumnName, or SQL_NTS if ColumnName is null-terminated.

Usage

Use this function to retrieve information about the columns of either a table or a set of tables. An application can call this function after a call to SQLTables() to determine the columns of a table. The application must use the character strings that are returned in the TABLE_SCHEMA and TABLE_NAME columns of the SQLTables() result set as input to this function.

The SQLColumns() function returns a standard result set that is ordered by TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and ORDINAL_POSITION. Columns returned by SQLColumns lists the columns that are in the result set.

The SchemaName, TableName, and ColumnName input arguments accept search patterns.

Sometimes, an application calls the function and no attempt is made to restrict the result set that is returned. For some data sources that contain a large quantity of tables, views, and aliases for example, this scenario maps to an extremely large result set and very long retrieval times. In order to help reduce the long retrieval times, you can specify the configuration keyword SchemaList in the CLI initialization file to help restrict the result set when the application has supplied a null pointer for the SchemaName. If the application specifies a SchemaName string, the SchemaList keyword is still used to restrict the output. Therefore, if the schema name supplied is not in the SchemaList string, the result is an empty result set.

This function does not return information about the columns of a result set. Instead, you should use SQLDescribeCol() or SQLColAttribute() function.

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 CLI initialization file, then the LOB data types are reported as SQL_LONGVARCHAR, SQL_LONGVARBINARY or SQL_LONGVARGRAPHIC.

In many cases, calls to the SQLColumns() function map to a complex and thus expensive query against the system catalog, so you should use the calls sparingly, and save the results rather than repeating calls.

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 the actual lengths of the TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME columns that are supported by the connected DBMS.

You can specify *ALL or *USRLIBL as values in the SchemaName to resolve unqualified stored procedure calls or to find libraries in catalog API calls. If you specify *ALL, CLI searches on all existing schemas in the connected database. You are not required to specify *ALL, as this behavior is the default in CLI. For IBM® Db2® for IBM i servers, if you specify *USRLIBL, CLI searches on the current libraries of the server job. For other Db2 servers, *USRLIBL does not have a special meaning and CLI searches using *USRLIBL as a pattern. Alternatively, you can set the SchemaFilter IBM Data Server Driver configuration keyword or the Schema List CLI/ODBC configuration keyword to *ALL or *USRLIBL.

Although new columns might be added and the names of the existing columns changed in future releases, the position of the current columns will not change.

Columns returned by SQLColumns

Column 1 TABLE_CAT (VARCHAR(128))

The name of the catalog. The value is NULL if this table does not have catalogs.

Column 2 TABLE_SCHEM (VARCHAR(128))

The name of the schema containing TABLE_NAME.

Column 3 TABLE_NAME (VARCHAR(128) not NULL)

The name of the table, view, alias, or synonym.

Column 4 COLUMN_NAME (VARCHAR(128) not NULL)

The column identifier. The name of the column of the specified table, view, alias, or synonym.

Column 5 DATA_TYPE (SMALLINT not NULL)

The SQL data type of the column that is identified by COLUMN_NAME. The DATA_TYPE is one of the values in the Symbolic SQL Data Type column in the table of symbolic and default data types for CLI.

Column 6 TYPE_NAME (VARCHAR(128) not NULL)

A character string that represents the name of the data type that corresponds to DATA_TYPE.

Column 7 COLUMN_SIZE (INTEGER)

If the DATA_TYPE column value denotes a character or binary string, this column contains the maximum length in SQLCHAR or SQLWCHAR elements for the column.

For date, time, and timestamp data types, the COLUMN_SIZE is the total number of SQLCHAR or SQLWCHAR elements that are required to display the value when converted to character data type.

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

For the columns that are defined with the CODEUNITS32 unit, the number of code units for the column is returned.

For the XML data type, the length of zero is returned.

See the table of data type precision.

Column 8 BUFFER_LENGTH (INTEGER)

The maximum number of bytes for the associated C buffer to store data from this column if SQL_C_DEFAULT is specified on the SQLBindCol(), SQLGetData() and SQLBindParameter() calls. This length does not include any null-terminator. For exact numeric data types, the length accounts for the decimal and the sign.

See the table of data type lengths.

Column 9 DECIMAL_DIGITS (SMALLINT)

The scale of the column. NULL is returned for data types where scale is not applicable.

See the table of data type scale.

Column 10 NUM_PREC_RADIX (SMALLINT)

Either 10, 2, or NULL. If DATA_TYPE is an approximate numeric data type, this column contains the value 2, and the COLUMN_SIZE column contains the number of bits that are 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 that are allowed for the column.

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

NULL is returned for data types where the 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))

Might contain descriptive information about the column. It is possible that no information is returned in this column. For more details, see Optimize SQL columns keyword and attribute.

Column 13 COLUMN_DEF (VARCHAR(254))

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

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

It is possible that no information is returned in this column. For more details, see Optimize SQL columns keyword and attribute.

Column 14 SQL_DATA_TYPE (SMALLINT not NULL)

The SQL data type, as it is displayed in the SQL_DESC_TYPE record field in the IRD. This column is the same as the DATA_TYPE column in Columns returned by SQLColumns for the date, time, and timestamp data types.

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)

For single byte character sets, this is the same as COLUMN_SIZE. For the columns that are defined with the CODEUNITS32 unit, the number of bytes for the column is returned. For the XML type, zero is returned. For all other data types, NULL is returned.

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' if the column is nullable.

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 that is specified in ODBC V2. The ODBC SQLColumns() result set includes every column in the same position.

Optimize SQL columns keyword and attribute

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

OPTIMIZESQLCOLUMNS CLI/ODBC configuration keyword
SQL_ATTR_OPTIMIZESQLCOLUMNS connection attribute of SQLSetConnectAttr()

If either of these values are set, the information that is contained in the following columns is not returned:

Column 12 REMARKS
Column 13 COLUMN_DEF

Return codes

SQL_ERROR
SQL_INVALID_HANDLE
SQL_STILL_EXECUTING
SQL_SUCCESS
SQL_SUCCESS_WITH_INFO

Diagnostics

Table 2. 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. It is likely that process-level memory has been exhausted for the application process. Consult the operating system configuration for information about process-level memory limitations.
HY008	Operation was Canceled.	Asynchronous processing was enabled for StatementHandle. The function was called and before it completed execution, `SQLCancel()` was called on StatementHandle from a different thread in a multithreaded application. Then the function was called again on StatementHandle.
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 StatementHandle and was still executing when this function was called. The function was called before a statement was prepared on the statement handle.
HY014	No more handles.	Db2 CLI was unable to allocate a handle due to resource limitations.
HY090	Invalid string or buffer length.	The value of one of the name-length arguments was less than 0, but not equal to SQL_NTS.
HYT00	Timeout expired.	The timeout period expired before the data source returned the result set. You can set the timeout period by using the SQL_ATTR_QUERY_TIMEOUT attribute for `SQLSetStmtAttr()`. Note: This SQLSTATE applies only to .Net applications.

Restriction

The SQLColumns() function does not support returning data from an alias of an alias. When called against an alias of an alias, the SQLColumns() function returns an empty result set.

Example

The following code example gets column information for a table.

  /* get column information for a table */
  cliRC = SQLColumns(hstmt,
                     NULL,
                     0,
                     tbSchemaPattern,
                     SQL_NTS,
                     tbNamePattern,
                     SQL_NTS,
                     colNamePattern,
                     SQL_NTS);