Micro Focus Visual COBOL 2.3 Update 2 for Eclipse (Windows) > Programming > Data Access > Database Access > Reference > OpenESQL > Embedded SQL Statements > QUERY ODBC

QUERY ODBC

Delivers a results set in the same way as a SELECT statement, and must therefore be associated with a cursor via DECLARE and OPEN, or DECLARE, PREPARE and OPEN.

Syntax, Format 1:

>>--EXEC SQL---QUERY ODBC---.-COLUMN--.------------------>
                            +-COLUMNS-+

 >---.--------------------------.--.------------------.--->
     +-QUALIFIER qualifier_name-+  +-OWNER owner_name-+

 >--.----------------------.--.------------------------.-->
    +-TABLENAME table_name-+  +-COLUMNNAME column_name-+

 >---END-EXEC---<>

Syntax, Format 2:

>>--EXEC SQL---QUERY ODBC---.-DATATYPE--.---------------->
                            +-DATATYPES-+

 >---.-------------------------.---END-EXEC---><
     +-TYPE--.-datatype_name--.+
             +-BIGINT---------+
             +-BINARY---------+
             +-BIT------------+
             +-CHAR-----------+
             +-DATE-----------+
             +-DECIMAL--------+
             +-DOUBLE---------+
             +-FLOAT----------+
             +-INTEGER--------+
             +-LONG VARBINARY-+
             +-LONG VARCHAR---+
             +-NUMERIC--------+
             +-REAL-----------+
             +-SMALLINT-------+
             +-TIME-----------+
             +-TIMESTAMP------+
             +-TINYINT--------+
             +-VARBINARY------+
             +-VARCHAR--------+

Syntax, Format 3:

>>--EXEC SQL---QUERY ODBC---.-TABLE--.------------------>
                            +-TABLES-+
>---.--------------------------.--.------------------.--->
    +-QUALIFIER qualifier_name-+  +-OWNER owner_name-+

 >--.----------------------.--.------------------------.-->
    +-TABLENAME table_name-+  +-TYPE tabletype_name----+

 >--END-EXEC--<>

Parameters:

`qualifier_name`	A host variable, identifier or literal which specifies a qualifier to be used to select tables. Not all ODBC drivers support qualifiers, and those that do may use them in different ways. For example, if a data source supports multiple databases, a qualifier can be used to specify which database to use. Alternatively, for drivers providing access to file based data sources, a qualifier can be used to specify a particular directory to be searched.
`owner_name`	A host variable, identifier or literal which specifies a table owner to be used to select tables. Not all ODBC drivers support table ownership.
`table_name`	A host variable, identifier or literal which specifies tables to be included in the query.
`datatype_name`	A host variable, identifier or literal which specifies a data type to be queried.
`tabletype_name`	A host variable, identifier or literal which specifies a list of table types to be included in the query.

Comments:

Search patterns consist of the legal characters for SQL identifiers plus underscore (_) which matches any single character, percent (%) which matches any sequence of zero or more characters, or a driver defined escape character which can be used to allow underscore or percent in a pattern to represent themselves rather than a wildcard.

If a search pattern parameter is not supplied, a pattern of % is used, which will match all relevant dictionary entries.

For table queries the following special rules apply:

If qualifier-name is % and owner-name and table-name are empty strings, the results set consists of a list of valid qualifiers at the data source. All columns apart from TABLE_QUALIFIER in the results set (see below) will be null.
If owner-name is % and qualifier-name and table-name are empty strings, the results set consists of a list of valid owners at the data source. All columns apart from TABLE_OWNER in the results set (see below) will be null.
If tabletype-name is % and qualifier-name, owner-name and table-name are empty strings the results set consists of a list of valid table types at the data source. All columns apart from TABLE_TYPE in the results set (see below) will be null.
If tabletype-name is not specified, tables of all types will be returned in the results set. If it is specified it must consist of a comma separated list of table types, for example'TABLE,VIEW'.

Example:

     EXEC SQL
        DECLARE tcurs CURSOR FOR QUERY ODBC TABLES
     END-EXEC

     EXEC SQL DECLARE C1 CURSOR FOR
        QUERY ODBC TABLES OWNER :tab-owner TABLETYPE 'TABLE,VIEW'
     END-EXEC

     MOVE 'staff' to tab-name
     EXEC SQL DECLARE C2 CURSOR FOR
        QUERY ODBC COLUMNS TABLENAME :tab-name
     END-EXEC

     EXEC SQL DECLARE C3 CURSOR FOR
        QUERY ODBC DATATYPES
     END-EXEC