aspose file tools*
The moose likes JDBC and the fly likes BatchUpdateException : getUpdateCounts doesn't work properly Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "BatchUpdateException : getUpdateCounts doesn Watch "BatchUpdateException : getUpdateCounts doesn New topic
Author

BatchUpdateException : getUpdateCounts doesn't work properly

Jigar M Gohil
Greenhorn

Joined: Dec 14, 2011
Posts: 25
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:


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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3431
    
  47

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
Greenhorn

Joined: Dec 14, 2011
Posts: 25
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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3431
    
  47

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
Greenhorn

Joined: Dec 14, 2011
Posts: 25
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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3431
    
  47

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: BatchUpdateException : getUpdateCounts doesn't work properly
 
Similar Threads
Java
How do i avoid this exception
problem with BatchUpdateException
Get results from user defined query
migrating code from hibernate 2 to hibernate 3.0