{"record":{"id":"86172b873e425732","repo":"pentaho/pentaho-kettle","slug":"unable-to-prepare-dimension-insert","errorCode":null,"errorMessage":"Unable to prepare dimension insert :","messagePattern":"Unable to prepare dimension insert :","errorType":"exception","errorClass":"KettleDatabaseException","httpStatus":null,"severity":"error","filePath":"engine/src/main/java/org/pentaho/di/trans/steps/dimensionlookup/DimensionLookup.java","lineNumber":1039,"sourceCode":"            break;\n        }\n      }\n\n      sql += \" )\";\n\n      try {\n        if ( technicalKey == null && databaseMeta.supportsAutoGeneratedKeys() ) {\n          logDetailed( \"SQL w/ return keys=[\" + sql + \"]\" );\n          data.prepStatementInsert =\n            data.db.getConnection().prepareStatement(\n              databaseMeta.stripCR( sql ), Statement.RETURN_GENERATED_KEYS );\n        } else {\n          logDetailed( \"SQL=[\" + sql + \"]\" );\n          data.prepStatementInsert = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) );\n        }\n        // pstmt=con.prepareStatement(sql, new String[] { \"klant_tk\" } );\n      } catch ( SQLException ex ) {\n        throw new KettleDatabaseException( \"Unable to prepare dimension insert :\" + Const.CR + sql, ex );\n      }\n\n      /*\n       * UPDATE d_customer SET dateto = val_datnow, last_updated = <now> last_version = false WHERE keylookup[] =\n       * keynrs[] AND versionfield = val_version - 1 ;\n       */\n      RowMetaInterface updateRowMeta = new RowMeta();\n\n      String sql_upd = \"UPDATE \" + data.schemaTable + Const.CR;\n\n      // The end of the date range\n      //\n      sql_upd += \"SET \" + databaseMeta.quoteField( meta.getDateTo() ) + \" = ?\" + Const.CR;\n      updateRowMeta.addValueMeta( new ValueMetaDate( meta.getDateTo() ) );\n\n      // The special update fields...\n      //\n      for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) {","sourceCodeStart":1021,"sourceCodeEnd":1057,"githubUrl":"https://github.com/pentaho/pentaho-kettle/blob/f3058517a153da500bf4551f46d79b91bf8ec552/engine/src/main/java/org/pentaho/di/trans/steps/dimensionlookup/DimensionLookup.java#L1021-L1057","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Inspect the SQL printed after 'SQL=[' in the log / included in the exception and validate it against the table DDL","Run the dialog's 'SQL' button to ALTER the table so it matches the step's field mapping","Quote or rename columns that collide with database reserved words","Confirm the connection targets the correct schema/database and the user has INSERT rights"],"exampleFix":"// before\n// INSERT INTO dim_order (date, ...) -> syntax error near 'date'\n// after\n// rename column: ALTER TABLE dim_order CHANGE date order_date DATE;\n// or quote it in the mapping: \"date\" (PostgreSQL) / `date` (MySQL)","handlingStrategy":"try-catch","validationCode":"// dry-run the generated INSERT syntax before execution\nString sql = buildInsertSql(meta);\nDatabase db = new Database(transMeta, databaseMeta);\ndb.connect();\ndb.getDatabaseMeta().checkSqlSyntax(db, sql); // or EXPLAIN the statement","typeGuard":null,"tryCatchPattern":"try {\n  dimInsert(rowMeta, row);\n} catch (KettleDatabaseException e) {\n  logError(\"Insert prepare failed, SQL=\" + e.getMessage()); // message embeds failing SQL\n  throw e;\n}","preventionTips":["Keep the table DDL and step field mapping in sync (use the 'SQL' button after changes)","Avoid reserved words as column names; quote or rename them","Verify INSERT privileges and correct schema on every environment"],"tags":["database","jdbc","insert","sql"],"backgroundTag":"sql-query-failed","analyzedSha":"f3058517a153da500bf4551f46d79b91bf8ec552","analyzedAt":"2026-09-13T14:04:16.340Z","contentChangedAt":"2026-09-13T14:04:16.340Z","schemaVersion":2},"datasetVersion":"2026-09-20T23:17:15.980Z"}