On 12-Apr-2012 14:34 , Thomas Garvey wrote:
CRPence on Wednesday, April 11, 2012 5:22 PM wrote:
<<SNIP>> If a SEQUENCE could possibly be used instead, <<SNIP>>
On 11-Apr-2012 09:47 , Thomas Garvey wrote:
I'm using embedded SQL to update a counter in a database field,
but would also like to know the new value of that counter without
having to retrieve the newly updated record.
For example, using embedded SQL in my RPGLE, as follows...
exec sql
update fileX
set counter = counter + 1
where keyfield = :something
Since the "counter" value in the original example [snippet inserted
above] seemed to be tied to a particular key value, I figured that using
a SEQUENCE would probably be impractical :-(
I do not recall a release level ever being mentioned.?
I can't use the SEQUENCE or IDENTITY phrase. My understanding is
that their scope is table wide, and my intention is subsidiary to
other fields.
The IDENTITY is. The SEQUENCE however, is separate from a TABLE, so
the application(s) decide for what and how the sequence is utilized.
MY use would be similar to a count of how many times a record has
been changed. So, the counter is not a key value or a sequence that
is unique across the table.
The SEQUENCE could be used, even in the described scenario, although
probably not very succinctly. Establishing a SEQUENCE for each
effective 'group' of data, for example, would be an undesirable
implementation due to the potentially large number of sequence objects
to maintain.
Presumably the keyfield is uniquely keyed such that just one counter
value is tracked to each key value.? And is the limit also specific to
each key value? If so, then the limit would seem best stored in the
same row, in another column. A CHECK CONSTRAINT could then enforce the
maximum in that case; e.g. alter table fileX add constraint
fileX_ck_counter check (counter < max_counter). This is easiest, if
possible; other applications could even remain unchanged, referring to a
LF without the new column.
Especially if the previous comments seem silly... Perhaps offering a
more explicit example(s) than the very generically described example
originally given, to include DDL with constraints and\or unique indexes
and sample data, would assist to better describe the data and scenario.?
I'd like to be able to execute the SQL UPDATE, incrementing the
counter field (multiple programs will be able to update this
counter),
Where are the limits defined\stored; a limits_test() function, a
limits database file, other...? Obviously the program that would be
doing the UPDATE knows the limits, because that program is supposed to
know to respond if the limit is reached.
Consider that the following statement variations for which "row not
found" is the result, the counter is known to have previously grown to
its limit [if the key value can be assumed to exist; e.g. in the second
statement that would be ensured when the limits_file is also a parent
file off fileX in a referential constraint]:
update fileX set counter=counter+1
where keyfield=:something
and counter<limits_test(:something)
update fileX set counter=counter+1
where keyfield=:something
and counter<(select counter_limit from limits_file
where keyfield=:something)
As a variation of the above... two separate updates could detect both
the will-become-maximum and the at-maximum, performing just one update
however in all but the /boundary/ cases. Using the UDF model from
above, in pseudo-code:
execsql
update fileX set counter=counter+1
where keyfield=:something
and counter<limits_test(:something)-1 -- two down from max
; -- if row-found and updated, increment done within bounds
if row-not-found then do // already one down from, or at max
notify(verging-on-max);
execsql
update fileX set counter=counter+1
where keyfield=:something
and counter<limits_test(:something)
; -- if row-found and updated, then increment was to maximum
if row-not-found then do // already at max, do not increment
notify(at-max);
end;
end;
and when the counter has reached a variable limit, subsequent logic
would send a message to a user that the limit has been reached.
Presumably a hard-stop on any other requests since having hit the
limit? That is, if the user who was notified of the condition has not
effected some resolution nor any other automation having effected
resolution, than surely any other increment should fail.?
FWiW:
While the standalone UPDATE is nice to just let the DB do the work
atomically, the row data is still paged [or faulted] into memory. If
the reason for not wanting "to retrieve the newly updated record" is
mostly about performance, actual testing may prove concerns to be
unfounded or at least subdued.
There would be other /tricks/ to get the column value from elsewhere,
for example via a TRIGGER or the journal receiver, but obtaining the
value from the row itself is most obvious [for the code]. With IBM i
7.1 the SQL Global Variables may enable a more integrated means; i.e.
less of a "trick".
Consider encapsulating the work in an atomic SQL or External stored
PROCEDURE, or if already running with isolation perhaps make the work
part of an overall transaction that is later committed after both the
increment activity and the remaining work in that transaction is deemed
complete:
declare cursor as select for update of counter
open
fetch
if below-limit then update set increment where current of
if limit-reached by increment then optionally:
feedback\signal sqlstate to inform of at-maximum
set an output variable with the new counter
the program, instead of the SQL UPDATE uses SQL CALL:
call theProc(:something, :rtn_counter)
if so_informed then this program notifies user of limit-reached;
either by returned counter value or failure
As a procedure instead of inline to the program, all programs
[including external client requester] that update the field could use
the same interface. However....
Having suggested that "multiple programs will be able to update this
counter" I wonder if that might intend to imply that all interfaces for
which an update might be requested should enforce that notification of
the verging-on-max condition, as a "business rule".? If so, then
perhaps the work might best be pushed down to a TRIGGER; if there could
not be an assurance that all programs would be coded to use a common
ROUTINE to effect the update\increment.?
Regards, Chuck
midrange.com
