Insert a DateObject into MySQL database?

1

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?

407PZ

Posted 2019-02-01T17:25:02.260

Reputation: 1 329

1I 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? – Rudy Potter – 2019-02-01T18:09:15.200

@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. – 407PZ – 2019-02-01T18:18:44.000

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. – Sjoerd Smit – 2019-02-01T19:29:53.307

Answers

3

You may use SQLDateTime with DateList.

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

Hope this helps.

Edmund

Posted 2019-02-01T17:25:02.260

Reputation: 35 657

2

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.

407PZ

Posted 2019-02-01T17:25:02.260

Reputation: 1 329