I am using RBD v.8, I connect to Oracle database. If I try to retrieve a key of a last inserted row as follows:

execute #sql{ insert into (...) values (...) returning id into :var1};

I get 

execute #sql{ insert into (...) values (...) returning id into var1

I get

EGL0504E EXECUTE: ORA-00905: missing keyword [sqlstate:42000][sqlcode:905] 

What is recommended? Thanks

Hi,

I think the problem is that EGL is not expecting any host variables to be returned in an INSERT.  Maybe one of the developers can provide more info on this, but assuming this is true, then you just need to break this up into two statements.  One for the insert and then a GET to return the id.

I think the second error is occuring because the "returning into" is expecting a host variable with the colon (":") in front of it.

Mark

Hello Mark,

a general pattern is to add a row and get the id of the added row. There is always risk of concurrency issues when two threads are trying to add  rows simultaneously. We have had a few approaches. (1) Using maximum value + 1 as the key but when we have two concurrent threads we will end up with duplicate key. (2) We can keep the last used id in a separate table and when it is opened for update it is locked. It works but it is clumsy. Your suggestion of insert + get is a new one but I wonder if there are concurrency considerations I should be aware of.

In case it matters, we are using DB2 on iSeries.

Tuukka

Maybe I am missing the point, but one technique I have seen is to have a timestamp column...and then use that timestamp value to make a particular row unique.   So, when you insert/update, you always specify the value as CURRENT TIMESTAMP.

Mark

Hello Mark,

I think you got the point perfectly. We could use timestamp but at least in theory there is a veeeery small risk of duplicates. Since we are mostly calling EGL programs from web, we could also use Java's uuid's as unique identifiers when needed. However, IHMO there is something kludgeish in both of these and it would be really nice if the programming language and database connection took care of this. We could simply lock database for inserts but I do not how to accomplish that in EGL (or if there is a good reason why we should not do that).

Tuukka

I know of two approaches using DB2:

1 sql command:

get dbrecord with #sql{    select        id, field1, field2    from new table(        insert into table_name(field1, field2)        values (value1, value2)    )}

2 sql command approach:

insert into tablename(field1, field2) values (value1, value2)SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1

I use the first first approach.

I don't know which one is most portable across databases.

Kind regards,

Bram

Tuukka,

I think the "returning id into :var" is an Oracle feature that does not exist in DB2.

Also, if the need is to lock the table while an insert is being done, then you could issue a "lock table" statement if I read the SQL reference correctly.  However, this might get you into some kind of deadlock situation or you would have to handle the situation in the code if a user is blocked from doing an insert/update due to someone else locking the table.

Bram,

thanks for the examples.  I was looking around as well and found the same techniques.  Here was a link on the subject, but sure there are more.

http://stackoverflow.com/questions/1109621/iseries-db2-is-there-any-way-to-select-the-identity-value-from-an-insert-state