SQL QUERY DATASOURCES Statement

 <     >         Browse Statements       Query Statements       Example       Flow Chart       Table of Contents

 

The SQL QUERY DATASOURCES statement returns a result set that lists and describes the data sources currently available.  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-sources query is to be stored.

A data-sources query is the only query that can be created without a prior connection being established by an SQL CONNECT DATASOURCE statement.  The data source names returned in the result set of a data-sources query can be used in the SQL CONNECT DATASOURCE statement to establish a connection.

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 DATASOURCES statement is executed, SQL FETCH ROW statements can be executed to fetch the data source description rows.  Each row describes one data source.

The SQL GET DATA statement can be used to transfer the data that describes the data source 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-sources query from the beginning, if desired.  The SQL QUERY DATASOURCES statement starts the data-sources query, so an initial SQL START QUERY statement is not required.

The SQL END QUERY statement can be used to end the data-sources query.

It is not possible to nest data-sources queries.  If a data-sources query has been started and not yet dropped, a second SQL QUERY DATASOURCES statement will re-start the data-sources query from the beginning and return the same query handle value as the not yet dropped data-sources query.

A successful SQL QUERY DATASOURCES statement sets the type of the query identified by the value of the QueryHandle argument to sql-QryDataSources.  (The type of a query can be obtained using the SQL DESCRIBE QUERY statement.)

A successful SQL QUERY DATASOURCES 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 columns in the result set for a data-sources query are as follows:

#

Column Name

Data Type

Comments

1

DATA_SOURCE_NAME

Varchar(128)
not NULL

The data source name.

2

DRIVER_DESCRIPTION

Varchar(255)
not NULL

Description of the driver associated with the data source.  For example, "Relativity (*.rcg)" or "SQL Server".

SQL QUERY DATASOURCES Statement Example:

 

000100 IDENTIFICATION DIVISION.

000200 PROGRAM-ID.  EX-QryDS.

000300

000400* InstantSQL Example - Query Data Sources.

000500

000600 ENVIRONMENT DIVISION.

000700

000800 DATA DIVISION.

000900 WORKING-STORAGE SECTION.

001000

001100 COPY "lisqlall.cpy".

001200

001300 01 DSNameLI                 PIC S9(9) BINARY.

001400 01 DSDescLI                 PIC S9(9) BINARY.

001500

001600 PROCEDURE DIVISION.

001700 BEGINNING.

001800

001900     SQL QUERY DATASOURCES

002000         sql-QueryHandle.

002100

002200     PERFORM WITH TEST AFTER UNTIL sql-EndOfData

002300       SQL FETCH ROW

002400           sql-QueryHandle

002500       IF sql-OK

002600         SQL GET DATA

002700           sql-QueryHandle,

002800           1, sql-DataSourceName, DSNameLI,

002900           2, sql-DataSourceDesc, DSDescLI

003000         IF sql-OK

003100           PERFORM DISPLAY-DATA-SOURCE

003200         END-IF

003300       END-IF

003400     END-PERFORM.

003500

003600     SQL END QUERY

003700         sql-QueryHandle.

003800

003900     SQL SHUTDOWN.

004000

004100     STOP RUN.

004200

004300

004400 DISPLAY-DATA-SOURCE.

004500

004600     DISPLAY " ".

004700

004800     DISPLAY "DSN  = """.

004900     IF DSNameLI > 0

005000       IF DSNameLI >

005100           LENGTH OF sql-DataSourcename

005200         MOVE LENGTH OF sql-DataSourcename TO

005300             DSNameLI

005400       END-IF

005500       DISPLAY sql-DataSourceName(1:DSNameLI) COL 0

005600     END-IF.

005700     DISPLAY """" COL 0.

005800

005900     DISPLAY "DESC = """.

006000     IF DSDescLI > 0

006100       IF DSDescLI >

006200           LENGTH OF sql-DataSourceDesc

006300         MOVE LENGTH OF sql-DataSourceDesc TO

006400             DSDescLI

006500       END-IF

006600       DISPLAY sql-DataSourceDesc(1:DSDescLI) COL 0

006700     END-IF.

006800     DISPLAY """" COL 0.

006900

007000 END PROGRAM EX-QryDS.

© Copyright 2000-2020 Micro Focus or one of its affiliates.