JDBC Batch insert exception handling

I am performing a JDBC batch insert (inserting 1000 rows approx at a time) each time my program is executed. But i am not able to handle the exception thrown by some of the records properly.

Suppose, the 100th record out of 1000 records is throwing an exception because of an invalid data or size of some value exceeds the column size. Once the exception has occured, the remaining records are not getting inserted and the program fails in between.

What i want is even if the 100th record is throwing exception, the remaining insertions should happen as usual before my program ends.

Here is a sample code i am using in my app for batch insert. Suppose result set have got approx 1000 records:

Code:

---

PreparedStatement ps = null; 
while(rs.next()){ 
 
//Retrieve the value and set it to a Prepared statement 
 
String name = rs.getString("Name"); 
int age = rs.getInt("Age"); 
ps.setInt(1, age); 
ps.setString(2, name); 
 
//Finally invoke addBatch 
 
ps.addBatch(); 
 
} 
//Finally call the executeBatch method 
 
ps.executeBatch();

---

If the 100th record is throwing exception then i want to trigger the process only from the 100th too 1000th record. Is there some way to do this such that i can restart the process from the record which threw exception onwards till the end again?

I am not able to understand how to achieve this. Please suggest.

Regards,

Catch the BatchUpdateException and get the update count array from that.
That will allow you to figure out where it got to.

You'll have to resubmit a new batch from the update after the one that failed.

You also might want to look into whether the db you are using has any settings that will allow it to continue processing a batch even after an error, but that's database specific.

Hi,

Thanks for the quick reply.

I am using Oracle 10g; classes12.jar for the JDBC connectivity.

Please inform is there some way to continue with the batch process even after an error/exception?

If you're using 10g then you really need to get a modern driver.
classes12 is ancient, and built for Java 1.2.

The oracle site will tell you which one is applicable for your mix of Java and Oracle.
Then you might find it'll continue the processing anyway.

Quote:

---

Originally Posted by Tolls

If you're using 10g then you really need to get a modern driver.
classes12 is ancient, and built for Java 1.2.

The oracle site will tell you which one is applicable for your mix of Java and Oracle.
Then you might find it'll continue the processing anyway.

---

Hi,

Thanks for the reply. I tried with another couple of drivers (ODBC5.jar & ODBC6.jar) but none of them worked. The probably reason may be:

Suppose the batch contains 1000 records to be inserted and the 100th record throws an exception. In such a case, as soon as the exception is thrown the program control moves into the catch block rather than inserting/checkinging the other remaining records yet to be inserted by the batch.

Hence i feel like changing the drivers only wont work. I need to fine tune my code which i posted above.

Unfortunately i don't have any idea, please help

Regards,

It does say in the JDBC API that how batch errors are handled is entirely db specific, so all you can do is rebuild the batch from the one that failed, which you know.