|
SQL QUERY TYPES |
|
< > Browse Statements Query Statements Connection Statements Example Flow Chart Table of Contents
The SQL QUERY TYPES statement returns a result set that lists and describes the data types supported by the data source identified by an InstantSQL connection handle. 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 data-types 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 data-types query. This value must have been returned by a successful SQL CONNECT DATASOURCE statement.
DataType (input). This argument must specify a signed numeric integer value. The value identifies the SQL data type or types to include in the result set. This argument is optional. The default is sql-All-Types, which causes all the data types for the specified connection to be included in the result set. The data type must be one of the SQL data types defined in lisqldef.cpy or a driver specific data type. See the topic SQL Data Types for more information on data types.
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 TYPES statement is executed, SQL FETCH ROW statements can be executed to fetch the data type description rows. Each row describes one data type supported by the connection. The result set is ordered by DATA_TYPE (see result set description below for an explanation of this result column name) and then by how closely the data type maps to the corresponding ODBC SQL data type. Data types defined by the data source take precedence over user-defined data types. For example, suppose that a data source defined INTEGER and COUNTER data types, where COUNTER is auto-incrementing, and that a user-defined data type WHOLENUM has also been defined. These would be returned in the order INTEGER, WHOLENUM, and COUNTER, because WHOLENUM maps closely to the ODBC SQL data type SQL_INTEGER, while the auto-incrementing data type, even though supported by the data source, does not map closely to an ODBC SQL data type.
The SQL GET DATA statement can be used to transfer the data that describes the data type 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 data-types query from the beginning, if desired. The SQL QUERY TYPES statement starts the data-types query, so an initial SQL START QUERY statement is not required.
The SQL END QUERY statement can be used to end the data-types query.
A successful SQL QUERY TYPES statement sets the type of the query identified by the value of the QueryHandle argument to sql-QryTypes. (The type of a query can be obtained using the SQL DESCRIBE QUERY statement.)
A successful SQL QUERY TYPES 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.)
Note The SQL QUERY TYPES statement might not return all data types. For example, a driver might not return user-defined data types. Applications can use any valid data type, regardless of whether it is returned by the SQL QUERY TYPES statement.
The data types returned by the SQL QUERY TYPES statement are those supported by the data source. They are intended for use in Data Definition Language (DDL) statements. Drivers may return result set data using data types other than the types returned by the SQL QUERY TYPES statement. In creating the result set for a catalog function, that is, those functions that support the InstantSQL browse statements, the driver might use a data type that is not supported by the data source.
The columns in the result set for a data-types query are as follows:
|
# |
Column Name (1) |
Data Type (2) |
Comments |
|
1 |
TYPE_NAME |
Varchar |
Data
source–dependent data type name; for example, "CHAR()",
"VARCHAR()", "MONEY", "LONG VARBINARY", or
"CHAR ( ) FOR BIT DATA". Applications must use this name in CREATE
TABLE and ALTER TABLE statements. |
|
2 |
DATA_TYPE |
Smallint |
SQL
data type. This may be an ODBC SQL
data type or a driver-specific SQL data type. For datetime or interval data types, this column returns the
concise data type (such as SQL_TYPE_TIME or SQL_INTERVAL_YEAR_TO_MONTH). |
|
3 |
COLUMN_SIZE
(PRECISION) |
Integer |
The
maximum column size that the server supports for this data type. For numeric data, this is the maximum
precision. For string data, this is
the length in characters. For
datetime data types, this is the length in characters of the string
representation (assuming the maximum allowed precision of the fractional
seconds component). NULL is returned
for data types where column size is not applicable. 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). |
|
4 |
LITERAL_PREFIX |
Varchar |
Character
or characters used to prefix a literal; for example, a single quotation mark (')
for character data types or 0x for binary data types; NULL is returned for
data types where a literal prefix is not applicable. |
|
5 |
LITERAL_SUFFIX |
Varchar |
Character
or characters used to terminate a literal; for example, a single quotation
mark (') for character data types; NULL is returned for data types where a
literal suffix is not applicable. |
|
6 |
CREATE_PARAMS |
Varchar |
A
list of keywords, separated by commas, corresponding to each parameter that the
application may specify in parentheses when using the name that is returned
in the TYPE_NAME column. The keywords
in the list may be any of the following:
length, precision, scale. They appear in the order that the syntax
requires that they be used. For
example, CREATE_PARAMS for DECIMAL would be "precision,scale";
CREATE_PARAMS for VARCHAR would be "length". NULL is returned if there are no parameters
for the data type definition; for example, INTEGER. The
driver supplies the CREATE_PARAMS text in the language of the country where
it is used. |
|
7 |
NULLABLE |
Smallint |
Whether
the data type accepts a NULL value: sql-No-Nulls if the data type does not accept NULL values. sql-Nullable if the data type accepts NULL values. sql-Nullable-Unknown if it is not known whether the data type accepts
NULL values. |
|
8 |
CASE_SENSITIVE |
Smallint |
Whether
a character data type is case-sensitive in collations and comparisons: sql-True if the data type is a character data type and is
case-sensitive. sql-False if the data type is not a character data type or is
not case-sensitive. |
|
9 |
SEARCHABLE |
Smallint |
How
the data type is used in a WHERE clause: sql-Pred-None if the column may not be used in a WHERE
clause. (This is the same as the SQL_UNSEARCHABLE value in ODBC 2.x.) sql-Pred-Char if the column may be used in a WHERE clause,
but only with the LIKE predicate. (This is the same as the
SQL_LIKE_ONLY value in ODBC 2.x.) sql-Pred-Basic if the column may be used in a WHERE clause
with all the comparison operators except LIKE (comparison, quantified
comparison, BETWEEN, DISTINCT, IN, MATCH, and UNIQUE).
(This is the same as the SQL_ALL_EXCEPT_LIKE value in ODBC 2.x.) sql-Pred-Searchable if the column may be used in a WHERE clause
with any comparison operator. |
|
10 |
UNSIGNED_ATTRIBUTE |
Smallint |
Whether
the data type is unsigned: sql-True if the data type is unsigned. sql-False if the data type is signed. NULL
is returned if the attribute is not applicable to the data type or the data
type is not numeric. |
|
11 |
FIXED_PREC_SCALE |
Smallint |
Whether
the data type has predefined fixed precision and scale (which are data
source–specific), like a money data type: sql-True if it has predefined fixed precision and scale. sql-False if it does not have predefined fixed precision and
scale. |
|
12 |
AUTO_UNIQUE_VALUE |
Smallint |
Whether
the data type is autoincrementing: sql-True if the data type is autoincrementing. sql-False if the data type is not autoincrementing. NULL
is returned if the attribute is not applicable to the data type or the data
type is not numeric. An
application can insert values into a column having this attribute, but
typically cannot update the values in the column. When
an insert is made into an auto-increment column, a unique value is inserted
into the column at insert time. The increment is not defined, but is data
source–specific. An application should not assume that an auto-increment
column starts at any particular point or increments by any particular value. |
|
13 |
LOCAL_TYPE_NAME |
Varchar |
Localized
version of the data source–dependent name of the data type. NULL is returned if a localized name is
not supported by the data source.
This name is intended for display only, such as in dialog boxes. |
|
14 |
MINIMUM_SCALE |
Smallint |
The
minimum scale of the data type on the data source. If a data type has a fixed scale, the MINIMUM_SCALE and
MAXIMUM_SCALE columns both contain this value. For example, an SQL_TYPE_TIMESTAMP column might have a fixed
scale for fractional seconds. NULL
is returned where scale is not applicable. |
|
15 |
MAXIMUM_SCALE |
Smallint |
The
maximum scale of the data type on the data source. If the maximum scale is not defined separately on the data
source, but is instead defined to be the same as the maximum precision, this
column contains the same value as the COLUMN_SIZE column. NULL
is returned where scale is not applicable. |
|
16 |
SQL_DATA_TYPE |
Smallint |
The
value of the SQL data type as it appears in the SQL_DESC_TYPE field of the
descriptor. This column is the same
as the DATA_TYPE column, except for interval and datetime data types. For
interval and datetime data types, the SQL_DATA_TYPE column in the result set
will return SQL_INTERVAL or SQL_DATETIME, and the SQL_DATETIME_SUB column
will return the subcode for the specific interval or datetime data type |
|
17 |
SQL_DATETIME_SUB |
Smallint |
When
the value of SQL_DATA_TYPE is SQL_DATETIME or SQL_INTERVAL, this column
contains the datetime/interval subcode.
For data types other than datetime and interval, this column is NULL. For
interval or datetime data types, the SQL_DATA_TYPE column in the result set
will return SQL_INTERVAL or SQL_DATETIME, and the SQL_DATETIME_SUB column
will return the subcode for the specific interval or datetime data type |
|
18 |
NUM_PREC_RADIX |
Integer |
If
the data type is an approximate numeric type, this column contains the value
2 to indicate that COLUMN_SIZE specifies a number of bits. For exact numeric types, this column
contains the value 10 to indicate that COLUMN_SIZE specifies a number of
decimal digits. Otherwise, this
column is NULL. |
|
19 |
INTERVAL_PRECISION |
Smallint |
If
the data type is an interval data type, then this column contains the value
of the interval leading precision.
Otherwise, this column is NULL. |
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. Because of these version issues, getting or binding data for a data-types 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. The SQL DESCRIBE COLUMN statement can be used to obtain the maximum length of any column in the result set.
Columns 16 through 19 are only returned by drivers that conform to ODBC version 3.0 or later. The existence of these columns can be determined using the SQL DESCRIBE QUERY statement.
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 19 (15 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, if any, start with column 16. Column numbers relative to the end of the result set should be used to access driver-specific columns.
SQL QUERY TYPES Statement Examples:
SQL QUERY TYPES
sql-QueryHandle,
sql-ConnectionHandle. *> list
all data types (default)
SQL QUERY TYPES
sql-QueryHandle,
sql-ConnectionHandle,
sql-All-Types. *> list all data types (explicit)
SQL QUERY TYPES
sql-QueryHandle,
sql-ConnectionHandle,
sql-Integer. *> list integer data type(s)
EVALUATE MyStringType
WHEN "ALL" MOVE sql-All-Types TO MySqlType
WHEN
"INTEGER" MOVE sql-Integer TO
MySqlType
WHEN
"VARCHAR" MOVE sql-VarChar TO
MySqlType
END-EVALUATE.
SQL QUERY TYPES
sql-QueryHandle,
sql-ConnectionHandle,
MySqlType. *> list selected data type(s)
Copyright
©2000 Liant Software Corp. All rights
reserved.