BUG #5058: [jdbc] Silent failure with executeUpdate() - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      5058
Logged by:          Joseph Shraibman
Email address:      jks@selectacast.net
PostgreSQL version: 8.2.14
Operating system:   Linux
Description:        [jdbc] Silent failure with executeUpdate()
Details:

I was trying to figure out why I was getting different results with sql I
was running through jdbc and running through psql.  I was able to fix the
bug by changing an executeUpdate() to an execute().  See attached code.

I ran this code with the latest 8.2 and 8.4 jdbc drivers against an 8.2.14
database.

--------------------------------------------
public class PgBug {

    transient protected Connection  conn  = null;
    static int verbose = 3;

    public PgBug() throws SQLException, ClassNotFoundException{
        // Load the driver
        Class.forName("org.postgresql.Driver");

        conn  =  DriverManager.getConnection("jdbc:postgresql:playpen",
"postgres", "");;
        System.out.println("jdbc version:
"+org.postgresql.Driver.getVersion());
    }

    private static StringBuilder toString(Object o, StringBuilder sb){
        if (sb == null)
            sb = new StringBuilder();
        if (o == null)
            sb.append("null");
        else if (o.getClass().isArray()){
            Class c = o.getClass().getComponentType();
            if (c.isPrimitive()){
                int len = java.lang.reflect.Array.getLength(o);
                sb.append("[");
                for(int i = 0; i < len ; i++){
                    sb.append(java.lang.reflect.Array.get(o, i));
                    if (i+1 < len)
                        sb.append(',');
                }
                sb.append("]");
            }else{
                Object[] arr = (Object[])o;
                sb.append("{");
                for(int i = 0; i < arr.length ; i++){
                    toString(arr[i],sb);
                    if (i+1 < arr.length)
                        sb.append(',');
                }
                sb.append("}");
            }
        }else
            sb.append(o);
        return sb;
    }
    static java.sql.Statement getStatement(java.sql.Connection conn)throws
java.sql.SQLException{
        return
conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
                                    java.sql.ResultSet.CONCUR_READ_ONLY);
    }
   static int getRsSize(java.sql.ResultSet rs)throws java.sql.SQLException{
        //if  ( ! rs.isBeforeFirst())
        //    throw new IllegalStateException("only call getRsSize on new
ResultSets");
        if (!rs.last()) return 0;
        int ans = rs.getRow();
        rs.first(); rs.previous();  //reset pointer to before first row
        if  ( ! rs.isBeforeFirst())
            throw new java.sql.SQLException(" getRsSize(): Could not reset
pointer");
        return ans;
    }

     private static void printAnyWarnings(java.sql.Statement st,CharSequence
line){
        try{
            java.sql.SQLWarning warn = st.getWarnings();
            if (warn != null)
               System.err.println("SQLWarning: "+warn.getMessage()+" from
sql: "+line);
        }catch(java.sql.SQLException e){}
    }
    public static Object[][] getResults(ResultSet rs)throws SQLException{
        final int num_rows = getRsSize(rs);
        if (num_rows == 0) return new Object[0][0];
        final int cols = rs.getMetaData().getColumnCount();
        if (false){
            String m = "getRes(): cols is "+cols;
            System.out.println(m);
        }
        Object answer[][] = new Object[num_rows][cols] ;

        for (int i = 0 ; rs.next() ; i++ ) {
            for (int j = 0 ; j < cols ; j++) {
                answer[i][j] = rs.getObject(j+1);
                if (verbose >= 3)
                    System.out.println("getResults() answer["+i+"]["+j+"] =
"+answer[i][j]);
                if (answer[i][j] instanceof java.sql.Array)
                    answer[i][j] =
((java.sql.Array)answer[i][j]).getArray();
               else if (answer[i][j] instanceof
org.postgresql.util.PGobject)
                    answer[i][j] = answer[i][j].toString();

            }
        }
        return answer;
    }

    public int doUpdate(String line)throws SQLException{
    if (line == null)
        throw new SQLException (": line is null.");

    Statement st = conn.createStatement();
    int ans = st.executeUpdate(line);
        printAnyWarnings(st,line);
    st.close();
    return ans;
    }
    public Object[][] doQuery(String pre_line, String line, String
post_line, boolean eu)throws SQLException{
        if (line == null)
            throw new SQLException ("doQuery: line is null.");

        boolean get_field_names = false;
        Statement st = null;
        if (pre_line != null || post_line != null){
        st = getStatement(conn);
            if (pre_line != null){
                if (eu)
                    st.executeUpdate(pre_line);
                else
                    st.execute(pre_line);
                printAnyWarnings(st,pre_line);
            }
        }
        ResultSet rs = st.executeQuery(line);
        Object[][] ans = getResults(rs);
        if (verbose >= 1)
            System.out.println("doQuery(): ans is: "+toString(ans, null));
        String[] fa = null;
        if (get_field_names){
            ResultSetMetaData meta = rs.getMetaData();
            int cols = meta.getColumnCount();
            fa = new String[cols];
            for(int i = 1; i <= cols ; i++)
                fa[i-1] = meta.getColumnLabel(i);
        }
        if (post_line != null){
            st.executeUpdate(post_line);
          printAnyWarnings(st,post_line);
        }
        if (st != null)
            st.close();
        if (get_field_names){

        }
        return ans;
    }
    public Object[][] doQuery(String[] pre_line, String line, String
post_line)throws SQLException{
        if (line == null)
            throw new SQLException ("doQuery: line is null.");

        boolean get_field_names = false;
        Statement st = null;
        if (pre_line != null || post_line != null){
        st = getStatement(conn);
            if (pre_line != null){
                for(int i = 0; i < pre_line.length ; i++){
                    st.execute(pre_line[i]);
                    printAnyWarnings(st,pre_line[i]);
                }
            }
        }
        ResultSet rs = st.executeQuery(line);
        Object[][] ans = getResults(rs);
        if (verbose >= 1)
            System.out.println("doQuery(): ans is: "+toString(ans, null));
        String[] fa = null;
        if (get_field_names){
            ResultSetMetaData meta = rs.getMetaData();
            int cols = meta.getColumnCount();
            fa = new String[cols];
            for(int i = 1; i <= cols ; i++)
                fa[i-1] = meta.getColumnLabel(i);
        }
        if (post_line != null){
            st.executeUpdate(post_line);
          printAnyWarnings(st,post_line);
        }
        if (st != null)
            st.close();
        if (get_field_names){

        }
        return ans;
    }
    private String getSeq(int num){
        StringBuilder sb = new StringBuilder();
        for(int i = 0; i < num ; i++){
            sb.append(i);
            if (i+1 < num)
                sb.append(',');
        }
        return sb.toString();
    }
    void setup()throws SQLException{
        doUpdate("CREATE TABLE joa (jobid int, uids int[])");
        for(int i = 1; i < 15 ; i++){
            doUpdate("INSERT INTO joa VALUES("+i+",
ARRAY["+getSeq(i)+"])");
        }
        String q = "CREATE OR REPLACE FUNCTION insert_to_japlcu_table(thearr
int[], jobid int) returns void AS $$\n"+
            "BEGIN\n"+
            " FOR i IN array_lower(thearr,1)..array_upper(thearr,1)
LOOP\n"+
            "execute 'INSERT INTO japlc (jobid, uid) VALUES( ' || jobid ||
',' || thearr[i] || ');';\n"+
            "END LOOP;\n"+
            "END;\n"+
            "$$ language 'plpgsql' VOLATILE ;";
        doUpdate(q);
    }
    void doTest1(boolean eu)throws SQLException{
        String pre = " BEGIN; CREATE TEMP TABLE japlc (jobid int, uid int);
SELECT insert_to_japlcu_table(uids, jobid)  FROM joa; ANALYZE japlc;";

        Object[][] oaa =  doQuery(pre, "SELECT count(*) FROM  japlc;",
"ABORT;", eu);
        System.out.print(toString(oaa, null));

    }
    void doTest2()throws SQLException{
        String[] pre = {" BEGIN;","CREATE TEMP TABLE japlc (jobid int, uid
int);",
                        " SELECT insert_to_japlcu_table(uids, jobid)  FROM
joa;","ANALYZE japlc;"};
        Object[][] oaa =  doQuery(pre, "SELECT count(*) FROM  japlc;",
"ABORT;");
        System.out.print(toString(oaa, null));

    }
    void teardown()throws SQLException{
          doUpdate("DROP TABLE joa;");
    }

    public static final void main(final String[] args)throws SQLException,
ClassNotFoundException {
        PgBug bug = new PgBug();

        try{
            System.out.print("tearing down (in case of previous bad
exit)...");
            bug.teardown();
            System.out.println("done.");
        }catch(SQLException e){
            System.out.println("nothing to do.");
        }

        System.out.print("setting up...");
        bug.setup();
        System.out.println("done.");

        System.out.print("running test 1a...");
        bug.doTest1(true);
        System.out.println("done.");

        System.out.print("running test 1b...");
        bug.doTest1(false);
        System.out.println("done.");

        System.out.print("running test 2...");
        bug.doTest2();
        System.out.println("done.");

        System.out.print("tearing down...");
        bug.teardown();
        System.out.println("done.");
    }

}