Although I have been around SQL databases for quite a few years, I am very new to Informix, and have encountered an issue in a project that I have inherited.

I need to retrieve the value of the RowID of the last record inserted so that I can ( informally ) link that table to another. ( Not an explicit constraint in the database, but the business logic would like to know. )


Any suggestions?


Thanks,
Brian

one foolproof and surefire way is to query back the row using the value of some other candidate key

one of the benefits of this method is that you do not need to use transaction locking

if your table has no candidate keys other than the surrogate autonumber/serial column, then you're in bigger trouble than you think

Thanks for the reply r937 ( Rudy? ).

Unfortunately, while I agree with your thought, and would use it under "normal" circumstances, I am working in the middle of "genericizing" code, wrapper code, and wanted to find an generic solution that would be able to give me that value as a side effect of the Insert.

I guess what I could do would be to double up all of the "save()"s ( which could be potentially either Inserts or Updates ) and follow them with Selects, if that is the Informix way.


Brian

I don't understand you completely, but you should never, never, NEVER work with rowids!

Rowids are unique in table but not consistent, they can change!

You can use a serial column, get the highest number with a select ( select max(serial_col) from tab; ) and you have your last inserted row.

You can also use a trigger which inserts the row in another table.

And never work with rowids

bzzzzt, wrong

that only works in a single-user environment

You want your own last inserted row and not the last one in general?

It could be possible with SQL Trace.

no, i'm talking about a race condition

user one adds a row, it gets assigned id 52
user two adds a row, it gets assigned id 53
user one does his SELECT MAX, and gets 53 (wrong!!)
user two does his SELECT MAX, and gets 53 (right!! a coincidence!!)

see tocttou

there are two options: use transaction locking, or find a different method

hint: requerying with a candidate key is lock-free and guaranteed safe against race conditions

I wound up with a version of R937's solution, because there are only a few places where the "Last Inserted ID" is needed for other uses.

As it happens, because not all of the tables in this system have Serial fields for their IDs, the Insert wrapper code does a "Lock Table Exclusive" followed by "select NVL( id, 0 ) from table" before any Insert that it is doing, so that solution is similar to another one in this thread.

Thank you all for the suggestions.

Brian

Try this:

RowID = SQLCA.SQLERRD[2]

where RowID is integer type.

Best regards.