RE: Auto increment value after insert

Thanks for the responses from everyone. This is what I was looking for but not finding.

Of course this design was still "in theory" mode using something new to me (never used the identity columns before) but I guess I should have read the details on the identity column because you can't create it using DDS and my client won't let me use DDL. ☹

Back to the drawing board.

Thanks again. I will log this for future reference with some other client or maybe some personal stuff.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Alan Campin
Sent: Thursday, January 13, 2022 12:58 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Auto increment value after insert

You don't say but assuming that the header field is an identity key, you want to use the IDENTITY_VAL_LOCAL function

exec sql INSERT INTO QTEMP.TESTTABLE (SOMETHING_ELSE)
VALUES('GEORGINA') ;

02 exec sql SET :LastId = IDENTITY_VAL_LOCAL() ;
03 dsply ('1. LastId = ' + %char(LastId)) ;


On Thu, Jan 13, 2022 at 9:32 AM <smith5646midrange@xxxxxxxxx> wrote:

I have a header table and a detail table. The header table has an
auto increment field for its key that is used to join the detail
table. If I have multiple users updating the header table at the same
time, how do I know the value on the header record that was just
inserted so it can used when inserting the detail records? I have
been googling and it looks like the only way to find it is to use
transactions and commit logic (and maybe the method I'm finding only works on MySQL and other non-DB2 platforms).
Is
there a way other than using transactions and commit logic?



While the program that I am writing is RPG and I considered posting
this to that forum, I'm asking this in the technical forum because I
assume other high level languages have the same problem (like windows
programs using ODBC connections).

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com