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 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:
**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 status; 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
IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd 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 in the words of the most interesting man in the world: Stay thirsty my friend.
Funky IBM i Email Validation Program using SQL Regex
IBM i SQL statement to convert or compare hundred year date format
Modernize AS400 iSeries Queries – Convert to IBM i SQL
Use IBM i SQL to validate email addresses in Customer Master File
Update an IBM i file with SQL cursor using SQLRPGLE WHERE CURRENT OF
Simple email validation SQL RPG ILE program