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-6
Retrieve the Values
The OPEN statement must execute before any FETCH statements for the cursor and
within the scope of all other SQL statements that refer to the cursor, including
DECLARE CURSOR, FETCH, UPDATE, DELETE, and CLOSE statements.
The OPEN statement does not acquire any locks unless a sort is necessary to order
the selected rows. (The FETCH statement acquires any locks associated with a
cursor.)
Example
This example opens the get_name_address cursor:
EXEC SQL OPEN get_name_address;
Retrieve the Values
Use the FETCH statement to position the cursor at the next row of the result table and
to transfer the values defined in the query expression of the cursor declaration to the
corresponding host variables. Use this general syntax:
For complete syntax, see the OPEN statement in the SQL/MX Reference Manual.
The cursor must be open when the FETCH statement executes. The FETCH
statement must also execute within the scope of all other SQL statements that refer to
the cursor, including DECLARE CURSOR, OPEN, DELETE, UPDATE, and CLOSE
statements.
For audited tables or views, the FETCH statement must execute within the same
transaction as the OPEN statement. This is not true for WITH HOLD cursors. 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 retrieves information from the PARTS table:
Example
EXEC SQL BEGIN DECLARE SECTION;
struct parts_type { /* host variables */
unsigned short partnum;
char partdesc[19];
long price;
long qty_available
} ;
struct parts_type parts_rec1, parts_rec2;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE list_by_partnum CURSOR FOR
SELECT partnum, partdesc, price, qty_available
FROM parts
WHERE partnum >= :parts_rec1.partnum
ORDER BY partnum
READ ONLY;
FETCH cursor-name INTO :hostvar [,:hostvar ]...
C