Store Matlab array in a SQL Server varbinary field

Does anyone know how to store a 1-dimensional Matlab array in a single field of a table in Microsoft SQL Server (and how to retrieve it too)? I would like to be able to store an array of Matlab data that isn't necessarily a fixed size, and I've considered storing it as a comma-delimited string, but I am hoping there is a more elegant solution. My thinking is that it should be the same as storing and reading a byte[]. However, I have been trying for hours now and haven't been able to find anything really helpful on the internet. Here is my Matlab code for storing the array (using ADO):

buf = [1,2,3,4,5]; 
buf = int8(buf);
cmd = actxserver('ADODB.Command');
cmd.ActiveConnection = db.connection; % db.connection stores my connection
% Note that Matlab throws an error when setting the ActiveConnection. This
% command is still valid though and works, so the error should be ignored

cmd.CommandText = 'INSERT INTO dbo.TESTTABLE VALUES(?)'; % According to the MSDN
% website, this should be @val instead of ?, but for some reason that doesn't 
% work and the ? does.

param = cmd.CreateParameter(@val',205,1,8000,buf);

It may be that this code is correct and I just don't know how to read it back again. Also, I gave an array of size 5 as an example, but I would like to be able to store much larger arrays. Thanks for your help,



You could serialize the matrix as a vector of bytes (uint8) using TYPECAST, and store the result in the database along with the original matrix size and type.

Consider the following example. I am using files instead of a database, but the idea still applies.

%# some matrix, and record its size and type
x = rand(4,3);
sz = size(x);
cls = class(x);

%# serialize and write to file
b = typecast(x(:),'uint8');          %# byte array
fid = fopen('file.dat','wb');
fwrite(fid, b, 'uint8')

%# read file and deserialize
fid = fopen('file.dat','rb');
b = fread(fid, '*uint8');
xx = reshape(typecast(b,cls), sz);

%# compare against original matrix

In the above I only store the serialized data, but you should also store the size and type along with it. In you case, just create two additional fields in your table for example, one for size, and the other for type..

As noted here, If you don't want to manually do the serialization on the matrix and its size/type, there are undocumented functions mxSerialize/mxDeserialize in libmx. There's even a link to simple MEX-wrappers in the comments.

Those have the advantage that they work for any MATLAB data type (including structures, cell array, etc..)

The type of the column in your SQL table should be binary. Then just use the regular mat lab one-D array and try to export them to the database.

You do not need any comma or any extra things. They are being handheld by Matlab libraries.

for example you could do:

colnames = {'ContentBinary' }; %this is the name of the column with the binary %content
data =   {[1:100000]}; % a one-d array in matlab
tablename = 'myData';
whereclause = 'where Id = 4028'; %this shows which row you are trying to write to
close(conn); %conn is the connection to SQL DB

For those of you who are attempting a similar problem, I have found a method that works. The code I posted in my question is correct for storing a byte array from Matlab into a MS SQL Server database. You should know, though, that I am storing it into a field of VARBINARY(MAX) and that it automatically gets stored unsigned. Also, it will only store an array that is like buf = [1,2,3,4,5] and will only store the first value of and array that is like buf = [1;2;3;4;5]

When you retrieve it, in a ADO recordset for example, you will have to convert it back to int8 which can be done like in Amro's answer.

As for the serialization, I wrote a simple Java class that implements Serializable to convert an object to a byte array and vice versa. Since Java can be called from Matlab, this method worked really well, and now I can store and retrieve any Matlab vector, array, or string in a varbinary column.

Need Your Help

Plotting a histogram on punctuation occurrence python

python text matplotlib histogram punctuation

I have thousands of sentences in series form (rows) . here's an example:

How to remove the time in report viewer ms-access

I`m using datetimepicker control in my system and the format is short. My problem is whenever I insert a data in database and see it in a microsoft report viewer the time is showing (12:00:00 AM). ...