SQL QUERY TYPES
Statement

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

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

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

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

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

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
(MONEY)

Smallint
not NULL

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
(AUTO_INCREMENT)

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

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-2020 Micro Focus or one of its affiliates.