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


On 14 June 2018 at 20:53, Justin Taylor <JUSTIN@xxxxxxxxxxxxx> wrote:

I would expect an RLA WRITE operation that touches the ID column to write
whatever happens to be in the buffer at that time.


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

Hi Justin,

Thanks for your reply.
Hmm that's kind of concerning.

My issue so far has always manifested itself on a WRITE.
But it happens that I've always ended up in a WRITE situation from my
"work with" program by copying another record.

That said, in the program the file is qualified and the qualified version
of the ID column does not appear anywhere in the source.

I'll have a play and see if come up with anything based on the idea that a
previous update might have corrupted things.

Because despite all of this talk about environments and activation groups
and closing files and cursors ( which I think I have a pretty good handle
on ) that is all irrelevant if the RLA is not supposed to have any impact
on an ID GENERATED ALWAYS column...

best regards,
Craig


--
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: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD