{"record":{"id":"d8b5671b5cd4aed8","repo":"pentaho/pentaho-kettle","slug":"unable-to-prepare-dimension-punchthrough-update-statement","errorCode":null,"errorMessage":"Unable to prepare dimension punchThrough update statement : ","messagePattern":"Unable to prepare dimension punchThrough update statement : ","errorType":"exception","errorClass":"KettleDatabaseException","httpStatus":null,"severity":"error","filePath":"engine/src/main/java/org/pentaho/di/trans/steps/dimensionlookup/DimensionLookup.java","lineNumber":1421,"sourceCode":"          sql_upd += \", \" + databaseMeta.quoteField( valueMeta.getName() ) + \" = ?\" + Const.CR;\n          data.punchThroughRowMeta.addValueMeta( valueMeta );\n        }\n      }\n\n      sql_upd += \"WHERE \";\n      for ( int i = 0; i < meta.getKeyLookup().length; i++ ) {\n        if ( i > 0 ) {\n          sql_upd += \"AND   \";\n        }\n        sql_upd += databaseMeta.quoteField( meta.getKeyLookup()[ i ] ) + \" = ?\" + Const.CR;\n        data.punchThroughRowMeta.addValueMeta( rowMeta.getValueMeta( data.keynrs[ i ] ) );\n      }\n\n      try {\n        data.prepStatementPunchThrough =\n          data.db.getConnection().prepareStatement( meta.getDatabaseMeta().stripCR( sql_upd ) );\n      } catch ( SQLException ex ) {\n        throw new KettleDatabaseException( \"Unable to prepare dimension punchThrough update statement : \"\n          + Const.CR + sql_upd, ex );\n      }\n    }\n\n    Object[] punchThroughRow = new Object[ data.punchThroughRowMeta.size() ];\n    int punchIndex = 0;\n\n    for ( int i = 0; i < meta.getFieldLookup().length; i++ ) {\n      if ( meta.getFieldUpdate()[ i ] == DimensionLookupMeta.TYPE_UPDATE_DIM_PUNCHTHROUGH ) {\n        punchThroughRow[ punchIndex++ ] = row[ data.fieldnrs[ i ] ];\n      }\n    }\n    for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) {\n      switch ( meta.getFieldUpdate()[ i ] ) {\n        case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP:\n        case DimensionLookupMeta.TYPE_UPDATE_DATE_UPDATED:\n          punchThroughRow[ punchIndex++ ] = new Date();\n          break;","sourceCodeStart":1403,"sourceCodeEnd":1439,"githubUrl":"https://github.com/pentaho/pentaho-kettle/blob/f3058517a153da500bf4551f46d79b91bf8ec552/engine/src/main/java/org/pentaho/di/trans/steps/dimensionlookup/DimensionLookup.java#L1403-L1439","documentation":"When the update type is 'Punch through' (update the past/beginning-of-history rows), the step prepares a dedicated UPDATE statement (sql_upd) on the JDBC connection. A SQLException during prepareStatement is wrapped in this KettleDatabaseException with the SQL appended. Like the regular prepare error, it indicates the SQL or the schema is unacceptable to the database, specifically for the punch-through statement.","triggerScenarios":"conn.prepareStatement(stripCR(sql_upd)) throws SQLException while preparing the punch-through UPDATE built from the key field, date-from field, and punch-through fields when meta.isUpdate() (punch through) is enabled.","commonSituations":"Punch-through fields no longer exist in the table; date-from field misconfigured; identifier quoting mismatch for the DB; insufficient UPDATE privilege; schema/table prefix wrong.","solutions":["Inspect the wrapped SQLException and the sql_upd text in the message for the actual database error","Verify all punch-through update fields and the date-from field exist in the dimension table","Confirm the connection user has UPDATE privileges on the table","Check schema/table naming and identifier quoting in the step matches the database","If punch-through is unintended, change the field update type from 'Punch through' to 'Insert'"],"exampleFix":"// before: punch-through field removed from table\n// UPDATE dim_customer SET dept = ? WHERE dim_customer_tk = ?  -- 'dept' dropped\n// after\nALTER TABLE dim_customer ADD COLUMN dept VARCHAR(100);\n// or change the field's update type from \"Punch through\" to \"Insert\" in the step dialog","handlingStrategy":"validation","validationCode":"// Java: verify punch-through fields exist in the table before enabling punch-through updates\nRowMetaInterface tableFields = db.getTableFields(schemaTable);\nfor (String f : meta.getFieldUpdate()) {\n  if (tableFields.searchValueMeta(f) == null)\n    throw new IllegalStateException(\"Punch-through field missing in table: \" + f);\n}","typeGuard":null,"tryCatchPattern":"try {\n  punchThrough(...);\n} catch (KettleDatabaseException e) {\n  if (e.getMessage().contains(\"punchThrough update statement\")) {\n    logError(\"Punch-through prepare failed: \" + e.getCause());\n    // fall back to Insert update type or fix schema/privileges\n  } else throw e;\n}","preventionTips":["Re-map punch-through fields whenever the dimension table changes","Verify UPDATE privileges for the connection user","Prefer 'Insert' update type unless history correction is genuinely needed","Check identifier quoting for the target database type"],"tags":["database","sql","jdbc","punch-through"],"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"}