org.pentaho.di.core.exception.KettleValueException: max_date Timestamp : I can't convert the specified value to data type : 9

Pentaho BI Platform Tracking | Matt Tucker | 2 years ago
  1. 0

    As part of a PDI Job, a variable is declared in the parent job based upon the value in a Timestamp field. In a later transformation, the "Get Variables" step is used to cast the variable back into a Timestamp field. This causes an error to be thrown saying that PDI cannot convert the value to "data type 9", even though it just converted the Timestamp to a String earlier in the job. {noformat} 2015/05/06 13:29:02 - Get Variables.0 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Unexpected error 2015/05/06 13:29:02 - Get Variables.0 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : org.pentaho.di.core.exception.KettleValueException: 2015/05/06 13:29:02 - Get Variables.0 - max_date Timestamp : I can't convert the specified value to data type : 9 2015/05/06 13:29:02 - Get Variables.0 - 2015/05/06 13:29:02 - Get Variables.0 - at org.pentaho.di.core.row.value.ValueMetaBase.convertData(ValueMetaBase.java:3547) 2015/05/06 13:29:02 - Get Variables.0 - at org.pentaho.di.trans.steps.getvariable.GetVariable.processRow(GetVariable.java:108) 2015/05/06 13:29:02 - Get Variables.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62) 2015/05/06 13:29:02 - Get Variables.0 - at java.lang.Thread.run(Thread.java:745) {noformat} In looking at [ValueMetaBase.convertData(...)|https://github.com/pentaho/pentaho-kettle/blob/master/core/src/org/pentaho/di/core/row/value/ValueMetaBase.java#L3537-L3558], this appears to be due to a missing switch statement for the Timestamp type. At a higher level, "Get Variables" uses the {{ValueMeta()}} class, instead of using {{ValueMetaFactory}}, which would have returned the correct {{ValueMetaTimestamp}} object, which has an override method that would have properly handled the conversion from String to Timestamp. Example patch for "Get Variables" step. {code:title=GetVariableMeta.java} - ValueMetaInterface v = new ValueMeta( fieldName[i], fieldType[i] ); + ValueMetaInterface v; + try { + v = ValueMetaFactory.createValueMeta( fieldName[i], fieldType[i] ); + } catch ( KettlePluginException e ) { + throw new KettleStepException( e ); + } {code} Reproduction Steps: # Download the attached zip file, and unzip the contents. # Open the {{test_max_date.kjb}} file in Spoon, and click Execute. Expected Results: The job runs successfully, and the correct timestamp is shown in the job log. Actual Results: The job fails with the error message shown above.

    Pentaho BI Platform Tracking | 2 years ago | Matt Tucker
    org.pentaho.di.core.exception.KettleValueException: max_date Timestamp : I can't convert the specified value to data type : 9
  2. 0

    As part of a PDI Job, a variable is declared in the parent job based upon the value in a Timestamp field. In a later transformation, the "Get Variables" step is used to cast the variable back into a Timestamp field. This causes an error to be thrown saying that PDI cannot convert the value to "data type 9", even though it just converted the Timestamp to a String earlier in the job. {noformat} 2015/05/06 13:29:02 - Get Variables.0 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Unexpected error 2015/05/06 13:29:02 - Get Variables.0 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : org.pentaho.di.core.exception.KettleValueException: 2015/05/06 13:29:02 - Get Variables.0 - max_date Timestamp : I can't convert the specified value to data type : 9 2015/05/06 13:29:02 - Get Variables.0 - 2015/05/06 13:29:02 - Get Variables.0 - at org.pentaho.di.core.row.value.ValueMetaBase.convertData(ValueMetaBase.java:3547) 2015/05/06 13:29:02 - Get Variables.0 - at org.pentaho.di.trans.steps.getvariable.GetVariable.processRow(GetVariable.java:108) 2015/05/06 13:29:02 - Get Variables.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62) 2015/05/06 13:29:02 - Get Variables.0 - at java.lang.Thread.run(Thread.java:745) {noformat} In looking at [ValueMetaBase.convertData(...)|https://github.com/pentaho/pentaho-kettle/blob/master/core/src/org/pentaho/di/core/row/value/ValueMetaBase.java#L3537-L3558], this appears to be due to a missing switch statement for the Timestamp type. At a higher level, "Get Variables" uses the {{ValueMeta()}} class, instead of using {{ValueMetaFactory}}, which would have returned the correct {{ValueMetaTimestamp}} object, which has an override method that would have properly handled the conversion from String to Timestamp. Example patch for "Get Variables" step. {code:title=GetVariableMeta.java} - ValueMetaInterface v = new ValueMeta( fieldName[i], fieldType[i] ); + ValueMetaInterface v; + try { + v = ValueMetaFactory.createValueMeta( fieldName[i], fieldType[i] ); + } catch ( KettlePluginException e ) { + throw new KettleStepException( e ); + } {code} Reproduction Steps: # Download the attached zip file, and unzip the contents. # Open the {{test_max_date.kjb}} file in Spoon, and click Execute. Expected Results: The job runs successfully, and the correct timestamp is shown in the job log. Actual Results: The job fails with the error message shown above.

    Pentaho BI Platform Tracking | 2 years ago | Matt Tucker
    org.pentaho.di.core.exception.KettleValueException: max_date Timestamp : I can't convert the specified value to data type : 9
  3. 0

    Kettle - Read CSV with comma as decimal mark

    Stack Overflow | 2 years ago | LeonardoSibela
    org.pentaho.di.core.exception.KettleValueException: Unexpected conversion error while converting value [codigo_cliente String<binary-string>] to an Integer codigo_cliente String<binary-string> : couldn't convert String to Integer codigo_cliente String<binary-string> : couldn't convert String to number : non-numeric character found at position 7 for value [21.237,00]
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    [PDI-795] Unexpected conversion error while converting value - Pentaho BI Platform Tracking

    pentaho.com | 12 months ago
    org.pentaho.di.core.exception.KettleValueException: Unexpected conversion error while converting value to an Integer Unexpected conversion error while converting value to an Integer
  6. 0

    Linux - Kettle - Read CSV with comma as decimal mark

    howtobuildsoftware.com | 12 months ago
    org.pentaho.di.core.exception.KettleValueException: Unexpected conversion error while converting value [codigo_cliente String<binary-string>] to an Integer codigo_cliente String<binary-string> : couldn't convert String to Integer codigo_cliente String<binary-string> : couldn't convert String to number : non-numeric character found at position 7 for value [21.237,00]

    1 unregistered visitors
    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. org.pentaho.di.core.exception.KettleValueException

      max_date Timestamp : I can't convert the specified value to data type : 9

      at org.pentaho.di.core.row.value.ValueMetaBase.convertData()
    2. org.pentaho.di
      RunThread.run
      1. org.pentaho.di.core.row.value.ValueMetaBase.convertData(ValueMetaBase.java:3547)
      2. org.pentaho.di.trans.steps.getvariable.GetVariable.processRow(GetVariable.java:108)
      3. org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
      3 frames
    3. Java RT
      Thread.run
      1. java.lang.Thread.run(Thread.java:745)
      1 frame