Hi all-
I am wondering if I can get some input on the logic that I would need to
code a function that would mimic mysql's last_insert_id()..
Here is what last_insert_id() does in mysql:
Returns the last automatically generated value that was inserted into an
AUTO_INCREMENT column.
mysql> SELECT LAST_INSERT_ID(); -> 195
The last ID that was generated is maintained in the server on a
per-connection basis. It will not be changed by another client. It will not
even be changed if you update another AUTO_INCREMENT column with a non-magic
value (that is, a value that is not NULL and not 0). If you insert many rows
at the same time with an insert statement, LAST_INSERT_ID() returns the
value for the first inserted row. The reason for this is so that you it
makes it possible to easily reproduce the same INSERT statement against some
other server. If expr is given as an argument to LAST_INSERT_ID(), then the
value of the argument is returned by the function, and is set as the next
value to be returned by LAST_INSERT_ID(). This can be used to simulate
sequences: First create the table:
mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);
Then the table can be used to generate sequence numbers like this:
mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
You can generate sequences without calling LAST_INSERT_ID(), but the utility
of using the function this way is that the ID value is maintained in the
server as the last automatically generated value (multi-user safe). You can
retrieve the new ID as you would read any normal AUTO_INCREMENT value in
MySQL. For example, LAST_INSERT_ID() (without an argument) will return the
new ID. The C API function mysql_insert_id() can also be used to get the
value. Note that as mysql_insert_id() is only updated after INSERT and
UPDATE statements, so you can't use the C API function to retrieve the value
for LAST_INSERT_ID(expr) after executing other SQL statements like SELECT or
SET.
FROM: http://www.mysql.com/doc/en/Miscellaneous_functions.html
Thanks for any pointers on how to tackle this problem (and if it is possible
;-)
I am thinking that I need to store in a table for each transaction whatever
autoincremented values were created (which they are for all my primary
keys)????
-Beth
