Read Once, Update Many
November 30, 2011 Ted Holt
|
Using SQL rather than native I/O to query and manipulate the database is more than replacing one syntactical regulation with another. It requires a different way of thinking. Nevertheless, corresponding features do differ, and today I show one way that the SQL update differs from a native update. Consider the following scenario from a project in which I was recently involved. I needed to read a database table (physical file) from top to bottom. After I retrieve each row (record), I called one or both of two high-level language programs, using parameters to pass data values from the table and to receive other data values in return. Depending on the values in the returned data, I had to update certain columns (fields). In other words, many different updates had to take place. To give you some idea of what I’m talking about, here is a much simplified RPG program that uses native I/O.
FSomeFile uf e disk prefix(A_) usropn
D CurrentTime s z inz(*sys)
D Status s 1a
/free
*inlr = *on;
open SomeFile;
dow '1';
read SomeRec;
if %eof();
leave;
endif;
//// do something here that sets Status
//// and may or may not affect Balance
A_RunTime = CurrentTime;
if Status = '1';
update SomeRec %fields(A_Balance: A_RunTime);
else;
update SomeRec %fields(A_RunTime);
endif;
enddo;
close *all;
return;
In this simple program, there are two updates. The system will only carry out one of them for each record in the file, depending on the value of the status field. Notice that the runtime field is updated in either case. In my more complex example, I needed to carry out multiple updates. To return to my simplified example, I needed to do this:
if Status = '1';
update SomeRec %fields(A_Balance);
endif;
update SomeRec %fields(A_RunTime);
But native I/O won’t allow more than one update for a single read. To make this code run, I would have to add an input operation before the second update. Updating a single row is one way that SQL differs from native I/O. When you update a table through a cursor, you can update as many times as you like, like this:
D CurrentTime s z inz(*sys)
D WorkRec ds qualified inz
D CustomerType 1a
D TAmount 7p 2
D Bal 7p 2
D Status s 1a
D Balance s 7p 2
/free
*inlr = *on;
exec sql
declare c1 cursor for
select c.custype, c.transamt, c.balance
from SomeFile as c
for update of balance, runtime;
exec sql
open c1;
dow '1';
exec sql
fetch c1 into :WorkRec;
if SqlState >= '02000';
leave;
endif;
//// do whatever to change status and balance
if Status = '1';
exec sql
update SomeFile as c
set c.Balance = :Balance
where current of c1;
endif;
exec sql
update SomeFile as c
set c.RunTime = :CurrentTime
where current of c1;
enddo;
exec sql
close c1;
return;
Notice the two back-to-back UPDATE commands. The fact that SQL allowed two updates for one fetch helped me in two ways. First, it eliminated the need to write a lot of messy conditional logic. Second, it permitted me to write a few, short UPDATE commands, rather than a lot of long ones.
|
