Re:

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(at)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(at)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/