SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

Dynamic SQL Cursors
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
11-3
Steps for Using a Dynamic SQL Cursor
Figure 11-2 shows the steps presented within the complete COBOL program. These
steps are executed in the sample program Example C-5 on page C-13.
For more information:
1. Declare Required Host Variables on page 11-4
2. Prepare the Cursor Specification on page 11-4
3. Declare the Cursor on page 11-4
4. Initialize the Dynamic Input Parameters on page 11-5
5. Open the Cursor on page 11-5
6. Retrieve the Values
on page 11-5
7. Process the Retrieved Values
on page 11-6
8. Fetch the Next Row on page 11-6
9. Close the Cursor and Deallocate the Prepared Statement on page 11-6
Figure 11-2. Using a Dynamic SQL Cursor in a COBOL Program
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 HOSTVAR 9(4) COMP.
01 CURSPEC PIC X(80).
...
EXEC SQL END DECLARE SECTION END-EXEC.
...
MOVE "SELECT column1, column2, column3"
- " FROM catalog.schema.table"
- " WHERE column1 = CAST(? AS sql_type)"
TO CURSPEC.
...
EXEC SQL PREPARE cursor_spec FROM :CURSPEC END-EXEC.
...
EXEC SQL DECLARE sql_cursor CURSOR FOR cursor_spec END-EXEC.
...
MOVE INITIAL-VALUE TO HOSTVAR.
...
EXEC SQL OPEN sql_cursor USING :HOSTVAR END-EXEC.
...
EXEC SQL FETCH sql_cursor
INTO :HOSTVAR1, :HOSTVAR2, :HOSTVAR3 END-EXEC.
...
* Process values in the host variable(s).
...
* If last row has not been processed,
* branch back to fetch another row.
...
EXEC SQL CLOSE sql_cursor END-EXEC.
...
EXEC SQL DEALLOCATE PREPARE cursor_spec END-EXEC.
...
COBOL
1
2
3
4
5
6
7
8
9