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 NamedbName = '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 JARjavaaddpath('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]
- Michael J Bommarito II's blog
- Add new comment
- 2178 reads
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.
Problem using global variable and db connection
Hallo,I use your code to connect to mysql db. I found out that all global variables are cleared after establishing db connection.Could somebody confirm this behaviour?Thanks Joe
I've got it. I forgot to
I've got it. I forgot to close the connection.Thanks :)