× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.