pentaho/pentaho-kettle · error · KettleDatabaseException

Unable to prepare dimension insert :

Error message

Unable to prepare dimension insert :

What it means

Preparing the INSERT PreparedStatement for adding a new dimension row failed. The step builds the INSERT SQL for the dimension table (including technical key, keys, and attribute columns) and wraps any SQLException from prepareStatement in a KettleDatabaseException that includes the failing SQL.

Solutions

  1. Inspect the SQL printed after 'SQL=[' in the log / included in the exception and validate it against the table DDL
  2. Run the dialog's 'SQL' button to ALTER the table so it matches the step's field mapping
  3. Quote or rename columns that collide with database reserved words
  4. Confirm the connection targets the correct schema/database and the user has INSERT rights

Example fix

// before
// INSERT INTO dim_order (date, ...) -> syntax error near 'date'
// after
// rename column: ALTER TABLE dim_order CHANGE date order_date DATE;
// or quote it in the mapping: "date" (PostgreSQL) / `date` (MySQL)
Defensive patterns

Strategy: try-catch

Validate before calling

// dry-run the generated INSERT syntax before execution
String sql = buildInsertSql(meta);
Database db = new Database(transMeta, databaseMeta);
db.connect();
db.getDatabaseMeta().checkSqlSyntax(db, sql); // or EXPLAIN the statement

Try / catch

try {
  dimInsert(rowMeta, row);
} catch (KettleDatabaseException e) {
  logError("Insert prepare failed, SQL=" + e.getMessage()); // message embeds failing SQL
  throw e;
}

Prevention

When it happens

Trigger: dimInsert (called from lookupValues when a new version/row must be inserted) builds insert SQL and calls data.db.getConnection().prepareStatement(); schema mismatches, wrong column count/types, or reserved-word column names raise it.

Common situations: Dimension table columns altered without updating the step mapping; a field named like a reserved keyword (e.g. `date`, `order`); connection points to wrong database; insufficient INSERT privileges surfacing at prepare time on some drivers.

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

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

Appendix: source

Thrown at engine/src/main/java/org/pentaho/di/trans/steps/dimensionlookup/DimensionLookup.java:1039

            break;
        }
      }

      sql += " )";

      try {
        if ( technicalKey == null && databaseMeta.supportsAutoGeneratedKeys() ) {
          logDetailed( "SQL w/ return keys=[" + sql + "]" );
          data.prepStatementInsert =
            data.db.getConnection().prepareStatement(
              databaseMeta.stripCR( sql ), Statement.RETURN_GENERATED_KEYS );
        } else {
          logDetailed( "SQL=[" + sql + "]" );
          data.prepStatementInsert = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) );
        }
        // pstmt=con.prepareStatement(sql, new String[] { "klant_tk" } );
      } catch ( SQLException ex ) {
        throw new KettleDatabaseException( "Unable to prepare dimension insert :" + Const.CR + sql, ex );
      }

      /*
       * UPDATE d_customer SET dateto = val_datnow, last_updated = <now> last_version = false WHERE keylookup[] =
       * keynrs[] AND versionfield = val_version - 1 ;
       */
      RowMetaInterface updateRowMeta = new RowMeta();

      String sql_upd = "UPDATE " + data.schemaTable + Const.CR;

      // The end of the date range
      //
      sql_upd += "SET " + databaseMeta.quoteField( meta.getDateTo() ) + " = ?" + Const.CR;
      updateRowMeta.addValueMeta( new ValueMetaDate( meta.getDateTo() ) );

      // The special update fields...
      //
      for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) {

View on GitHub (pinned to f3058517a1)