Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Date Value NULL giving an Error

  1. #1
    Join Date
    Mar 2009
    Posts
    1

    Default Date Value NULL giving an Error

    I am transferring data form MySQL to Oracle DB. The MySQL table has some date field where the data is NULL. While execution, I am getting Date NULL Exception.

    I am using 3.1.0 version of Kettle.

    Can anyone help me in resolving this.

    2009/03/03 11:24:18 - RPM_EMPLOYEES - Dispatching started for transformation [RPM_EMPLOYEES]
    2009/03/03 11:24:18 - RPM_EMPLOYEES - This transformation can be replayed with replay date: 2009/03/03 11:24:18
    2009/03/03 11:24:21 - Table output.0 - Connected to database [Rel_Ora] (commit=100)
    2009/03/03 11:24:22 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : Unexpected error :
    2009/03/03 11:24:22 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : org.pentaho.di.core.exception.KettleDatabaseException:
    2009/03/03 11:24:22 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : Couldn't get row from result set
    2009/03/03 11:24:22 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : Value '0000-00-00' can not be represented as java.sql.Timestamp
    2009/03/03 11:24:22 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) :
    2009/03/03 11:24:22 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at org.pentaho.di.core.database.Database.getRow(Database.java:2861)
    2009/03/03 11:24:22 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at org.pentaho.di.core.database.Database.getRow(Database.java:2776)
    2009/03/03 11:24:22 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:137)
    2009/03/03 11:24:22 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at org.pentaho.di.trans.step.BaseStep.runStepThread(BaseStep.java:2664)
    2009/03/03 11:24:22 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at org.pentaho.di.trans.steps.tableinput.TableInput.run(TableInput.java:343)
    2009/03/03 11:24:22 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : Caused by: java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Timestamp
    2009/03/03 11:24:22 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at com.mysql.jdbc.ResultSet.getNativeDateTimeValue(ResultSet.java:8226)
    2009/03/03 11:24:22 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at com.mysql.jdbc.ResultSet.getNativeTimestamp(ResultSet.java:3938)
    2009/03/03 11:24:22 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at com.mysql.jdbc.ResultSet.getTimestampInternal(ResultSet.java:5561)
    2009/03/03 11:24:22 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at com.mysql.jdbc.ResultSet.getTimestamp(ResultSet.java:5219)
    2009/03/03 11:24:22 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : at org.pentaho.di.core.database.Database.getRow(Database.java:2841)
    2009/03/03 11:24:22 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : ... 4 more
    2009/03/03 11:24:22 - Table input.0 - Finished reading query, closing connection.
    2009/03/03 11:24:22 - Table input.0 - Finished processing (I=1603, O=0, R=0, W=1602, U=1602, E=1)
    2009/03/03 11:24:22 - RPM_EMPLOYEES - Transformation detected one or more steps with errors.
    2009/03/03 11:24:22 - RPM_EMPLOYEES - Transformation is killing the other steps!
    2009/03/03 11:24:24 - Table output.0 - Finished processing (I=0, O=101, R=101, W=0, U=0, E=0)
    2009/03/03 11:24:24 - Spoon - The transformation has finished!!

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    Google a bit for "Value '0000-00-00' can not be represented as java.sql.Timestamp" ... massive amounts of people out there with the same "problem"

    2 fixes... you either convert the timestamp to NULL in the SQL or you get the option "zeroDateTimeBehavior=convertToNull" in the connection string (probably you can add it in the PDI database connection dialog).

    Regards,
    Sven

  3. #3
    Join Date
    Mar 2009
    Posts
    1

    Default

    In Kettle (Pentaho Data Integration), when you use a "Table Input" component, you have to create a database connection.
    In the database connection form, fill the fields of your mysql database, and test it.
    If successfull, go to Options (left tab)
    In the right tab, below defaultFetchSize and useCursorFetch, add zeroDateTimeBehavior and it's value convertToNull

    Hope this helps !

  4. #4
    DEinspanjer Guest

    Default

    Darn, I didn't know about the connection parameter. I should have done better research!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.