Re: SQL Update value in RPG?

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

I need to be able to control subsequent logic if the counter has
reached a certain limit. I'd like to not have to re-retrieve the
record to find out if the limit has been reached.

Anyone have any ideas?

Perhaps possible, to implement the limit via either the data type\length or a constraint, and then the SQLCODE or SQLSTATE can inform of the detected condition. That of course is slightly different, because the limit would already have been reached, as contrasted with knowing the next increment would encounter the limit; i.e. "subsequent logic" would not be the same as for the posited scenario.

If a SEQUENCE could possibly be used instead, SET the NEXT VALUE for the SEQUENCE into a variable. That value would then be used both "to control subsequence logic if the counter has reached a certain limit" and to set the COUNTER [instead of using the arithmetic calculation COUNTER+1]; e.g.:
set :NxtInSeq=NEXT VALUE FOR CounterSeq ;
update fileX set counter=:nxtinseq where keyfield=:something ;

An IDENTITY column has already been mentioned.

Regards, Chuck