Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: got error on timestamp

  1. #1
    Join Date
    Jun 2011
    Posts
    3

    Default got error on timestamp

    Hi there,

    I have a Date with Null Values. When I run a transformation seems like the null value is converted to 0000-00-00 00:00:00 and it shows error message. How can I overcome this problem? The error message is as below:

    2011/07/03 05:09:59 - sec_project.0 - ERROR (version 4.2.0-RC1, build 15366 from 2011-06-14 12.04.35 by buildguy) : Couldn't get row from result set
    2011/07/03 05:09:59 - sec_project.0 - ERROR (version 4.2.0-RC1, build 15366 from 2011-06-14 12.04.35 by buildguy) :
    2011/07/03 05:09:59 - sec_project.0 - ERROR (version 4.2.0-RC1, build 15366 from 2011-06-14 12.04.35 by buildguy) : Unable to get value 'Date' from database resultset, index 19
    2011/07/03 05:09:59 - sec_project.0 - ERROR (version 4.2.0-RC1, build 15366 from 2011-06-14 12.04.35 by buildguy) : Cannot convert value '0000-00-00 00:00:00' from column 20 to TIMESTAMP.
    2011/07/03 05:09:59 - sec_project.0 - ERROR (version 4.2.0-RC1, build 15366 from 2011-06-14 12.04.35 by buildguy) :
    2011/07/03 05:09:59 - sec_project.0 - ERROR (version 4.2.0-RC1, build 15366 from 2011-06-14 12.04.35 by buildguy) :

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    It was a mistake from MySQL to ever allow these illegal date values.
    However, they do support conversion to null. Set the following option in your MySQL database connection:

    zeroDateTimeBehavior = convertToNull

  3. #3
    Join Date
    Jun 2011
    Posts
    3

    Default How to set up the option?

    where should I set the option? Can you give me the specific file or location or syntax that I can follow the step. Can simply do in my db connection of Spoon?

    Thank you in advance.

  4. #4
    Join Date
    Mar 2012
    Posts
    276

    Default Default value

    You Need to specify a default value for Date,Date field doesn't work with NULL VALUE(DB error) in DI.

  5. #5
    Join Date
    May 2012
    Posts
    1

    Smile Setting database connection options

    Quote Originally Posted by noraniy View Post
    where should I set the option? Can you give me the specific file or location or syntax that I can follow the step. Can simply do in my db connection of Spoon?

    Thank you in advance.
    In "Table input", select "Edit..." to change the connection. In the "Database Connection" window, select Options, and put zeroDateTimeBehavior in an empty Parameter, and convertToNull in the corresponding Valule.

  6. #6
    Join Date
    Oct 2012
    Posts
    3

    Default Same error with me

    Quote Originally Posted by abrenner View Post
    In "Table input", select "Edit..." to change the connection. In the "Database Connection" window, select Options, and put zeroDateTimeBehavior in an empty Parameter, and convertToNull in the corresponding Valule.
    I made the changes as suggested bu still dont see any change in error. Is there any other way out to solve this issue?

    error message i got
    Dispatching started for transformation [F:\studymaterial\pentaho\pdi-ce-4.2.1-stable\data-integration\mytransform\testkyc.ktr : testkyc]
    2013/03/07 12:30:42 - Table input.0 - ERROR (version 4.2.1-stable, build 15952 from 2011-10-25 15.27.10 by buildguy) : Unexpected error
    2013/03/07 12:30:42 - Table input.0 - ERROR (version 4.2.1-stable, build 15952 from 2011-10-25 15.27.10 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
    2013/03/07 12:30:42 - Table input.0 - ERROR (version 4.2.1-stable, build 15952 from 2011-10-25 15.27.10 by buildguy) : Couldn't get row from result set
    2013/03/07 12:30:42 - Table input.0 - ERROR (version 4.2.1-stable, build 15952 from 2011-10-25 15.27.10 by buildguy) :
    2013/03/07 12:30:42 - Table input.0 - ERROR (version 4.2.1-stable, build 15952 from 2011-10-25 15.27.10 by buildguy) : Unable to get value 'Date' from database resultset, index 14
    2013/03/07 12:30:42 - Table input.0 - ERROR (version 4.2.1-stable, build 15952 from 2011-10-25 15.27.10 by buildguy) : Value '222ExistingUpgrade 000001-022012-06-05 00:00:002012-06-05 00:00:00 Dinesh DuraiTest BDM
    Pune,Dighi 000001-02-011240000-00-00 00:00:00

    Thanks in advance.
    Shreyas D.

  7. #7
    Join Date
    Nov 2013
    Posts
    2

    Default

    thank you

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.