|
SQL QUERY COLUMNS Statement |
|
< > Browse Statements Query Statements Connection Statements Example Flow Chart Table of Contents
The SQL QUERY COLUMNS statement returns a result set that lists and describes the columns of a named table of an InstantSQL connection. The result set is identified by the query handle value that is returned.
QueryHandle (output). This argument must refer to a numeric integer data item with at least six digits of precision. The argument specifies the data item where the query handle value that identifies the table-columns query is to be stored.
ConnectionHandle (input). This argument must specify a numeric integer value with at least six digits of precision. The value identifies the connection to be used for the table-columns query. This value must have been returned by a successful SQL CONNECT DATASOURCE statement.
TableName (input). This argument must refer to a nonnumeric value. The value specifies the table name of the table or tables for which the column descriptions are to be returned. Table names available from a data source can be obtained with the SQL QUERY TABLES statement. This argument identifies the table name or names using a string search pattern. (See the topic InstantSQL Search Patterns for information on search patterns.) For letters in this string, case is significant.
CatalogName (input). This argument must refer to a nonnumeric value. The value specifies the catalog name (or, for ODBC v2, the qualifier name) of the table or tables for which the column descriptions are to be returned. This argument is optional. The default is no catalog name constraint on the query results. For letters in this string, case is significant. CatalogName cannot contain a string search pattern. If a driver supports catalogs for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string (all spaces) denotes those tables that do not have catalogs. This argument must be omitted (or specified as OMITTED) if the data source does not support catalog names.
SchemaName (input). This argument must refer to a nonnumeric value. The value specifies the schema name (or, for ODBC v2, the owner name) of the table or tables for which the column descriptions are to be returned. This argument is optional. The default is no schema name constraint on the query results. If a driver supports schemas for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string (all spaces) denotes those tables that do not have schemas. This argument identifies the schema name or names using a string search pattern. (See the topic InstantSQL Search Patterns for information on search patterns.) For letters in this string, case is significant. If the driver does not support schema names, this argument must be omitted (or specified as OMITTED); otherwise, an ODBC error may occur with SQLSTATE S1C00, optional feature not implemented.
ColumnName (input). This argument must refer to a nonnumeric value. The value specifies the column name or names for which the column descriptions are to be returned. This argument is optional. The default is to return all columns. This argument identifies the column name or names using a string search pattern. (See the topic InstantSQL Search Patterns for information on search patterns.) For letters in this string, case is significant.
Valid query handle values returned by InstantSQL are in the range 1 through 9999. The application program may use the value zero in a query handle data item to indicate that the query has not been created or has been dropped.
After the SQL QUERY COLUMNS statement is executed, SQL FETCH ROW statements can be executed to fetch the column description rows. Each row describes one column of the specified table. The result set is ordered by TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and ORDINAL_POSITION (see result set description below for an explanation of these result column names).
Note When an application works with an ODBC 2.x driver, no ORDINAL_POSITION column is returned in the result set. As a result, when working with ODBC 2.x drivers, the order of the columns in the column list returned by the SQL QUERY COLUMNS statement is not necessarily the same as the order of the columns returned when the application performs a SELECT statement on all columns in that table, that is, a "SELECT * FROM TableName".
Note The SQL QUERY COLUMNS statement might not return all the columns of the table. For example, a driver might not return information about pseudo-columns, such as the Oracle ROWID column. Applications may use any valid column, regardless of whether it is returned by the SQL QUERY COLUMNS statement.
Note Some columns that can be returned by the SQL QUERY INDEXES statement are not returned by the SQL QUERY COLUMNS statement. For example, the SQL QUERY COLUMNS statement does not return the columns in an index created over an expression or filter, such as SALARY + BENEFITS or DEPT = 0012.
The SQL GET DATA statement can be used to transfer the data that describes the column or the SQL BIND COLUMN statement can be used so that the data is transferred into COBOL data items as part of the SQL FETCH ROW statement.
The SQL START QUERY statement can be used with the returned query handle value to re-start the table-columns query from the beginning, if desired. The SQL QUERY COLUMNS statement starts the table-columns query, so an initial SQL START QUERY statement is not required.
The SQL END QUERY statement can be used to end the table-columns query.
A successful SQL QUERY COLUMNS statement sets the type of the query identified by the value of the QueryHandle argument to sql-QryColumns. (The type of a query can be obtained using the SQL DESCRIBE QUERY statement.)
A successful SQL QUERY COLUMNS statement sets the status of the query identified by the value of the QueryHandle argument to sql-StatExecuting. (The current status of a query can be obtained using the SQL DESCRIBE QUERY statement.)
The SQL QUERY COLUMNS statement can be used to determine the existence of a table. If the table named in the TableName argument does not exist, the result set will contain zero rows indicating the table has no columns and therefore does not exist. However, care must be taken that the case of the TableName argument value is correct for the data source. For example, DB2 stores table names in upper case in the catalog, so specifying a table name with any lower-case letters will always return zero columns for a DB2 data source. The SQL DESCRIBE CONNECTION statement can be used to determine the case used to store identifiers such as table names for a connected data source.
The columns in the result set for a table-columns query are as follows:
|
# |
Column Name (1) |
Data Type (2) |
Comments |
|
1 |
TABLE_CAT
(TABLE_QUALIFIER) |
Varchar |
Catalog
name; NULL if not applicable to the data source. If a driver supports catalogs for some tables but not for
others, such as when the driver retrieves data from different DBMSs, it
returns an empty string ("") for those tables that do not have
catalogs. |
|
2 |
TABLE_SCHEM
(TABLE_OWNER) |
Varchar |
Schema
name; NULL if not applicable to the data source. If a driver supports schemas for some tables but not for
others, such as when the driver retrieves data from different DBMSs, it
returns an empty string ("") for those tables that do not have
schemas. |
|
3 |
TABLE_NAME |
Varchar |
Table
name. |
|
4 |
COLUMN_NAME |
Varchar |
Column
name. The driver returns an empty string for a column that does not have a
name. |
|
5 |
DATA_TYPE |
Smallint |
SQL
data type of the column. This may be
an ODBC SQL data type or a driver-specific SQL data type. For datetime and interval data types, this
column returns the concise data type (such as SQL_TYPE_DATE or SQL_INTERVAL_YEAR_TO_MONTH,
rather than the non-concise data type such as SQL_DATETIME or SQL_INTERVAL). The
data types that are returned for ODBC 3.x and ODBC 2.x applications may be
different. |
|
6 |
TYPE_NAME |
Varchar |
Data
source–dependent data type name; for example, "CHAR",
"VARCHAR", "MONEY", "LONG VARBINAR", or
"CHAR ( ) FOR BIT DATA". |
|
7 |
COLUMN_SIZE
(PRECISION) |
Integer |
If
DATA_TYPE is SQL_CHAR or SQL_VARCHAR, then this column contains the maximum
length in characters of the column. For
datetime data types, this is the total number of characters required to
display the value when converted to characters. For numeric data types, this is either the total number of
digits or the total number of bits allowed in the column, according to the
NUM_PREC_RADIX column. For interval
data types, this is the number of characters in the character representation
of the interval literal (as defined by the interval leading precision). |
|
8 |
BUFFER_LENGTH
(LENGTH) |
Integer |
The
length in bytes of data transferred to the C data buffer on an SQL FETCH ROW
operation. For numeric data, this
size may be different than the size of the data stored on the data source. This value is the same as the COLUMN_SIZE
column for character or binary data.
This size is generally different from the size of the data transferred
to the COBOL data item for an SQL GET DATA statement. |
|
9 |
DECIMAL_DIGITS
(SCALE) |
Smallint |
The
total number of significant digits to the right of the decimal point. For SQL_TYPE_TIME and SQL_TYPE_TIMESTAMP,
this column contains the number of digits in the fractional seconds
component. For the other data types,
this is the decimal digits of the column on the data source. For interval data types that contain a
time component, this column contains the number of digits to the right of the
decimal point (fractional seconds).
For interval data types that do not contain a time component, this
column is 0. NULL
is returned for data types where decimal digits is not applicable. |
|
10 |
NUM_PREC_RADIX
(RADIX) |
Smallint |
For
numeric data types, either 10 or 2. If
it is 10, the values in COLUMN_SIZE and DECIMAL_DIGITS give the number of
decimal digits allowed for the column.
For example, a DECIMAL(12,5) column would return a NUM_PREC_RADIX of
10, a COLUMN_SIZE of 12, and a DECIMAL_DIGITS of 5; a FLOAT column could
return a NUM_PREC_RADIX of 10, a COLUMN_SIZE of 15 and a DECIMAL_DIGITS of
NULL. If
it is 2, the values in COLUMN_SIZE and DECIMAL_DIGITS give the number of bits
allowed in the column. For example, a
FLOAT column could return a RADIX of 2, a COLUMN_SIZE of 53, and a
DECIMAL_DIGITS of NULL. NULL
is returned for data types where NUM_PREC_RADIX is not applicable. |
|
11 |
NULLABLE |
Smallint |
sql-No-Nulls if the column does not accept NULL values. sql-Nullable if the column accepts NULL values. sql-Nullable-Unknown if it is not known if the column accepts NULL
values. The
value returned for this column is different from the value returned for the
IS_NULLABLE column (column number 18).
The NULLABLE column indicates with certainty that a column can accept
NULLs, but cannot indicate with certainty that a column does not accept
NULLs. The IS_NULLABLE column
indicates with certainty that a column cannot accept NULLs, but cannot
indicate with certainty that a column accepts NULLs. |
|
12 |
REMARKS |
Varchar |
A
description of the column. |
|
13 |
COLUMN_DEF |
Varchar |
The
default value of the column. The
value in this column should be interpreted as a string if it is enclosed in
quotation marks. If
NULL was specified as the default value, then this column is 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 was specified, then
this column is NULL. The
value of COLUMN_DEF can be used in generating a new column definition, except
when it contains the value TRUNCATED. |
|
14 |
SQL_DATA_TYPE |
Smallint |
SQL
data type, as it appears in the SQL_DESC_TYPE record field in the IRD. This may be an ODBC SQL data type or a
driver-specific SQL data type. This
column is the same as the DATA_TYPE column, with the exception of datetime
and interval data types. This column
returns the non-concise data type (such as SQL_DATETIME or SQL_INTERVAL),
rather than the concise data type (such as
SQL_TYPE_DATE or SQL_INTERVAL_YEAR_TO_MONTH) for datetime and
interval data types. If this column
returns SQL_DATETIME or SQL_INTERVAL, the specific data type can be
determined from the SQL_DATETIME_SUB column |
|
15 |
SQL_DATETIME_SUB |
Smallint |
The
subtype code for datetime and interval data types. For other data types, this column returns a NULL. |
|
16 |
CHAR_OCTET_LENGTH |
Integer |
The
maximum length in bytes of a character or binary data type column. For all other data types, this column
returns a NULL. |
|
17 |
ORDINAL_POSITION |
Integer |
The
ordinal position of the column in the table.
The first column in the table is number 1. |
|
18 |
IS_NULLABLE |
Varchar |
"NO" if the column does not include NULLs. "YES" if the column could include NULLs. This
column returns a zero-length string if nullability is unknown. ISO
rules are followed to determine nullability.
An ISO SQL – compliant DBMS may not return an empty string. The
value returned for this column is different from the value returned for the
NULLABLE column (column number 11).
See the description of the NULLABLE column for the reason. |
Note 1 The first column names listed are those returned by ODBC drivers that conform to ODBC version 3.0 and later. ODBC drivers that conform to prior versions return the column names listed second in parentheses when the column name is different and only return columns 1 through 12. Because of these version issues, getting or binding data for a table-columns query should use column numbers rather than column names. (The version of ODBC supported by the driver can be obtained in sql-ConDriverODBCVersion by using the SQL DESCRIBE CONNECTION statement with an extended connection description group.)
Note 2 The lengths of VARCHAR columns for the result set are not shown because the actual lengths depend on the data source. To determine the actual lengths of the TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME columns, an application can use the SQL DESCRIBE CONNECTION statement with an extended connection description group. The maximum lengths of the indicated columns will then be available in sql-ConMaxCatalogNameLength, sql-ConMaxSchemaNameLength, sql-ConMaxTableNameLength, and sql-ConMaxColumnNameLength, respectively. Also, the SQL DESCRIBE COLUMN statement can be used to obtain the maximum length of any column in the result set.
Some drivers may return additional driver-specific columns. The existence of such columns can be determined using the SQL DESCRIBE QUERY statement to obtain the sql-QryNoCols data item and comparing its value to 18 (12 for ODBC 2.x drivers). The descriptions of such columns can be obtained with the SQL DESCRIBE COLUMN statement. For ODBC drivers that are not ODBC version 3.0 or later conformant, driver-specific columns start with column 13. Column numbers relative to the end of the result set should be used to access driver-specific columns.
SQL
QUERY COLUMNS Statement Examples:
SQL QUERY COLUMNS
sql-QueryHandle,
sql-ConnectionHandle,
sql-TableName.
SQL QUERY COLUMNS
sql-QueryHandle,
sql-ConnectionHandle,
sql-TableName,
"MyCatalog", *>
catalog (qualifier) name
"MySchema", *> schema (owner) name
"Stock%". *> All column names beginning with
"Stock"
SQL QUERY COLUMNS
sql-QueryHandle,
sql-ConnectionHandle,
sql-TableName,
"Northwind", *> catalog (qualifier) name
"dbo". *> schema (owner) name
Copyright
©2000 Liant Software Corp. All rights
reserved.