One Star

get error with default date in mysql 0000-00-00

Hello
I would like to create a excel report .
I have to export table from mysql DB
in the table I have date format where the default value is 0000-00-00
Too create my report I use a tMysqlInput
whene I run my job I get an error message
Exception in component tMysqlInput_1
java.sql.SQLException: Cannot convert value '0000-00-00' from column 24 to TIMESTAMP.
at com.mysql.jdbc.ResultSet.getTimestampFromBytes(ResultSet.java:6965)
at com.mysql.jdbc.ResultSet.getTimestampInternal(ResultSet.java:6999)
at com.mysql.jdbc.ResultSet.getTimestamp(ResultSet.java:6319)
at hbrepport.report.report.tMysqlInput_1Process(report.java:496)
at hbrepport.report.report.main(report.java:754)
I change in the tMysqlInput the default value for date to 1600-01-01 but nothing change
Pls help
DN
9 REPLIES
One Star

Re: get error with default date in mysql 0000-00-00

It doesn't work either for a mysql date '0000-00-00' store in a java.sql.Date.
A possible workaround is to retrieve this value as a String instead of a Date in your input schema and with a routine, cast it back to a date with an acceptable format.
But this is definately problematic and having it working directly (since 0000-00-00 is a valid mysql date) would be great.
Employee

Re: get error with default date in mysql 0000-00-00

Hello,
I have just reproduced your bug.
This is a regression.
Can you post a new bug in our bugtracker http://www.talendforge.org/bugs
One Star

Re: get error with default date in mysql 0000-00-00

One Star

Re: get error with default date in mysql 0000-00-00

Ok thx for post the bug
DO you have any idea how can I by pass this issue
DN
One Star

Re: get error with default date in mysql 0000-00-00

Instead of retrieving this value as a date type, select the String type from your schema. It will return the string value "0000-00-00" without throwing an exception.
After that, you can import the string into your excel file!
One Star

Re: get error with default date in mysql 0000-00-00

sologlobe

I tryed this solution in the beguining but I get the same error message
Starting job test3 at 12:29 01/06/2007.
Exception in component tMysqlInput_1
java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1056)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ResultSet.getDateFromString(ResultSet.java:2129)
at com.mysql.jdbc.ResultSet.getStringInternal(ResultSet.java:5833)
at com.mysql.jdbc.ResultSet.getString(ResultSet.java:5645)
at hbrepport.test3.test3.tMysqlInput_1Process(test3.java:491)
at hbrepport.test3.test3.main(test3.java:740)
Job test3 ended at 12:30 01/06/2007.

DN
One Star

Re: get error with default date in mysql 0000-00-00

If the exception is saying that it can't put date '0000-00-00' in a java.sql.Date, it's because you extracted it as a DATE and not as a STRING in the schema.
CLick on your tMysqlInput_1, edit its schema and on 'your_date_field' line, change the TYPE field to STRING instead of DATE.
One Star

Re: get error with default date in mysql 0000-00-00

I alreay do it
the probleme is about the DB Type not the tMysqlInput TYPE
I can not change the DB Type
DN
One Star

Re: get error with default date in mysql 0000-00-00

Oh my bad, I'm so sorry denis. I forgot one very important step!
Your SQL query must convert your date field in String first using any function which convert to string such as date_format (to get a particular formatting)
EXAMPLE :
SELECT date_format( my_date_field, '%Y-%m-%d %H:%i:%s') FROM MyTable;