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. )
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.
You want your own last inserted row and not the last one in general?
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!!)
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.