Matlab has a database toolbox, but there are several reasons why you might decide to take matters into your own hands and query SQL Server data using the ADO library. One simple reason might be that you have not yet purchased the database toolbox but you want to get started now. Another reason is that the Matlab toolbox provides support only for ODBC and JDBC, and you may wish to use SQL Server features not accessible with these libraries. Most importantly, however, is that ODBC and JDBC do not provide support for the SQL Server Analysis Services. To query a multidimensional or a tabular mode database from Matlab, we must use ADO. Unfortunately, this option is only available to Matlab users running on Windows.
Data access from Matlab is very comparable to data access from Microsoft Excel, but there are some important distinctions. Matlab has a function, actxserver, that will instantiate COM objects. COM, of course, is a Windows-only technology. Here is an example of creating an ADO connection object in Matlab.
conn = actxserver(‘ADODB.Connection’);
As usual, we can open a connection once we have specified correct attribute values for the connection string.
conn.Open(‘Provider=sqloledb;Data Source=.;Initial Catalog=Northwind;Integrated Security=SSPI;’);
If we were creating a VBA in macro in Excel, we would likely wish to create an ADO recordset from the query results. We can do this in Matlab as well, but in Matlab, it is often more direct and more convenient to create a Matlab cell array.
rs=conn.Execute(‘select UnitPrice * Quantity from [Order Details]’).GetRows;
In contrast with a matrix, which consists of numeric values of a single datatype, a cell array can contain different data types. Like a conventional SQL result set, different columns will typically have different types. We must be careful, now, about what we refer to as a “column”. The query above returns only one data column, so the resulting cell array has only one row. In the Northwind sample database, this row has 2155 columns.
Cell arrays containing a single data type can be converted into a vector or matrix using the cell2mat( ) function. Matlab will infer the data type for the matrix. It is not sufficient for the cell array to contain only numeric types; integers and floating point values cannot be mixed if they are to be converted into a matrix. Let’s consider the following query executed in Matlab:
rs=conn.Execute(‘select ProductID, UnitPrice * Quantity from [Order Details]’).GetRows;
If we would like to obtain the line item totals as a column vector in Matlab, we would use the following conversion:
v= cell2mat(rs(2,:))’ ;
Of course, when we are finished we always remember to clean up after ourselves.
In contrast with VBA, we cannot set the conn object variable to Nothing (or anything equivalent). We call the Matlab function clearvars to remove the variable conn, and its associated COM object reference, from memory.
In Matlab running under windows, it is easy to instantiate ADO objects and query data from SQL Server. In the next section, we will see that similar techniques can be used to query both multidimensional and tabular mode Analysis Services databases.