pentaho/pentaho-kettle · error · KettleDatabaseException

Unable to retrieve value of auto-generated technical key …

Error message

Unable to retrieve value of auto-generated technical key : no value found!

What it means

This BaseDatabaseMeta method executes an INSERT via PreparedStatement and retrieves the auto-generated key through getGeneratedKeys. If the returned generated-keys row metadata has zero fields — meaning the driver returned no key columns — it throws a KettleDatabaseException stating no auto-generated technical key value was found.

Solutions

Confirm the target table has an identity/auto-increment primary key column.
Check the JDBC driver supports generated keys; for PostgreSQL use getGeneratedKeys with RETURNING or an appropriate driver version.
Pass the key column name(s) to prepareInsertStatement so the driver knows which generated column to return.
As a fallback, retrieve the key via a database-specific mechanism (e.g. SELECT currval, LAST_INSERT_ID()) instead of relying on generated keys.

Example fix

// before
PreparedStatement ps = connection.prepareStatement(sql); // no generated-key hint

// after
PreparedStatement ps = connection.prepareStatement(sql, new String[] { "id" });

Defensive patterns

Strategy: validation

Validate before calling

DatabaseMetaData md = connection.getMetaData();
if (!md.supportsGetGeneratedKeys()) {
  // fall back to dialect-specific key retrieval instead of the generated-keys path
}

Try / catch

try {
  Long key = db.getGeneratedKey(...);
} catch (KettleDatabaseException e) {
  LOG.warn("Driver returned no generated keys; falling back to SELECT currval/LAST_INSERT_ID", e);
}

Prevention

Confirm the target table has an auto-increment/identity primary key.
Verify the JDBC driver's supportsGetGeneratedKeys() returns true before using this API.
Supply the key column names when preparing the insert statement.
Prefer dialect-specific key retrieval (RETURNING, currval) when generated keys are unsupported.

When it happens

Trigger: Calling the insert-with-generated-keys API on a database/driver combination that does not return generated keys (getGeneratedKeys returns an empty result set), or the INSERT inserted nothing so no key was generated.

Common situations: Databases whose JDBC drivers don't support Statement.RETURN_GENERATED_KEYS (or need column-name hints); INSERT ... SELECT statements that return no keys; using the method against a table without an auto-increment/identity column.

Understand the failure class

Background: EmptyResultError / "no results found": when an API or scraper succeeds but returns zero rows — this error's family across 9 libraries.

Related errors

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

Appendix: source

Thrown at core/src/main/java/org/pentaho/di/core/database/BaseDatabaseMeta.java:2011

  @Override
  public boolean releaseSavepoint() {
    return releaseSavepoint;
  }

  public Long getNextBatchIdUsingSequence( String sequenceName, String schemaName, DatabaseMeta dbm, Database ldb ) throws KettleDatabaseException {
    return ldb.getNextSequenceValue( schemaName, sequenceName, null );
  }

  public Long getNextBatchIdUsingAutoIncSQL( String autoIncSQL, DatabaseMeta dbm, Database ldb ) throws KettleDatabaseException {
    Long rtn = null;
    PreparedStatement stmt = ldb.prepareSQL( autoIncSQL, true );
    try {
      stmt.executeUpdate();
      RowMetaAndData rmad = ldb.getGeneratedKeys( stmt );
      if ( rmad.getRowMeta().size() > 0 ) {
        rtn = rmad.getRowMeta().getInteger( rmad.getData(), 0 );
      } else {
        throw new KettleDatabaseException( "Unable to retrieve value of auto-generated technical key : "
          + "no value found!" );
      }
    } catch ( KettleValueException kve ) {
      throw new KettleDatabaseException( kve );
    } catch ( SQLException sqlex ) {
      throw new KettleDatabaseException( sqlex );
    } finally {
      try {
        stmt.close();
      } catch ( SQLException ignored ) {
        // Ignored
      }
    }
    return rtn;
  }

  public Long getNextBatchIdUsingLockTables( DatabaseMeta dbm, Database ldb, String schemaName, String tableName,
    String fieldName ) throws KettleDatabaseException {

View on GitHub (pinned to f3058517a1)