I have a connection wrapper that lets me use only one Statement per connection. (ie- you pass in SQL string, it opens a connecection, executes your statements, grabs the results, closes the connection and returns the results in a custom object, but not the ResultSet.) What I need is to get the LAST_INSERT_ID, but I can't figure out how to do it.

With MS SQLServer, I can pass in two SQL statements, separated by a ';' and get the result set for the last one.

INSERT INTO [table] field1, field2 VALUES(value1, value2); SELECT @@IDENTITY;

and the ResultSet returns the auto-generated number. This doesn't seem to work with the MM drivers (I know the brackets don't belong in the MySQL SQL). Is there any workaround? Remember, it has to be able to be encapsulated in a single Statement execution because if I use two separate calls, I could lose atomicity in that another client could perform an insert between the original INSERT and the SELECT LAST_INSERT_ID() calls.

Thanks,

Greg