Re: retrieving auto-generated keys on bulk insert

[Paul DuBois]

At 16:41 -0500 1/7/03, Dane Foster wrote:
>Hi Mark.
>
>The Connector/J JDBC driver does not correctly handle returning auto-generated
>keys on multi row inserts.

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'm not sure if this is a bug because the multi row
>INSERT syntax in MySQL is MySQL specific therefore the JDBC rules 
>may not apply.
>On the other hand, the ANSI/ISO SQL standard supports multi row inserts, and
>since the JDBC API is geared towards the ANSI/ISO SQL standard maybe the JDBC
>rules do apply and it is indeed a bug.
>
>Let my back up for a moment and clarify in case I lost anyone.  Lets 
>pretend we
>have a database table named 'MY_TABLE' that looks something like this:
>
>CREATE TABLE MY_TABLE(
>pk    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
>misc  VARCHAR (255),
>PRIMARY KEY (pk));
>
>Using Connector/J I have code similar to:
><code>
>String sql = "INSERT INTO MY_TABLE (misc) VALUES ('Test')";
>Statement st = connection.createStatement();
>
>st.executeUpdate( sql );
>ResultSet rs = st.getGeneratedKeys();
>
>if( rs.next() )
>     System.out.println( "The auto-generated key is " + rs.getLong( 1 ) );
>else
>     throw new IllegalStateException( "No auto-generated key found." );
></code>
>
>For a newly created 'MY_TABLE' table the code snippet above would produce:
>The auto-generated key is 1
>
>The next snippet of code is where the problem lies.
><code>
>String sql = "INSERT INTO MY_TABLE (misc) VALUES ('Test1'),('Test2')";
>Statement st = connection.createStatement();
>
>st.executeUpdate( sql );
>ResultSet rs = st.getGeneratedKeys();
>
>while( rs.next() )
>     System.out.println( "The auto-generated key is " + rs.getLong( 1 ) );
></code>
>
>For a newly created 'MY_TABLE' table the code snippet would produce:
>The auto-generated key is 1
>
>If you missed it; There is no second line of output!
>
>Mark, please get back to me about this when you can.  It's kind of 
>important for
>a project that I'm working on.  I would make recommendations on a 
>fix but I know
>absolutely zilch about the MySQL network protocol.  Thanx.
>
>
>Dane Foster
>Equity Technology Group, Inc.
>http://www.equitytg.com
>954.360.9800