pentaho/pentaho-kettle · warning · KettleDatabaseException

Unable to clear batch for prepared statement

Error message

Unable to clear batch for prepared statement

What it means

clearBatch(PreparedStatement) wraps a SQLException from PreparedStatement.clearBatch() as 'Unable to clear batch for prepared statement'. It means the JDBC driver rejected clearing the batch, usually because the statement or its connection is in a bad or closed state.

Solutions

1. Check the cause SQLException — 'closed statement/connection' means the real issue happened earlier
2. Reopen the database connection and re-prepare the statement instead of reusing the stale one
3. Guard cleanup: treat clearBatch failure during error handling as non-fatal
4. Investigate any preceding executeBatch exception in the logs
5. Verify connection validity (isValid/ping) before continuing batch processing

Example fix

// before
database.clearBatch(preparedStatement);
// after
try {
  database.clearBatch(preparedStatement);
} catch (KettleDatabaseException e) {
  log.logMinimal("Discarding batch after clear failure: " + e.getMessage());
}

if (preparedStatement == null || preparedStatement.isClosed()) {
  return; // nothing to clear
}

try {
  database.clearBatch(preparedStatement);
} catch (KettleDatabaseException e) {
  log.logDebug("Batch clear skipped: " + e.getMessage()); // non-fatal during cleanup
}

When it happens

Trigger: Calling Database.clearBatch(preparedStatement) when the underlying statement/connection is closed, the connection was lost, or the driver considers the statement no longer usable (e.g. after an aborted batch execution).

Common situations: Connection killed by network idle timeout mid-batch; statement closed elsewhere before clearBatch; cleanup code running after an earlier executeBatch failure left the statement invalid; driver-specific state restrictions.

Understand the failure class

Background: Database query failed: Internal Server Error 500s wrapping SQL, Prisma, and connection failures — what to check first — this error's family across 16 libraries.

Related errors

• Couldn't close query: resultset or prepared statements
• Error closing
• Error updating batch
• Unable to close prepared statement pstmt
• Unable to close prepared statement sel_stmt

AI-assisted analysis of pentaho/pentaho-kettle@f3058517a1 (2026-09-13). Data as JSON: /api/errors/dc7ce8ebf17f4aee. Report an issue: GitHub.

Appendix: source

Thrown at core/src/main/java/org/pentaho/di/core/database/Database.java:1642

    }
  }

  /**
   * Clears batch of insert prepared statement
   *
   * @throws KettleDatabaseException
   * @deprecated
   */
  @Deprecated
  public void clearInsertBatch() throws KettleDatabaseException {
    clearBatch( prepStatementInsert );
  }

  public void clearBatch( PreparedStatement preparedStatement ) throws KettleDatabaseException {
    try {
      preparedStatement.clearBatch();
    } catch ( SQLException e ) {
      throw new KettleDatabaseException( "Unable to clear batch for prepared statement", e );
    }
  }

  public void executeAndClearBatch( PreparedStatement preparedStatement ) throws KettleDatabaseException {
    try {
      if ( written > 0 && getDatabaseMetaData().supportsBatchUpdates() ) {
        preparedStatement.executeBatch();
      }

      written = 0;
      preparedStatement.clearBatch();
    } catch ( SQLException e ) {
      throw new KettleDatabaseException( "Unable to clear batch for prepared statement", e );
    }
  }

  public void insertFinished( boolean batch ) throws KettleDatabaseException {
    insertFinished( prepStatementInsert, batch );

View on GitHub (pinned to f3058517a1)