Over the past few months, I’ve been working on a project that integrates sophisticated Matlab financial models with a Microsoft environment. Back in February, I wrote about writing from Matlab to Excel using ActiveX. Since then, I’ve been focusing on SQL Server and want to share those experiences.

The simplest way to access a Microsoft SQL Server from Matlab is to use the Database Toolbox. This toolbox allows for an ODBC or a JDBC connection to a database. Since our functions are running in a Microsoft environment, we went with an ODBC connection. The connection is very simple to open:

conn = database('ODBCdatasourcename','ODBCusername','ODBCpassword')

In our case, we would then ‘ping’ the connection to make sure everything has connected properly, and if not, return an error:

try
ping(conn) ;
catch ME
% insert error processing code here …
return;
end

We then execute our SQL and check the results

sql = ‘select a,b from c where d = 10’;
curs = exec(conn, string) ;
curs = fetch( curs ) ;
NumRows = rows(curs);
if (NumRows < 1 )
% insert error processing code here about no rows returned
return ;
end

result = curs.Data;
for i=1:1:NumRows
if (~isnan(result{i,1}))
a{i} = result{i,1};
b{i} = result{i,2};
end
end
close(curs) ;
close(conn);

It has worked nicely for just about all of our needs. However, for speed and flexibility, we found it useful to use ActiveX for some of our database access.

With this, we used actxserver to establish our connection. We also used this as an opportunity to start using Stored Procedures. Here is an example of how we did this:

databasestring='Provider=sqloledb;Data Source=oledbserver;Initial Catalog=oledbcat;User Id=oledbuser;Password=oledbuser;';

% Establish ActiveX OleDB connection
h=actxserver('ADODB.Connection');
h.Open(databasestring);

sql = ‘exec update_data @key = 10, @value = 20’;

try
res=h.Execute(sql);
catch ME
% insert error processing code here …
return;
end
h.Close();
delete(h);

Since we were doing an update, we did not need to look at any returned values, only the success or failure of the update, as caught by the catch block. However, if wanted, we could have added code like

x=res.GetRows();

to get data back. In addition, we could have used

databasestring='Provider=sqloledb;Data Source=oledbserver;Initial Catalog=oledbcat;Trusted_Connection=yes;';

if we wanted to use a trusted connection instead of passing userids and passwords.

All of this worked well until we tried to pass a binary object or BLOB as a parameter to a stored procedure. With this, we had to get a bit more sophisticated with our ActiveX coding.

Instead of executing a string, we used an ADODB.Command. This was set up as follows:

adoCmd = actxserver('ADODB.Command');
adoCmd.CommandType = 4; % Stored Procedure
adoCmd.CommandTimeOut = 0;
adoCmd.CommandText = ‘funky_stored_procedure';
adoCmd.ActiveConnection = h; % The existing open connection from above.

For each parameter, we needed to set up an ADOCB.Parameter

parameter1 = actxserver('ADODB.Parameter');
parameter1.Name = '@SPparameter1';
parameter1.Type = 2; % smallint
parameter1.Direction = 1; % Input
parameter1.Value = matlab_parameter1;
adoCmd.Parameters.Append(parameter1);

The parameter type and direction values can be found in the w3cshools’ ADO CreateParameter Method documentation.

This worked well, until we got to BLOBs. What we wanted to do was store a Matlab graph as a binary file in the database. We produced the graph and then read it with fread(). However, we could find no way of getting that binary data to successfully load into the database.

On further investigation, I found the ADODB.Stream method. We set up the stream as follows:

stream1 = actxserver('ADODB.Stream');
stream1.Type = 1 ; % adTypeBinary
stream1.Open;
stream1.LoadFromFile(filename);

With this, we could then set up a parameter that passed the value from the stream:

pgraph = actxserver('ADODB.Parameter');
pgraph.Name = '@SPGraph';
pgraph.Type = 204; % varBinary
pgraph.Direction = 1; % Input
pgraph.Value = stream1.Read;
pgraph.Size = size(pgraph.Value,2);

You can get more information on working with ADO Streams at w3school’s page on the ADO Stream Object.

With all of our parameters finally in place, we could then call the execute method for the adodb.command:

try
res=adoCmd.Execute();
catch ME
% insert error processing code here …
return;

When all the pieces are put together, it is really quite simple. However, finding the right pieces can at times be a challenge. Whether you end up using the simple method from the Database Toolkit, or a fairly complicated method including passing streams to a stored procedure via ActiveX, there are many powerful ways to get data from Matlab in and out of SQL Server.

(Originally posted at Toomre Capital Markets.)