SQL JDBC Zerodatetime

Question

I am trying to query SQL data with MATHEMATICA - however, the MySQL database contains zerodates and that leads to the following error message:

e.g. (1) *SQLSelect[conn, "orders", {"time_created"}, MaxRows -> 5]* --> JDBC::error: "!(\"Cannot convert value '0000-00-00 00:00:00' from column 1 to TIMESTAMP.\")"

e.g. (2) *SQLExecute[conn, "SELECT * FROM orders", MaxRows -> 5]* --> JDBC::error: "!(\"Cannot convert value '0000-00-00 00:00:00' from column 6 to TIMESTAMP.\")"

I found out that this is a typical issue with a JDBC connection and that this can be solved by allowing zerodatetimes as true with examples from other systems. What I didn't find out is: How to apply this specifically in Mathematica? Can you advise me?

It would help me a lot - so thank you very much for any help!

Answer 1

I hope the following helps, it worked for me (I used it once, but then converted dates and thus got rid of those zero dates didn't use it anymore, so I hope it's somewhat stable...).

What I did is specify the property when setting up the connection, i.e. if using the wizard to set up the connection, I added ?zeroDateTimeBehavior=convertToNull to the database name, see screenshot:

where DB is your database. When doing it directly (without wizard), it will be something like:

conn = OpenSQLConnection[
   JDBC["MySQL(Connector/J)", 
   "localhost/DB?zeroDateTimeBehavior=convertToNull"], 
   Username -> "Hello", Password -> "Kitty"]

As a consequence, all "zerodates" will show up as "Null".

EDIT

Please see the following link for more detailed information (and other settings/parameters) - I figured this might be of interest as well (choose mysql version top left):

mySQL documentation