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 38. 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. Columns Returned by SQLColumns() 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.
Optimize SQL Columns Keyword and Attribute
It is possible to set up the DB2 CLI/ODBC Driver to optimize calls to SQLColumns() using either:
If either of these values are set, then the following columns will not return any information:
Columns Returned by SQLColumns
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 194.
See also, Table 196.
See also, Table 195.
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.
SQL_NULLABLE if the column accepts NULL values.
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.
It is possible that no information is returned in this column; see Optimize SQL Columns Keyword and Attribute for more details.
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
Diagnostics
Table 39. 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.
(The complete sample tbinfo.c is also available here .)
/* From the CLI sample TBINFO.C */ /* ... */ /* call SQLColumns */ printf("\n Call SQLColumns for:\n"); printf(" tbSchemaPattern = %s\n", tbSchemaPattern); printf(" tbNamePattern = %s\n", tbNamePattern); printf(" colNamePattern = %s\n", colNamePattern); sqlrc = SQLColumns( hstmt, NULL, 0, tbSchemaPattern, SQL_NTS, tbNamePattern, SQL_NTS, colNamePattern, SQL_NTS);
References