Re: Multiple Statement result set problem with PreparedStatements

Lenard, Rohan (Rohan) wrote:
> I have one but it relies on particular tables existing - if I get a few
> minutes I too will simplify it to cause it ..

Hm, ok, try the attached testcase. It works fine for me here against
8.0.1 and 7.3.8 servers:

> oliver@extrashiny ~/pgjdbc-test $ java -classpath .:/home/oliver/pgjdbc/jars/postgresql.jar TestMultiInsert
'jdbc:postgresql://localhost:5432/test?user=oliver'
> Result #1 has resultset? false update count 1
> Result #2 has resultset? false update count 1
> Result #3 has resultset? true update count -1
> oliver@extrashiny ~/pgjdbc-test $ java -classpath .:/home/oliver/pgjdbc/jars/postgresql.jar TestMultiInsert
'jdbc:postgresql://localhost:5738/test?user=oliver'
> Result #1 has resultset? false update count 1
> Result #2 has resultset? false update count 1
> Result #3 has resultset? true update count -1

Those are the results I was expecting, anyway.. were you expecting
something different?

-O
import java.sql.*;

// Run with one argument: a JDBC url to connect to.
public class TestMultiInsert {
    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver");
        Connection c = DriverManager.getConnection(args[0]);

        c.setAutoCommit(false);

        Statement s = c.createStatement();

        s.executeUpdate("CREATE SEQUENCE seq_i");
        s.executeUpdate("CREATE SEQUENCE seq_j");
        s.executeUpdate("CREATE TEMP TABLE t1 (i int4 default nextval('seq_i'), zz text)");
        s.executeUpdate("CREATE TEMP TABLE t2 (j int4 default nextval('seq_j'), i int4, zz text)");

        PreparedStatement ps = c.prepareStatement("INSERT INTO t1(zz) VALUES (?); INSERT INTO t2(i,zz)
VALUES(currval('seq_i'),?);SELECT currval('seq_i'), currval('seq_j')"); 
        ps.setString(1, "A");
        ps.setString(2, "B");

        boolean resultType = ps.execute();
        int updateCount = ps.getUpdateCount();
        int num = 1;

        do {
            System.err.println("Result #" + num + " has resultset? " + resultType + " update count " + updateCount);
            ++num;

            resultType = ps.getMoreResults();
            updateCount = ps.getUpdateCount();
        } while (resultType || updateCount != -1);

        s.close();

        c.rollback();
        c.close();
    }
}