Updating one row at a time using SQL

I have been asked to write some more about using SQL embedded in RPG programs. One question I have been asked by several people is how to update a row from a file that had been previously Fetched, just like READ and UPDATE using RPG's native database access. I thought it would be useful to show examples using both RPG native database access and SQL.

First I need a file, as this is for example purposes it very simple, just two fields and one key field:

01 A R TESTFILER 02 A FKEY 5A 03 A FFIELD 5A 04 A K FKEY

This file contains seven records:

FKEY FFIELD 1 1 1 2 2 1 1 3 3 1 2 2 1 4

What I want to do is to read/fetch all the records where the key field is equal to '1' and change the value of the second field to 'A'. I am sure we all could write a simple program to do this in RPG using its native database access. Mine looks like:

01 **free 02 dcl-pr QCMDEXC extpgm ; 03 *n char(100) options(*varsize) const ; 04 *n packed(15:5) const ; 05 end-pr ; 06 dcl-f TESTFILE usage(*update) keyed usropn ; 07 QCMDEXC('CPYF FROMFILE(TESTFSAVE) + TOFILE(TESTFILE) + MBROPT(*REPLACE)':56) ; 08 open TESTFILE ; 09 setll ('1') TESTFILER ; 10 dow (1 = 1) ; 11 reade ('1') TESTFILER ; 12 if (%eof) ; 13 leave ; 14 endif ; 15 FFIELD = 'A' ; 16 update TESTFILER %fields(FFIELD) ; 17 enddo ; 18 close TESTFILE ; 19 *inlr = *on ;

Line 1: As this program was written on an IBM i running 7.3 I am going to use totally free RPG.

Lines 2 – 5: As I will be calling the QCMDEXC API program I need to define a procedure prototype for the API. More on why I am using QCMDEXC in a few lines.

Line 6: I need to define the file, TESTFILE, with a DCL-F statement. This file will be used for update, it is keyed, and user opened.

Line 7: As this is a test program I want to be able to run this multiple times to make sure I update the right field with the right value. Rather than resetting the fields in the file I am going to copy the records from a "saved" file before any other processing. This also gives me a excuse to show again the differences in using QCMDEXC in a RPG program and in SQL. As I had defined QCMDEXC as a external procedure previously I can call it as I would a procedure. Rather than using variables to pass the Copy File command to the API I am using strings for both parameters.

Line 8: If I had defined TESTFILE without the user open the file would have been opened at program initialization, and the Copy File command would have failed. By using the user open in the file's definition the file remains close until I open it. Which I do here.

Line 9: This SETLL operation shows that I don't have to use a variable for the key field. I can use a string instead.

Lines 10 – 17: My Do loop to read all of the records in the file.

Line 11: I can also use a string for the key when using the READE operation.

Lines 12 – 14: If end of file is encountered exit the Do loop.

Line 15: Change the value of the field FFIELD.

Line 16: As I only want to update one field I am using the %FIELDS built in function. This will leave the value in the other field unchanged.

Line 18: As the file is user open I need to close it.

If I had changed many fields in the file rather than listing them all in the %FIELDS built in function I could have just used the UPDATE operation like this:

16 update TESTFILER ;

The danger is in a complex program I could have changed the value in a field in another file with the same name as one in this file. By using just the UPDATE operation that field would be changed. But If I had update one hundred fields in a file I would probably not list them all in the %FIELDS and just used the UPDATE operation.

And onto the equivalent using embedded SQL in my RPG:

01 **free 02 dcl-ds DataDs 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 :DataDS ; 15 if (SQLCOD <> 0) ; 16 leave ; 17 endif ; 18 FFIELD = 'A' ; 19 exec sql UPDATE TESTFILE 20 SET FFIELD = :FFIELD 21 WHERE CURRENT OF C0 ; 22 enddo ; 23 exec sql CLOSE C0 ; 24 *inlr = *on ;

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. With this in mind I have coded a data structure to be like my file TESTFILE. I will be using the data structure to receive the input from the SQL Fetch.

Line 4: As this is only a test I do not want to commit changes to the file, therefore I am using the SET OPTION to turn it off. I can do this when compiling the program too, but this makes absolutely sure just in case the person who compiles the program forgets to change the compile command parameter.

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.

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. 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.

Line 12: All cursors have to be opened before they can be used.

Lines 13 – 22: The Do loop to Fetch (read) all the rows and update.

Line 14: A fetch is like a read, it retrieve a row (record) from the file. By using a data structure that is defined to be the same as the file, lines 2 and 3, I can fetch directly into it, rather than have to list individual columns (fields).

Lines 15 – 17: This is the equivalent of end of file processing. If the SQL Code, SQLCOD is not zero then some error was encountered. In my experience the most common error is end of file.

Lines 19 – 21: As I only want to update one column (field) I use SQL's update statement giving the column name I want to change with the value I want to change it to. The part to notice is line 21, this tells the cursor to update the cursor's current row.

Line 23: Having finished with the cursor I need to close it.

If I want to update all the columns in the cursor's current row I would change my update statement to be:

19 exec sql UPDATE TESTFILE 20 SET ROW = :DataDs 21 WHERE CURRENT OF C0 ;

Same caution needs to be taken with this approach as it does with just using the update operation code without the %FIELDS built in function. Line 20 shows that I am setting the row to be the same as the data structure I fetched the data into. I could use another data structure if I so desired. It would just need to be defined to be the same as the file being updated.

As IBM works to improve the efficiency of data base access using SQL, rather than RPG, this is another good reason to write your programs using SQL for database access.

 

This article was written for IBM i 7.3, and should work for earlier releases too.