Re: retrieving auto-generated keys on bulk insert

[Christopher Taylor]

Not to get into an English parsing war here, but there's also the word
"any", which means some (including the singular).

Unless Mark can work some magic with the MySQL protocol, there's no way
(AFAIK) that this can be done... unless he parses the statement and issues
INSERT calls one at a time, fetching the LAST_INSERT_ID() on each operation.
Not very efficient, but it might just work...

-Chris

----- Original Message -----
From: "Dane Foster" <dfoster@stripped>
To: "Paul DuBois" <paul@stripped>
Cc: <java@stripped>
Sent: Wednesday, January 08, 2003 4:20 PM
Subject: Re: retrieving auto-generated keys on bulk insert


> 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>
>
>
>
> ---------------------------------------------------------------------
> 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-thread4856@stripped>
> To unsubscribe, e-mail
<java-unsubscribe-cstaylor=nanshu.com@stripped>
>
>