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
Compilation of second program ends with a :
MSG ID SEV RECORD TEXT
SQL0312 30 23 Position 51 Variable sillyfileDataStructure not defined or not
usable for reason code 1.
Message Summary