Re: FW: SQL Update value in RPG?

[CRPence <CRPbottle@xxxxxxxxx>]

  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.