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?
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[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?
Rob Berendt
--
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: "Thomas Garvey" <tgarvey@xxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>,
Date: 04/12/2012 05:35 PM
Subject: FW: SQL Update value in RPG?
Sent by: midrange-l-bounces@xxxxxxxxxxxx
-----Original Message-----
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
their
scope is table wide, and my intention is subsidiary to other fields. 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. 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
a
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.
Thanks.
Tom Garvey
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, April 11, 2012 5:22 PM
To: midrange-l@xxxxxxxxxxxx
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...
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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
