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

Static SQL Cursors
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
6-10
Fetch the Next Row
EXEC SQL DELETE FROM sales.parts
WHERE CURRENT OF get_by_partnum
END-EXEC.
* Branch back to retrieve the next row.
...
EXEC SQL CLOSE get_by_partnum END-EXEC.
Fetch the Next Row
In Retrieve the Values on page 6-6, the FETCH statement positions the cursor at the
next row of the result table and transfers the values defined in the query expression of
the DECLARE CURSOR statement to the corresponding host variables. After the
FETCH statement has retrieved all rows specified by the query expression, a
subsequent FETCH causes a no-data exception (SQLSTATE equal to 02000).
This example uses SQLSTATE to control a while loop. Fetch the first row of the result
table of the query expression. Following this first FETCH statement, construct a while
loop that executes provided SQLSTATE returns the 00000 value.
Within the while loop, your program processes the fetched row and then executes
another fetch for the next row of the result table. Following the while loop (when
SQLSTATE is no longer 00000), you can test for the end no-data condition for the
cursor and branch to the CLOSE statement if no more data is available:
Examples
EXEC SQL BEGIN DECLARE SECTION; /* host variables */
char SQLSTATE[6];
...
EXEC SQL END DECLARE SECTION;
char SQLSTATE_NODATA[6]="02000";
char SQLSTATE_OK[6]="00000";
...
EXEC SQL OPEN get_by_partnum;
...
EXEC SQL FETCH get_by_partnum ... ; /* Get first row */
while (strcmp(SQLSTATE, SQLSTATE_OK) == 0) {
if ... /* Test the value(s) in the current row */
EXEC SQL DELETE FROM sales.parts /* Delete current row */
WHERE CURRENT OF get_by_partnum ;
EXEC SQL FETCH get_by_partnum ... ; /* Get next row */
}
if (strcmp(SQLSTATE, SQLSTATE_NODATA) == 0)
EXEC SQL CLOSE get_by_partnum;
else
... /* Handle other SQLSTATE errors */
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 SQLSTATE PIC X(5).
...
EXEC SQL END DECLARE SECTION END-EXEC.
01 SQLSTATE-NODATA PIC X(5) VALUE "02000".
C
COBOL