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?

$\endgroup$
  • 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 Feb 1 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$ – 407Peezy Feb 1 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 Feb 1 at 19:29
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.
$\endgroup$
2
$\begingroup$

You may use SQLDateTime with DateList.

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

Hope this helps.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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