retrieving auto-generated keys on bulk insert

[Dane Foster]

Hi Mark.

The Connector/J JDBC driver does not correctly handle returning auto-generated
keys on multi row inserts.  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