Re: Problem with executeBatch and "A result was returned when none was expected" - Mailing list pgsql-jdbc

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
>