Cursor for updating records

Rated 4.62/5 based on 656 customer reviews

Sales Order Detail WHERE Sales Order ID=43659 -- open cursor and fetch first row into variables OPEN insert_cursor FETCH NEXT FROM insert_cursor into @orderid,@orderqty -- check for a new row WHILE @@FETCH_STATUS=0 BEGIN -- do complex operation here Insert into #tmp SELECT @orderid,@orderqty -- get next available row into variables FETCH NEXT FROM insert_cursor into @orderid,@orderqty END close insert_cursor Deallocate insert_cursor GO The above query creates a temporary table #tmp.It then declares an insert_cursor to iterate through rows of Sales.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.However, this script can only be run on string variables because of the way the query string is set up.Notice that the old value is put in quotes, like this: .Be aware that you can't call he cursor does not require us to select the data based on a key; we can just grab all of it.

cursor for updating records-14

cursor for updating records-39

cursor for updating records-81

Second, list only the columns that you will update.If you list columns that you don’t update, SQL will not notice the inconsistency.The possible performance problem occurs when SQL thinks you might update a column that is used in the WHERE clause.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.

Leave a Reply