|
Send RPG400-L mailing list submissions to
rpg400-l@xxxxxxxxxxxx
To subscribe or unsubscribe via the World Wide Web, visit
http://lists.midrange.com/mailman/listinfo/rpg400-l
or, via email, send a message with subject or body 'help' to
rpg400-l-request@xxxxxxxxxxxx
You can reach the person managing the list at
rpg400-l-owner@xxxxxxxxxxxx
When replying, please edit your Subject line so it is more specific
than "Re: Contents of RPG400-L digest..."
*** NOTE: When replying to this digest message, PLEASE remove all text
unrelated to your reply and change the subject line so it is meaningful.
Today's Topics:
1. Re: AW: Identity columns and RLA (JRusling@xxxxxxxxxxx)
2. Re: AW: Identity columns and RLA (JRusling@xxxxxxxxxxx)
3. Re: AW: Identity columns and RLA (Vernon Hamberg)
4. Re: AW: Identity columns and RLA (Vernon Hamberg)
5. AW: Identity columns and RLA (Birgitta Hauser)
6. AW: Identity columns and RLA (Birgitta Hauser)
7. Re: AW: Identity columns and RLA (JRusling@xxxxxxxxxxx)
----------------------------------------------------------------------
message: 1
date: Fri, 2 Sep 2016 09:37:35 -0500
from: JRusling@xxxxxxxxxxx
subject: 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.
------------------------------
message: 2
date: Fri, 2 Sep 2016 09:45:58 -0500
from: JRusling@xxxxxxxxxxx
subject: Re: AW: Identity columns and RLA
Ooops never mind... this one worked great.
v7r2
cnCUST = s1CUST;
cnDESC = s1DESC;
write mytabr;
-->> exec sql VALUES IDENTITY_VAL_LOCAL() INTO :gLastId;
s1MSG = 'Record identity ' + %char(gLastId) + ' written.';
<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.
------------------------------
message: 3
date: Fri, 2 Sep 2016 09:55:12 -0500
from: Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
subject: Re: AW: Identity columns and RLA
Hi John
I think the main problem is, Identiry_val_local is a function - so you
need the parentheses, like this -
identity_val_local()
As the message says, it's not a column name and it's not a global variable.
On a little different bent - I wonder what would happen if you had
created a data structure using LIKEREC for IO with this table - that's
why some suggest having an LF that has all the columns except the
identity one - but maybe a WRITE using DS just ignores whatever you put
in that last column - if it's even there!
It's Friday - YOU can do more testing, right? LOL
Vern
On 9/2/2016 9:37 AM, JRusling@xxxxxxxxxxx wrote:
I was playing with identity columns (cuz it's Friday)...for
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
me...sql
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
statement isidentity
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
value of your write operation.privileged.
Exec SQL SET :LastId = Identity_Val_Local;
</snip>
<br />
The information in this email is confidential and may be legally
It is intended solely for the addressee. Access to this email by anyoneelse is
unauthorized. If you are not the intended recipient, any disclosure,copying,
distribution or any action taken or omitted to be taken in reliance onit, is
prohibited and may be unlawful.
------------------------------
message: 4
date: Fri, 2 Sep 2016 09:56:29 -0500
from: Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
subject: Re: AW: Identity columns and RLA
Yep - and the other should have worked (I think) if you'd included the
parentheses.
Ah the devil and the details!
Vern
On 9/2/2016 9:45 AM, JRusling@xxxxxxxxxxx wrote:
Ooops never mind... this one worked great.privileged.
v7r2
cnCUST = s1CUST;
cnDESC = s1DESC;
write mytabr;
-->> exec sql VALUES IDENTITY_VAL_LOCAL() INTO :gLastId;
s1MSG = 'Record identity ' + %char(gLastId) + ' written.';
<br />
The information in this email is confidential and may be legally
It is intended solely for the addressee. Access to this email by anyoneelse is
unauthorized. If you are not the intended recipient, any disclosure,copying,
distribution or any action taken or omitted to be taken in reliance onit, is
prohibited and may be unlawful.
------------------------------
message: 5
date: Fri, 2 Sep 2016 17:00:49 +0200
from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
subject: AW: Identity columns and RLA
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.
------------------------------
message: 6
date: Fri, 2 Sep 2016 17:02:26 +0200
from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
subject: AW: Identity columns and RLA
Sorry not column, but parenthesisYou missed the empty column after the identity_Val_Local() function:
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
Birgitta
Hauser
Gesendet: Friday, 02.9 2016 17:01
An: 'RPG programming on the IBM i (AS/400 and iSeries)'
Betreff: AW: Identity columns and RLA
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.
--
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.
------------------------------
message: 7
date: Fri, 2 Sep 2016 10:20:16 -0500
from: JRusling@xxxxxxxxxxx
subject: Re: AW: Identity columns and RLA
Thanks Vern & Birgitta!
"...LIKEREC for IO with this table..."
We'll see... how much playing i can get done before fires start cropping
up. <g>
John
<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.
------------------------------
Subject: Digest Footer
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
digest 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.
------------------------------
End of RPG400-L Digest, Vol 15, Issue 444
*****************************************
As an Amazon Associate we earn from qualifying purchases.
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.
midrange.com
