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.
SQLColumnsW()
. Refer to Unicode in Db2 for i CLI 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
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.
SQLColumns()
returns
a standard result set. Table 2 lists
the columns in the result set.
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.
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 | NVARCHAR(2000) | Contains descriptive information about the column. |
13 COLUMN_DEF | NVARCHAR(2000) | 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:
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(3) | 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
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. |