Re: 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(at)gmail(dot)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(at)gmail(dot)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(at)gmail(dot)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(at)gmail(dot)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
>