{"record":{"id":"6fd9b7f20a168edd","repo":"pentaho/pentaho-kettle","slug":"unable-to-prepare-dimension-lookup","errorCode":null,"errorMessage":"Unable to prepare dimension lookup","messagePattern":"Unable to prepare dimension lookup","errorType":"exception","errorClass":"KettleDatabaseException","httpStatus":null,"severity":"error","filePath":"engine/src/main/java/org/pentaho/di/trans/steps/dimensionlookup/DimensionLookup.java","lineNumber":907,"sourceCode":"      sql += \" AND ? >= \" + dateFromField + Const.CR;\n      sql += \" AND ? < \" + dateToField + Const.CR;\n\n      data.lookupRowMeta.addValueMeta( new ValueMetaDate( meta.getDateFrom() ) );\n      data.lookupRowMeta.addValueMeta( new ValueMetaDate( meta.getDateTo() ) );\n    }\n\n    try {\n      logDetailed( \"Dimension Lookup setting preparedStatement to [\" + sql + \"]\" );\n      data.prepStatementLookup = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) );\n      if ( databaseMeta.supportsSetMaxRows() ) {\n        data.prepStatementLookup.setMaxRows( 1 ); // alywas get only 1 line back!\n      }\n      if ( databaseMeta.getDatabaseInterface().isMySQLVariant() ) {\n        data.prepStatementLookup.setFetchSize( 0 ); // Make sure to DISABLE Streaming Result sets\n      }\n      logDetailed( \"Finished preparing dimension lookup statement.\" );\n    } catch ( SQLException ex ) {\n      throw new KettleDatabaseException( \"Unable to prepare dimension lookup\", ex );\n    }\n  }\n\n  protected boolean isAutoIncrement() {\n    return techKeyCreation == CREATION_METHOD_AUTOINC;\n  }\n\n  /**\n   * This inserts new record into dimension Optionally, if the entry already exists, update date range from previous\n   * version of the entry.\n   */\n  public Long dimInsert( RowMetaInterface inputRowMeta, Object[] row, Long technicalKey, boolean newEntry,\n                         Long versionNr, Date dateFrom, Date dateTo ) throws KettleException {\n    DatabaseMeta databaseMeta = meta.getDatabaseMeta();\n\n    if ( data.prepStatementInsert == null\n      && data.prepStatementUpdate == null ) { // first time: construct prepared statement\n      RowMetaInterface insertRowMeta = new RowMeta();","sourceCodeStart":889,"sourceCodeEnd":925,"githubUrl":"https://github.com/pentaho/pentaho-kettle/blob/f3058517a153da500bf4551f46d79b91bf8ec552/engine/src/main/java/org/pentaho/di/trans/steps/dimensionlookup/DimensionLookup.java#L889-L925","documentation":"The step failed to prepare the JDBC PreparedStatement used for the dimension lookup SELECT. Any SQLException during connection.prepareStatement() for the lookup SQL is wrapped into a KettleDatabaseException with this message and the original SQLException as cause.","triggerScenarios":"setDimLookup (invoked from processRow/init before lookups) builds the lookup SELECT and calls prepareStatement; a bad table name, missing column in WHERE/ORDER BY, or connection-level failure triggers it. The MySQL-specific setFetchSize(0) line in the source shows this happens after statement creation, so most failures come from SQL syntax/schema.","commonSituations":"Dimension table doesn't exist or is in another schema; renamed column still referenced in the key mapping; database user lacks SELECT privileges; MySQL variant specific statement attributes unsupported by the driver.","solutions":["Check the 'Caused by' SQLException in the log for the exact SQL error","Use the step dialog's 'SQL' button to generate/verify the dimension table DDL and run it","Verify connection settings (URL, schema, credentials) and that the table/columns exist","Grant SELECT on the dimension table to the connection user"],"exampleFix":"// before (log)\n// Unable to prepare dimension lookup ... Unknown column 'cust_ky' in 'where clause'\n// after\n// ALTER TABLE dim_customer CHANGE cust_ky customer_key BIGINT;  -- or fix the key field name in the dialog","handlingStrategy":"try-catch","validationCode":"// verify lookup SQL objects exist before prepare\nDatabase db = new Database(transMeta, databaseMeta);\ndb.connect();\nif (!db.checkTableExists(dimTableMeta)) {\n  throw new IllegalStateException(\"Dimension table missing: \" + dimTableMeta.getTableName());\n}","typeGuard":null,"tryCatchPattern":"try {\n  setDimLookup();\n} catch (KettleDatabaseException e) {\n  logError(\"Lookup prepare failed: \" + e.getMessage() + \", cause=\" + e.getCause());\n  throw e;\n}","preventionTips":["Run the dialog's 'SQL' button to create/align the table before production runs","Validate connection and schema settings per environment (dev/test/prod)","Grant SELECT privileges on the dimension table to the integration user"],"tags":["database","jdbc","sql","prepared-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"}