pentaho/pentaho-kettle · error · KettleDatabaseException

Unable to prepare statement for update [" + sql + "]

Error message

Unable to prepare statement for update [" + sql + "]

What it means

KettleDatabaseException thrown by Database.setLookup when connection.prepareStatement(sql) fails while preparing the lookup statement for update/lookup. The generated lookup SQL is included in the message; the underlying SQLException is the cause.

Solutions

1. Verify the target table and key columns exist and are accessible with the current connection
2. Print the lookup SQL (in the exception message) and test it directly in the database
3. Check schema/catalog qualification and identifier quoting settings in the DatabaseMeta
4. Validate the connection (ping) and re-connect before preparing the statement

Example fix

// before
db.setLookup(tablename, keys, condition, extraCondition);
db.getLookup(...);
// after
if (db.checkTableExists(tablename)) {
  db.setLookup(tablename, keys, condition, extraCondition);
  db.getLookup(...);
} else {
  throw new KettleException("Lookup table missing: " + tablename);
}

if (!db.checkTableExists(tablename)) {
  throw new KettleException("Lookup table missing: " + tablename);
}

null

try {
  db.setLookup(tablename, keys, condition, extraCondition);
} catch (KettleDatabaseException e) {
  logError("Lookup SQL failed: " + e.getMessage()); // SQL is embedded in message
  throw e;
}

When it happens

Trigger: prepareStatement() rejects the generated lookup SQL: syntax error in the dialect's key-lookup clause, table/column doesn't exist, schema mismatch, closed connection, or missing table when using setLookup + setValuesLookup/getLookup.

Common situations: Lookup/update step pointed at a wrong or renamed table; case-sensitivity or schema qualification issues; driver rejects dialect-generated SQL (quote/identifier differences); connection already terminated.

Understand the failure class

Background: "query failed", "%w: SQL error" — wrapped database query errors in Go libraries explained — this error's family across 3 libraries.

Related errors

• Couldn't prepare statement:
• Unable to prepare dimension lookup
• An error occurred executing SQL:
• Couldn't execute SQL:
• Couldn't find any rows because of an error :

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

Appendix: source

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

        } else {
          sql.append( " " ).append( condition[ i ] ).append( " ? " );
        }
      }

      if ( orderby != null && orderby.length() != 0 ) {
        sql.append( " ORDER BY " ).append( orderby );
      }

      try {
        if ( log.isDetailed() ) {
          log.logDetailed( "Setting preparedStatement to [" + sql + "]" );
        }
        prepStatementLookup = connection.prepareStatement( databaseMeta.stripCR( sql.toString() ) );
        if ( !checkForMultipleResults && databaseMeta.supportsSetMaxRows() ) {
          prepStatementLookup.setMaxRows( 1 ); // alywas get only 1 line back!
        }
      } catch ( SQLException ex ) {
        throw new KettleDatabaseException( "Unable to prepare statement for update [" + sql + "]", ex );
      }
    } finally {
      log.snap( Metrics.METRIC_DATABASE_SET_LOOKUP_STOP, databaseMeta.getName() );
    }
  }

  public boolean prepareUpdate( String table, String[] codes, String[] condition, String[] sets ) {
    return prepareUpdate( null, table, codes, condition, sets );
  }

  // Lookup certain fields in a table
  public boolean prepareUpdate( String schemaName, String tableName, String[] codes, String[] condition,
                                String[] sets ) {
    try {
      log.snap( Metrics.METRIC_DATABASE_PREPARE_UPDATE_START, databaseMeta.getName() );

      StringBuilder sql = new StringBuilder( 128 );

View on GitHub (pinned to f3058517a1)