IDENTIFICATION DIVISION.

PROGRAM-ID. EXBROW01.

*

* InstantSQL Browse Query Example 01.

*

*This example lists the data sources registered

*on the machine, tables in the Payroll data source,

*columns and, finally, indexes in the Employees table.

*

*Note: To simplify the example, error handling is incomplete.

DATA DIVISION.

WORKING-STORAGE SECTION.

COPY "lisqlall.cpy".

01 ws-LengthIndicators.

05 ws-IndexNameLenInd PIC S9(9) BINARY.

05 ws-OrdPosLenInd PIC S9(9) BINARY.

05 ws-ColumnNameLenInd PIC S9(9) BINARY.

01 ws-ColumnData.

05 ws-ColumnTypeName PIC X(64).

05 ws-OrdPos PIC 9(4).

PROCEDURE DIVISION.

A.

*List available data sources.

SQL QUERY DATASOURCES sql-QueryHandle.

PERFORM UNTIL NOT sql-OK

SQL FETCH ROW sql-QueryHandle

IF sql-OK

SQL GET DATA sql-QueryHandle

1 sql-DataSourceName OMITTED

2 sql-DataSourceDesc OMITTED

DISPLAY "DSN: " sql-DataSourceName

" DS Description: " sql-DataSourceDesc

END-IF

END-PERFORM.

SQL END QUERY sql-QueryHandle.

*Connect to Payroll data source.

SQL CONNECT DATASOURCE sql-ConnectionHandle

"Payroll"

"MyName"

"MyPassword".

IF NOT sql-OK

DISPLAY "<Error connecting to Payroll data source.>"

STOP RUN

END-IF.

*List tables in Payroll data source.

SQL QUERY TABLES sql-QueryHandle

sql-ConnectionHandle.

PERFORM UNTIL NOT sql-OK

SQL FETCH ROW sql-QueryHandle

IF sql-OK

SQL GET DATA sql-QueryHandle

"TABLE_NAME" sql-TableName OMITTED

DISPLAY "Table Name: " sql-TableName

END-IF

END-PERFORM.

SQL END QUERY sql-QueryHandle.

*List columns in Employees table of Payroll data source.

SQL QUERY COLUMNS sql-QueryHandle

sql-ConnectionHandle

"Employees".

PERFORM UNTIL NOT sql-OK

SQL FETCH ROW sql-QueryHandle

IF sql-OK

SQL GET DATA sql-QueryHandle

"COLUMN_NAME" sql-ColumnName OMITTED

"TYPE_NAME" ws-ColumnTypeName OMITTED

DISPLAY "Column Name: " sql-ColumnName

" Type: " ws-ColumnTypeName

END-IF

END-PERFORM.

SQL END QUERY sql-QueryHandle.

*List indexes of Employees table of Payroll data source.

SQL QUERY INDEXES sql-QueryHandle

sql-ConnectionHandle

"Employees".

SQL BIND COLUMN sql-QueryHandle

6 sql-IndexName ws-IndexNameLenInd

8 ws-OrdPos ws-OrdPosLenInd

9 sql-ColumnName ws-ColumnNameLenInd.

PERFORM UNTIL NOT sql-OK

SQL FETCH ROW sql-QueryHandle

IF sql-OK

DISPLAY "Index Name: "

IF ws-IndexNameLenInd = sql-Null-Data

DISPLAY "<NULL>" COL 0

ELSE

DISPLAY sql-IndexName COL 0

END-IF

DISPLAY " Ordinal Position: " COL 0

IF ws-OrdPosLenInd = sql-Null-Data

DISPLAY "<NULL>" COL 0

ELSE

DISPLAY ws-OrdPos COL 0

END-IF

DISPLAY " Column Name: " COL 0

IF ws-ColumnNameLenInd = sql-Null-Data

DISPLAY "<NULL>" COL 0

ELSE

DISPLAY sql-ColumnName COL 0

END-IF

END-IF

END-PERFORM.

SQL END QUERY sql-QueryHandle.

*Disconnect from Payroll data source.

SQL DISCONNECT DATASOURCE sql-ConnectionHandle.

*Terminate InstantSQL.

SQL SHUTDOWN.

STOP RUN.

END PROGRAM EXBROW01.