MatlabMysql
From FarmShare
Mysql queries in Matlab scripts
This document describes how to access mysql databases from your matlab scripts. Potentially any mysql database can be used, however this example was built around the mysql database service described here: https://itservices.stanford.edu/service/sql
Lets assume you have the following mysql database:
$ mysql -p --user=gfarmsharetest --host=mysql-user.stanford.edu g_farmshare_testing Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 186013282 Server version: 5.1.49-3~bpo50+1-log (Debian) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show tables; +-------------------------------+ | Tables_in_g_farmshare_testing | +-------------------------------+ | matlabfoo | +-------------------------------+ 1 row in set (0.00 sec) mysql> desc matlabfoo; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | foo | int(11) | YES | | NULL | | | bar | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from matlabfoo; +------+------+ | foo | bar | +------+------+ | 1 | 2 | | 2 | 4 | | 3 | 8 | | 4 | 16 | | 5 | 32 | +------+------+ 5 rows in set (0.00 sec)
You can access this same data inside Matlab via (save this script as matmysql):
% Database Server host = 'mysql-user.stanford.edu'; % Database Username/Password user = 'gfarmsharetest'; password = 'putyourpasswordhere'; % Database Name dbName = 'g_farmshare_testing'; % JDBC Parameters jdbcString = sprintf('jdbc:mysql://%s/%s', host, dbName); jdbcDriver = 'com.mysql.jdbc.Driver'; % Set this to the path to your MySQL Connector/J JAR javaaddpath('/usr/share/java/mysql-connector-java.jar') % Create the database connection object dbConn = database(dbName, user , password, jdbcDriver, jdbcString); % Check to make sure that we successfully connected if isconnection(dbConn) % Fetch the symbol, market cap, and last close for the 10 largest % market cap ETFs result = get(fetch(exec(dbConn, 'SELECT foo,bar from matlabfoo')), 'Data'); disp(result); else % If the connection failed, print the error message disp(sprintf('Connection failed: %s', dbConn.Message)); end % Close the connection so we don't run out of MySQL threads close(dbConn);
submit the script to grid engine
See MATLAB for further information.
$ cat matlab_mysql.script # #$ -cwd #$ -j y #$ -S /bin/bash #$ -N matlabmysql /afs/ir.stanford.edu/software/matlab-2011b/bin/matlab -nodesktop < matmysql
submit the job
$ qsub matlab_mysql.script
You should see following output:
Warning: No display specified. You will not be able to display graphics on the screen. Warning: No window system found. Java option 'MWT' ignored < M A T L A B (R) > Copyright 1984-2011 The MathWorks, Inc. R2011b (7.13.0.564) 64-bit (glnxa64) August 13, 2011 To get started, type one of these: helpwin, helpdesk, or demo. For product information, visit www.mathworks.com. >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> [1] [ 2] [2] [ 4] [3] [ 8] [4] [16] [5] [32]