From FarmShare

Revision as of 17:45, 4 May 2012

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]