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

Highlighted
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.

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 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch