What causes " : Unable to set value on prepared statement on index " + index" in pentaho/pentaho-kettle?

Check the chained cause for the driver-level error

pentaho/pentaho-kettle · error · KettleDatabaseException

: Unable to set value on prepared statement on index " +…

Error message

 : Unable to set value on prepared statement on index " + index

What it means

Thrown by ValueMetaTimestamp.setPreparedStatementValue when binding the timestamp to a JDBC PreparedStatement fails — either setTimestamp() or setNull() throws. Wrapped in a KettleDatabaseException with the field metadata and 1-based? (actually internal) index in the message.

Solutions

Check the chained cause for the driver-level error
Verify target column type accepts java.sql.Timestamp (cast or alter the column)
Reconnect before executing if the connection timed out
Ensure the object being bound is actually a Timestamp, not a Date (use convertData first)

Example fix

// before
preparedStatement.setObject(index, new java.util.Date());
// after
preparedStatement.setTimestamp(index, new java.sql.Timestamp(date.getTime()));

Defensive patterns

Strategy: try-catch

Validate before calling

if (!(data instanceof java.sql.Timestamp)) data = tsMeta.convertData(dateMeta, data);
if (data == null) { /* setNull path is safe */ }

Try / catch

try {
  psMeta.setPreparedStatementValue(dbMeta, preparedStatement, index, data);
} catch (KettleDatabaseException e) {
  logError("Bind failed at index " + index + ": " + e.getCause());
  preparedStatement.setObject(index, data); // driver-generic fallback
}

Prevention

Bind java.sql.Timestamp, not java.util.Date
Confirm target column type/precision accepts the value
Keep connections alive / reconnect on timeout before batch execution
Always test the prepared statement binding in a smoke test against the real DB

When it happens

Trigger: Table output / execute of a prepared statement where the target column rejects the Timestamp (type mismatch, precision overflow, driver rejects java.sql.Types.TIMESTAMP setNull on special columns) or the statement/connection was closed.

Common situations: Writing to TIMESTAMP WITH TIME ZONE columns, inserting into columns with narrower precision, stale connections after timeout.

Related errors

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

Appendix: source

Thrown at core/src/main/java/org/pentaho/di/core/row/value/ValueMetaTimestamp.java:537

    } catch ( Exception e ) {
      throw new KettleDatabaseException(
        toStringMeta() + " : Unable to get timestamp from resultset at index " + index, e );
    }
  }

  @Override
  public void setPreparedStatementValue( DatabaseMeta databaseMeta, PreparedStatement preparedStatement, int index,
                                         Object data ) throws KettleDatabaseException {

    try {
      if ( data != null ) {
        preparedStatement.setTimestamp( index, getTimestamp( data ) );
      } else {
        preparedStatement.setNull( index, java.sql.Types.TIMESTAMP );
      }
    } catch ( Exception e ) {
      throw new KettleDatabaseException( toStringMeta() + " : Unable to set value on prepared statement on index "
        + index, e );
    }
  }

  @Override
  public Object convertDataUsingConversionMetaData( Object data2 ) throws KettleValueException {
    if ( conversionMetadata == null ) {
      throw new KettleValueException(
        "API coding error: please specify the conversion metadata before attempting to convert value " + name );
    }

    return super.convertDataUsingConversionMetaData( data2 );
  }

  @Override
  public byte[] getBinaryString( Object object ) throws KettleValueException {

    if ( object == null ) {

View on GitHub (pinned to f3058517a1)