MySQL and Matlab

The MySQL database server is very popular for its openness, robustness, and speed. Matlab is a wonderful commercial product for scientific and technical computing. Using them together is a great tool for quantitative data analysis. You can do this using the Matlab Database Toolbox, but it is more efficient to connect directly using the APIs for both products. This code implements that connection, with a fairly rich framework for handling data conversion, especially dates and times.

Author: Robert Almgren. (I wrote it while I was running the Master of Mathematical Finance Program at the University of Toronto.)

The code is copyright (c) Robert Almgren 2005, and is made available under the BSD license: it is provided "as is", and anyone may freely use it and incorporate it into other projects, as long as the copyright notice is maintained and no endorsement by me is implied.

This code is also available at the Matlab File Exchange: search for "mysql".

As far as I know, these source versions can be compiled against any reasonably recent versions of MySQL and of Matlab, and I don't know of any particular platform dependences although I have tried only Linux and Windows. I have had reports of problems on MacOSX which I hope to sort out soon.

mysql.cpp

The C++ source code.

mysql.m

This M-file contains the help text, displayed if you type help mysql; it is the documentation for how to use the mysql function.

You will also need the appropriate version of MySQL from the Downloads page. If your server is on a different machine then you may be able to get only the "Include files and libraries for development" rather than the full server.

You will need a C++ compiler (standard on Linux, or Microsoft Visual Studio) and you should configure Matlab for compiling by doing mex -setup.

Linux/Unix

I assume you have the MySQL include files and libraries in /usr/include/mysql and /usr/lib/mysql respectively (look for the include file mysql.h and the library libmysqlclient.a). Modify the below compile command as necessary if your installation is different. Then, either from the Matlab command line or from the shell, cd to the directory where you have put mysql.cpp and compile it with

mex -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient mysql.cpp

This will produce the file mysql.mexglx which you can copy to somewhere on your Matlab search path, for example ~/matlab.

Windows

I assume you have MySQL in the default location C:\mysql. From the Matlab command line, cd to the directory where you have put mysql.cpp, and compile it with

mex -I"C:\mysql\include" -DWIN32 mysql.cpp "C:\mysql\lib\opt\libmySQL.lib"

(The -DWIN32 flag enables some Windows-specific options inside mysql.cpp.) This will produce the file mysql.dll which you can copy to somewhere on your Matlab search path. Also copy C:\mysql\lib\opt\libmySQL.dll into C:\WINDOWS\system32.

In either case, test it from inside Matlab with

mysql('status')

Additional useful files

datelabel.m

Make axis labels look like reasonable dates. It is like Matlab's dateaxis except that it works better (you can zoom in, then type datelabel again to get new labels).