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-6
Process the Retrieved Values
Alternatively, use this general syntax when transferring values to an output descriptor
area (that has been allocated and described):
For further information on using this method, see Dynamic SQL Cursors Using
Descriptor Areas on page 11-10.
Process the Retrieved Values
After the FETCH statement returns the values to the host variables, your program can
process the values. For example, you can test one or more values and then perform
one of these operations:
•
Update columns in the current row by using a positioned UPDATE statement.
•
Delete the current row by using a positioned DELETE statement.
•
List or display the values.
•
Save the values in an array and process them later.
After you process a row, execute the FETCH statement to retrieve the next row.
Fetch the Next Row
Program control returns to the FETCH statement. Use the FETCH statement to
position the cursor at the next row of the result table. Continue executing this loop until
you have processed all rows specified by the query expression. After the FETCH
statement has retrieved the last row, a subsequent FETCH causes a no-data exception
(SQLSTATE is 02000 and SQLCODE is 100).
Close the Cursor and Deallocate the Prepared Statement
The CLOSE statement closes the cursor and releases the result table established by
the OPEN statement. After the CLOSE statement executes, the result table no longer
exists. To use the same cursor again, you must reopen it by using an OPEN
statement. If the cursor specification contains a dynamic input parameter, the host
variable in the USING clause of the OPEN statement can be initialized with a new
value before the cursor is opened.
A CLOSE statement must execute within the scope of all other SQL statements that
refer to the cursor, including the DECLARE CURSOR, OPEN, FETCH, INSERT, and
DELETE statements:
For complete syntax, see the CLOSE statement in the SQL/MX Reference Manual.
At this point, program control could return to step described in Initialize the Dynamic
Input Parameters on page 11-5 to continue fetching rows with another input parameter
value for the dynamic cursor.
FETCH cursor-name USING SQL DESCRIPTOR descriptor-name
CLOSE cursor-name