jdbc bug/feature? - Mailing list pgsql-jdbc

Hello,

There is a bug (I say) or a lack of proper functionality in JDBC driver regarding batch updates.

The following code is expected to work by some library that I use and it does work with inet tds driver for M$ SQL server. It however does not work with the latest jdbc from cvs or anything older than that either.

PreparedStatements st = c.prepareStatement("insert into my_table (field1, field2) values (?, ?)");

// we have say 30 inserts to do
Iterator it = inserts.iterator();
int i=0;
while(it.hasNext()) {

        // there is a maximum batch size of 15, so we must periodically execute it
        if(i==batchMax) {
                st.executeBatch();
                i=0;

                // the problem - after this call a PreparedStatement is empty and consequtive binds fail
        }

        Object [] binds = (Object [])it.next();
        st.setString(binds[0]);
        st.setString(binds[1]);
        st.addBatch();
        i++;
}

Upon some research I discovered the problem and found a very simple solution:

In org.postgresql.jdbc2.AbstractJdbc2Statement:

        public int[] executeBatch() throws SQLException
        {
System.out.println("### executeBatch");
                if (batch == null)
                        batch = new Vector();
                int size = batch.size();
                int[] result = new int[size];
                int i = 0;

// >>> added
                // save m_binds and m_sqlFragments because executeUpdate(String) will destroy them
                String [] oldFrags = m_sqlFragments;
                Object [] oldMBinds = m_binds;
// <<

                try
                {                      
                        for (i = 0;i < size;i++)
                                result[i] = this.executeUpdate((String)batch.elementAt(i));

                }
                catch (SQLException e)
                {
                        int[] resultSucceeded = new int[i];
                        System.arraycopy(result, 0, resultSucceeded, 0, i);

                        PBatchUpdateException updex =
                                new PBatchUpdateException("postgresql.stat.batch.error",
                                                                                  new Integer(i), batch.elementAt(i), resultSucceeded);

                        updex.setNextException(e);

                        throw updex;
                }
                finally
                {
                        batch.removeAllElements();
// >> added                    
                        // restore m_binds and m_sqlFragments
                        m_sqlFragments = oldFrags;
                        m_binds = oldMBinds;
// <<
                }
                return result;
        }

Please consider this as a bug, and patch it in cvs. I did not test this very well, so the described patch may possibly cause some incosistencies somewhere - the people who wrote that class will know best. It does work for my case though.

And thanks for PostgreSQL, I've just started using it and I love it.

- Marko