`SQLColumns()` - Get column information

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

ODBC specifications for `SQLColumns()`

Table 1. `SQLColumns()` specifications
ODBC specification level	In X/Open CLI CAE specification?	In ISO CLI specification?
1.0	Yes	No

Syntax

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

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. `SQLColumns()` arguments
Data type	Argument	Use	Description
SQLHSTMT	hstmt	input	Identifies the statement handle.
SQLCHAR *	szCatalogName	input	Identifies the buffer that can 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	Specifies the length, in bytes, of szCatalogName. This must be set to 0.
SQLCHAR *	szSchemaName	input	Identifies the buffer that can contain a pattern-value to qualify the result set by schema name.
SQLSMALLINT	cbSchemaName	input	Specifies the length, in bytes, of szSchemaName.
SQLCHAR *	szTableName	input	Identifies the buffer that can contain a pattern-value to qualify the result set by table name.
SQLSMALLINT	cbTableName	input	Specifies the length, in bytes, of szTableName.
SQLCHAR *	szColumnName	input	Identifies the buffer that can contain a pattern-value to qualify the result set by column name.
SQLSMALLINT	cbColumnName	input	Specifies the length, in bytes, of szColumnName.

Usage

This function retrieves information about the columns of a table or a set of tables. Typically, you call this function after you call SQLTables() to determine the columns of a table. Use the character strings that are returned in the TABLE_SCHEM 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. Table 3 lists the columns in the result set.

The szSchemaName, szTableName, and szColumnName arguments accept search patterns.

Because calls to SQLColumns() frequently result in a complex and expensive query to the catalog, use these calls sparingly, and save the results rather than repeat the calls.

The VARCHAR columns of the catalog functions result set are declared with a maximum length attribute of 128 bytes (which is consistent with ANSI/ISO SQL standard of 1992 limits). Because Db2 names are less than 128 characters, the application can choose to always set aside 128 characters (plus the nul-terminator) for the output buffer. You can alternatively call SQLGetInfo() with the InfoType argument set to each of the following values:

SQL_MAX_CATALOG_NAME_LEN, to determine the length of TABLE_CAT columns that the connected database management system supports
SQL_MAX_SCHEMA_NAME_LEN, to determine the length of TABLE_SCHEM columns that the connected database management system supports
SQL_MAX_TABLE_NAME_LEN, to determine the length of TABLE_NAME columns that the connected database management system supports
SQL_MAX_COLUMN_NAME_LEN, to determine the length of COLUMN_NAME columns that the connected database management system supports

Although new columns might be added and the names of the existing columns might change in future releases, the position of the current columns will remain unchanged. The following table lists the columns in the result set that SQLColumns() currently returns.

Table 3. Columns returned by `SQLColumns()`
Column number	Column name	Data type	Description
1	TABLE_CAT	VARCHAR(128)	Always null.
2	TABLE_SCHEM	VARCHAR(128)	Identifies the name of the schema that contains TABLE_NAME.
3	TABLE_NAME	VARCHAR(128) NOT NULL	Identifies the name of the table, view, alias, or synonym.
4	COLUMN_NAME	VARCHAR(128) NOT NULL	Identifies the column that is described. This column contains the name of the column of the specified table, view, alias, or synonym.
5	DATA_TYPE	SMALLINT NOT NULL	Identifies the SQL data type of the column that COLUMN_NAME indicates.
6	TYPE_NAME	VARCHAR(128) NOT NULL	Identifies the character string that represents the name of the data type that corresponds to the DATA_TYPE result set column.
7	COLUMN_SIZE	INTEGER	If the DATA_TYPE column value denotes a character or binary string, this column contains the maximum length in characters for the column. For date, time, timestamp data types, this is the total number of characters that are required to display the value when it is converted to character. For numeric data types, this 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 XML data type, the length of zero is returned.
8	BUFFER_LENGTH	INTEGER	Indicates 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 nul-terminator. For exact numeric data types, the length accounts for the decimal and the sign.
9	DECIMAL_DIGITS	SMALLINT	Indicates the scale of the column. NULL is returned for data types where scale is not applicable.
10	NUM_PREC_RADIX	SMALLINT	Specifies 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 database management system can return a NUM_PREC_RADIX value of either 10 or 2. NULL is returned for data types where the NUM_PREC_RADIX column does not apply.
11	NULLABLE	SMALLINT NOT NULL	Contains SQL_NO_NULLS if the column does not accept null values. Contains SQL_NULLABLE if the column accepts null values.
12	REMARKS	VARCHAR(762)	Contains any descriptive information about the column.
13	COLUMN_DEF	VARCHAR(254)	Identifies the default value for the column. If the default value is a numeric literal, this column contains the character representation of the numeric literal with no enclosing single quotes. If the default value is a character string, this column is that string, enclosed in single quotes. 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 quotes. If NULL was specified as the default value, this column returns the word NULL, with no enclosing single quotes. If the default value cannot be represented without truncation, this column contains the value TRUNCATED with no enclosing single quotes. If no default value was specified, this column is null.
14	SQL_DATA_TYPE	SMALLINT NOT NULL	Indicates the SQL data type. This column is the same as the DATA_TYPE column. For datetime data types, the SQL_DATA_TYPE field in the result set is SQL_DATETIME, and the SQL_DATETIME_SUB field returns the subcode for the specific datetime data type (SQL_CODE_DATE, SQL_CODE_TIME, or SQL_CODE_TIMESTAMP).
15	SQL_DATETIME_SUB	SMALLINT	The subtype code for datetime data types can be one of the following values: SQL_CODE_DATE SQL_CODE_TIME SQL_CODE_TIMESTAMP For all other data types, this column returns NULL.
16	CHAR_OCTET_LENGTH	INTEGER	Contains the maximum length in bytes for a character data column. For single-byte character sets, this is the same as COLUMN_SIZE. For the XML type, zero is returned. For data types other than character data types or XML data type, it is null.
17	ORDINAL_POSITION	INTEGER NOT NULL	The ordinal position of the column in the table. The first column in the table is number 1.
18	IS_NULLABLE	VARCHAR(254)	Contains the string 'NO' if the column is known to be not nullable; and 'YES' otherwise.

The result set that the preceding table describes is identical to the X/Open CLI Columns() result set specification, which is an extended version of the SQLColumns() result set that ODBC 2.0 specifies. The ODBC SQLColumns() result set includes every column in the same position up to the REMARKS column.

Db2 ODBC applications that issue SQLColumns() against a Db2 for z/OS® server should expect the result set columns that are listed in Table 3.

Return codes

After you call SQLColumns(), it returns one of the following values:

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 4. `SQLColumns()` SQLSTATEs
SQLSTATE	Description	Explanation
08S01	Communication link failure.	The communication link between the application and data source fails before the function completes.
24000	Invalid cursor state.	A cursor is open on the statement handle.
HY001	Memory allocation failure.	Db2 ODBC is not able to allocate the required memory to support the execution or the completion of the function.
HY010	Function sequence error.	The function is called during a data-at-execute operation. (That is, the function is called during a procedure that uses the `SQLParamData()` or `SQLPutData()` functions.)
HY014	No more handles.	Db2 ODBC is not able to allocate a handle due to low internal resources.
HY090	Invalid string or buffer length.	The value of one of the name length argument is less than 0 and not equal to SQL_NTS.
HYC00	Driver not capable.	Db2 ODBC does not support "catalog" as a qualifier for table name.

Example

The following example shows an application that queries the system catalog for information about columns in a table.

Figure 1. An application that returns information about table columns

/* ... */
SQLRETURN
list_columns(SQLHDBC hdbc, SQLCHAR *schema, SQLCHAR *tablename )
{
/* ... */
    rc = SQLColumns(hstmt, NULL, 0, schema, SQL_NTS,
                    tablename, SQL_NTS, "NTS);
    rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER) column_name.s, 129,
                    &column_name.ind);
    rc = SQLBindCol(hstmt, 6, SQL_C_CHAR, (SQLPOINTER) type_name.s, 129,
                    &type_name.ind);
    rc = SQLBindCol(hstmt, 7, SQL_C_LONG, (SQLPOINTER) &length,
                    sizeof(length), &length_ind);
    rc = SQLBindCol(hstmt, 9, SQL_C_SHORT, (SQLPOINTER) &scale,
                    sizeof(scale), &scale_ind);
    rc = SQLBindCol(hstmt, 12, SQL_C_CHAR, (SQLPOINTER) remarks.s, 129,
                    &remarks.ind);
    rc = SQLBindCol(hstmt, 11, SQL_C_SHORT, (SQLPOINTER) & nullable,
                    sizeof(nullable), &nullable_ind);
printf("Schema: %s Table Name: %s\n", schema, tablename);
    /* Fetch each row, and display */
    while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS) {
        printf(" %s", column_name.s);
        if (nullable == SQL_NULLABLE) {
            printf(", NULLABLE");
        } else {
        printf(", NOT NULLABLE");
        }
        printf(", %s", type_name.s);
        if (length_ind != SQL_NULL_DATA) {
            printf(" (%ld", length);
        } else {
            printf("(\n");
        }
        if (scale_ind != SQL_NULL_DATA) {
        printf(", %d)\n", scale);
        } else {
            printf(")\n");
        }
    }                           /* endwhile */
/* ... */

SQLColumns() - Get column information

ODBC specifications for SQLColumns()