POSTGRESQL: Batch entry 0 SELECT

Question

Hi I have the following method which calls a stored function in postgresql. The call works when I use a standard executequery() method but does not work when I start using batchs. Any help will be appreciated.

public void addstuff3() throws Exception {
 Statement statement = null;
  ResultSet resultSet = null;
  Connection conn = null;
      try {
      // this will load the MySQL driver, each DB has its own driver
      Class.forName("org.postgresql.Driver");
      // setup the connection with the DB.
      conn = DriverManager
          .getConnection("jdbc:postgresql://localhost/newmydb?"
              + "user=new_user&password=password");

   // statements allow to issue SQL queries to the database
      statement = conn.createStatement();
      conn.setAutoCommit(false);
     statement.addBatch("SELECT ADDSTUFF('comp1',     'mdel1','power','PROPERTY','STRING','ON', '1396983600000', 'testing');");
     statement.addBatch("SELECT ADDSTUFF('comp2',     'mdel2','power','PROPERTY','STRING','ON', '1396983600000', 'testing');");
     conn.commit();
     statement.executeBatch();
    } catch (ClassNotFoundException | SQLException e) {
      throw e;
    } finally {
       conn.close();
    //   resultSet.close();
       statement.close();

    }

This is the Error I get:

Batch entry 0 SELECT ADDSTUFF('comp1', 'mdel1','power','PROPERTY','STRING','ON',     '1396983600000', 'testing') was aborted.  Call getNextException to see the cause.
at     org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Sta    tement.java:2743)
    at org.postgresql.core.v3.QueryExecutorImpl$1.handleError(QueryExecutorImpl.java:461)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1928)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)
at     org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2892)
at jdbc.testing.MySQLAccess.addIndicators3(MySQLAccess.java:125)
at jdbc.testing.JDBCTesting.main(JDBCTesting.java:21)

Any help? I am using jdbc and postgresql.

you are committing before executing the batch. There may be other issues but that's the first thing which stood out to me. Also you close your statement/connection in the wrong order (and you're not doing null checks when you do) — Dave
– Dave, Commented Jul 28, 2014 at 21:53
Incidentally, did you follow the advice in the error and call getNextException to see the cause? — Dave
– Dave, Commented Jul 28, 2014 at 21:54
A quick search turns up this which looks helpful stackoverflow.com/questions/15848359/… — Dave
– Dave, Commented Jul 28, 2014 at 22:07
Actually it seems even simpler than that; it's just a method on the exception you catch: docs.oracle.com/javase/7/docs/api/java/sql/… (call that and log the result in your catch block, or just re-throw the result for simplicity since you don't seem to have a logger configured) — Dave
– Dave, Commented Jul 28, 2014 at 22:10

wwjdm · Accepted Answer · 2017-02-06 00:46:50Z

1

ok thanks to @Dave I found that

e.getNextException()

Prints:

 A result was returned when none was expected

I should not return a value

Works!

edited Feb 6, 2017 at 0:46

answered Jul 28, 2014 at 22:13

wwjdm

2,6067 gold badges37 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dave Over a year ago

Ah yes, quite right; a batch is for adding/modifying/removing records/tables, not retrieving data.

Collectives™ on Stack Overflow

POSTGRESQL: Batch entry 0 SELECT

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related