Re: Using "As Identify" clause generates duplicate key error -- RPG400-L

The other posts mention a fix the problem, GENERATED ALWAYS AS IDENTITY.

Now let me explain what the problem was.

Without the GENERATED ALWAYS, the AS IDENTITY defaults to "GENERATED
BY DEFAULT AS IDENTITY".
The manual says:
"BY DEFAULT Specifies that the database manager will generate a value
for the column when a row is inserted only if a value is not specified
for the column. If a value is specified, the database manager uses
that value."

Since RPG writes always write every field, the system sees the value
for the identity value as being passed, thus it doesn't generate a
value.

If you are using SQL, you simply don't include the identity column in
the list of fields.
insert into mytble (DaysSubm, DaysNCrd, ....) values (:nDySubmit, nDysCrd, ...);

Note there is a difference between leaving the column off vs. trying
to pass NULL. Trying to pass NULL won't work.

Moral of the story, if you are using RPG record i/o you probably want
to use GENERATED ALWAYS AS IDENTITY.

Lastly, let me point out that I ran into trouble with Aldon CMS when
using Identity columns. If I made a change to the file, and promoted
it back into production. Aldon would rename the old version, create
the new, and CPYF *MAP *DROP the data from the old to the new. The
records kept thier original generated IDs; which is good. But that
meant the new table was left thinking that the next ID to generate was
1. I ended up putting together a little command that would determine
the highest current value of the identity column and issue an ALTER
TABLE ALTER COLUMN myID RESTART WITH :MaxNbr + 1.

HTH,
Charles

On Tue, Feb 17, 2009 at 4:53 PM, Doug Palme <DPalme@xxxxxxxxxxx> wrote:

We have created a new ddl file which has the following as the first field
listed in the ddl:

0001.01 VndKey Numeric(20) As Identity(Increment by 1)
0001.02 Primary Key,

Now, before anyone starts yelling about using imbedded SQL in the program;
it is not going to happen....beyond my control, but the following code is
what writes the new records.......

0007.70 DaysSubm = nDySubmit;
0007.71 DaysNCrd = nDysCrd;
0007.72 MinAmtRq = nMinAmt;
0007.73 MaxAmtRq = nReqApp;
0007.74 AutFaxNo = nFaxNo;
0007.75 AutFaxDy = nDysRsnd;
0007.76 AutFaxRs = nResend;
0007.77 Write Policy;

Now, at one point this file had a usr open and close attached to it, and
maybe that was part of the problem, but it would never write a new
record.....always tried to write the same old key (16)......

We finally yanked the IDENTITY clause out of the file and moved on, but I
thought I would run it past the group and see if anyone had any
ideas.......

Douglas
--
This is the RPG programming on the IBM i / System i (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.