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-5
Initialize the Host Variables
ORDER BY postcode
FOR READ ONLY;
Example
This example declares an updatable cursor named get_by_partnum that accesses
the PARTS table. The query expression specifies all part numbers greater than or
equal to the host variable named min-partnum:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 min-partnum pic 9(4) COMP.
...
EXEC SQL END DECLARE SECTION END-EXEC.
...
EXEC SQL DECLARE get_by_partnum CURSOR FOR
SELECT partnum, partdesc, price, qty_available
FROM parts
WHERE partnum >= :min-partnum
FOR UPDATE OF price, qty_available
END-EXEC.
...
Initialize the Host Variables
Initialize the host variables you specified in the query expression in the DECLARE
CURSOR declaration. You must initialize the host variables before you execute the
OPEN statement, or these problems can occur:
•
If a host variable contains values with unexpected data types, overflow or
truncation errors can occur.
•
If a host variable contains old values from the previous execution of the program, a
subsequent FETCH statement uses these old values as the starting point to
retrieve data. As a result, the FETCH might not begin at the expected location in
the result table.
The host variables must be declared within the scope of the OPEN statement.
Open the Cursor
Use the OPEN statement to establish the result table and position the cursor before
the first row of the table. You can sort the result table if the query expression specified
in the cursor declaration includes the ORDER BY clause. Use this general syntax:
For complete syntax, see the OPEN statement in the SQL/MX Reference Manual.
For audited tables or views, use the OPEN statement to associate a cursor with a TMF
transaction. SQL/MX format tables and views are always audited. SQL/MP format
tables provide a choice of whether to audit.
OPEN cursor-name
C