Tags: database, db2, error, ese, following, function, identity_val_local, mostup-to-date, mysql, oracle, record, server, sql, udb, win2000

Error with IDENTITY_VAL_LOCAL()

On Database » DB2

2,278 words with 4 Comments; publish: Wed, 05 Dec 2007 05:10:00 GMT; (250203.13, « »)

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

All Comments

Leave a comment...

  • 4 Comments
    • 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()"

      #1; Tue, 11 Dec 2007 17:56:00 GMT
    • 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

      #2; Tue, 11 Dec 2007 17:57:00 GMT
    • 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

      #3; Tue, 11 Dec 2007 17:58:00 GMT
    • 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.

      #4; Tue, 11 Dec 2007 17:59:00 GMT