Re: embedded SQL - how to insert with a GENERATED ALWAYS AS IDENTITY column

On 2/14/11 2:51 PM, Gqcy wrote:

I have a table with a "GENERATED ALWAYS AS IDENTITY" column, but I
don't know how to get my INSERT to work with that column: Is there
some variable type that matches the identity column???

DDL:
CREATE TABLE PDATALIB/TKT001DPF (
TKTSYSID DECIMAL(3,0) DEFAULT 0,
TKTAEMAIL CHAR(64),
TKTREMAIL CHAR(64),
TKTTEMAIL CHAR(64),
TKTPRBNAM CHAR(64),
TKTPRIRTY DEC(2,0) DEFAULT 0,
TKTSTATUS CHAR(26),
TKTTKTTYP CHAR(16),
TKTSUBJEC CHAR(64),
TKTBODY CHAR(1024),
TKTNUMBER INTEGER,
TKTTIMSTMP TIMESTAMP,
TKTTKTDID INT NOT NULL GENERATED ALWAYS AS IDENTITY

/* , primary key (TKTTKTDID) -- may be desired here? */

)
RCDFMT TKT001DR;

code:
Exec SQL
Insert into pdatalib/TKT001DPF

/* column list (omits identity column): */
( TKTSYSID , TKTAEMAIL , TKTREMAIL
, TKTTEMAIL , TKTPRBNAM , TKTPRIRTY
, TKTSTATUS , TKTTKTTYP , TKTSUBJEC
, TKTBODY , TKTNUMBER , TKTTIMSTMP
)

VALUES
( :P1_SysID , :P2_AEmail , :P3_REmail,
:P4_TEmail , :P5_PrbName , :P6_Priority,
:P7_Status , :P8_TktType, :P9_Subject,
:PA_Body , :TICKET# , :TIMSTAMP ) ;


above code generates SQL0117 Statement contains wrong number of
values.

Specify the column-list into which the listed VALUES correlate; see above the inline addendum to the quoted INSERT statement. As noted in another reply, the column list instead can be established using a VIEW, so as to remove that requirement from the INSERT. However again for having omitted the identity column, a column-list for the SELECT in the VIEW must be specified explicitly since there is no effective "omit column" clause for SQL SELECT *; so for example:

create view pdatalib/TKT001DPFv as
( select
TKTSYSID , TKTAEMAIL , TKTREMAIL
, TKTTEMAIL , TKTPRBNAM , TKTPRIRTY
, TKTSTATUS , TKTTKTTYP , TKTSUBJEC
, TKTBODY , TKTNUMBER , TKTTIMSTMP
from pdatalib/TKT001DPF
) /* omit identity column TKTTKTDID */

Note: The CREATE of a TABLE with an IDENTITY column is generally best effected, having a PRIMARY KEY constraint established. There is little protection to ensure uniqueness without either a PRIMARY key or UNIQUE key.

Regards, Chuck