SQLColumns - Get Column Information for a Table

Purpose

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 92. SQLColumns arguments

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_NAME of the SQLTables() result set as input to this function.

SQLColumns() returns a standard result set, ordered by TABLE_NAME, and ORDINAL_POSITION. *** lists the columns in the result set.

The TableName, and ColumnName arguments accept search patterns.

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

Calls to SQLColumns() should be used sparingly, because in many cases they map to a complex and thus expensive query against the system catalog. The results should be 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_TABLE_NAME_LEN, and SQL_MAX_COLUMN_NAME_LEN to determine respectively the actual lengths of the 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.

Columns Returned By SQLColumns

Column 1 TABLE_CAT (VARCHAR(128))

This is always NULL.

Column 2 TABLE_SCHEM (VARCHAR(128))

This is always NULL.

Column 3 TABLE_NAME (VARCHAR(128) not NULL)

Name of the table.

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 SQL symbolic and default data types.

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, or 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 the total number of digits allowed in the column.

See also, Data type attributes.

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 were 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 also, Data type attributes

Column 9 DECIMAL_DIGITS (SMALLINT)

The scale of the column. NULL is returned for data types where scale is not applicable. See also, Data type attributes

Column 10 NUM_PREC_RADIX (SMALLINT)

Either 10 or NULL.

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 returns a NUM_PREC_RADIX of 10.

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))

This is always NULL.

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 no default value was specified, then this column is NULL.

Column 14 SQL_DATA_TYPE (SMALLINT not NULL)

This column is the same as the DATA_TYPE column.

Column 15 SQL_DATETIME_SUB (SMALLINT)

This column is always 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.

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.

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_ERROR
• SQL_INVALID_HANDLE

Diagnostics

Table 93. SQLColumns SQLSTATEs

Restrictions

None.

Related reference

• SQLTables - Get Table Information