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

Static Rowsets
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
7-38
Updating Rows by Using Rowset-Derived Tables
Example
This example updates the SALARY column of all rows in the EMPLOYEE table where
the jobcode value is equal to one of the values in the hva_jobcode host variable
array. The UPDATE statement is executed for each matching job code:
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
ROWSET[5] unsigned NUMERIC (4) hva_jobcode;
...
EXEC SQL END DECLARE SECTION;
...
/* Input the salary increment. */
...
/* Populate the rowset in some way. */
hva_jobcode[0] = 100;
hva_jobcode[1] = 200;
hva_jobcode[2] = 300;
hva_jobcode[3] = 400;
hva_jobcode[4] = 500;
...
EXEC SQL
UPDATE persnl.employee
SET salary = salary * :hv_inc
WHERE EXISTS
(SELECT * FROM ROWSET(:hva_jobcode) AS rs(jobcode)
WHERE jobcode = rs.jobcode);
...
The number of updated rows is stored in the ROW_COUNT field of the statement
information in the diagnostics area. Retrieve the value in the ROW_COUNT field by
using the GET DIAGNOSTICS statement.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 sqlstate pic x(5).
01 hvainc pic 9(4).
01 rs.
02 ROWSET[5] hvajobcode pic 9(4) comp.
EXEC SQL END DECLARE SECTION END-EXEC.
...
**** Input the salary increment *****
**** Populate the rowset in some way *****
Move 100 TO hvajobcode(1).
Move 200 TO hvajobcode(2).
Move 300 TO hvajobcode(3).
Move 400 TO hvajobcode(4).
Move 500 TO hvajobcode(5).
EXEC SQL
UPDATE employee
SET salary = salary * :hvainc
WHERE EXISTS
(SELECT * FROM ROWSET(:hvajobcode) AS rs(jobcode)
WHERE jobcode = rs.jobcode) END-EXEC.
...
C
COBOL