Re: - Mailing list pgsql-jdbc

Patrice,

Yes, that is exactly what I realized yesterday (in my case, it was a previous query in the same
transaction that I ported from an Oracle-based app that wasn't parsing right in postgresql).

David asked me to write a simple test case that demonstrates the problem.  Here it is:

-- BEGIN NoResultTest.java --

import java.io.*;
import java.sql.*;
import java.text.*;

public class NoResultTest {

  Connection db;
  Statement st;

  public void testNoResult(String args[]) throws Exception {

    String url = args[0];
    String usr = args[1];
    String pwd = args[2];

    // Load the driver
    Class.forName("org.postgresql.Driver");

    // Connect to database
    System.err.println("Connecting to Database URL = " + url);
    db = DriverManager.getConnection(url, usr, pwd);

    System.err.println("Connected...Now creating a statement");
    st = db.createStatement();

    // create table outside of transaction to simulate a pre-existing table
    st.executeUpdate("create table empty (empty_id integer)");

    // No results error does not occur unless auto-commit is turned off
    db.setAutoCommit(false);

    try {
      PreparedStatement ps =
        db.prepareStatement("select empty_id emptyID from empty");
      ResultSet rs = ps.executeQuery();
      rs.next();
      rs.close();
    } catch (SQLException e) {
      // should always throw a parse exception
      e.printStackTrace();
      // this fixes the problem
      // db.rollback();
    }

    PreparedStatement ps =
      db.prepareStatement("select empty_id AS emptyID from empty");
    ResultSet rs = ps.executeQuery();
    System.err.println("Has result from well-formed query: " + rs.next());
    rs.close();

    st.executeUpdate("drop table empty");

    // Finally close the database
    System.err.println("Now closing the connection");
    st.close();
    db.close();
  }

  public static void main(String args[]) throws Exception {

    NoResultTest test = new NoResultTest();
    test.testNoResult(args);
  }
}
-- END NoResultTest.java --

Here is the output:

[karl@phoenix karl]$ java -version
java version "1.4.1-rc"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.1-rc-b19)
Java HotSpot(TM) Client VM (build 1.4.1-rc-b19, mixed mode)
[karl@phoenix karl]$ java -cp .:pgjdbc2.jar NoResultTest jdbc:postgresql:karl k\
arl karl
Connecting to Database URL = jdbc:postgresql:karl
Connected...Now creating a statement
java.sql.SQLException: ERROR:  parser: parse error at or near "emptyid"

        at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:94)
        at org.postgresql.Connection.ExecSQL(Connection.java:398)
        at org.postgresql.jdbc2.Statement.execute(Statement.java:130)
        at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:54)
        at org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatemen\
t.java:99)
        at NoResultTest.testNoResult(NoResultTest.java:32)
        at NoResultTest.main(NoResultTest.java:57)
Exception in thread "main" No results were returned by the query.
        at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:58)
        at org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatemen\
t.java:99)
        at NoResultTest.testNoResult(NoResultTest.java:42)
        at NoResultTest.main(NoResultTest.java:57)

Thanks,

Karl

__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/