Rick,
V5R4 is not a problem.
We run these examples at V5R4
Using SQL to insert a record into file ARCFILEID0 which has an identity
column
C/exec sql
C+ insert into ARCFILEID0 (CREATEDATE,FILENAME, FILETEXT)
C+ values( current_timestamp, upper(:File_name), :File_Text
C/end-exec
C/exec sql
C+ VALUES IDENTITY_VAL_LOCAL() INTO :File_ID
C/end-exec
And also using RPG to insert a record into file Pgmstack whuch has an
identity column
C Write PgmStack0
C/EXEC SQL VALUES IDENTITY_VAL_LOCAL() INTO :ID
C/END-EXEC
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
Rick.Chevalier@xxxxxxxxxxxxxxx
Sent: Thursday, 6 November 2008 8:11 a.m.
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: Retrieving identity column value when adding a record
Thanks to everyone who replied.
I forgot to mention that we are at V5R4 so I can't get the value from an
insert statement.
I just want to clarify something. I saw Charles solution from the
archives. When I looked it up in the SQL reference manual it
specifically states the VALUES statement is only allowed in a trigger.
I am not using a trigger and doubt that will change. Is the manual
wrong and the statement can be used from a SQLRPGLE program?
From the V5R4 SQL reference manual:
VALUES
The VALUES statement provides a method for invoking a user-defined
function from a trigger. Transition variables can be passed to the
user-defined function.
Invocation
This statement can only be used in the triggered action of a CREATE
TRIGGER statement.
Rick
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Wednesday, November 05, 2008 11:18 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: Retrieving identity column value when adding a record
Use:
VALUES IDENTITY_VAL_LOCAL() INTO :IVAR
HTH,
Charles
Privileged and Confidential. This e-mail, and any attachments there to,
is intended only for use by the addressee(s) named herein and may
contain privileged or confidential information. If you have received
this e-mail in error, please notify me immediately by a return e-mail
and delete this e-mail. You are hereby notified that any dissemination,
distribution or copying of this e-mail and/or any attachments thereto,
is strictly prohibited.
--
This is the RPG programming on the AS400 / 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.
#####################################################################################
This correspondence is for the named person's use only. It may contain confidential or legally privileged information, or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any views expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the views of Veda Advantage. If you need assistance, please contact Veda Advantage on either :- Australia 133124 or New Zealand +64 9 367 6200
midrange.com
