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

[Rodrigo Moya <rodrigo gnome-db org>]

On Mon, 2004-04-19 at 23:30 +0200, Fernando Martins wrote:
> 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.
> 
yes, we probably need to do something...

> BTW, does libgda provide functions to define BEGIN/END of transactions?
> 
yes, of course.

cheers