Wednesday, July 12, 2017

Updating one row at a time using SQL

update current row/record 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:

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 ;

                  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 ;

                                      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.


  1. The interesting thing about using UPDATE WHERE CURRENT OF is that the row remains locked *after* the update. You can see this using debug and DSPRCDLCK. To unlock the row, fetch another row, or close the cursor or compile with CLOSQLCSR = *ENDMOD, or probably a few other options I can't think of right now.

    1. Why would one want to circumvent the locking mechanism for UPDATE WHERE CURRENT OF? You would not do this with native RPG READ for update so why would you for SQL UPDATE? This is not an issue regarding CLOSQLCST=*ENDMOD.

    2. I would ask why would someone want to leave a row locked after the module returns (exiting loop before a fetch hit EOF, did not close cursor)? In general, you probably don't.

      Imagine a part master child part that is used in hundreds of parent parts and a process needs to add to the manufacturing allocations via a commonly used sub-procedure when creating a job order BOM. If you do that update and leave the row locked, dozens of jobs could suddenly throw an CPF5032 or CPF5027 message (row locked). But if you have a reason to leave the row locked after the update, you can do that.

      Generally, RPG developers may think updating a row also releases a lock. In this case, no.


  2. I like SQL vs Native, but it should be pointed out Block updates will improve performance. Data sets are the word of the day for the future...IBM drilled into me think data sets not file records...but that is maybe another post.

    1. I have written about retrieving more than one at a time in several posts, starting with: SQL blocking fetches, getting more than one row at a time

  3. Good to know this is possible

  4. Excellent article, Simon! Scott is correct, it would be best to perform set processing where the UPDATE effects many rows, and doing this is a lot faster than any RPG native RLA I/O. Its astounding how the UPDATE gets "abused" way too often by 1 row at a time instead of the developer taking advantage of set processing (when is can be applied). As to set processing, commitment-control should be used as much as possible (all rows or none get updated).

  5. If you want to translate the RPG source code using the %FIELDS() BIF to the SQL Equivalent, you should list the columns to be updated within the FOR UPDATE clause.

    exec sql DECLARE C0 CURSOR FOR
    SELECT *
    WHERE FKEY = '1'

  6. 04 exec sql SET OPTION COMMIT = *NONE ;

    I believe that the statement: "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." is technically incorrect, as written (at least how I'm reading it).

    SET OPTION COMMIT = *NONE turns *commitment control* off, so an updated row gets committed to the table immediately, it does not bypass the update of the record in the table.


To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.