Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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!

share

1 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:

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

share
    
Very helpful - I added ?zeroDateTimeBehavior=convertToNull at the end of the JDBC Connection and it works perfectly. Thanks! –  Patrick Bernhard Feb 8 '13 at 9: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 8 '13 at 12:44

This site is currently not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .