Skip site navigation (1) Skip section navigation (2)

jdbc bug/fetaure?

From: Marko Štrukelj <strukelj(at)parsek(dot)net>
To: "'pgsql-jdbc(at)postgresql(dot)org'" <pgsql-jdbc(at)postgresql(dot)org>
Subject: jdbc bug/fetaure?
Date: 2002-11-16 11:52:32
Message-ID: EE047BABDB69D6119C6200508B8B7F5E093C62@triglav.parsek.net (view raw or flat)
Thread:
Lists: 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.

pgsql-jdbc by date

Next:From: Marko ŠtrukeljDate: 2002-11-16 12:20:00
Subject: jdbc bug/feature?
Previous:From: Barry LindDate: 2002-11-16 03:28:22
Subject: Re: how can I recover from truncate write?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group