pentaho/pentaho-kettle · error · KettleDatabaseException

Error looking up row in database

Error message

Error looking up row in database

What it means

Generic wrapper thrown by Database's lookup method when the JDBC query used for a row lookup throws a SQLException. The meaningful cause (syntax error, closed connection, timeout, permission) is attached as the chained exception.

Solutions

  1. Inspect the chained SQLException cause for the driver's actual message
  2. Verify the connection is alive (call database.connect() or check network/DB availability)
  3. Confirm the lookup table and field names match the current schema
  4. Check the user has SELECT permission on the lookup table

Example fix

// before
database.getLookup(tablename, keys, values); // connection long idle, dropped by firewall
// after
if ( !database.checkConnection() ) { database.disconnect(); database.connect(); }
database.getLookup(tablename, keys, values);
Defensive patterns

Strategy: try-catch

Validate before calling

// ensure connection is alive before the lookup
if ( !database.checkConnection() ) { database.disconnect(); database.connect(); }

Try / catch

try {
  return database.getLookup(tablename, keys, values);
} catch ( KettleDatabaseException e ) {
  Throwable cause = e.getCause();
  if ( cause instanceof SQLException && isConnectionError((SQLException) cause) ) {
    database.disconnect(); database.connect();
    return database.getLookup(tablename, keys, values); // one retry on connection loss
  }
  throw e;
}

Prevention

When it happens

Trigger: Any SQLException during execution of the prepared lookup SELECT - e.g. connection already closed/timed out, invalid column or table name in the lookup definition, wrong parameter types, or driver-level errors.

Common situations: Stale connections after DB failover or network drops; renamed/dropped tables or columns after schema changes; mismatched key field types between stream and table; insufficient SELECT grants.

Understand the failure class

Background: Database query failed: Internal Server Error 500s wrapping SQL, Prisma, and connection failures — what to check first — this error's family across 16 libraries.

Related errors

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

Appendix: source

Thrown at core/src/main/java/org/pentaho/di/core/database/Database.java:3385

  public Object[] getLookup( PreparedStatement ps, boolean failOnMultipleResults, boolean lazyConversion )
    throws KettleDatabaseException {
    log.snap( Metrics.METRIC_DATABASE_GET_LOOKUP_START, databaseMeta.getName() );
    try ( ResultSet res = ps.executeQuery() ) {
      Object[] ret = getRow( res, lazyConversion );

      if ( failOnMultipleResults ) {
        if ( ret != null && res.next() ) {
          // if the previous row was null, there's no reason to try res.next()
          // again.
          // on DB2 this will even cause an exception (because of the buggy DB2
          // JDBC driver).
          throw new KettleDatabaseException(
            "Only 1 row was expected as a result of a lookup, and at least 2 were found!" );
        }
      }
      return ret;
    } catch ( SQLException ex ) {
      throw new KettleDatabaseException( "Error looking up row in database", ex );
    } finally {
      log.snap( Metrics.METRIC_DATABASE_GET_LOOKUP_STOP, databaseMeta.getName() );
    }
  }

  public DatabaseMetaData getDatabaseMetaData() throws KettleDatabaseException {
    if ( dbmd == null ) {
      try {
        log.snap( Metrics.METRIC_DATABASE_GET_DBMETA_START, databaseMeta.getName() );

        if ( connection == null ) {
          throw new KettleDatabaseException( BaseMessages.getString( PKG,
            "Database.Exception.EmptyConnectionError", databaseMeta.getDatabaseName() ) );
        }

        dbmd = connection.getMetaData(); // Only get the metadata once!
      } catch ( Exception e ) {
        throw new KettleDatabaseException( BaseMessages.getString( PKG,

View on GitHub (pinned to f3058517a1)