Not sure how a trigger would help me. How would it inform the program that
initiated the file update what the new Counter value is, so that program can
perform its logic when the counter reaches a limit?
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Friday, April 13, 2012 6:07 AM
To: Midrange Systems Technical Discussion
Subject: Re: FW: SQL Update value in RPG?
What about a trigger program?
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
Kendallville, IN 46755
From: "Thomas Garvey" <tgarvey@xxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
Date: 04/12/2012 05:35 PM
Subject: FW: SQL Update value in RPG?
Sent by: midrange-l-bounces@xxxxxxxxxxxx
From: Thomas Garvey [mailto:tgarvey@xxxxxxxxxxx]
Sent: Thursday, April 12, 2012 12:13 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL Update value in RPG?
I can't use the SEQUENCE or IDENTITY phrase. My understanding is that
scope is table wide, and my intention is subsidiary to other fields. MY
would be similar to a count of how many times a record has been changed.
the counter is not a key value or a sequence that is unique across the
table. I'd like to be able to execute the SQL UPDATE, incrementing the
counter field (multiple programs will be able to update this counter), and
when the counter has reached a variable limit, subsequent logic would send
message to a user that the limit has been reached.
I like the idea of NEXT VALUE though, and have other applications where I
might use that in the future.
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, April 11, 2012 5:22 PM
Subject: 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...
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
or a constraint, and then the SQLCODE or SQLSTATE can inform of the
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
for the posited scenario.
If a SEQUENCE could possibly be used instead, SET the NEXT VALUE for
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
set :NxtInSeq=NEXT VALUE FOR CounterSeq ;
update fileX set counter=:nxtinseq where keyfield=:something ;
An IDENTITY column has already been mentioned.
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
or change list options,
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l