Results 1 to 6 of 6
  1. #1
    butny is offline Member
    Join Date
    Nov 2011
    Posts
    14
    Rep Power
    0

    Unhappy 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:

    Java 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,

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default Re: JDBC Batch insert exception handling

    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.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  3. #3
    butny is offline Member
    Join Date
    Nov 2011
    Posts
    14
    Rep Power
    0

    Default Re: JDBC Batch insert exception handling

    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?

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default Re: JDBC Batch insert exception handling

    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.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  5. #5
    butny is offline Member
    Join Date
    Nov 2011
    Posts
    14
    Rep Power
    0

    Default Re: JDBC Batch insert exception handling

    Quote Originally Posted by Tolls View Post
    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,

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default Re: JDBC Batch insert exception handling

    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.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

Similar Threads

  1. exception handling
    By niksipandit in forum New To Java
    Replies: 3
    Last Post: 09-22-2011, 02:25 PM
  2. Error in JDBC batch update
    By murali23krishna in forum JDBC
    Replies: 8
    Last Post: 02-21-2011, 11:42 AM
  3. Replies: 0
    Last Post: 04-01-2008, 10:17 AM
  4. JDBC - Exception handling
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 12-05-2007, 04:00 PM
  5. Replies: 0
    Last Post: 09-28-2007, 12:56 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •