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

[Paul V. <pa...@vi...>]

Roman wrote:

> 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).

I don't know if this is a good or bad idea. Personally, I don't like the
fact that LAST_INSERT_ID() returns the last value generated by *any*
generator.

If I need to know a generated ID, it's usually from a specific generator.
If I want it in a variable, I can have the trigger that calls the
generator store it in a user context variable.


Paul Vinkenoog