How to get exact sql query that contains exception in batch processing

Question

I want to execute the following:

(a transfer to PRESENTATION DB of a row from a fetched row of PROCESSING DB)

    PreparedStatement stmt;
    Statement st;
    Connection conn = ConnectionManager.getInstance().getConnection(ConnectionManager.PRESENTATION_DB);
    try {
        conn.setAutoCommit(false);
        stmt = conn.prepareStatement(
               "insert into customer (name,category,age) values (?,?,?)");  
                    stmt.clearParameters();
                    stmt.setString(1,"name2");
                    stmt.setString(2,"cat2");
                    stmt.setInt(3,25);
                    stmt.addBatch();
                    stmt.clearParameters();
                    stmt.setString(1,"name3");
                    stmt.setString(2,"cat3");
                    stmt.setInt(3,25);
                    stmt.addBatch();
                    stmt.clearParameters();
                    stmt.setString(1,"name4");
                    stmt.setString(2,"cat2");
                    stmt.setInt(3,25);
                    stmt.addBatch();
                    stmt.clearParameters();
                    stmt.setString(1,"name4");
                    stmt.setString(2,"cat2");
                    //stmt.setInt(3,25);

                    //this is parameter with false input
                    stmt.setString(3,"null"); 

                    stmt.addBatch();
                    stmt.clearParameters();
                    stmt.setString(1,"name5");
                    stmt.setString(2,"cat5");
                    stmt.setInt(3,25);
                    stmt.addBatch();
                    stmt.clearParameters();

        int [] updateCounts = stmt.executeBatch();
        conn.commit();
        conn.setAutoCommit(true);

                    stmt.close();
        conn.close();
    } 
    catch(BatchUpdateException b) {
                    System.err.println("-----BatchUpdateException-----");
                    System.err.println("SQLState:  " + b.getSQLState());
                    System.err.println("Message:  " + b.getMessage());
                    System.err.println("Vendor:  " + b.getErrorCode());
                    System.err.print("Update counts:  ");
                    int [] updateCounts = b.getUpdateCounts();
                    for (int i = 0; i < updateCounts.length; i++) {
                            System.err.print(updateCounts[i] + "   ");
                    }
                    System.err.println("");
            }
    catch (Exception e) {
        e.printStackTrace();
    }

How can I identify a row that caused the exception to be able to re-execute the batch-processing without this particular row? (By updating the row's field hasError).

Currently I am not saving the objects by batch so I can mark the row if it causes an error and skip that row and move on to process (i.e. save/transfer and delete) another row.

Thanks!

Update:

Ok, I used the following code to track the rows (if any row causes an error):

        public static void processUpdateCounts(int[] updateCounts) {
            for (int i=0; i<updateCounts.length; i++) {
                if (updateCounts[i] >= 0) {
                    // Successfully executed; the number represents number of affected rows
                    logger.info("Successfully executed: number of affected rows: "+updateCounts[i]);
                } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
                    // Successfully executed; number of affected rows not available
                    logger.info("Successfully executed: number of affected rows not available: "+updateCounts[i]);
                } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
                    logger.info("Failed to execute: "+updateCounts[i]);
                    // Failed to execute
                }
            }
        }

And added the line:

        processUpdateCounts(updateCounts);

so I can see changes with or without any error, but it seems like BatchUpdateException isn't working?

Answer 1

I'm using mysql-connector-java-3.1.14 with MySQL. The update at the top of this topic (which uses BatchUpdateException and getUpdateCounts) are working correctly. Now i can find the problematic row in batch insert/update.

Answer 2

Quotes from the javadoc of BatchUpdateException :

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.

Answer 3

As already pointed out, you get access only to the index of the SQL statement that failed during the batch. This means that in order to recreate the same statement that failed to execute you should store parameters in an indexable way, such as:

List<...>paramList = new ArrayList<...>();
for (... params in paramList) {
    stmt.clearParameters();
    stmt.setString(...something involving params...);
    // etc.
}

This way the index of the getUpdateCounts with EXECUTE_FAILED array can be used to identify which parameters caused the failure.

Answer 4

Create a DB layer that help to execute command, if sql command fail the retry it few times using a while loop or something, if still failed after few times then log it.