Update an IBM i file with SQL cursor using SQLRPGLE WHERE CURRENT OF

  • Home
  • /
  • Blog
  • /
  • Update an IBM i file with SQL cursor using SQLRPGLE WHERE CURRENT OF

August 10, 2017

Using SQL in RPGLE programs is easier than you think.

Making the move from RPG native file IO to SQL database IO is really quite straightforward:  Changing from good old READE loops to SQL FOR Loops simply means using the SQL CURSOR function.

SQL has a groovy way of referring to what the stuff that it just read: “where current of”.   So what does SQLRPGLE WHERE CURRENT OF mean?

WHERE CURRENT OF MEANS – if we have just read something using an SQL cursor then the operation that we are applying to “where current of” is being applied to the exact data set that just been read. Read that slowly and it will make sense. LOL.

Let’s take an example of native file access technique for reading a file and updating it.

PROGRAM SPEC: How about we read a silly file, matching a key field value of STATUS = ‘1’ and if the field “FLAG” has a value of ‘X’ in s status field and change the value to ‘Y’.

So, lets use a quick and dirty little keyed physical file that looks like this:

A   STATUS     1A COLHDG('Active Users' '*ON or *OFF') 
A                 VALUES('0' '1') 
A   USERNAME  10A COLHDG('This blokes name') 
A   USERDESC 200A COLHDG('Some other guff') 
A   FLAG       1A COLHDG('A silly flag X/Y') 
A                 VALUES('X' 'Y') 

So, here is a basic RPGLE program to read all the record in the table matching the key, checking the FLAG value and updating it.

note: before you leave a snarky comment saying “why didn’t you just put the flag as part of the key” –  my equally salty answer is “it’s obviously deliberate to make the example clear for RPG freshers” 😉

Buckle up and lets look at this goofy code example:

RPG Code Snippet
**FREE dcl-f SILLYFILE keyed usage(*update) usropn; Open SILLYFILE; status = '1'; Setll (status) filename; Reade (status) filename; Dow not %EOF(filename); if flag = 'X'; // some program logic stuff could be here flag = 'Y'; update record; endif; Reade (status) SILLYFILE; Enddo; Close SILLYFILE; *inlr = *on;

I hate people that say things like “I didnt add any program comments because RPG is a self documenting language“. So, I am not going to give any excuse for my lack of comments… That being said – the code is self explanatory.  🙂

So, now lets update that exact same program and change it to use SQL to do the exact same thing. There are many ways to do things, but I prefer to use qualified data structure to reference files in the code, if you are learning RPG then I strongly recommend it as a good technique, so thats the style I will use here.

If we wanted to do the same thing in SQLRPGLE we could do it like this:


dcl-ds sillyfileDataStructure extname('SILLYFILE') qualified end-ds;

exec sql declare NicksCursor cursor for
 select * from sillyfile
 where status = '1' and flag = 'X'
 for update of flag;

exec sql open NicksCursor;

exec sql fetch next from NicksCursor into :sillyfileDataStructure;

Dow sqlstt='00000' or %subst(sqlstt:1:2)='01';

 // some program logic stuff could be here
 // we could use those file fields field we just read as
 // sillyfileDataStructure.username or
 // sillyfileDataStructure.userdesc

  exec sql update filename set flag = 'Y' where current of NicksCursor;

exec sql fetch next from NicksCursor into :sillyfileDataStructure;


exec sql close NicksCursor;

*inlr = *on;

The words CURRENT OF, followed by the cursor name, refer to the last row that was retrieved through the cursor with the FETCH command. #simples

IBM calls this type of update a positioned update.

There is a way cooler, big data selection that we could do updating the whole set-at-a-time also called a searched update. Searched updates give you a lot of bang for the buck (or pickles to the pound) , but these simple positioned updates are best when the update logic is complex and it’s a direct replacement for old RPG style UPDATE/DELETE opcodes.

/me pencils in searched updates SQL blog for later


IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Shameless Trekkie, Belligerent Nerd, Englishman Abroad and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and remember: If at first you don't succeed then skydiving probably isn't a hobby you should look into.

Nick Litten

related posts:

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Start your
7-day free trial

Take This Course with ALL ACCESS

Unlock your Learning Potential with instant access to every course and all new courses as they are released.
 [ For Serious Software Developers only ]

IBM i Training For Technology Experts


Successfully Work Remote 

 $ 129.00  $59.00

By adding new skills in information technology, employers will be confident that you have the necessary skills and tools needed to successfully work remotely


Snug CBD

 20% Discount

I have partnered with SNUG CBD givING you Organic CBD
20% discount code "NL20"