RPGFree reading and updating a file using SQL embedded syntax

Reading and Updating a File Using SQL Embedded in RPGFree

In this tutorial, I’ll show you how to read a file from the beginning and update a field in the same file using embedded SQL in free-format RPG IV (RPGFree).

---

Step 1: Create the Physical File

First, we need to create a file and populate it with some test data. Create the file using the following SQL source:

Source member: IPFLSQL
Source file: myLib/MySourceFile

-- IPFLSQL.SQL
-- Create the physical table IPFLI00F

CREATE OR REPLACE TABLE myLib/IPFLI00F (
  IIFANN CHARACTER(1) NOT NULL WITH DEFAULT,
  IITEXT CHARACTER(25) NOT NULL WITH DEFAULT
)
RCDFMT IPFLI;

Run this member with the following command:

RUNSQLSTM SRCFILE(myLib/MySourceFile) SRCMBR(IPFLSQL)

Now, manually insert a few records into the file IPFLI00F using DFU, ACS, or any method of your choice. The values are not important — just a few test records.

---

Step 2: Compile and Run the RPG Program

Below is the RPGFree program that reads all records from the file and updates the field IIFANN to '*' for each record:

Source member: IPFL01
Type: SQLRPGLE

**free
// ********************************************************
// *  How to read an entire file from the beginning       *
// *  and update a field in the same file                 *
// *  using SQL in RPG IV                                 *
// ********************************************************
// *  This program reads the file IPFLI00F and updates    *
// *  the field IIFANN to '*' for all records             *
// *                                                      *
// *  The program uses a cursor to fetch records and      *
// *  updates them in a loop.                             *
// *  The program is written in RPGfree with SQL.         *
// *  The SQL cursor is declared to read the file.        *
// *  The program uses a data structure to hold the       *
// *  fetched record data.                                *
// *  The program ends by closing the cursor and setting  *
// *  the last record indicator to on.                    *
// *  The program is expected to be run in an environment *
// *  where the file IPFLI00F exists and is accessible.   *
// ********************************************************
ctl-opt option(*nodebugio:*srcstmt:*nounref);
 
// IPFLI00F This file is readed from the beginning
 
dcl-ds RecordDsC1;
  rDsIIFANN char( 1);
  rDsIITEXT char(25);
end-ds;
 
exec sql set option Commit = *None;
exec sql set option SRTSEQ = *LANGIDSHR;
exec sql set option CLOSQLCSR = *ENDMOD;
// preapre the data recordset
exec sql declare C1 cursor for
  select
         IIFANN,
         IITEXT
  from IPFLI00F
  where
      IIFANN <> 'A'
  order by IITEXT
  for read only;
 
// ********************************************************
// * PGM start                                            *
// ********************************************************
 
exec sql close C1;
exec sql open C1;
 
dow sqlCode = 0;
  exec sql fetch C1 into :RecordDsC1;
  if sqlcode = 0;
    // update field IIFANN table IPFLI00F
    exec sql update IPFLI00F
      set IIFANN = '*';
  endif;
enddo;
exec sql close C1;
*inlr = *on;

🔎 Note: The UPDATE statement should include a WHERE clause to avoid updating all records unconditionally.

---

Step 3: Run the Program

Use the CALL command to run the program:

CALL IPFL01

If you want to debug it, use:

STRDBG PGM(IPFL01) UPDPROD(*YES) OPMSRC(*YES)

After the program runs, all records will have IIFANN set to '*'.

---

Conclusion

This is a simple example of how to use embedded SQL in RPGFree to read and update data. By using cursors and data structures, you can manipulate records in a clean and modern way on IBM i.

Feel free to share your thoughts or ask questions in the comments — I read and appreciate every single one!

---

---

Update: Thanks to anonymous – A better way to update rows using cursors

A big thank you to the anonymous reader who left a great comment below this post.

They pointed out a better way to perform updates in an SQLRPGLE program using cursors, by simply including FOR UPDATE OF in the DECLARE CURSOR statement and using WHERE CURRENT OF in the UPDATE.

This avoids the need to perform a second search with a WHERE clause (based on keys), since the row can be updated directly using the cursor's current position. This is more efficient, cleaner, and less error-prone.

Here is the updated version of the source code using this approach:

**free
// ********************************************************
// *  How to read an entire file from the beginning       *
// *  and update a field in the same file                 *
// *  using SQL in RPG IV                                 *
// ********************************************************
// *  This program reads the file IPFLI00F and updates    *
// *  the field IIFANN to '*' for all records             *
// *                                                      *
// *  The program uses a cursor to fetch records and      *
// *  updates them in a loop.                             *
// *  The program is written in RPGfree with SQL.         *
// *  The SQL cursor is declared to read the file.        *
// *  The program uses a data structure to hold the       *
// *  fetched record data.                                *
// *  The program ends by closing the cursor and setting  *
// *  the last record indicator to on.                    *
// *  The program is expected to be run in an environment *
// *  where the file IPFLI00F exists and is accessible.   *
// ********************************************************

ctl-opt option(*nodebugio:*srcstmt:*nounref);

// IPFLI00F This file is read from the beginning

dcl-ds RecordDsC1;
  rDsIIFANN char( 1);
  rDsIITEXT char(25);
end-ds;

exec sql set option Commit = *None;
exec sql set option SRTSEQ = *LANGIDSHR;
exec sql set option CLOSQLCSR = *ENDMOD;

// Prepare the data recordset and allow update on IIFANN
exec sql declare C1 cursor for
  select
         IIFANN,
         IITEXT
  from IPFLI00F
  where
      IIFANN <> 'A'
  order by IITEXT
  for update of IIFANN;

// ********************************************************
// * PGM start                                            *
// ********************************************************

exec sql close C1;
exec sql open C1;

dow sqlCode = 0;
  exec sql fetch C1 into :RecordDsC1;
  if sqlcode = 0;
    // Update field IIFANN in table IPFLI00F using cursor
    exec sql update IPFLI00F
      set IIFANN = '*'
      where current of C1;
  endif;
enddo;

exec sql close C1;
*inlr = *on;

I always appreciate constructive feedback like this. It helps improve both the code and the learning experience for the whole IBM i community.

Thank you again, anonymous! 😊
Aldo

---

Update2: Thanks to Christian Larsen – A better way to update rows using cursors

A special thanks to Christian Larsen.

I really appreciate Christian’s contribution. His attention and input help make these examples cleaner, more modern, and more professional.

Here is the updated version of the source code using Christian suggestion:

**free // ******************************************************** // *  How to read an entire file from the beginning       * // *  and update a field in the same file                 * // *  using SQL in RPG IV                                 * // ******************************************************** // *  This program reads the file IPFLI00F and updates    * // *  the field IIFANN to '*' for all records             * // *                                                      * // *  The program uses a cursor to fetch records and      * // *  updates them in a loop.                             * // *  The program is written in RPGfree with SQL.         * // *  The SQL cursor is declared to read the file.        * // *  The program uses a data structure to hold the       * // *  fetched record data.                                * // ********************************************************   ctl-opt main(main)         option(*nodebugio:*srcstmt:*nounref)         dftactgrp(*no)         actgrp(*new);   // IPFLI00F - This file is read from the beginning   dcl-ds RecordDsC1;   rDsIIFANN char(1);   rDsIITEXT char(25); end-ds;   dcl-proc main;     exec sql     set option Commit = *None,                SRTSEQ = *LANGIDSHR,                CLOSQLCSR = *ENDMOD;     // Prepare the data recordset and allow update on IIFANN   exec sql declare C1 cursor for     select IIFANN,            IITEXT       from IPFLI00F      where IIFANN <> 'A'      order by IITEXT      for update of IIFANN;     exec sql open C1;     dow sqlCode = 0;     exec sql fetch C1 into :RecordDsC1;     if sqlcode = 0;       // Update field IIFANN in table IPFLI00F using cursor       exec sql update IPFLI00F         set IIFANN = '*'         where current of C1;     endif;   enddo;   end-proc;

Thanks, Christian, for taking the time to share your insights!