This discussion is archived
4 Replies Latest reply: May 17, 2013 9:24 AM by rp0428 RSS

How can i get the errors when using executeBatch?

1009488 Newbie
Currently Being Moderated
I use batch to insert some data, for example, i want to insert 3 records.
But, 1 record has wrong data which will cause jdbc exception(the length of data exceed the column's defined length).
Then, after executeBatch executed, it returns -2 without any exception.

My question is:
how can i get the wrong record when using executeBatch? is there any method to get the record and exception message?
  • 1. Re: How can i get the errors when using executeBatch?
    gimbal2 Guru
    Currently Being Moderated
    Sure, check the DBMS logs.
  • 2. Re: How can i get the errors when using executeBatch?
    Joe Weinstein Expert
    Currently Being Moderated
    If the driver returns the exception, that's your info. The -2 response is what the driver
    returns for every one of the counts. Look at the spec. -2 means the driver doesn't have
    the info to give the real number.
  • 3. Re: How can i get the errors when using executeBatch?
    rp0428 Guru
    Currently Being Moderated
    >
    The -2 response is what the driver
    returns for every one of the counts.
    >
    Can't test right now but that is not what the API docs say and I don't think that is correct.
  • 4. Re: How can i get the errors when using executeBatch?
    rp0428 Guru
    Currently Being Moderated
    >
    how can i get the wrong record when using executeBatch? is there any method to get the record and exception message?
    >
    You need to check the array of counts that are returned by the statement and look for negative numbers.

    For an exception one, or more, of the values will be negative depending on whether the DB continues to process commands after the first error. The index of the negative numbers matches the order the statements were added to the batch.

    That means you need to KNOW what statements were added so, in the event of an exception, you can find the ones that caused a problem.

    As Joe suggested see the Java API's

    executeBatch
    http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#executeBatch()
    >
    A value of SUCCESS_NO_INFO -- indicates that the command was processed successfully but that the number of rows affected is unknown
    If one of the commands in a batch update fails to execute properly, this method throws a BatchUpdateException, and a JDBC driver may or may not continue to process the remaining commands in the batch. However, the driver's behavior must be consistent with a particular DBMS, either always continuing to process commands or never continuing to process commands. If the driver continues processing after a failure, the array returned by the method BatchUpdateException.getUpdateCounts will contain as many elements as there are commands in the batch, and at least one of the elements will be the following:

    A value of EXECUTE_FAILED -- indicates that the command failed to execute successfully and occurs only if a driver continues to process commands after a command fails
    >
    BatchUpdateException
    http://docs.oracle.com/javase/6/docs/api/java/sql/BatchUpdateException.html
    >
    The subclass of SQLException thrown when an error occurs during a batch update operation. In addition to the information provided by SQLException, a BatchUpdateException provides the update counts for all commands that were executed successfully during the batch update, that is, all commands that were executed before the error occurred. The order of elements in an array of update counts corresponds to the order in which commands were added to the batch.

    After a command in a batch update fails to execute properly and a BatchUpdateException is thrown, the driver may or may not continue to process the remaining commands in the batch. If the driver continues processing after a failure, the array returned by the method BatchUpdateException.getUpdateCounts will have an element for every command in the batch rather than only elements for the commands that executed successfully before the error. In the case where the driver continues processing commands, the array element for any command that failed is Statement.EXECUTE_FAILED.
    >
    As for this:
    >
    is there any method to get the record and exception message?
    >
    The above should show you how to get the index of the commands that caused the error.

    The exception applies to the entire batch. Individual exceptions for the problem statements are not captured.

    For Oracle you could use DML error logging to capture individual rows that fail a DML operation; for your example (the length of data exceed the column's defined length).

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points