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

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 R RECORD 
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') 
A K STATUS

So, here is a basic SQLRPGLE 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:

**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:

**FREE

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;

Enddo;

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