Updating row selected by cursor
Line 6 – 11: Here I declare/define the cursor I will be using to fetch rows (records) from the file.The Select statement is used to define which columns (fields) I want, from which file, and the selection criteria in this case just the rows where FKEY is '1', and the sort order.Line 5: In the RPG program I had to provide create declare a procedure prototype of QCMDEXC, and pass it the length of the string.I can far more easily just call the QCMDEXC SQL procedure.
Lines 13 – 22: The Do loop to Fetch (read) all the rows and update.
I always add line 11, it is not necessary as without the cursor is still opened for update.
I just think it is better as it documents how the file will be used.
If I had changed many fields in the file rather than listing them all in the 01 **free 02 dcl-ds Data Ds extname('TESTFILE') 03 end-ds ; 04 exec sql SET OPTION COMMIT = *NONE ; 05 exec sql CALL QSYS2.
QCMDEXC('CPYF FROMFILE(TESTFSAVE) TOFILE(TESTFILE) MBROPT(*REPLACE)') ; 06 exec sql DECLARE C0 CURSOR FOR 07 SELECT * 08 FROM TESTFILE 09 WHERE FKEY = '1' 10 ORDER BY FKEY, FFIELD 11 FOR UPDATE ; 12 exec sql OPEN C0 ; 13 dow (1 = 1) ; 14 exec sql FETCH C0 INTO : Data DS ; 15 if (SQLCOD Lines 2 - 3: I know in this example my file only has two fields, but I like to code my examples to cover more complex scenarios.
Search for updating row selected by cursor:
Lines 15 – 17: This is the equivalent of end of file processing.