IDENTIFICATION DIVISION.

PROGRAM-ID. EXAQRY01.

*

* InstantSQL Advanced Query Example 01.

*

*This example requests a department code and uses

*a parameterized query to display the employee number

*and surname for all employees in that department.

*The process is repeated until a department code of

*99 is entered. The example also improves its

*efficiency by binding the result columns to COBOL

*data items.

*

*Note: To simplify the example, error handling

* is incomplete.

DATA DIVISION.

WORKING-STORAGE SECTION.

COPY "lisqlall.cpy".

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.

SQL CONNECT DATASOURCE sql-ConnectionHandle

"Payroll"

"MyName"

"MyPassword".

IF NOT sql-OK

DISPLAY "<Error connecting to Payroll DS.>"

STOP RUN

END-IF.

SQL PREPARE QUERY sql-QueryHandle

sql-ConnectionHandle

"SELECT Number, Surname FROM Employees

- " WHERE DeptCode = ?".

SQL BIND PARAMETER sql-QueryHandle

1 sql-Integer sql-Param-Input

ws-deptcode OMITTED.

SQL BIND COLUMN sql-QueryHandle

1 ws-number OMITTED

2 ws-surname OMITTED.

PERFORM WITH TEST AFTER UNTIL ws-deptcode = 99

DISPLAY "Department Code: "

ACCEPT ws-deptcode COL 0

IF ws-deptcode NOT = 99

SQL START QUERY sql-QueryHandle

PERFORM WITH TEST AFTER UNTIL NOT sql-OK

SQL FETCH ROW sql-QueryHandle

IF sql-OK

DISPLAY "Employee #" ws-number

" Surname: " ws-surname

END-IF

END-PERFORM

END-IF

END-PERFORM.

SQL END QUERY sql-QueryHandle.

SQL DISCONNECT DATASOURCE sql-ConnectionHandle.

SQL SHUTDOWN.

STOP RUN.

END PROGRAM EXAQRY01.