I am wondering...
Could using option 1 yield an incorrect result if more than one process is
adding records to the table simultaneously? Or is the function local to the
program.
I.E.
Prog A writes a record
Prog B writes a record
Prog A used the Identity_Val_Local().
Would Prog A get the identity of the record that Prog B wrote?
Granted that the above all occurs within less than a thousandth of a second.
Thanks,
Rich
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Mike
Jones
Sent: Monday, November 30, 2015 9:08 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Identity column
Hi Booth,
Couple ways:
1) Use the IDENTITY_VAL_LOCAL() function which returns the most recently
assigned identity column value.
Ex: VALUES IDENTITY_VAL_LOCAL() INTO :MY_ID_VAR
2) Use the data change table reference FINAL TABLE.
Ex: select NAID# into :MY_ID_VAR from final table ( put your insert
statement here )
I'm guessing option 2) is likely to perform slightly better since it gets
the job done in one request to the database, whereas option 1 takes two
requests. Note: I've never taken the time to benchmark that hypothesis
though.
Note on option 2): you can select additional columns, not just the ID
column, if you wish. If I recall correctly, option 2) will reflect changes
made to the data in BEFORE TRIGGERS, if any are present.
If the table receiving the INSERT has AFTER TRIGGERS present that modify
data in the inserted row, you should use option 1). Typically though,
column value changes in triggers are handled in BEFORE TRIGGERS.
Mike
date: Mon, 30 Nov 2015 21:35:25 -0600
from: Booth Martin <booth@xxxxxxxxxxxx>
subject: Identity column
How do I retrieve a new record that I have just created?
The file is created with a key:
NAID# decimal(5) not null // ID#
generated always as identity
(start with 1),
The scenario is this:
-User keys the new data into blank fields on the screen, but of course
not into the key field.
-User presses Enter key. Data is used to create a new record using
embedded SQL -User wants to see the record on the screen, with the key
field populated.
-I can't figure out how to do that.
--
Booth Martin
www.martinvt.com
(802)461-5349
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7227 / Virus Database: 4477/11095 - Release Date: 11/30/15
midrange.com
