Monday, March 02, 2009

Understanding return codes of JDBC batchUpdate

Recently I had to make use of the JdbcTemplate.batchUpdate() facility in Spring. I was connecting to the Oracle database using Oracle JDBC driver. As per the documentation, the batchUpdate() function is supposted to return an integer array. Each element in the array contains the number of rows affected the respective INSERT/UPDATE/DELETE query in the batch. But during my testing I found that, I was always getting all the elements to be -2.

Initially I was thinking it was a bug in the driver code. Then when I was referring to the JDBC Programmers Guide, I figured the following:
For a prepared statement batch, it is not possible to know the number of rows affected in the database by each individual statement in the batch. Therefore, all array elements have a value of -2. According to the JDBC 2.0 specification, a value of -2 indicates that the operation was successful but the number of rows affected is unknown.

There are more examples and explonation of error codes in the same page. Especially pay more attention to the case when one of the statements throw an exception:
For example, if there were 20 operations in the batch, the first 13 succeeded, and the 14th generated an exception, then the update counts array will have 13 elements, containing actual update counts of the successful operations.

Hope this helps you are seeing the mysterious -2 as return code!

2 comments:

Abeto said...

So there´s no way to know how many registers were updated?? That's crap!

L. Wayne Precht said...

It's actually even worse than is described here. I had a case where the name of the column changed slightly and the batch inserts worked...mostly. Like 85% of the time. And the batch result array never had a failure code in it, ever. You basically have to take it on faith that it worked, or check your results very carefully.