Re: retrieving auto-generated keys on bulk insert

[Dane Foster]

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.

Dane Foster
Equity Technology Group, Inc.
http://www.equitytg.com
954.360.9800
----- Original Message -----
From: "Paul DuBois" <paul@stripped>
To: "Dane Foster" <dfoster@stripped>; <java@stripped>
Sent: Tuesday, January 07, 2003 23:31
Subject: Re: retrieving auto-generated keys on bulk insert


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


---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/doc/         (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <java-thread4855@stripped>
To unsubscribe, e-mail <java-unsubscribe-dfoster=equitytg.com@stripped>