7

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!

Patrick Bernhard
  • 707
  • 5
  • 12

1 Answers1

9

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:

enter image description here

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

Pinguin Dirk
  • 6,519
  • 1
  • 26
  • 36
  • Very helpful - I added ?zeroDateTimeBehavior=convertToNull at the end of the JDBC Connection and it works perfectly. Thanks! – Patrick Bernhard Feb 08 '13 at 09:02
  • Great, glad it helps. Please see the link I added for more information on parameters (I never used any others, but well, you might... ) – Pinguin Dirk Feb 08 '13 at 12:44