Hi all,

I got into a problem. I am using ADO in order to insert and update the data. Whenever I tried to insert a row in a table that has an identity column, it does not return the identity column value.

Here is the SQL for our table

CREATE TABLE T_WORKSTATION(
Id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
START WITH 1,
INCREMENT BY 1,
NO CACHE ),
Location_Id INTEGER DEFAULT 0,
Workstation VARCHAR(255),
CONSTRAINT CC1263817423857 PRIMARY KEY(Id) );

Here is the ADO code I am using to insert new row in the table

ADODB::_ConnectionPtr ipConn;
ipConn.CreateInstance( __uuidof(ADODB::Connection) );

ipConn->Open( "Provider=IBMDADB2.DB2COPY1;"
"Persist Security Info=False;"
"User ID=\"\";"
"Data Source=LABLINE;"
"Location=\"\";"
"Extended Properties=\"trusted_connection=yes\"",
"",
"",
ADODB::adConnectUnspecified );


ipConn->Attributes = ipConn->Attributes | ADODB::adXactCommitRetaining;

ipConn->BeginTrans();

ADODB::_RecordsetPtr ipRec;
ipRec.CreateInstance( __uuidof(ADODB::Recordset) );

ipRec->CursorLocation = ADODB::adUseClient;

ipRec->Open( "SELECT * FROM T_WORKSTATION WHERE Location_Id = 1 ",
(IDispatch *)ipConn,
ADODB::adOpenStatic,
ADODB::adLockOptimistic,
ADODB::adCmdUnknown );


ipRec->AddNew();
ipRec->Fields->Item["Location_Id"]->Value = 1L;
ipRec->Fields->Item["Workstation"]->Value = "\\\\Test";

ipRec->Update();
ipConn->CommitTrans();

long Id = (long)ipRec->Fields->Item["Id"]->Value;

ipRec->Close();
ipRec = NULL;


Here in this code, the value of Id column is always returned zero.

Any help will be highly appreciated.

Thanks in advance.

Regards.

The only way I know is to use the IDENTITY_VAL_LOCAL function.

Andy

I am not really sure I understand your question, since I don't see an insert statement in your code. However, you may be interested in this SQL statment:

Select ID from FINAL TABLE (insert into T_WORKSTATION (Location_Id, Workstation) VALUES (2, 'Workstation name');

You will get the generated column value back with the above statement.