Re: Batches of single-insert statements vs batches of multi-insert statements - Mailing list pgsql-jdbc

Hi Christopher,
 I suggest you avoid trying to hard coding data values in the statement sql. Instead use parameter place-holders (?) and use the API to bind the value. You will find it works much better for you. Like this ....

String insert = "INSERT INTO T_AbCd (T_AbCd_ID, SomeDate, ASmallInt) VALUES (?, ?, ?)";
PreparedStatement pst = conn.prepareStatement(insert, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
pst.setLong(1, 24202712L);
pst.setDate(2, new java.sql.Date(2014-1900, 3-1, 21));
pst.setInt(3, 0);
pst.addBatch();
pst.setLong(1, 24202713L);
pst.setDate(2, new java.sql.Date(2014-1900, 3-1, 22));
pst.setInt(3, 0);
pst.addBatch();
pst.executeBatch();


 The reason is the batched re-write feature builds dynamically the sql issued to the back end. Using parameter place-holders. So you can see hard coding data values isn't going to work without some horribly complex statement parsing added to the driver. This time parsing data and recognizing all data values and type permutations. yikes

 I'd say you have these options. If you want to enable batched re-writing.

a) Change your code using the same style of coding as the above example. Re-deploying to production.
b) If re-deploy to production isn't possible in the short term try using Byteman [1]. Byteman will unobtrusively mend your existing code. See the attached Byteman rule. The attached rule fixes your test class.
c) Provide a patch to add implement sql statement parsing support for hard coded data. I see you have already started to try this [2].

Regards,
Jeremy

[1] http://byteman.jboss.org/
[2] https://github.com/pgjdbc/pgjdbc/pull/580

On 05/06/16 21:39, Christopher Deckers wrote:

-- 
Jeremy Whiting
Senior Software Engineer, Middleware Performance Team
Red Hat

------------------------------------------------------------
Registered Address: Red Hat UK Ltd, 64 Baker Street, 4th Floor, London. W1U 7DF. United Kingdom.
Registered in England and Wales under Company Registration No. 03798903. Directors: Directors:Michael Cunningham (US), Michael O'Neill(Ireland), Eric Shander (US)