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
not NULL

Table name.

4

COLUMN_NAME

Varchar
not NULL

Column name. The driver returns an empty string for a column that does not have a name.

5

DATA_TYPE

Smallint
not NULL

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
not NULL

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
not NULL

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
not NULL

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
not NULL

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.