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)...

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.