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.