| Specification: | DB2 CLI 2.1 | ODBC 1.0 |
|
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 36. SQLColumns Arguments
| Data Type | Argument | Use | Description |
|---|---|---|---|
| SQLHSTMT | StatementHandle | input | Statement handle. |
| SQLCHAR * | CatalogName | input | Buffer that may contain a pattern-value to qualify the result
set. Catalog is the first part of a 3 part table name.
This must be a NULL pointer or a zero length string. |
| SQLSMALLINT | NameLength1 | input | Length of CatalogName. This must be set to 0. |
| SQLCHAR * | SchemaName | input | Buffer that may contain a pattern-value to qualify the result set by schema name. |
| SQLSMALLINT | NameLength2 | input | Length of SchemaName |
| SQLCHAR * | TableName | input | Buffer that may contain a pattern-value to qualify the result set by table name. |
| SQLSMALLINT | NameLength3 | input | Length of TableName |
| SQLCHAR * | ColumnName | input | Buffer that may contain a pattern-value to qualify the result set by column name. |
| SQLSMALLINT | NameLength4 | input | Length of ColumnName |
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_SCHEMA 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 37 lists the columns in the result set.
The SchemaName, TableName, and ColumnName arguments accept search patterns. For more information about valid search patterns, see "Input Arguments on Catalog Functions".
This function does not return information on the columns in a result set, SQLDescribeCol() or SQLColAttribute() should be used instead.
If the SQL_ATTR_LONGDATA_COMPAT attribute is set to SQL_LD_COMPAT_YES via either a call to SQLSetConnectAttr() or by setting the LONGDATACOMPAT keyword in the DB2 CLI initialization file, then the LOB data types are reported as SQL_LONGVARCHAR, SQL_LONGVARBINARY or SQL_LONGVARGRAPHIC.
Since calls to SQLColumns() in many cases map to a complex and thus expensive query against the system catalog, they should be used sparingly, and the results 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_CATALOG_NAME_LEN, SQL_MAX_OWNER_SCHEMA_LEN, SQL_MAX_TABLE_NAME_LEN, and SQL_MAX_COLUMN_NAME_LEN to determine respectively the actual lengths of the TABLE_CAT, TABLE_SCHEM, 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. There were changes to these columns between version 2 and version 5. See "Changes to SQLColumns() Return Values" for more information if you are running a version 2 DB2 CLI application (that uses SQLColumns()) against a version 5 or later server.
Table 37. Columns Returned By SQLColumns
| Column Number/Name | Data Type | Description | ||
|---|---|---|---|---|
| 1 TABLE_CAT | VARCHAR(128) | This is always NULL. | ||
| 2 TABLE_SCHEM | VARCHAR(128) | The name of the schema containing TABLE_NAME. | ||
| 3 TABLE_NAME | VARCHAR(128) not NULL | Name of the table, view, alias, or synonym. | ||
| 4 COLUMN_NAME | VARCHAR(128) not NULL | Column identifier. Name of the column of the specified table, view, alias, or synonym. | ||
| 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 Table 3. | ||
| 6 TYPE_NAME | VARCHAR(128) not NULL | Character string representing the name of the data type corresponding to DATA_TYPE. | ||
| 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, 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 either the total number of digits, or the total number of bits allowed in the column, depending on the value in the NUM_PREC_RADIX column in the result set. See also, Table 197. | ||
| 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, Table 199. | ||
| 9 DECIMAL_DIGITS | SMALLINT | The scale of the column. NULL is returned for data types where scale is
not applicable.
See also, Table 198. | ||
| 10 NUM_PREC_RADIX | SMALLINT | Either 10 or 2 or NULL. If DATA_TYPE is an approximate numeric data type,
this column contains the value 2, then the COLUMN_SIZE 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 COLUMN_SIZE contains the number of decimal digits allowed for the column. For numeric data types, the 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) | May 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 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 the default value cannot be represented without truncation, then this column contains TRUNCATED with no enclosing single quotes. If no default value was specified, then this column is NULL. | ||
| 14 SQL_DATA_TYPE | SMALLINT not NULL | SQL data type, as it appears in the SQL_DESC_TYPE record field in the IRD. This column is the same as the DATA_TYPE column. | ||
| 15 SQL_DATETIME_SUB | SMALLINT | The subtype code for datetime data types:
| ||
| 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. | ||
| 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
Diagnostics
Table 38. SQLColumns SQLSTATEs
| SQLSTATE | Description | Explanation |
|---|---|---|
| 24000 | Invalid cursor state. | A cursor was already opened on the statement handle. |
| 40003 08S01 | Communication link failure. | The communication link between the application and data source failed before the function completed. |
| HY001 | Memory allocation failure. | DB2 CLI is unable to allocate memory required to support execution or completion of the function. |
| HY008 | Operation canceled. |
Asynchronous processing was enabled for the StatementHandle. The function was called and before it completed execution, SQLCancel() was called on the StatementHandle. Then the function was called again on the StatementHandle. The function was called and, before it completed execution,
SQLCancel() was called on the StatementHandle from a
different thread in a multithread application.
|
| HY010 | Function sequence error. |
The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation. The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation. An asynchronously executing function (not this one) was called for the
StatementHandle and was still executing when this function was
called.
|
| HY014 | No more handles. | DB2 CLI was unable to allocate a handle due to internal resources. |
| HY090 | Invalid string or buffer length. | The value of one of the name length arguments was less than 0, but not equal SQL_NTS. |
| HYC00 | Driver not capable. | DB2 CLI does not support catalog as a qualifier for table name. |
| HYT00 | Timeout expired. | The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetConnectAttr(). |
Restrictions
None.
/* From CLI sample browser.c */
/* ... */
SQLRETURN list_columns( SQLHANDLE hstmt,
SQLCHAR * schema,
SQLCHAR * tablename
) {
/* ... */
rc = SQLColumns(hstmt, NULL, 0, schema, SQL_NTS,
tablename, SQL_NTS, (SQLCHAR *)"%", SQL_NTS);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER) column_name.s, 129,
&column_name.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 6, SQL_C_CHAR, (SQLPOINTER) type_name.s, 129,
&type_name.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 7, SQL_C_LONG, (SQLPOINTER) &length,
sizeof(length), &length_ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 9, SQL_C_SHORT, (SQLPOINTER) &scale,
sizeof(scale), &scale_ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 12, SQL_C_CHAR, (SQLPOINTER) remarks.s, 129,
&remarks.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 11, SQL_C_SHORT, (SQLPOINTER) & nullable,
sizeof(nullable), &nullable_ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
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 */
References