0

I have a transformation in PDI which basically read data from amazon S3 bucket and push data into mysql database.But today i am facing below error in PDI in time of transformation execution.

org.pentaho.di.core.exception.KettleDatabaseException: 
Couldn't get row from result set

Timestamp : Unable to get timestamp from resultset at index 5
Value '37467412015-04-18 13:58:472015-04-18 13:58:4700000-00-00 00:00:001:138' can not be represented as java.sql.Timestamp


at org.pentaho.di.core.database.Database.getRow(Database.java:2397)
at org.pentaho.di.core.database.Database.getRow(Database.java:2368)
at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:145)
at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.pentaho.di.core.exception.KettleDatabaseException:

I have already used zeroDateTimeBehavior = convertToNull option in PDI

Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
  • On looking into the error message it indicates that 3 datetime values are concatenated like **'37467412015-04-18 13:58:472015-04-18 13:58:4700000-00-00 00:00:001:138'**. Kindly check the input file from S3 – Viki888 Nov 08 '16 at 09:47
  • Additionally, may I suggest to read the dates as String and to convert them (if needed) with a `Select Value`/`Metadata`. At least for debug. – AlainD Aug 18 '17 at 15:15
  • Do you get the error on the step that reads from S3 or the step that writes to mysql? I get a similar error on a table input step using mysql. Zaynul's solution below solves it for me. – matthiash Jun 18 '19 at 07:32

2 Answers2

3

Go to database connection window then select Option then put below values

Parameter=zeroDateTimeBehavior
Value= convertToNull

Then restart spoon

Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
0

The problem is due that Java Timestamp doesn't accept the '0000-00-00' date.

You can add this parameter to the jdbc connection string in the JNDI jdbc.properties:

jdbc:mysql://localhost:3306/......&zeroDateTimeBehavior=CONVERT_TO_NULL

The valid values are "EXCEPTION", "ROUND" and "CONVERT_TO_NULL"

abo
  • 1