How to handle DATETIME values with zero Timestamp using MySQL and TIBCO ActiveMatrix BusinessWorks Plug-in for Database with MYSQL JDBC driver?
Article ID: KB0079877
Updated On:
| Products | Versions |
|---|---|
| TIBCO ActiveMatrix BusinessWorks Plug-in for Database | 8.x |
Description
When ADB Poller activity of TIBCO ActiveMatrix BusinessWorks™ Plug-in for Database is configured with MySQL database and MYSQL JDBC driver then while polling the records Plug-in throws below exception:
==========
com.tibco.bw.palette.adbplugin.fault.ADBSQLExceptionFault: java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp-{ProcessStarterActivityName=DataEventPoller, ProcessName=adbissue1.Process,ModuleName=ADBIssue1}
==========
The above exception is caused by storing zero dates ('0000-00-00 00:00:00') in MySQL and trying to convert them into date objects in Java.
The important point here is that MySql considers this '0000-00-00' to be a valid date, but it can't be represented as java.sql.Date.
MySQL JDBC driver will throw java.sql.SQLException because Java does not understand dates in this format '0000-00-00'.
Environment
Resolution
1) Edit JDBC URL by setting a parameter called 'zeroDateTimeBehavior' to 'convertToNull'.
e.g jdbc:mysql://localhost:3306/yourMySqlDatabase?zeroDateTimeBehavior=convertToNull
------------------
OR
------------------
2) Use the TIBCO Database Drivers Supplement Software
Issue/Introduction
Feedback
