SQLColumns - Get column information for a table

SQLColumns - Get column information for a table

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

Syntax
SQLRETURN SQLColumns  (SQLHSTMT       hstmt,
                       SQLCHAR        *szCatalogName,
                       SQLSMALLINT    cbCatalogName,
                       SQLCHAR        *szSchemaName,
                       SQLSMALLINT    cbSchemaName,
                       SQLCHAR        *szTableName,
                       SQLSMALLINT    cbTableName,
                       SQLCHAR        *szColumnName,
                       SQLSMALLINT    cbColumnName);
Function arguments

Table 1. SQLColumns arguments
Data type Argument Use Description

SQLHSTMT hstmt Input Statement handle.
SQLCHAR * szCatalogName Input Buffer that might contain a pattern-value to qualify the result set. Catalog is the first part of a three-part table name.
This must be a NULL pointer or a zero length string.
SQLSMALLINT cbCatalogName Input Length of szCatalogName. This must be set to 0.
SQLCHAR * szSchemaName Input Buffer that might contain a pattern-value to qualify the result set by schema name.
SQLSMALLINT cbSchemaName Input Length of szSchemaName
SQLCHAR * szTableName Input Buffer that might contain a pattern-value to qualify the result set by table name.
SQLSMALLINT cbTableName Input Length of szTableName
SQLCHAR * szColumnName Input Buffer that might contain a pattern-value to qualify the result set by column name.
SQLSMALLINT cbColumnName Input Length of szColumnName

Usage

This function retrieves information about the columns of a table or a list of tables.
SQLColumns() returns a standard result set. Table 2 lists the columns in the result set. Applications should anticipate that additional columns beyond the REMARKS columns can be added in future releases.
The szCatalogName, szSchemaName, szTableName, and szColumnName arguments accept search patterns. An escape character can be specified in conjunction with a wildcard character to allow that actual character to be used in the search pattern. The escape character is specified on the SQL_ATTR_ESCAPE_CHAR environment attribute. This function does not return information about the columns in a result set, which is retrieved by SQLDescribeCol() or SQLColAttributes(). If an application wants to obtain column information for a result set, it should always call SQLDescribeCol() or SQLColAttributes() for efficiency. SQLColumns() maps to a complex query against the system catalogs, and can require a large amount of system resources.
Table 2. Columns returned by SQLColumns
Column number/name Data type Description

1 TABLE_CAT VARCHAR(128) The current server.
2 TABLE_SCHEM VARCHAR(128) The name of the schema containing TABLE_NAME.
3 TABLE_NAME VARCHAR(128) Name of the table, view or alias.
4 COLUMN_NAME VARCHAR(128) Column identifier. The name of the column of the specified view, table, or table's column the alias is built for.
5 DATA_TYPE SMALLINT not NULL DATA_TYPE identifies the SQL data type of the column. For CHAR FOR BIT DATA and VARCHAR FOR BIT DATA data types, the CLI returns SQL_BINARY and SQL_VARBINARY to indicate it is a FOR BIT DATA column.
6 TYPE_NAME VARCHAR(128) not NULL TYPE_NAME is a character string representing the name of the data type corresponding to DATA_TYPE. If the data type is FOR BIT DATA, then the corresponding string FOR BIT DATA is appended to the data type, for example, CHAR () FOR BIT DATA.
7 LENGTH_PRECISION INTEGER If DATA TYPE is an approximate numeric data type, this column contains the number of bits of mantissa precision of the column. For exact numeric data types, this column contains the total number of decimal digit allowed in the column. For time and timestamp data types, this column contains the number of digits of precision of the fractional seconds component; otherwise, this column is NULL.
The ODBC definition of precision is typically the number of digits to store the data type.
8 BUFFER_LENGTH INTEGER The maximum number of bytes to store data from this column if SQL_DEFAULT were specified on the SQLBindCol(), SQLGetData() and SQLBindParam() calls.
9 NUM_SCALE SMALLINT The scale of the column. NULL is returned for data types where scale is not applicable.
10 NUM_PREC_RADIX SMALLINT The value is 10, 2, or NULL. If DATA_TYPE is an approximate numeric data type, this column contains the value 2; then the LENGTH_PRECISION 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 LENGTH_PRECISION and NUM_SCALE columns contain the number of decimal digits allowed for the column.
For numeric data types, the Database Management System (DBMS) can return a NUM_PREC_RADIX of either 10 or 2.
NULL is returned for data types where radix is not applicable.
11 NULLABLE SMALLINT not NULL SQL_NO_NULLS if the column does not accept NULL values.
SQL_NULLABLE if the column accepts NULL values.
12 REMARKS VARCHAR(254) It might contain descriptive information about the 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 quotation marks. If the default value is a character string, then this column is that string enclosed in single quotation marks. 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 (for example, CURRENT DATE) with no enclosing quotation marks.
If NULL is specified as the default value, then this column returns the word NULL, not enclosed in quotation marks. If the default value cannot be represented without truncation, then this column contains TRUNCATED with no enclosing single quotation marks. If no default value is specified, then this column is NULL.
14 DATETIME_CODE INTEGER The subtype code for date and time data types:

SQL_DATE

SQL_TIME

SQL_TIMESTAMP

For all other data types, this column returns NULL.
15 CHAR_OCTET_LENGTH INTEGER This contains the maximum length in octets for a character data type column. For single byte character sets, this is the same as LENGTH_PRECISION. For all other data types, it is NULL.
16 ORDINAL_POSITION INTEGER NOT NULL The ordinal position of the column in the table. The first column in the table is number 1.

Return codes

SQL_SUCCESS

SQL_SUCCESS_WITH_INFO

SQL_ERROR

SQL_INVALID_HANDLE

Diagnostics

Table 3. SQLColumns SQLSTATEs
SQLSTATE Description Explanation

HY001 Memory allocation failure The driver is unable to allocate memory required to support the processing or completion of the function.
HY009 String or buffer length that is not valid The value of one of the name length arguments is less than 0, but not equal SQL_NTS.
HY010 Function sequence error There is an open cursor for this statement handle, or there is no connection for this statement handle.
HY021 Internal descriptor that is not valid The internal descriptor cannot be addressed or allocated, or it contains a value that is not valid.

Parent topic:
DB2 UDB CLI functions

Data type	Argument	Use	Description
SQLHSTMT	hstmt	Input	Statement handle.
SQLCHAR *	szCatalogName	Input	Buffer that might contain a pattern-value to qualify the result set. Catalog is the first part of a three-part table name. This must be a NULL pointer or a zero length string.
SQLSMALLINT	cbCatalogName	Input	Length of szCatalogName. This must be set to 0.
SQLCHAR *	szSchemaName	Input	Buffer that might contain a pattern-value to qualify the result set by schema name.
SQLSMALLINT	cbSchemaName	Input	Length of szSchemaName
SQLCHAR *	szTableName	Input	Buffer that might contain a pattern-value to qualify the result set by table name.
SQLSMALLINT	cbTableName	Input	Length of szTableName
SQLCHAR *	szColumnName	Input	Buffer that might contain a pattern-value to qualify the result set by column name.
SQLSMALLINT	cbColumnName	Input	Length of szColumnName

Column number/name	Data type	Description
1 TABLE_CAT	VARCHAR(128)	The current server.
2 TABLE_SCHEM	VARCHAR(128)	The name of the schema containing TABLE_NAME.
3 TABLE_NAME	VARCHAR(128)	Name of the table, view or alias.
4 COLUMN_NAME	VARCHAR(128)	Column identifier. The name of the column of the specified view, table, or table's column the alias is built for.
5 DATA_TYPE	SMALLINT not NULL	DATA_TYPE identifies the SQL data type of the column. For CHAR FOR BIT DATA and VARCHAR FOR BIT DATA data types, the CLI returns SQL_BINARY and SQL_VARBINARY to indicate it is a FOR BIT DATA column.
6 TYPE_NAME	VARCHAR(128) not NULL	TYPE_NAME is a character string representing the name of the data type corresponding to DATA_TYPE. If the data type is FOR BIT DATA, then the corresponding string FOR BIT DATA is appended to the data type, for example, CHAR () FOR BIT DATA.
7 LENGTH_PRECISION	INTEGER	If DATA TYPE is an approximate numeric data type, this column contains the number of bits of mantissa precision of the column. For exact numeric data types, this column contains the total number of decimal digit allowed in the column. For time and timestamp data types, this column contains the number of digits of precision of the fractional seconds component; otherwise, this column is NULL. The ODBC definition of precision is typically the number of digits to store the data type.
8 BUFFER_LENGTH	INTEGER	The maximum number of bytes to store data from this column if SQL_DEFAULT were specified on the `SQLBindCol(), SQLGetData()` and `SQLBindParam()` calls.
9 NUM_SCALE	SMALLINT	The scale of the column. NULL is returned for data types where scale is not applicable.
10 NUM_PREC_RADIX	SMALLINT	The value is 10, 2, or NULL. If DATA_TYPE is an approximate numeric data type, this column contains the value 2; then the LENGTH_PRECISION 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 LENGTH_PRECISION and NUM_SCALE columns contain the number of decimal digits allowed for the column. For numeric data types, the Database Management System (DBMS) can return a NUM_PREC_RADIX of either 10 or 2. NULL is returned for data types where radix is not applicable.
11 NULLABLE	SMALLINT not NULL	SQL_NO_NULLS if the column does not accept NULL values. SQL_NULLABLE if the column accepts NULL values.
12 REMARKS	VARCHAR(254)	It might contain descriptive information about the 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 quotation marks. If the default value is a character string, then this column is that string enclosed in single quotation marks. 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 (for example, CURRENT DATE) with no enclosing quotation marks. If NULL is specified as the default value, then this column returns the word NULL, not enclosed in quotation marks. If the default value cannot be represented without truncation, then this column contains TRUNCATED with no enclosing single quotation marks. If no default value is specified, then this column is NULL.
14 DATETIME_CODE	INTEGER	The subtype code for date and time data types: SQL_DATE SQL_TIME SQL_TIMESTAMP For all other data types, this column returns NULL.
15 CHAR_OCTET_LENGTH	INTEGER	This contains the maximum length in octets for a character data type column. For single byte character sets, this is the same as LENGTH_PRECISION. For all other data types, it is NULL.
16 ORDINAL_POSITION	INTEGER NOT NULL	The ordinal position of the column in the table. The first column in the table is number 1.

SQLSTATE	Description	Explanation
HY001	Memory allocation failure	The driver is unable to allocate memory required to support the processing or completion of the function.
HY009	String or buffer length that is not valid	The value of one of the name length arguments is less than 0, but not equal SQL_NTS.
HY010	Function sequence error	There is an open cursor for this statement handle, or there is no connection for this statement handle.
HY021	Internal descriptor that is not valid	The internal descriptor cannot be addressed or allocated, or it contains a value that is not valid.