JDBC and Matlab: A simple example

by 10/09/2014 09:18:00 AM 7 comments
Matlab is an excellent tool for ad-hoc analysis and prototyping. If you store your data in a relational database, Mathworks provides a database toolbox for fetching your data. However, you do not need to purchase this to interact with your database. As Matlab has excellent Java integration, you can use JDBC directly to talk to pretty much any database you want. In order to do this do the following:

Get a JDBC Driver

First, you will need the JDBC driver required to talk to your database. If you do not know how to find the driver, follow this link for an example on how to locate your driver. Once, you have your JDBC driver, you will need to add it to Matlab's static classpath. If you are running an older version of Matlab, say earlier than R2013b, you probably need to add the full path to the driver in the classpath.txt file. You can edit this by typing open classpath.txt in Matlab. If the file says "Do Not Edit This File" at the top then you will need to add the full path to the driver in ~/.matlab/[matlab version]/javaclasspath.txt. (e.g. ~/.matlab/R2014b/javaclasspath.txt). You may need to create this file if it doesn't exist.

Figure out your Database URL

Next, you'll need to figure out the URL to your database. Refer to your drivers documentation. Here's a few example URLS:
Server Driver Jar JDBC URL
SQL Server sqljdbc4.jar jdbc:sqlserver://servername.mydomain.org;database=MyDatabase
SQL Server jtds-1.3.1.jar jdbc:jtds:sqlserver://servername.mydomain.org:1433/MyDatabase
PostgreSQL postgresql-9.3-1102.jdbc41.jar jdbc:postgresql://servername.mydomain.org/MyDatabase

Matlab Code

Now all you need is some Matlab code to fetch your data.

database.m

First, here's a function to setup a database connection:

function c = database(url, user, password, driverName)
% DATABASE - connect to a SQL database
%
% Usage:
%   c = database(url, user, password, driverName)
% 
% Inputs:
%   url = The database URL. e.g. jdbc:jtds:sqlserver://solstice.shore.mbari.org:1433/EXPD
%   user = The user name to connect to the database
%   password = THe password for user
%   driverName = the name of the JDBC driver to use for the connection. 
%       e.g. 'net.sourceforge.jtds.jdbc.Driver' (optional)
%
% Outputs
%   c = A java.sql.Connection object to your database. Remember to call
%   close, either as 'close(c)' or c.close() when you are done with the 
%   connection.
%
% NOTES: In order to connect to a database, you will need the appropriate
% JDBC driver on the classpath. Most drivers do not work on Matlab's dynamic
% classpath. Instead add them to ~/.matlab/R2014b/javaclasspath.txt

% Brian Schlining
% 2014-10-08

if nargin == 4
    d = eval(driverName);
end
c = java.sql.DriverManager.getConnection(url, user, password);

jdbcquery.m

This function runs a query and returns the results as a Matlab structure. Each column of the ResultSet is represented as a separate field in the structure:

function s = jdbcquery(conn, sqlstr)
% Connect to database, execute SQL string, and pass back structure array.
%
% Usage:
%   s = jdbcquery(conn, sqlstr)
% 
% Input   
%   conn: A java.sql.Connection object. See Also database
%   sqlstr: SQL string, i.e. 'SELECT * FROM Observation WHERE ConceptName LIKE ''Pandalus%'''
%
% Output  
%   s: structure array, each field matches the column names in the query.
%
% Note: 
%   The Java class path to the sqljdbc driver must be specified before call.
%   For best results put it in your ~/.matlab/R2014b/javaclasspath.txt file.  
%   This path will vary depending on your Matlab version.
%   
% See also database

% Brian Schlining
% 2014-10-08 - Modified Reiko's version used for EXPD queries
% 2014-11-01 - Using UTC calendar to read dates to avoid timezone issues.

%% Java Imports
import java.sql.ResultSet;

cal = java.util.Calendar.getInstance();
cal.setTimeZone(java.util.TimeZone.getTimeZone('UTC'));

%% Fetch Loop
try    
    % query database
    q = conn.prepareStatement(sqlstr, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    rs = q.executeQuery();

    metaData = rs.getMetaData();
    columnCount = metaData.getColumnCount();
    n = resultSetSize(rs);
    s = initStruct(metaData, n);
    
    row = 0; 
    % assign variables from resultset
    while rs.next()
        row = row + 1;
        for i = 1:columnCount
            coltype = char(metaData.getColumnTypeName(i));      % assess the column type
            colName = char(metaData.getColumnName(i));
            switch coltype
                case {'bigint', 'int', 'int4', 'decimal', 'double', 'float',  ...
                        'float8', 'float16', 'real', 'serial'}
                    s.(colName)(row) = toDouble(rs.getObject(i));
                case {'date'}
                    s.(colName)(row) = toDate(rs.getDate(i, cal));
                case {'datetime'}
                    s.(colName)(row) = toDate(rs.getTimestamp(i, cal));
                case {'geometry'}
                    s.(colName){row} = rs.getObject(i);
                otherwise % value is a string, so just pass it into the output array
                    s.(colName){row} = toString(rs.getString(i));
            end
        end
    end 

    % close all database connections
    try 
        q.close();     
    catch
        % DO NOTHING ON EXCEPTION
    end
    try 
        rs.close();     
    catch
        % DO NOTHING ON EXCEPTION
    end

catch me
    ex = MException('JDBC:jdbcquery', ['An error occurred while executing the SQL: \n' sqlstr ]);
    ex = ex.addCause(me);
    ex.throw;
end
end


%%
function s = resultSetSize(resultSet)
    ok = resultSet.last();
    if ~ok
        s = 0;
    else
        s = resultSet.getRow();
        resultSet.beforeFirst();
    end
end

%%
function s = initStruct(metaData, rows)
    for i = 1:metaData.getColumnCount()
       colType = char(metaData.getColumnTypeName(i));
       colName = char(metaData.getColumnName(i));
       switch char(colType)
           case {'bigint', 'int', 'int4', 'decimal', 'double', 'float',  ...
                    'float8', 'float16', 'real', 'serial' 'date', 'datetime'}
               s.(colName) = ones(rows, 1);
           otherwise
               s.(colName) = cell(rows, 1);
       end
    end
end

%%
function v = toDouble(n)
    if isempty(n)
        v = NaN;
    else
        v = double(n);
    end
end

%%
function v = toDate(n)
    if isempty(n)
        v = NaN;
    else
        %datenum('01 Jan 1970 00:00:00') = 719529
        v = n.getTime() / 1000 / 60 / 60 / 24 + 719529;
    end
end

%%
function v = toString(n)
    if isempty(n)
        v = '';
    else
        v = char(n);
    end
end

Putting it all together

Finally, you can use it in Matlab like this:

c = database('jdbc:jtds:sqlserver://severname.mydomain.org:1433/mydatabase', 'someusername', 'supersecretpassword');
sql = 'SELECT * FROM SomeTable';
v = jdbcquery(c, sql);
c.close();
If you query a database often you can dump that all into a function for that particular database. For example:

mydbquery.m


function v = mydbquery(sql)

c = database('jdbc:jtds:sqlserver://servername.mydomain.org:1433/mydatabase', 'someusername', 'supersecretpassword');
v = jdbcquery(c, sql);
c.close();
And then you would just use it as a one liner:

>> v = mydbquery('SELECT * FROM Observation WHERE ConceptName LIKE ''Pandalus%''')

v = 

                   id: [1989x1 double]
      VideoFrameID_FK: [1989x1 double]
       ObservationDTG: [1989x1 double]
             Observer: {1989x1 cell}
          ConceptName: {1989x1 cell}
                Notes: {1989x1 cell}
    LAST_UPDATED_TIME: [1989x1 double]
                    X: [1989x1 double]
                    Y: [1989x1 double]
              rowguid: {1989x1 cell}

Brian Schlining

Developer

Cras justo odio, dapibus ac facilisis in, egestas eget quam. Curabitur blandit tempus porttitor. Vivamus sagittis lacus vel augue laoreet rutrum faucibus dolor auctor.

7 comments:

Preethi said...

I couldn't get it to work until I changed a line in the "Putting it all together" section from


c = database('jdbc:jtds:sqlserver://severname.mydomain.org:1433/mydatabase', 'someusername', 'supersecretpassword');

to this:

c = java.sql.DriverManager.getConnection('jdbc:jtds:sqlserver://severname.mydomain.org:1433/mydatabase', 'someusername', 'supersecretpassword');


When I tried it in the way originally mentioned in the tutorial, I got the message 'ODBC is not supported on Unix. Please use a JDBC driver.'

Any explanation for what was causing this error? My fix works, but I want to know what I was doing wrong the first time around. Thanks!

Preethi said...

I couldn't get it to work until I changed a line in the "Putting it all together" section from


c = database('jdbc:jtds:sqlserver://severname.mydomain.org:1433/mydatabase', 'someusername', 'supersecretpassword');

to this:

c = java.sql.DriverManager.getConnection('jdbc:jtds:sqlserver://severname.mydomain.org:1433/mydatabase', 'someusername', 'supersecretpassword');


When I tried it in the way originally mentioned in the tutorial, I got the message 'ODBC is not supported on Unix. Please use a JDBC driver.'

Any explanation for what was causing this error? My fix works, but I want to know what I was doing wrong the first time around. Thanks!

Brian Schlining said...

Sorry, I can't say exactly as I can't duplicate your issue. I'm honestly very surprised you are running into an issue as your workaround is doing exactly what the database funciton is.

Preethi said...

Yeah, I do realize that it's doing the exact same thing as database.m.... either way, I'm glad to have the functionality working. Thanks a lot for the helpful tutorial!

vishal sharma said...

Very nice blog...
Great information provided. We also provide Matlab Training in Noida. I appreciate your work.

Nitesh Kumar said...

I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in MATLAB, kindly contact us http://www.maxmunus.com/contact
MaxMunus Offer World Class Virtual Instructor led training on MATLAB. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
For Demo Contact us.
Nitesh Kumar
MaxMunus
E-mail: nitesh@maxmunus.com
Skype id: nitesh_maxmunus
Ph:(+91) 8553912023
http://www.maxmunus.com/




onlinedistancembaindia said...

Online mba in India
DEIEDU is the best online Institute in the world with high class course outline and up to date learning materials. DEIEDU is providing the online mba in india, online mba in india, Distance learning mba courses in india, Correspondence mba in India Mba from distance in India, Online Executive Mba in India, distance Mba from India, Online distance mba in India. Distance learning mba degree in India.
Address:
401, fourth floor sg alpha tower
Vashundhra (up)
Phone: 9811210788
Email: info@deiedu.in
Website: http://www.deiedu.in/
online mba in india