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

COBOL Sample Programs
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
C-14
Using a Dynamic SQL Cursor
PROCEDURE DIVISION.
START-LABEL.
DISPLAY "This example uses a dynamic cursor.".
EXEC SQL WHENEVER SQLERROR GOTO sqlerrors END-EXEC.
MOVE "SELECT partnum, partdesc, price, qty_available"
& " FROM samdbcat.sales.parts"
& " WHERE qty_available <= CAST(? AS NUMERIC(5))"
TO curspec.
* Prepare cursor specification.
EXEC SQL PREPARE cursor_spec FROM :curspec END-EXEC.
* Declare the dynamic cursor from the prepared statement.
EXEC SQL
DECLARE get_by_partnum CURSOR FOR cursor_spec
END-EXEC.
* Initialize the parameter in the WHERE clause.
DISPLAY "Enter the quantity to initiate the order: ".
ACCEPT in-qty-available.
* Open the cursor using the values of the dynamic parameter.
EXEC SQL
OPEN get_by_partnum USING :in-qty-available
END-EXEC.
* Fetch the first row of result from table.
EXEC SQL
FETCH get_by_partnum
INTO :hv-partnum, :hv-partdesc,
:hv-price, :hv-qty-available
END-EXEC.
* Fetch rest of the results from table.
PERFORM UNTIL sqlstate = sqlstate-nodata
MOVE hv-partnum TO print-partnum
MOVE hv-qty-available TO print-qty
DISPLAY print-line
EXEC SQL FETCH get_by_partnum
INTO :hv-partnum, :hv-partdesc,
:hv-price, :hv-qty-available
END-EXEC.
END-PERFORM.
* Close the cursor.
EXEC SQL CLOSE get_by_partnum END-EXEC.
IF sqlstate = sqlstate-ok
DISPLAY "The program completed successfully.".
STOP RUN.
Example C-5. Using a Dynamic SQL Cursor (page 2 of 3)