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

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(at)fastcrypt(dot)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(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.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
>>