Re: INSERTing datastructure into table with GENERATED ALWAYS column.

Thanks Dave, Chris and Charles but Brian gave the best answer.

I created a test table


CREATE TABLE RROGERSON/TESTTBL (
COL1 BIGINT GENERATED ALWAYS AS IDENTITY (
START WITH 1 INCREMENT BY 1
NO MINVALUE NO MAXVALUE
NO CYCLE NO ORDER
CACHE 20 )
,
COL2 CHAR(1) CCSID 37 NOT NULL ,
COL3 CHAR(1) CCSID 37 NOT NULL ,
CONSTRAINT RROGERSON/Q_RROGERSON_TESTTBL_COL1_00001 PRIMARY KEY( COL1
) )

and a test program

Hoption(*nodebugio:*srcstmt)
Hdftactgrp(*no) actgrp(*caller)
Hbnddir('COMBND':'BNDDIR02')
*
D testpgm pi
*
D testtbl_Ds...
D e ds extname(testtbl)
D qualified inz
/free
// Set the SQL environment
Exec SQL
SET OPTION COMMIT=*none, DYNUSRPRF=*owner, NAMING=*sys,
DLYPRP=*yes, CLOSQLCSR=*endmod;
*inlr = '1';
Clear testtbl_Ds;
testtbl_Ds.col2 = 'A';
testtbl_Ds.col3 = 'B';
Exec sql
INSERT INTO testtbl OVERRIDING USER VALUE VALUES :testtbl_Ds;
/end-free

It both compiled and when run correctly updated the generated column.

I had never heard of the OVERRIDING USER VALUE so did some research and it
did do what I wanted. I also found [1]this past thread in the archives.

Being able to insert from a data structure without hard coding a list of
columns will save a lot of time.

Thanks for the help.

Rob

On 1/23/2017 12:43 PM, Brian Garland wrote:

We do this all the time by adding OVERRIDING USER VALUE to the SQL
statement.


EXEC SQL INSERT INTO wiitm OVERRIDING USER VALUE VALUES :wiitm_Ds;

References

Visible links
1. http://archive.midrange.com/rpg400-l/201102/msg00283.html