Re: FW: SQL Update value in RPG?

Ignoring any previous references to multiple programs... The originally described program would just as well use an update CURSOR, FETCH the row, then optionally UPDATE WHERE CURRENT OF according to the value of the COUNTER column [not being maximum] and informing of the impending maximum if appropriate, or informing of the already-maximum. The row would be retrieved just once and optionally updated, thus no need for a re-retrieval of the row; albeit the work would not have been deferred to a single non-cursor UPDATE, but that is what currently is deemed insufficient.

However there was actually reference to other programs and an apparent implication they should similarly effect the desired notification of the maximum value. Thus triggers [or constraints] are often a better choice.

As to making a TRIGGER do the work to communicate the value, non-exception messages would be better; an exception from the trigger only for the maximum already met. In a prior message I referred to /tricks/ that could be performed by a trigger to communicate the information back to the application, and messaging is one of them. That is somewhat trickery because the application must actively obtain the information; knowing to look for and remove the messaging when updating that file with the trigger for which now there is a contract between all apps and that trigger. That does not make for a good design because the trigger would best act according to the data and be done. The use of Global Variables in the SQL statements presumably could make the use of an effective return value seamless; I have not used them, and I doubt they are very worthwhile for a client interface.

Anyhow, a TRIGGER would be better to implement the notification as a "business rule" rather than the application ever having to know the value of the COUNTER column [in order to make that decision at the application level]. That is, a trigger that would not be used to communicate the value of the COUNTER back to the application.

Regards, Chuck

On 13-Apr-2012 08:22 , Thomas Garvey wrote:

I'm just trying to see if there is a way to get the value just
updated by an SQL statement, without having to re-retrieve the
record. I mean, I could just resort to native data retrieval but
was trying to stay with SQL.

rob on Friday, April 13, 2012 9:26 AM wrote:

Why would the update program need to perform the logic? Why
couldn't the trigger program? Seriously. What happens if you adopt
a batch program, web interface, C/S interface, etc; do you still
want the logic in each of these programs?

But if it just HAS to be the update program you could study
throwing and catching exceptions.

<<SNIP intermediate replies>>

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.