JDBC and Matlab: A simple example

by 10/09/2014 09:18:00 AM 8 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}

hohonuuli

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.

8 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!

hohonuuli 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.

rama said...

Anexas is the best institute for six sigma certification in Bangalore that offers an internationally-recognized six sigma green belt training and certification program. It is kpmg approved six sigma training coaching center in Bangalore. This lean six sigma green belt certification course in Bangalore is ideal for individuals and enterprises that are looking to gain an in-depth understanding and ensure that you clear lean six sigma green belt certification exam in your first attempt.

https://www.anexas.net/six-sigma-training/six-sigma-certification-bangalore

bhanupratap said...

Hey,cool information man i really enjoyed..lets chinchilla chinchilla... Best software Training institute in Bangalore

supreet said...


hi, nice information is given in this blog. Thanks for sharing this type of information, it is so useful for me. nice work keep it up.
best selenium training institute in hyderabad
best selenium online training institute in hyderabad
best institute for selenium training in hyderabad
best software testing training institute in hyderabad
selenium online training
selenium training in hyderabad
selenium online training in hyderabad