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|improve this question
add comment

1 Answer

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|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.