Updating a record through cursor big show dating
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 sillyfile Data Structure extname('SILLYFILE') qualified end-ds; exec sql declare Nicks Cursor cursor for select * from sillyfile where status = '1' and flag = 'X' for update of status; exec sql open Nicks Cursor; exec sql fetch next from Nicks Cursor into :sillyfile Data Structure; 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 // sillyfile Data Structure.username or // sillyfile Data Structure.userdesc exec sql update filename set flag = 'Y' where current of Nicks Cursor; exec sql fetch next from Nicks Cursor into :sillyfile Data Structure; Enddo; exec sql close Nicks Cursor; *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.The positioned delete operation deletes the row that the cursor is currently positioned to in the active set.After the positioned delete operation, the cursor is positioned before the row immediately following the row just deleted, or after the last row if no such immediately following row exists.That's a little slower than I expected but not shocking. I assumed that a cursor could return the row once and then test the values returned to generate multiple counts.The only way a non-cursor solution could produce these results is through multiple SELECT statements.Each of these did a count of the records that matched a certain criteria. The five SELECT statements (the first plus my four additional SELECTs) averaged 66 ms per run.None of these four additional queries should use an index beyond what the initial query did. The cursor was also updated to keep track of four individual record counts based on the characteristics of the records returned. That's 4.6 times slower than the single SELECT ran. The cursor to return all five counts averaged 165 ms or about 25% slower than the cursor to determine one total.
Notice: If you extend the mapper and try to read or modify mapper fields (especially those that also exist as class property) with magic getter/setter within those event handlers, you will get unpredictable behaviours or run into errors.
#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.
So, lets use a quick and dirty little 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 STATUSnote: 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” 😉**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;“.
So, I am not going to give any excuse for my lack of comments… 🙂 So, now lets update that exact same program and change it to use SQL to do the exact same thing.