How to connect to Microsoft SQL Server 2008 (MSSQL) from Matlab?

Question

This is probably a simple question:

• How do I connect to Microsoft SQL Server 2008 R2 from Matlab?
• How do I read a table into a matrix given some SQL query?

Update

I'd prefer a method that doesn't require use of manual setup using ODBC.

Answer 1

I present below a review of the different approaches for accessing databases in MATLAB. Here is a list of Stack Overflow questions where some of them were discussed:

• How can I access a postgresql database from matlab with without matlabs database toolbox?
• connection of MATLAB 7.0 and MYSQL
• communicate MATLAB SQL Server
• Getting names of Access database tables with Matlab
• Invoking ADO.NET from MATLAB

Java

MATLAB have an embedded Java JVM, allowing you to directly call the JDBC drivers from MATLAB. You first need to make them available on the Java classpth in MATLAB:

javaclasspath('sqljdbc4.jar');

%# load driver and create connection
driver = com.microsoft.sqlserver.jdbc.SQLServerDriver;
conn  = driver.connect('jdbc:sqlserver://<HOST>:<PORT>;databaseName=<DB>');

%# query database
q = conn.prepareStatement('select * from <TABLE>');
rs = q.executeQuery();
while rs.next()
    char(rs.getString(0))
end
rs.close();
conn.close();

Database Toolbox

If you have access to the Database Toolbox, it can simplify the above as it acts as a wrapper around JDBC/ODBC stuff:

conn = database('<DB>', '<USER>','<PASS>', ...
    'com.microsoft.sqlserver.jdbc.SQLServerDriver', ...
    'jdbc:sqlserver://<HOST>:<PORT>;database=<DB>');
curs = exec(conn, 'select * from <TABLE>');
curs = fetch(curs);
curs.Data
close(curs)
close(conn)

You can also access the database through ODBC. First create a DSN to MSSQL Server (Control Panel > ODBC Data Sources), then use it from the Database Toolbox:

conn = database('myDB', '', '');    %# User/System DSN
%...
close(conn)

COM

You can directly use the ADO OLEDB component from MATLAB. One way is to specify a connection string (DNS-less):

conn = actxserver('ADODB.Connection');
conn.Open('Provider=sqloledb;Data Source=<HOST>;Initial Catalog=<DB>;User Id=<USER>;Password=<PASS>;');
conn.Execute('select * from <TABLE>').GetRows
conn.Close()

.NET

Finally, recent versions of MATLAB added the ability to call .NET from MATLAB. So you can use the ADO.NET data providers:

import System.Data.SqlClient.*
NET.addAssembly('System.Data');
conn = SqlConnection('Data Source=<HOST>;Initial Catalog=<DB>');
conn.Open();
q = SqlCommand('select * from <TABLE>', conn);
r = q.ExecuteReader();
while r.Read()
    char(r.GetString(0))
end
r.Close()
conn.Close()