Using Matlab's Database Toolbox with MySQL Connector/J

I normally don't delve too much into the programmatic details of my work, but I've seen enough interest in this topic that I figured I'd lend a helping hand to those fellow frustrated souls.

There are a few awkward, non-native MEX implementations of various database interfaces.  However, Matlab has its own database toolbox built around ODBC/JDBC, and when developing distributable software, one always hopes to minimize third-party library usage.  As a result, I've put a good deal of effort into integrating Matlab with both MySQL and SQLite.  In fact, the data for every post on this site is stored in a 2GB MySQL database server running on my laptop.

As you can see, this requires only that you distribute the platform-independent JAR.  No DLLs, no MEX compilation.

% Database Server
host = 'localhost';

% Database Username/Password

user = 'user';
password = 'password';

% Database Name
dbName = 'assets'; 

% 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('mysql-connector-java-5.1.6-bin.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 info.symbol,info.marketcap,series.close FROM info, series WHERE info.type=''ETF'' AND info.id = series.symbolid AND series.date = ''2008-04-11'' ORDER BY marketcap DESC LIMIT 10')), 'Data');
    disp(result);

% If the connection failed, print the error message
else
    disp(sprintf('Connection failed: %s', dbConn.Message));
end

% Close the connection so we don't run out of MySQL threads
close(dbConn); 
 

Output:
    'SPY'     [8.2300e+010]    [133.3800]
    'EFA'     [4.5420e+010]    [ 72.6400]
    'EEM'     [2.3850e+010]    [139.3400]
    'GLD'     [1.9260e+010]    [ 91.3000]
    'QQQQ'    [1.7040e+010]    [ 44.2800]
    'IVV'     [1.6410e+010]    [133.5200]
    'IWF'     [1.2860e+010]    [ 55.2500]
    'DIA'     [1.0830e+010]    [123.3400]
    'IWM'     [1.0470e+010]    [ 68.7200]
    'VTI'     [9.7800e+009]    [132.2900]


Connection failed

Michael hello,Thanks for this great example. Better than anything I have seen so far on this mater, altough I have few errors - I will appreciate if you can assist me with any of them.I use Matlab 6.1 and MySql 5.1 on a  windows XP platform.First error I got was :dbConn = database(dbName, user , password, jdbcDriver, jdbcString);??? Undefined function or variable 'javaaddpath'.When trying to connect I got:Connection failed: JDBC Driver Error: com.mysql.jdbc.Driver. Driver Not Found/Loaded.Here is what I did during installation:I downloaded the MySQL Jdbc.unzipped it to C:\MATLAB6p1\java\MySQLand Added at the classpath.txt the following line:$matlabroot/java/mysql/mysql-connector-java-5.1.6-bin.jar Is there some important step I am missing?I would appreciate if you can give a bit more data on the installation side of the MySQL and MalLab connectionThanks in advance,Tripy  

I have similar questions regarding javaaddpath

From mathworks website, it has the referenceshttp://www.mathworks.com/access/helpdesk/help/techdoc/index.html?/access... However, my matlab doesn't recognize this command >> versionans =6.0.0.88 (R12) Is it because my Matlab is an older version( running on Sun Unix), or because there are some missing plug-in. Such as the database toolbox. Do we have to purchase the database toolbox, individually? Thanks for reading this and providing helps.

database toolbox questions

First we figured out that I have to purchase a license of database toolbox, seperately. Then R12, may not support connection to mySQL yet. so we switched to R14.Here is the error message I get. It is trying to connect, but the driver is wrong. Anyone can shed a few insight on this?>> dbTest dbConn =        Instance: 'RM2'       UserName: 'optDev'         Driver: []            URL: []    Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]        Message: 'JDBC Driver Error: com.mysql.jdbc.Driver. Driver Not Found/Loaded.'         Handle: 0        TimeOut: []     AutoCommit: 'off'           Type: 'Database Object'database connection failed

Same Prob

I have nearly the exact same problem trying to connect to a test database.  Anyone find an answer?

Solved!

I did manage to solve my own problem, when I looked at my database setup.  By default, the database server is set to refuse connections from anything that comes from outside its network connection.  I commented out the line in the database config (it's my server that runs our home server, so I have access).  The line in /etc/mysql/my.cnf in my case was:bind-address 192.168.0.104I commented this out and connections were allowed.  Note that this has a security trade-off.  It should probably be something more selective that will only accept trusted computers or sub-nets.