Using MySQL databases in Mathematica

Question

I've seen it's possible to make a connection between Mathematica and MySQL databases using Input Needs["DatabaseLink"] and conn = OpenSQLConnection[JDBC["MySQL(Connector/J)", "yourserver/yourdatabase"], "Username" -> "yourusername", "Password" -> "yourpassword"] (in case anyone wants to give it a try). Documentation of DatabaseLink here, by the way.

Does anyone have experience using Mathematica in this way, probably to analyze data contained in the database? Are there obvious drawbacks (speed, memory needed, etc.)?

Answer 1

I have not used DatabaseLink` with MySQL, but I have used it extensively with Oracle, SQL Server and HSQLDB. Most of my DatabaseLink` usage has been on Mathematica versions 6 through 8, on Windows. I have used it for both reading and writing, sometimes with very large data sets. My experience has been that the facility works as expected. I did not run into any unusual situations involving performance or memory. I would expect you to find SQL access in Mathematica to be comparable to what you have experienced in other languages. In particular, it performs very much like using Java -- presumably because Mathematica uses Java internally to do all of the heavy lifting.

I will mention that the "obvious" way to execute queries in Mathematica, SQLExecute, will read the entire result set into memory. If you want work with large result sets without running out of memory, take a look at the result set functions SQLResultSetOpen et al.

For a very basic example of SQL use in Mathematica (using HSQLDB), see my response to The best way to construct a function with memory.