1
$\begingroup$

I want to use Mathematica to do some SQL tasks like insert. However, I have difficulty in inserting date into SQL. Here is an example.

conn = OpenSQLConnection[
  JDBC["MySQL(Connector/J)", "localhost:3306/dbTest"], 
  "Name" -> "dbTest", "Username" -> "mathematica", 
  "Password" -> "password"]

SQLInsert[conn, SQLTable["match"], {"id", "time", "home_id"}, {1, Now, 100}]

I got the following error:

JDBC::error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match (id,season,league_id,time,home_id,away_id,fthg,ftag,hthg,htag) VALUES (1,2' at line 1

I think it's probably because of the date object. I also converted the DateObject into DateString, the same happens.

SQLInsert[conn, SQLTable["match"], {"id", "time", "home_id"}, 
{1, DateString[Now, {"ISODate", " ", "Time", ".", "Millisecond", "000"}], 100}]

JDBC::error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match (id,season,league_id,time,home_id,away_id,fthg,ftag,hthg,htag) VALUES (1,2' at line 1

Any ideas?

Improve this question
$\endgroup$
3
  • 1
    $\begingroup$ I think you are on the right track with DateString. It returns something like "2019-02-01 10:00:35.952000" Are all of those characters legal in your table? $\endgroup$
    – Rudy Potter
    Commented Feb 1, 2019 at 18:09
  • $\begingroup$ @RudyPotter you are right here with the approach DateString. As it turns out, the DateObject was not the only problem. See my own answer below. $\endgroup$
    – 407PZ
    Commented Feb 1, 2019 at 18:18
  • $\begingroup$ I'm fairly sure that inserting DateObjects should be possible, as long as the column type allows for it. I've definitely done that in the past. It might depend on the database and the connector, though. $\endgroup$
    – Sjoerd Smit
    Commented Feb 1, 2019 at 19:29

2 Answers 2

Reset to default
3
$\begingroup$

You may use SQLDateTime with DateList.

SQLInsert[conn, 
  SQLTable["match"], 
  {"id", "time", "home_id"}, 
  {1, SQLDateTime@DateList[], 100}
]

Hope this helps.

Improve this answer
$\endgroup$
2
$\begingroup$

As it turns out, DateObject is not the only problem here. I kind of lack in some basic knowledge in SQL. Here are the mistakes I made.

  1. DateObject cannot be passed to SQL. DateString[Now, "ISODate"] was a right approach.
  2. I shouldn't name my table match, because it is in conflict with the SQL command.
  3. Column name as is also not allowed. Same reason here.
Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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