Re: retrieving auto-generated keys on bulk insert

[Mark Matthews]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Dane Foster wrote:
> Paul DuBois wrote:
> 
> "It certainly does.  The behavior you describe below is how INSERT is
> documented in the manual to work with respect to LAST_INSERT_ID()
> and multiple-row inserts -- that is, LAST_INSERT_ID() returns the
> *first* auto-generated ID value.
> 
> This isn't a JDBD issue."
> 
> I would like to point out that yes it is a JDBC issue because (the following is
> copied from the JDBC javadoc):
> <copy>
> getGeneratedKeys
> public ResultSet getGeneratedKeys()throws SQLExceptionRetrieves any
> auto-generated keys created as a result of executing this Statement object. If
> this Statement object did not generate any keys, an empty ResultSet object is
> returned.
> 
> 
>     Returns:
>     a ResultSet object containing the auto-generated key(s) generated by the
> execution of this Statement object
>     Throws:
>     SQLException - if a database access error occurs
>     Since:
>     1.4
> </copy>
> Notice that the word key is plural, meaning if a statement causes the creating
> of more than one key then the ResultSet object should return all keys, not just
> the first key.  So from the JDBC perspective the MySQL JDBC driver's
> implementation of the getGeneratedKeys method is not consistent with the JDBC
> documentation.

If you read the _entire_ JDBC spec, it also states that JDBC drivers are 
only expected to implement what is _possible_ to implement due to 
differences between RDBMSs. That's where the rubber meets the road in 
JDBC-land, and is true for every JDBC driver/Database combination.

The way that the MySQL server currently works makes it _not_possible_ to 
implement this in the way you expect it to work. The text you refers to 
is is more likely describing behavior for multiple IDENTITY columns in a 
single row (which MySQL does not support).

As an aside, this functionality is not tested _at_all_ in the JDBC 
compliance testsuite, especially with multiple generated keys.

If you want to do multiple inserts and get all of the autogenerated 
keys, you could use an updatable result set...The primary key values 
will be filled in by the driver.

Otherwise you will have to do it the way the MySQL manual states.

	-Mark
- -- 
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/

For technical support contracts, visit https://order.mysql.com/?ref=mmma

     __  ___     ___ ____  __
    /  |/  /_ __/ __/ __ \/ /  Mark Matthews <mark@stripped>
   / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
  /_/  /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
         <___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE+HCg8tvXNTca6JD8RAlqrAJ0bLMNV25PFJ+fJOYBZMD98kMcSagCbBAMp
HdLI347t8CVknZ7359mwrLU=
=Lhgr
-----END PGP SIGNATURE-----