Originally posted by: Ajve

Hi,

Iam new to using Database adapter to insert values into Table via WTX. One of the column is defined as an identity column which will autoincrement when a row is being inserted into database. I created the type tree using the DBTable in DID. Iam able to retrieve the details for other columns using map rules in designer studio, but unable to specify the default value to the column that is defined as identity. Please, help.

Thanks

Ajve

Originally posted by: PaulBrett

Please find a Microsoft SQL Server based example attached.  For the identity column, I just map '=NONE' and let the database pick the next number in the sequence.

Thank you.

Paul

Follow me on Twitter

Attachment(s)

Identity.zip   7 KB 1 version

Originally posted by: Ajve

First of all Thank you Paul for the help.

I tried using NONE in the map rule and it still gave me the following error:

Message: [IBM][CLI Driver][DB2] SQL0803N  One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "INSRT" constrains table "000000020A" from having duplicate values for the index key.  SQLSTATE=23505

Also, the IDENTITY column has a datatype DECIMAL(11,0) and we are using DB2 database. the mainframe code is using the DEFAULT as the value in the INSERT query.

Please, help

Originally posted by: Ajve

Hi Paul,

After resolving the SQLSTATE=23505 by resetting the sequece with the help of DBAs, the insert to identity column was successful after mapping the column to NONE in WTX map rule.

Thanks for the help. 

Originally posted by: Ajve

Hi Paul,

How to implement the following insert command in ITX? The requirement is to insert into DB which has the identity column and also retreive the value to be inserted to DB in an output card, so that item can be used for further processing?

INSERT INTO EMPLOYEE
  (NAME, SALARY, DEPTNO)
  VALUES ('Rupert', 989.99, 50);
  VALUES IDENTITY_VAL_LOCAL() INTO :IVAR; 

Can you please, help on the approach.

Thanks 

Ajve