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-8
Process the Retrieved Values
•
Save the values in an array and process them later.
After you process a row, retrieve the next row by using the FETCH statement.
Continue executing this loop until you have processed all rows specified by the query
expression. After all rows have been processed, SQLSTATE is 02000, and
SQLCODE is 100.
Positioned UPDATE Statement
Use the positioned UPDATE statement to update a row in a table at the current cursor
position. You can update multiple rows, one row at a time. Before you update a row,
you can also test one or more column values if necessary. Use this general syntax:
For complete syntax, see the UPDATE statement in the SQL/MX Reference Manual.
The WHERE CURRENT OF clause specifies the row to update. The SET clause
updates each column in the current row by using the new values in the host variables.
You can use the WHERE CURRENT OF syntax only with simple SELECT statements.
To execute an UPDATE statement, the cursor must be declared as updatable (the
default is read-only) in the query expression of the DECLARE CURSOR declaration.
An UPDATE statement must execute within the scope of all other SQL statements that
refer to the cursor, including the DECLARE CURSOR, OPEN, FETCH, INSERT, and
CLOSE statements.
For audited tables and views, the UPDATE statement must also execute within the
same transaction as the OPEN and FETCH statements for the cursor.
After the positioned UPDATE statement executes, the cursor remains positioned on
the current row.
Example
Use the get_by_partnum cursor and the host variables named new_price and
new_qty to update the PARTS table:
EXEC SQL DECLARE get_by_partnum CURSOR FOR
SELECT partnum, partdesc, price, qty_available
FROM sales.parts
WHERE partnum >= :min_partnum
FOR UPDATE OF price, qty_available;
... /* Set the value of min_partnum host variable */
EXEC SQL OPEN get_by_partnum;
EXEC SQL FETCH get_by_partnum INTO ... ;
... /* Test value(s) in the current row. */
... /* Set new values in the host variables. */
/* Update the current row. */
EXEC SQL UPDATE sales.parts
UPDATE table-name
SET column = :hostvar [,column = :hostvar ]...
WHERE CURRENT OF cursor-name
C