Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    7

    Unanswered: Error with IDENTITY_VAL_LOCAL()

    Hi,

    I am using DB2 UDB ESE 8.1.5 on a win2000 server.
    I tried to use the function IDENTITY_VAL_LOCAL to get the most
    up-to-date record with following statement:

    VALUES IDENTITY_VAL_LOCAL() INTO :IVAR

    in the command center, but it prompt me for the below message:
    CLI0100E Wrong number of parameters. SQLSTATE=07001

    I've already turned off the auto commit. Could anyone can give me a
    helping hand, please? Many thanks!

    And also, if I want to use the same function for programming with C#
    .NET, by using OLEDB provider, should I bear anything in mind?

    Regards,
    Chunglun

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by chunglun
    Hi,

    I am using DB2 UDB ESE 8.1.5 on a win2000 server.
    I tried to use the function IDENTITY_VAL_LOCAL to get the most
    up-to-date record with following statement:

    VALUES IDENTITY_VAL_LOCAL() INTO :IVAR

    in the command center, but it prompt me for the below message:
    CLI0100E Wrong number of parameters. SQLSTATE=07001
    The Command Center won't take parameters like that...

    Just use "values identity_val_local()"
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Apr 2004
    Posts
    7
    I also tried just using VALUES IDENTITY_VAL_LOCAL(), but it returned a table with nothing to me. I can't get the identity that I want.

    Actually, I just want to make sure I can get the past inserted identity while programming with C#. Anyone has idea? Thanks!

    Chunglun

  4. #4
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64
    SELECT MAX(key) FROM TABLE;
    With this, you can't go too low.

    Another way is to make an insert AND only then IDENTITY_VAL_LOCAL() will return a value.

    Cheers, Bill

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by chunglun
    I also tried just using VALUES IDENTITY_VAL_LOCAL(), but it returned a table with nothing to me. I can't get the identity that I want.

    Actually, I just want to make sure I can get the past inserted identity while programming with C#. Anyone has idea? Thanks!

    Chunglun
    That should work, assuming you did an insert immediately before into a table with a column defined as IDENTITY, and that you didn't supply a value for that column.

    MAX() is dangerous as someone may have inserted another row in that split second before you get the value... and it can be expensive if the column is not in an index defined for reverse scans or DESC.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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