Re: SELECTing the last value inserted into a SERIAL column
I've read over all the answers to this question and most of them
suggested using the sqlca.sqlerrd[2] variable. However, I'm using
third party software to retrieve data from the database, and of course
it doesn't allow me to use the sqlca structure.
Others suggested using the dbinfo function to retrieve the last value.
However, couldn't another insert into that column cause you to retrieve
the incorrect value? Is there anyway to apply a lock so another insert
cannot happen?
I wrote a stored procedure to something similar to what I want. It's
listed below. Does anyone have any suggestions on how to improve it?
I tried to do a select from the table I'm updating within the update
statement, but this wasn't allowed. I got a "Cannot modify table or
view used in subquery" error. Also, in the procedure I wrote I lock
the table. Informix said you have to lock tables within a transaction.
I tried to unlock the table within the transaction, but Informix then
complained that you couldn't do this and the lock would be freed by the
commit work statement. So why would you ever use the unlock table
command.
Thanks for your help.
-Clay Amerault
CREATE PROCEDURE informix.getnextkeyvalue
(inputdatatable CHAR(32))
RETURNING INT;DEFINE localkeyvalue INT;
SET LOCK MODE TO WAIT;
SET ISOLATION TO REPEATABLE READ;
BEGIN WORK;
LOCK TABLE keyvaluetb IN EXCLUSIVE MODE;
SELECT keyvalue + 1 nextkeyvalue FROM keyvaluetb
WHERE datatable = inputdatatable INTO TEMP tempkeyvaluetb;
SELECT nextkeyvalue INTO localkeyvalue FROM tempkeyvaluetb;
UPDATE keyvaluetb
SET keyvalue = localkeyvalue
WHERE datatable = inputdatatable;
DROP TABLE tempkeyvaluetb;
COMMIT WORK;
RETURN localkeyvalue;
END PROCEDURE;