Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Posts
    21

    Unanswered: Retrieving identity column after AddNew

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The only way I know is to use the IDENTITY_VAL_LOCAL function.

    Andy

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •