RE: [gnome-db] Access to last auto_increment value(mysql_insert_id)

["Fernando Martins" <fernando cmartins demon nl>]

Rodrigo Moya wrote:
> I think I'm going to add a get_last_insert_id virtual method to the
> GdaServerProvider, to be implemented by each provider. Anyone has any
> objections?
>

Just a few remarks that might be useful for users of this function in MySQL:

- the LAST_INSERT_ID() SQL function (corresponding mysql_insert_id() in the
C API) is a non SQL 92 standard feature. I don't know if other providers can
implement it, but I would expect (hope) yes;

- if you insert a batch of rows and then call mysql_insert_id() you'll get
the id of the first row;

- the last_insert_id() documentation says:

"The most recently generated ID is maintained in the server on a
per-connection basis. It will not be changed by another client."

and

"If you need to save the value for later, be sure to call mysql_insert_id()
immediately after the query that generates the value."

(Note also no mention to different tables)

Based on this information, I have to assume that:

o if you have a multi-threaded client _carelessly_ sharing a connection and
o if you don't call last_insert_id() in the same transaction in which you
UPDATEd or INSERTed a row

you might very well get into trouble with inconsistent data caused by using
incorrect IDs.

BTW, does libgda provide functions to define BEGIN/END of transactions?

Regards,
Fernando