Upload tool - MySQL 'NULL' date handling

Hi,

I hope that this may be useful to someone else as it gave me a right headache!

We use the Reports upload tool to push data from a central MySQL database behind a firewall.

Several of our columns have a date type, and an acceptable value in this column is 'NULL'. When we upload these manually as a spreadsheet this works fine.

When we automate this with the upload tool we always get the error:

java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date

It appears that this is because the JDBC MySQL driver's default handling of an invalid date is to throw an exception.

This can be overridden in the connection string for JDBC by setting

zeroDateTimeBehavior=convertToNull

rather than 'exception' which is the default.

I found this to work - it's a hack though:

Open database_connection_params.conf

Find the line that looks like:

DBNAME= yourdbname

Append this

?zeroDateTimeBehavior=convertToNull&

to give

DBNAME= yourdbname?zeroDateTimeBehavior=convertToNull&

This is a hack because the connection string used is revealed to be:

CONNECTION URL :: jdbc:mysql://database.aws.com:3306/ yourdbname?zeroDateTimeBehavior=convertToNull&?useUnicode=true&characterEncoding=UTF-8

Note the 'rogue' ? before 'useUnicode...'

But it does seem to work.

Hope it save someone a few hours....