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

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
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.
Highlighted
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;
One Star

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

My work around solution:
 1. Change schema type from DATE to String in the tDBInput component

 2. CAST column to  VARCHAR in SQL Query:
    SELECT 
       CAST (column_name as CHAR(32)) as column_name
   FROM table_name

Best Regards.,

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 3

Read about some useful Context Variable ideas

Blog

Talend Studio Improvements for API Services

Take a look at the Talend Studio improvements for API Services

Watch Now

Data Integration Success Stories

Take a look at some Data Integration success stories

Read