{"record":{"id":"19a25fe9c386655e","repo":"pentaho/pentaho-kettle","slug":"couldn-t-prepare-statement-dimensionlookup","errorCode":null,"errorMessage":"Couldn't prepare statement :","messagePattern":"Couldn't prepare statement :","errorType":"exception","errorClass":"KettleDatabaseException","httpStatus":null,"severity":"error","filePath":"engine/src/main/java/org/pentaho/di/trans/steps/dimensionlookup/DimensionLookup.java","lineNumber":1333,"sourceCode":"          }\n          comma = true;\n          sql += meta.getDatabaseMeta().quoteField( valueMeta.getName() ) + \" = ?\" + Const.CR;\n          data.dimensionUpdateRowMeta.addValueMeta( valueMeta );\n        }\n      }\n\n      sql += \"WHERE  \" + meta.getDatabaseMeta().quoteField( meta.getKeyField() ) + \" = ?\";\n      data.dimensionUpdateRowMeta\n        .addValueMeta( new ValueMetaInteger( meta.getKeyField() ) ); // The tk\n\n      try {\n        if ( isDebug() ) {\n          logDebug( \"Preparing statement: [\" + sql + \"]\" );\n        }\n        data.prepStatementDimensionUpdate =\n          data.db.getConnection().prepareStatement( meta.getDatabaseMeta().stripCR( sql ) );\n      } catch ( SQLException ex ) {\n        throw new KettleDatabaseException( \"Couldn't prepare statement :\" + Const.CR + sql, ex );\n      }\n    }\n\n    // Assemble information\n    // New\n    Object[] dimensionUpdateRow = new Object[ data.dimensionUpdateRowMeta.size() ];\n    int updateIndex = 0;\n    for ( int i = 0; i < data.fieldnrs.length; i++ ) {\n      // Ignore last_version, last_updated, etc. These are handled below...\n      //\n      if ( data.fieldnrs[ i ] >= 0 ) {\n        dimensionUpdateRow[ updateIndex++ ] = 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:","sourceCodeStart":1315,"sourceCodeEnd":1351,"githubUrl":"https://github.com/pentaho/pentaho-kettle/blob/f3058517a153da500bf4551f46d79b91bf8ec552/engine/src/main/java/org/pentaho/di/trans/steps/dimensionlookup/DimensionLookup.java#L1315-L1351","documentation":"DimensionLookup builds an UPDATE statement for updating the current dimension row (e.g. setting date_to to infinity / updating non-key fields) and prepares it via the JDBC connection. A SQLException from prepareStatement is wrapped in this KettleDatabaseException including the failing SQL. It means the SQL text is not acceptable to the database at prepare time — usually a syntax or schema problem, not data.","triggerScenarios":"conn.prepareStatement(sql) throws SQLException while preparing the dimension-update statement built from the step's table, key field, date-from/to fields and update fields.","commonSituations":"Wrong table name/schema or missing privileges; quoting/identifier case issues between database types; a field listed in the step no longer exists in the table; incompatible characters in identifiers; connecting to a different DB type than the metadata assumes.","solutions":["Read the wrapped SQLException cause for the real database error and check the SQL printed in the message","Verify the dimension table, schema and all configured key/lookup/update fields exist in the database","Check the database connection type and identifier quoting (schema.table prefix) matches your actual database","Grant the connection user UPDATE privileges on the dimension table","Re-enter the field mappings in the step so stale columns are removed"],"exampleFix":"// before: step references removed column\n// UPDATE dim_customer SET email = ? WHERE dim_customer_tk = ?  -- column 'email' dropped\n// after: re-add the column or remove it from the step's update fields\nALTER TABLE dim_customer ADD COLUMN email VARCHAR(255);","handlingStrategy":"validation","validationCode":"// Java: pre-flight the table and columns before preparing the statement\nDatabase db = new Database(transMeta, databaseMeta);\ndb.connect();\nif (!db.checkTableExists(schemaTable)) throw new IllegalStateException(\"Dimension table missing: \" + schemaTable);\nfor (String f : updateFields) if (!db.getTableFields(schemaTable).indexOfValue(f) < 0) throw new IllegalStateException(\"Column missing: \" + f);","typeGuard":null,"tryCatchPattern":"try {\n  dimUpdate(...);\n} catch (KettleDatabaseException e) {\n  if (e.getMessage().startsWith(\"Couldn't prepare statement\")) {\n    logError(\"Prepare failed, SQL=\" + e.getMessage() + \" cause=\" + e.getCause());\n    // fix SQL/privileges/schema, then retry\n  } else throw e;\n}","preventionTips":["Run the step's check() in Spoon after changing the table schema","Keep the connection's database type metadata in sync with the actual DB","Ensure the runtime DB user has SELECT/UPDATE privileges on the dimension table","Avoid hand-editing SQL-relevant identifiers (case, special characters) in the step"],"tags":["database","sql","jdbc","prepare-statement"],"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"}