Re: Problem with executeBatch and "A result was returned when none was expected"

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.