IDENTIFICATION DIVISION.

PROGRAM-ID. EXSINQ01.

*

* InstantSQL Status Inquiry Example 01.

*

*This example prepares a SELECT query for all

*the columns in the Employees table for a specified

*department code value. The example then gets the

*column numbers for the employee Number and Surname

*columns before listing these columns for department

*code 20. Before disconnecting from the data source,

*the example gets the connection status to ensure that

*there are no queries associated with the connection.

*

*Note: To simplify the example, error handling

* is incomplete.

DATA DIVISION.

WORKING-STORAGE SECTION.

COPY "lisqlall.cpy".

01 ws-DataItems.

05 ws-number-ColNo PIC 9(4) BINARY.

05 ws-surname-ColNo PIC 9(4) BINARY.

01 ws-emp-data.

10 ws-number PIC 9(09). *> employee number

10 ws-surname PIC X(30). *> employee surname

10 ws-deptcode PIC 9(02). *> department code

PROCEDURE DIVISION.

A.

*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.

*Prepare SELECT query.

SQL PREPARE QUERY sql-QueryHandle

sql-ConnectionHandle

"SELECT * FROM Employees WHERE DeptCode = ?"

*Get query description.

SQL DESCRIBE QUERY sql-QueryHandle

sql-Query-Description.

DISPLAY "Query has "

sql-QryNoCols OF sql-Query-Description

" result columns.".

*Get column descriptions and save column numbers

*for employee Number and Surname columns.

SQL DESCRIBE COLUMN sql-QueryHandle

"Number" sql-Column-Description.

MOVE sql-ColNumber OF sql-Column-Description

TO ws-number-ColNo.

SQL DESCRIBE COLUMN sql-QueryHandle

"Surname" sql-Column-Description.

MOVE sql-ColNumber OF sql-Column-Description

TO ws-surname-ColNo.

*Bind query parameter.

SQL BIND PARAMETER sql-QueryHandle

1 sql-Integer sql-Param-Input ws-deptcode OMITTED.

*Set parameter value and start (execute) query.

MOVE 20 TO ws-deptcode.

SQL START QUERY sql-QueryHandle.

*Display result set from query of department code 20.

PERFORM UNTIL NOT sql-OK

SQL FETCH ROW sql-QueryHandle

IF sql-OK

SQL GET DATA sql-QueryHandle

ws-number-ColNo ws-number OMITTED

ws-surname-ColNo ws-surname OMITTED

DISPLAY "Employee #" ws-number

" Surname: " ws-surname

END-IF

END-PERFORM

*End query.

SQL END QUERY sql-QueryHandle.

*Get connection description.

SQL DESCRIBE CONNECTION sql-ConnectionHandle

sql-Connection-Description.

*Disconnect from Payroll data source if there are

*no associated queries.

IF sql-ConNoQueries of sql-Connection-Description = 0

SQL DISCONNECT DATASOURCE sql-ConnectionHandle

END-IF.

*Terminate InstantSQL and application.
SQL SHUTDOWN.

STOP RUN.

END PROGRAM EXSINQ01.