SQLColumns - Get column information for a table

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

Unicode (UTF-16) equivalent: This function can also be used with the Unicode (UTF-16) character set. The corresponding Unicode function is SQLColumnsW(). Refer to Unicode in DB2 for iCLI for more information about Unicode support for DB2 for i CLI.

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 can 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.

Start of changeSQLColumns() returns a standard result set. Table 2 lists the columns in the result set. End of change

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 SQLColAttribute(). If an application wants to obtain column information for a result set, it should always call SQLDescribeCol() or SQLColAttribute() 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.
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 COLUMN_SIZE 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.
Note: 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 DECIMAL_DIGITS 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 SQL_DATA_TYPE SMALLINT not NULL DATA_TYPE identifies the SQL data type of the column.
15 SQL_DATETIME_SUB SMALLINT
The subtype code for date and time data types:
  • SQL_DATE
  • SQL_TIME
  • SQL_TIMESTAMP

For all other data types, this column returns NULL.
16 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.
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.

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 for i CLI functions