Converting date '0000-00-00' from MySQL to Java

One Star

Converting date '0000-00-00' from MySQL to Java

Hello,
I'm using TOS 4 to extract data from a MySQL database to another MySQL DB.
In the datasource, it seems that some dates are filled with '0000-00-00', which leadds to a problem when the job tries to manipulate data, even if the field is not mapped.
here's the generated error:
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 bo_project_mgt_kpi.ods_risques_alertes_0_1.ODS_Risques_Alertes.tMysqlInput_1Process(ODS_Risques_Alertes.java:2899)
at bo_project_mgt_kpi.ods_risques_alertes_0_1.ODS_Risques_Alertes.tMysqlConnection_2Process(ODS_Risques_Alertes.java:1393)
at bo_project_mgt_kpi.ods_risques_alertes_0_1.ODS_Risques_Alertes.tMysqlConnection_1Process(ODS_Risques_Alertes.java:1281)
at bo_project_mgt_kpi.ods_risques_alertes_0_1.ODS_Risques_Alertes.tFileInputProperties_1Process(ODS_Risques_Alertes.java:1088)
at bo_project_mgt_kpi.ods_risques_alertes_0_1.ODS_Risques_Alertes.runJobInTOS(ODS_Risques_Alertes.java:7512)
at bo_project_mgt_kpi.ods_risques_alertes_0_1.ODS_Risques_Alertes.main(ODS_Risques_Alertes.java:7329)

I read some threads about this problem (known as bug in the version 2), I tried to use "zeroDateTimeBehavior=convertToNull" additional parameter, but it seems to be not taken into account.
Please could you help me with this problem?
Here are the TOS and Java versions:
TOS:
Version: 4.0.2
Build id: r43696-20100604-0918
java version "1.6.0_21"
Java(TM) SE Runtime Environment (build 1.6.0_21-b07)
Java HotSpot(TM) Client VM (build 17.0-b17, mixed mode, sharing)

Thanks.
Community Manager

Re: Converting date '0000-00-00' from MySQL to Java

Hi
You can find a solution on 12687.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Converting date '0000-00-00' from MySQL to Java

Hello,
thank you for your answer.
Following your advice, I added this line to job properties:
AdditionalParams=noDatetimeStringSync=true&zeroDateTimeBehavior=convertToNull
and it is working fine now.

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads