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.