BatchUpdateException : getUpdateCounts doesn't work properly

[Jigar M Gohil]

Hi All,
I am trying to capture details of failed records in the batch update using the getUpdateCounts() method of BatchUpdateException class.
Even if single query failes to execute from the betch, the method returns -3 (Statement.EXECUTE_FAILED) for all queries.
Following is the log:

>>>>Total Queries : 8
Update Count For Query 1 >>> -3
Update Count For Query 2 >>> -3
Update Count For Query 3 >>> -3
Update Count For Query 4 >>> -3
Update Count For Query 5 >>> -3
Update Count For Query 6 >>> -3
Update Count For Query 7 >>> -3
Update Count For Query 8 >>> -3

I read that the behaviour of getUpdateCounts() method depends on the Driver Implementation. I am using oracle.jdbc.driver.OracleDriver for the connection.
Is there a problem with this implemetation?

Let me know if anyone knows the solution/workaround for this.

Thanks & Regards
Jigar Gohil.

[Martin Vashko]

Hi Jigar, and welcome to the Ranch!

This is documented here; look up the correct version of the documentation if you're using Oracle JDBC driver other than 11g.

Some of the updates in your batch failed. The stack trace might contain more information about what went wrong with the statements. I'm using update batching with Oracle JDBC myself without problems, so I assume it generally works well.

[Jigar M Gohil]

Thanks Martin!!!

We are using 10g. Found the guide http://docs.oracle.com/cd/B19306_01/java.102/b14355.pdf
It mentions the behavior for Prepared Statement as:

For a prepared statement batch, it is not possible to know which operation failed.
The array has one element for each operation in the batch, and each element has a
value of -3. According to the JDBC 2.0 specification, a value of -3 indicates that
an operation did not complete successfully. In this case, it was presumably just one
operation that actually failed, but because the JDBC driver does not know which
operation that was, it labels all the batched operations as failures.

The method has not been implemented as expected

Thinking of some other way of implementation by which I can track the failed statements.

[Martin Vashko]

Did you try to have a look at the stack trace? I'd expect it to contain an exception describing what has caused the exception (database constraint, wrong numeric format, etc.). Even if you don't know the exact row which caused the problem, his information might help you find it.

You could also keep all the values you used in one batch in a list, for example, and in an error occurs, dump them out to the log. You wouldn't know which of them has failed, but you could at least inspect the full contents of the batch that failed. (If the batch is written successfully, throw the contents of the list away, of course.)

[Jigar M Gohil]

Yes Martin, I am already keeping track of the PKs at Batch level.
Its not that I am facing some issue. But before deploying my code into Production, I wanted to handle this.
So that instead of digging into the whole batch, if I can log the exact data to look for fix.

[Martin Vashko]

Logging the batch contents only when it failed seems reasonable to me, most of the time it won't be logged, so it won't blow up your logs in normal situations.

And remember, even if you can pinpoint the exact row which caused the exception, the issue might not be obvious. If you're entering ten rows, and the last row collides with the first on an unique index, you won't see that this was what the cause unless you've logged the first row too. Generally, you need to know the initial state of the database and the full contents of a transaction to be able to pinpoint every possible error that could happen.