× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.