SQLException during executeBatch() when I’m handling BatchUpdateException

Loading...

SQLException during executeBatch() when I’m handling BatchUpdateException

I’m having some trouble with this.
I’m trying to list some data about the queries that failed during an executeUpdate() and I have read that BatchUpdateException can be caught and then get the updateCount, that tell you which queries worked and which not, but when a query fails because a bad data conversion, it launches an SQLException and I can’t catch the whole batch execution error.
The data to make the queries is fetched from a XML without any kind of validations.
Here’s the code:
try {
pSt_insertCabecera.executeBatch();
}
catch (BatchUpdateException buex){
int[] updateCounts = buex.getUpdateCounts();
for (int i = 0; i < updateCounts.length; i++) { if (updateCounts[i] == Statement.EXECUTE_FAILED) { logger.error(nombreClase + "[ESB_P]: Ha fallado la inserción de la cabecera de pedidos: " + cabecerasAInsertar.get(i).toString()); throw new SQLException(); } } } The SQLException thrown besides the if it's because later on the code I catch it to perform a rollback. The StackTrace looks as this: com.microsoft.sqlserver.jdbc.SQLServerException: Error al convertir una cadena de caracteres en fecha y/u hora. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatementBatch(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtBatchExecCmd.doExecute(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(Unknown Source) I'm using SQLServer 2012. If you need more code or information please ask. So much thanks to everyone.

Solutions/Answers:

Solution 1:

I was able to recreate your issue. For a table named [Clients] with a datetime column named [DOB] the code

String[] datesToApply = new String[] 
        {
        "1978-12-31",
        "junk",
        "1981-11-11"
        };

String sql = 
        "UPDATE Clients SET DOB=? WHERE ID=1";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    for (String dt : datesToApply) {
        ps.setString(1, dt);
        ps.addBatch();
    }
    int[] updateCounts = null;
    try {
        updateCounts = ps.executeBatch();
    } catch (BatchUpdateException bue) {
        System.out.println("executeBatch threw BatchUpdateException: " + bue.getMessage());
        updateCounts = bue.getUpdateCounts();
    } catch (SQLException se) {
        System.out.println("executeBatch threw SQLException: " + se.getMessage());
    }
    if (updateCounts != null) {
        for (int uc : updateCounts) {
            System.out.println(uc);
        }
    }
    if (!conn.getAutoCommit()) {
        conn.commit();
    }

throws a SQLException if AutoCommit is off, but it throws a BatchUpdateException if AutoCommit is on. Inspecting the array returned by .getUpdateCounts() shows us the point in the batch at which the first failure occurred

executeBatch threw BatchUpdateException: Conversion failed when converting date and/or time from character string.
1
-3
-3

but it also shows that SQL Server “gives up” after the first failure. There seems to be no option to tell SQL Server to continue processing the rest of the batch (like continueBatchOnError for MySQL Connector/J).

In addition, since AutoCommit is “on” the updates to that point are already committed so there’s no option to roll back those changes en masse. If you wanted the batch to be “all or nothing” you’d have to write some code to go back and reverse the changes that were successfully applied before the first failure.

(The other option, of course, is to validate the data before trying to put it into your database.)

References

Loading...