[Firebird-devel] Simplify MySQL migration: LAST_INSERT_ID()

[Roman R. <ro...@ro...>]

Hi,

I am trying to port Joomla CMS to Firebird (just started, so no progress 
here), but I found that it uses one feature that MySQL has in regard to 
auto-increment columns.

Excert from the docs:

"When a new AUTO_INCREMENT value has been generated, you can also obtain 
it by executing a SELECT LAST_INSERT_ID() statement with mysql_query() 
and retrieving the value from the result set returned by the statement.

For LAST_INSERT_ID(), the most recently generated ID is maintained in 
the server on a per-connection basis. It is not changed by another 
client. It is not even changed if you update another AUTO_INCREMENT 
column with a nonmagic value (that is, a value that is not NULL and not 
0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously 
from multiple clients is perfectly valid. Each client will receive the 
last inserted ID for the last statement that client executed."

We do not have the AUTO_INCREMENT data type, but I was thinking that for 
the most use cases (80/20 principle), storing the last value used in the 
gen_id(..) function in the context would do the trick.



Therefore, I suggest to add to the next possible releases feature that 
automatically stores value of the gen_id() function call (as well as 
NEXT VALUE FOR ... clause) into the SYSTEM context (or some new context 
to avoid conflicts).

Comments, please!

Roman