RE: Identity Columns

I just meant whatever you're writing to the PF, whether a DS or just individual variables.



-----Original Message-----
From: Craig Richards [mailto:craig@xxxxxxxxxxxxxxxx]
Sent: Thursday, June 14, 2018 4:22 PM
To: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxx>
Subject: Re: Identity Columns

I'm not sure which buffer you mean Justin.

But if you are talking about the memory location that the program uses for the ID Column of the table - well that is never referenced by the program.

If assignment of ID Column values was down to my program, the first ID would write as zero and all subsequent ones would fail as duplicates.

The file is Prefixed with Cnt_

The ID Column is called UniqueID.

The field Cnt_UniqueID is never referenced in the program.

Debugging the program - stopped on the WRITE. The File contains UniqueID values of 1,2,3,4,5,6,7,8 and 32.

The first test I try is to Add a record so the Service Program has not yet accessed the table.
Well, it has to build a subfile page but that bit has been done in SQL.

The value of Cnt_UniqueID has a Value of 0 because no RLA has occurred yet in the Service Program.

After the WRITE there is a new UniqueID of 46 on the file. I've been adding and deleting a few records in testing which would account for the gap.
So the RLA WRITE ( or whatever goes on between my program and the Database Manager ) has ignored the value of 0 which was in CNT_UniqueID and generated the new value of 46.

Second test. I Update a record with UniqueID 5 and then go on to Copy a Record to create a new record on the file..
This time, before the WRITE there is a value of 5 in CNT_UniqueID ( from the RLA Access for the previous Update ) After the WRITE there is a new UniqueID of 47 in the file.

So this all appears as I expected, that for RLA Writes to this table the values my program has for the ID field are inconsequential.
Hence my confusion about how a Duplicate Key Error can occur if the Database Manager is looking after it.

Anyway, I've tried adding / deleting / copying / updating records and switching from one library to another ( after coming out of the program which correctly closes everything down AND reclaims the named activation group ) and now it's decided to behave itself.

I'll just have to wait and see if it appears again...
I like a good mystery /sigh

As usual - thanks all for your thoughts. It is very helpful to share knowledge on these forums and I'm always grateful for the people who take time to add their thoughts.
best regards,
Craig