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

From: Sean Bowman <pico303(at)gmail(dot)com>
To: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Problem with executeBatch and "A result was returned when none was expected"
Date: 2011-03-16 09:14:35
Message-ID: C9A5DB9A.5002%pico303@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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(A
bstractJdbc2Statement.java:2586)
at
org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleResultR
ows(AbstractJdbc2Statement.java:2550)
at
org.postgresql.core.v3.QueryExecutorImpl$1.handleResultRows(QueryExecutorImp
l.java:437)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.ja
va:1747)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statem
ent.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

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Oliver Jowett 2011-03-16 09:59:58 Re: Problem with executeBatch and "A result was returned when none was expected"
Previous Message Florent Guillaume 2011-03-11 17:21:12 unwrapping InvocationTargetException in PGXAConnection.ConnectionHandler