GeeCON Prague 2014*
The moose likes JDBC and the fly likes How to track the failed record in batch excution? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to track the failed record in batch excution?" Watch "How to track the failed record in batch excution?" New topic
Author

How to track the failed record in batch excution?

kiran nyala
Ranch Hand

Joined: Sep 21, 2009
Posts: 47

Hi,

I am inserting a milliions of Emplyoee details in to a table with batch operation as 1000 records per batch. Even a record failed to insert still executeBatch() method executes the remaining statements added to batch. But how can we track the failed record?

For eaxample in first 1000 records 50th record is failed to insert even though remaining 950 records inserted successfully. Now I want to track the 50th record which is failed.

Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30586
    
154

Can you do a select query to see which primary key has not been added?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Maxim Karvonen
Ranch Hand

Joined: Jun 14, 2013
Posts: 103
    
  11
Hi, kiran.

Did you read documentation on executeBatch and BatchUpdateException? It contains all the information necessary to find failed rows during batch updates.
kiran nyala
Ranch Hand

Joined: Sep 21, 2009
Posts: 47

First Thank you for you suggestion..

After reading API I tried like below


Output:
Number of affected rows before Batch Error :: 999
Number of affected rows not available: 0
Failed Count in Batch because of Error: 1

Here For first batch of 1000 records 800th record is made exception but remaining records in batch continued to insert so 999 records affected in DB and 1 record failed (i.e., 800th record) to insert. Then No Batch added as Exception raised in first batch. Here I am able to find count of records which are success and failed but not the exact record which is failed.

"you may ask that 800th gets failed, but i know that manually its a wrong one."
E Armitage
Rancher

Joined: Mar 17, 2012
Posts: 892
    
    9
Likely the positions on the update count in the array follow the order in which the statements were executed so if updateCounts[i] == Statement.EXECUTE_FAILED then statement i failed.
kiran nyala
Ranch Hand

Joined: Sep 21, 2009
Posts: 47

Thanks again

The Below code will print the index position of failed record



output:

Failed to execute record at: 800


So we can get the index of failed record. Can't we get this record values like Employee Name etc...???
E Armitage
Rancher

Joined: Mar 17, 2012
Posts: 892
    
    9
Find one record that failed and compare the values from your list of employees at the given position. e.g print out the employee at employee.get(i) and surrounding values and check if it matches with the reported index.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

kiran nyala wrote:So we can get the index of failed record. Can't we get this record values like Employee Name etc...???

You could keep the data you've used to set parameters of the last batch and the use the index to find the record in your data.

However, not all databases implement this functionality. Oracle, for example, doesn't report back which rows failed and which succeeded. You should therefore verify it with all the databases you want to run the code on.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to track the failed record in batch excution?