You missed the empty column after the identity_Val_Local() function:
Exec SQL SET :gLastId = Identity_Val_Local();
Will work correctly (VALUES INTO is only an alternate)
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von
JRusling@xxxxxxxxxxx
Gesendet: Friday, 02.9 2016 16:38
An: RPG programming on the IBM i (AS/400 and iSeries)
Cc: RPG400-L
Betreff: Re: AW: Identity columns and RLA
I was playing with identity columns (cuz it's Friday)...
I created a simple table as below (using strsql on greenscreen)...
CREATE TABLE johnr/MYTAB (
CNCUST DECIMAL(6, 0) NOT NULL DEFAULT 0 ,
CNDESC CHAR(30) NOT NULL DEFAULT '' ,
CNIDCOL INTEGER GENERATED ALWAYS AS IDENTITY) Table MYTAB in JOHNR created
but was not journaled.
And have a simple rpg program with an F-spec using WRITE to write records to
MYTAB...
fmytab uf a e k disk rename(MYTAB:MYTABR)
It works and the identity increments as expected.
I tried to add the piece indicated in this thread about retrieving the
identity value after the write operation but that piece is not working for
me...
d gLastId s 10i 0
cnCUST = s1CUST;
cnDESC = s1DESC;
write mytabr;
exec sql set :gLastId = Identity_Val_Local;
s1MSG = 'Record identity ' + %char(gLastId) + ' written.';
the rpg variable gLastId is always zero, and the sqlcod after the exec sql
statement is
SQL0206 Column or global variable not found.
Any idea what I'm missing or doing wrong?
Thanks,
John
<snip>
There is no need to convert everything into SQL.
If the identity column is generated always, the new identity value is
determined correctly when writing a new record.
If you perform an SQL SET statement, with IDENTITY_VAL_LOCAL, the identity
value of your write operation.
Exec SQL SET :LastId = Identity_Val_Local; </snip> <br /> The information in
this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by anyone else
is unauthorized. If you are not the intended recipient, any disclosure,
copying, distribution or any action taken or omitted to be taken in reliance
on it, is prohibited and may be unlawful.
--
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.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
midrange.com
