Обсуждение: Problem with executeBatch and "A result was returned when none was expected"

Sean,

Apparently the only database it works for is mysql. It certainly works
for single prepared statements.

Dave

On Wed, Mar 16, 2011 at 5:14 AM, Sean Bowman <pico303@gmail.com> wrote:
> I've been trying to batch create a bunch of records using stored procedures
> and the executeBatch() method (so I can get back the generated PKID's) with
> no luck.  I've tried PostgreSQL 8.4.2 with the 8.4-701.jar and 8.4-702.jar
> on OS X, PostgreSQL 9.0.3 with the latest 9.0 JAR on both OS X and Ubuntu
> Linux, all under JDK 1.6, and all with the same results.  When I try to call
> executeBatch, I get a BatchUpdateException with the message:  "A result was
> returned when none was expected".
> Here's my simple example that I whittled down from my larger application:
> import java.sql.*;
> public class Check {
>     public static void main(String[] args) throws Exception {
>         try {
>             Class.forName("org.postgresql.Driver");
>             Connection con = DriverManager.getConnection(
>                     "jdbc:postgresql://localhost/mydb", "postgres", null);
>             con.setAutoCommit(false);
>             PreparedStatement stmt = con.prepareStatement(
>                     "INSERT INTO users (email,password,login) VALUES
> (?,?,?)",
>                     Statement.RETURN_GENERATED_KEYS);
>             stmt.setObject(1, "myemail@gmail.com");
>             stmt.setObject(2, "test");
>             stmt.setObject(3, "sbowman");
>             stmt.addBatch();
>             stmt.executeBatch();
>             System.out.println("Got " + stmt.getGeneratedKeys());
>         } catch (ClassNotFoundException e) {
>             e.printStackTrace();
>         } catch (SQLException e) {
>             System.out.println(e.getMessage());
>             System.out.println(e.getNextException().getMessage());
>         }
>     }
> }
> This always, in all settings described above, returns (raised from the
> stmt.executeBatch() line):
> Batch entry 0 INSERT INTO users (email,password,login) VALUES
> ('myemail@gmail.com','test','sbowman') RETURNING * was aborted.  Call
> getNextException to see the cause.
> A result was returned when none was expected.
> Here's the stacktrace:
> java.sql.BatchUpdateException: Batch entry 0 INSERT INTO users
> (email,password,login) VALUES ('myemail@gmail.com','test','sbowman')
> RETURNING * was aborted.  Call getNextException to see the cause.
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2586)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleResultRows(AbstractJdbc2Statement.java:2550)
> at
> org.postgresql.core.v3.QueryExecutorImpl$1.handleResultRows(QueryExecutorImpl.java:437)
> at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1747)
> at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2725)
> at Check.main(Check.java:21)
> I think my code is correct.  If I modify the above code to use a MySQL
> database instead of a PostgreSQL database, everything works fine and I do
> get the PKID's generated by the database back.
> I was under the impression that getGeneratedKeys() was now working, since
> 8.4-701.jar, but for the life of me I can't figure out how.
> If I skip using the batch methods and insert user records one at a time,
> calling "stmt.execute()", then the getGeneratedKeys() call returns a
> ResultSet with the generated ID in it.  So execute() works, but
> executeBatch() doesn't.
> Any suggestions or pointing out my code flaws would be most appreciated.  Or
> does executeBatch just not work?  I've been battling this issue most of the
> day and I'm just about at my wits' end.  Should I just give up and switch to
> calling execute() one at a time?  Seems far less efficient, but at least it
> seems to work.
> Thanks,
> Sean
>

On 16 March 2011 22:14, Sean Bowman <pico303@gmail.com> wrote:

> Any suggestions or pointing out my code flaws would be most appreciated.  Or
> does executeBatch just not work?  I've been battling this issue most of the
> day and I'm just about at my wits' end.  Should I just give up and switch to
> calling execute() one at a time?  Seems far less efficient, but at least it
> seems to work.

You are correct, generated keys don't work with executeBatch() yet.
(There was another thread about this a couple of days ago)

Oliver

On Wed, Mar 16, 2011 at 5:59 AM, Oliver Jowett <oliver@opencloud.com> wrote:
> On 16 March 2011 22:14, Sean Bowman <pico303@gmail.com> wrote:
>
>> Any suggestions or pointing out my code flaws would be most appreciated.  Or
>> does executeBatch just not work?  I've been battling this issue most of the
>> day and I'm just about at my wits' end.  Should I just give up and switch to
>> calling execute() one at a time?  Seems far less efficient, but at least it
>> seems to work.
There are some what more efficient ways to do this, such as
incrementing the sequence by the number of keys you need.

Dave

Thanks for the quick response.  I'll switch it around to execute() each
prepared statement individually, and accumulate the generated ID's.


On 3/16/11 4:04 AM, "Dave Cramer" <pg@fastcrypt.com> wrote:

>Sean,
>
>Apparently the only database it works for is mysql. It certainly works
>for single prepared statements.
>
>Dave
>
>On Wed, Mar 16, 2011 at 5:14 AM, Sean Bowman <pico303@gmail.com> wrote:
>> I've been trying to batch create a bunch of records using stored
>>procedures
>> and the executeBatch() method (so I can get back the generated PKID's)
>>with
>> no luck.  I've tried PostgreSQL 8.4.2 with the 8.4-701.jar and
>>8.4-702.jar
>> on OS X, PostgreSQL 9.0.3 with the latest 9.0 JAR on both OS X and
>>Ubuntu
>> Linux, all under JDK 1.6, and all with the same results.  When I try to
>>call
>> executeBatch, I get a BatchUpdateException with the message:  "A result
>>was
>> returned when none was expected".
>> Here's my simple example that I whittled down from my larger
>>application:
>> import java.sql.*;
>> public class Check {
>>     public static void main(String[] args) throws Exception {
>>         try {
>>             Class.forName("org.postgresql.Driver");
>>             Connection con = DriverManager.getConnection(
>>                     "jdbc:postgresql://localhost/mydb", "postgres",
>>null);
>>             con.setAutoCommit(false);
>>             PreparedStatement stmt = con.prepareStatement(
>>                     "INSERT INTO users (email,password,login) VALUES
>> (?,?,?)",
>>                     Statement.RETURN_GENERATED_KEYS);
>>             stmt.setObject(1, "myemail@gmail.com");
>>             stmt.setObject(2, "test");
>>             stmt.setObject(3, "sbowman");
>>             stmt.addBatch();
>>             stmt.executeBatch();
>>             System.out.println("Got " + stmt.getGeneratedKeys());
>>         } catch (ClassNotFoundException e) {
>>             e.printStackTrace();
>>         } catch (SQLException e) {
>>             System.out.println(e.getMessage());
>>             System.out.println(e.getNextException().getMessage());
>>         }
>>     }
>> }
>> This always, in all settings described above, returns (raised from the
>> stmt.executeBatch() line):
>> Batch entry 0 INSERT INTO users (email,password,login) VALUES
>> ('myemail@gmail.com','test','sbowman') RETURNING * was aborted.  Call
>> getNextException to see the cause.
>> A result was returned when none was expected.
>> Here's the stacktrace:
>> java.sql.BatchUpdateException: Batch entry 0 INSERT INTO users
>> (email,password,login) VALUES ('myemail@gmail.com','test','sbowman')
>> RETURNING * was aborted.  Call getNextException to see the cause.
>> at
>>
>>org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleErro
>>r(AbstractJdbc2Statement.java:2586)
>> at
>>
>>org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleResu
>>ltRows(AbstractJdbc2Statement.java:2550)
>> at
>>
>>org.postgresql.core.v3.QueryExecutorImpl$1.handleResultRows(QueryExecutor
>>Impl.java:437)
>> at
>>
>>org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl
>>.java:1747)
>> at
>>
>>org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:4
>>07)
>> at
>>
>>org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Sta
>>tement.java:2725)
>> at Check.main(Check.java:21)
>> I think my code is correct.  If I modify the above code to use a MySQL
>> database instead of a PostgreSQL database, everything works fine and I
>>do
>> get the PKID's generated by the database back.
>> I was under the impression that getGeneratedKeys() was now working,
>>since
>> 8.4-701.jar, but for the life of me I can't figure out how.
>> If I skip using the batch methods and insert user records one at a time,
>> calling "stmt.execute()", then the getGeneratedKeys() call returns a
>> ResultSet with the generated ID in it.  So execute() works, but
>> executeBatch() doesn't.
>> Any suggestions or pointing out my code flaws would be most
>>appreciated.  Or
>> does executeBatch just not work?  I've been battling this issue most of
>>the
>> day and I'm just about at my wits' end.  Should I just give up and
>>switch to
>> calling execute() one at a time?  Seems far less efficient, but at
>>least it
>> seems to work.
>> Thanks,
>> Sean
>>

I'm actually using UUID's, so it's even easier.  I was just hoping to let
the database routines do the UUID generation, but I can do that on the
Java side instead.

On 3/16/11 4:10 AM, "Dave Cramer" <pg@fastcrypt.com> wrote:

>On Wed, Mar 16, 2011 at 5:59 AM, Oliver Jowett <oliver@opencloud.com>
>wrote:
>> On 16 March 2011 22:14, Sean Bowman <pico303@gmail.com> wrote:
>>
>>> Any suggestions or pointing out my code flaws would be most
>>>appreciated.  Or
>>> does executeBatch just not work?  I've been battling this issue most
>>>of the
>>> day and I'm just about at my wits' end.  Should I just give up and
>>>switch to
>>> calling execute() one at a time?  Seems far less efficient, but at
>>>least it
>>> seems to work.
>There are some what more efficient ways to do this, such as
>incrementing the sequence by the number of keys you need.
>
>Dave

So looking at the code if you add RETURN_GENERATED_KEYS to the
statement it returns all of the columns. That's rather ugly. Is there
a way to determine just the generated keys ?

Dave

So looking at the code if you add RETURN_GENERATED_KEYS to the
statement it returns all of the columns. That's rather ugly. Is there
a way to determine just the generated keys ?

On Wed, Mar 16, 2011 at 6:43 AM, Vitalii Tymchyshyn <tivv00@gmail.com> wrote:
> 16.03.11 12:40, Dave Cramer написав(ла):
>
> So looking at the code if you add RETURN_GENERATED_KEYS to the
> statement it returns all of the columns. That's rather ugly. Is there
> a way to determine just the generated keys ?
>
> AFAIR it was a long discussion on what is "generated". E.g. is calculated
> default value (CURRENT TIMESTAMP) generated? Constant default value? So, the
> driver now either returns all or you can pass columns you require by
> yourself with "boolean execute(String sql,  String[] columnNames)".
>
So ideally we need some backend support here. The backend can
certainly figure out what was the result of a default column.

Dave

16.03.11 12:57, Dave Cramer написав(ла):
> On Wed, Mar 16, 2011 at 6:43 AM, Vitalii Tymchyshyn<tivv00@gmail.com>  wrote:
>> 16.03.11 12:40, Dave Cramer написав(ла):
>>
>> AFAIR it was a long discussion on what is "generated". E.g. is calculated
>> default value (CURRENT TIMESTAMP) generated? Constant default value? So, the
>> driver now either returns all or you can pass columns you require by
>> yourself with "boolean execute(String sql,  String[] columnNames)".
>>
> So ideally we need some backend support here. The backend can
> certainly figure out what was the result of a default column.
The question AFAIK is not technical, there's simply no strict definition
of "generated". And no public consensus on it.

Best regards, Vitalii Tymchyshyn

On Wed, Mar 16, 2011 at 7:04 AM, Vitalii Tymchyshyn <tivv00@gmail.com> wrote:
> 16.03.11 12:57, Dave Cramer написав(ла):
>>
>> On Wed, Mar 16, 2011 at 6:43 AM, Vitalii Tymchyshyn<tivv00@gmail.com>
>>  wrote:
>>>
>>> 16.03.11 12:40, Dave Cramer написав(ла):
>>>
>>> AFAIR it was a long discussion on what is "generated". E.g. is calculated
>>> default value (CURRENT TIMESTAMP) generated? Constant default value? So,
>>> the
>>> driver now either returns all or you can pass columns you require by
>>> yourself with "boolean execute(String sql,  String[] columnNames)".
>>>
>> So ideally we need some backend support here. The backend can
>> certainly figure out what was the result of a default column.
>
> The question AFAIK is not technical, there's simply no strict definition of
> "generated". And no public consensus on it.

Yes, this is a typical problem with JDBC.

Oliver Jowett wrote:
> Sean Bowman wrote:
>> I've been trying to batch create a bunch of records using stored procedures and
>> the executeBatch() method (so I can get back the generated PKID's) with no luck. ...
>> When I try to call executeBatch, I get a BatchUpdateException with the message:
>> "A result was returned when none was expected".

This is the documented result when a statement tries to create a result set:
"Throws BatchUpdateException (a subclass of SQLException) if one of the
commands sent to the database fails to execute properly or attempts to return
a result set."

...

>> Any suggestions or pointing out my code flaws would be most appreciated.  Or
>> does executeBatch just not work?  I've been battling this issue most of the
>> day and I'm just about at my wits' end.  Should I just give up and switch to
>> calling execute() one at a time?  Seems far less efficient, but at least it
>> seems to work.
>
> You are correct, generated keys don't work with executeBatch() yet.
> (There was another thread about this a couple of days ago)

How would you retrieve the result if 'executeBatch()' did what you're asking?

There's nothing in the documentation for 'Statement#getGeneratedKeys()' that
indicates a need for a 'RETURNING' clause in the 'INSERT'.

As near as I can tell from the API docs, the behavior you experienced is
required.  Postgres tells us about RETURNING that:
"If the INSERT command contains a RETURNING clause, the result will be similar
to that of a SELECT statement containing the columns and values defined in the
RETURNING list, computed over the row(s) inserted by the command."
http://www.postgresql.org/docs/9.0/interactive/sql-insert.html

A SELECT would return a 'ResultSet' through JDBC, thus the API requires your
statement to throw the exception that you got.

--
Lew
Honi soit qui mal y pense.

Please stop top-posting.

Sean Bowman wrote:
> Thanks for the quick response.  I'll switch it around to execute() each
> prepared statement individually, and accumulate the generated ID's [sic].

Why don't you try it batched without the RETURNING clause, and use
'getGeneratedKeys()' as the docs suggest?

--
Lew
Honi soit qui mal y pense.

On 19 March 2011 11:49, Lew <noone@lewscanon.com> wrote:

> Sean Bowman wrote:
>>
>> Thanks for the quick response.  I'll switch it around to execute() each
>> prepared statement individually, and accumulate the generated ID's [sic].
>
> Why don't you try it batched without the RETURNING clause, and use
> 'getGeneratedKeys()' as the docs suggest?

There *is* no RETURNING clause in the application's original query
(see the code in the OP), that clause is being added by the driver to
implement Statement.RETURN_GENERATED_KEYS.

Merely requesting generated keys shouldn't really make executeBatch()
explode like this - the app is executing bare INSERTs as far as it
knows. We could definitely do better here.

Oliver

On 19 March 2011 11:47, Lew <noone@lewscanon.com> wrote:
> Oliver Jowett wrote:

> This is the documented result when a statement tries to create a result set:
> "Throws BatchUpdateException (a subclass of SQLException) if one of the
> commands sent to the database fails to execute properly or attempts to
> return a result set."

However, the query that the app provided was just a simple INSERT,
which does not return a result set, so this isn't really relevant
here. If the driver decides to transform the query, it's also
responsible for cleaning the mess up afterwards!

>> You are correct, generated keys don't work with executeBatch() yet.
>> (There was another thread about this a couple of days ago)
>
> How would you retrieve the result if 'executeBatch()' did what you're
> asking?

Via getGeneratedKeys(). The driver already knows that it added the
RETURNING clause to implement Statement.RETURN_GENERATED_KEYS, and it
deals with the eventual resultset in the simple query execution path
(so that it looks to the caller as if the original query executed with
no RETURNING clause). The missing piece is equivalent code for the
batch execution path.

Oliver

SQL databases won't return any data that was created without the RETURNING
clause.  Also, if you read the original post, you'd see the RETURNING
clause gets appended and does just fine when not in batch mode.

But that all said, individual SQL execute() statements are fine for the
near future.  I'll worry about performance when we've got a million
customers.

On 3/19/11 2:46 AM, "Oliver Jowett" <oliver@opencloud.com> wrote:

>There *is* no RETURNING clause in the application's original query
>(see the code in the OP), that clause is being added by the driver to
>implement Statement.RETURN_GENERATED_KEYS.