BLOB and CLOB using SQL query

How can I insert to a BLOB and CLOB an array/matrix of numbers and a cell array of strings (all combinations), using sql queries (exec(conn,sqlquery)) in Matlab? I'm using JDBC.

[Ed Yu]

Blob and Clob are somewhat difficult since they are not totally standardized for all databases... I would suggest you to use native java JDBC to perform the manipulation by first dumping out the variables in a .mat file and then save the .mat file into your database as Blob.

Good Luck!

Answer 1

[James Anderson]

Hi,

I've used the BLOB quite a bit for storing large arrays - I found that because writing to database rows is a time consuming process, combining a large array into a blob and then writing that value to SQL database is much faster. However, of course you can then only read it using Matlab, so I only use this option when I am using SQL as a sort of cache for intermediary data in my algorithm:

Here is what I do:

1. Create two varbinary(max) fields in SQL server, one for the data array, and another one for the size of the array
2. Convert your array into uint8
3. Also calculate the size of your array, and convert that into uint8 as well
4. Optional but recommended: zip your blob - this will considerably reduce the size, and make it much faster to load and unload from SQL
5. write your data blob and your size blob to DB

To unload, do the opposite.

Here are two functions that will do the job for you:

        function [datasize, databinzip] = double2varbinary(Inputdata)

            datasize = typecast(size(Inputdata),'uint8');

            databin = typecast(Inputdata(:),'uint8')';
            databinzip = dzip(databin)';

        end

        function OutputData = varbinary2double(databin,datasize)

            UnzipData = dunzip(databin);
            bindata = typecast(UnzipData,'double');
            binsize = typecast(datasize,'double');

            OutputData = reshape(bindata,binsize);

The zip function I took from here.

Finally - worth noting, that in terms of the Database connection, I only got this to work using ADOBD, although I presume it might be possible to get it in using JDBC - but you'd have to work that out:

   function writeBasketBinData(Conn,ID,inputdata)

               [DataSize, DataBin] = double2varbinary(inputdata);     

              cmd = CreateCommand(Conn);

              cmd.CommandText = 'INSERT INTO MyTable (bindata,binsize, ID) VALUES(?,?,?)';

              param = cmd.CreateParameter('@bindata',205,1,length(DataBin),DataBin);
              param2 = cmd.CreateParameter('@binsize',205,1,length(DataSize),DataSize);
              param3 = cmd.CreateParameter('@ID',3,1,50,ID);
              cmd.Parameters.Append(param);
              cmd.Parameters.Append(param2);
              cmd.Parameters.Append(param3);
              cmd.Execute();     
          end

Answer 2

[Chris P]

I've been trying to do the same as the OP. But using this thread and the one here...https://uk.mathworks.com/matlabcentral/answers/457285-how-can-we-create-database-with-mat-files. ...I've managed to get something working, so thought I'd share in case anyone else finds it helpful.

I've broken down my script into different headings, but if you copy each back into the editor it will all run as a single file.

For reference, I'm using:

• MATLAB 2019a (which I think has recently introduced/enhanced database interaction and 2019b more so)
• MySQL Workbench 8.0 to create the tables in the database

Read .mat file into workspace

fileToRead = 'someFile.mat';            % File name to be read

Original_MAT_File = load(fileToRead);   % Load into workspace for later reference 

f = fopen(fileToRead,'r');              % Open file
[~,~,fileFmt,fileEncoding] = fopen(f);  % FYI, format and encoding of file 
fileContent = fread(f,Inf);             % Read file into workspace
fclose(f);                              % Close file

Convert to signed 8-bit intiger (int8)

When I read the data stored as BLOB in MySQL table back into worksapce, it is class int8, NOT uint8. Therefore, cast the data to int8.

N.B. typecast(fileContent,'int8') does not seem to be the same as int8(fileContent). Also note the transposing of the array to be a 1xN rather than Nx1

fileContent_int8 = typecast(fileContent,'int8')'; 

Make connection to database

conn = database('myDB','user','type_password_here');

Query the database to obtain data in the table

% Get all values from the table
query = ['SELECT * ' ...
    'FROM test.rundata '];

% Set Database Import Options
opts = databaseImportOptions(conn,query);

% Execute query and fetch results
data = fetch(conn,query,opts); % as reading in all data equavalent to using: data = sqlread(conn,"runData");

Prep data to be written to MySQL table as a MATLAB table

nextRow = max(data.rowID__RunData)+1;   % Unique key used by the table
insertDateTime = string(datestr(now,'yyyy-mm-dd HH:MM:SS')); % Time of writing the data in
refID = 123;                            % Some number
txtComment = "Some comment.";           % Some text/comment of class varchar(45)

% FYI, in this example, the MySQL column types are: ["INT(11)" "TIMESTAMP" "INT(11)" "VARCHAR(45)" "LONGBLOB"]
colNames = opts.VariableNames; % get the column names from the MySQL table

% Create a MATLAB table of the data you want to add to the MySQL table
tab2Write = table(nextRow, insertDateTime ,refID, txtComment, fileContent_int8, 'VariableNames', colNames);

Write MATLAB table to MySQL table

sqlwrite(conn, "runData",tab2Write);

Read back in the table from the MySQL database & close the connection

data_After_Write = sqlread(conn,"rundata");

% Close connection to database
close(conn)

Export imported BLOB data to .mat file

N.B. You MUST ignore the first 27 characters of the BLOB data read from the database. It seems to have extra information inserted at the front. This is beyond my knowledge/understanding, but my hunch is to do with the encoding of the data in the MySQL database. I've tried changing the Charset and Collation type of the datase, but not to any effect as well as litte experience of what I'm doing there...

BLOB_int8 = data_After_Write.dataAsBLOB__RunData{end}; % Use the last entry into the MySQL database and obtain the BLOB 
BLOB_double = typecast(BLOB_int8(28:end),'double'); % Data from database is int8, typcast back to double  

fileToWrite = 'someFile__FROM_db.mat';              % Name of .MAT file to create
fw_ = fopen(fileToWrite,'w','n','UTF-8');           % Open file
kw_ = fwrite(fw_, BLOB_double);                     % Write BLOB data to file 
fclose(fw_);                                        % Close file

Sanity check!

% Reload newly written .mat into workspace and compare with the original
% file you opened
Reloaded_MAT_File = load(fileToWrite);

% Visually compare...
disp(Original_MAT_File) % Display content of original data
disp(Reloaded_MAT_File) % Display content of reloaded data

% Check fieldnames are all the same...
fieldNameCheck = isempty(cellfun(@strcmp,fieldnames(Original_MAT_File),fieldnames(Reloaded_MAT_File))==0);
fprintf('Number of missmatching fieldnames = %i\n', fieldNameCheck);

% Check byte size of the two files...
Original_Info = whos('Original_MAT_File');
Reloaded_Info = whos('Reloaded_MAT_File');
bytesAgree = Original_Info.bytes ==Reloaded_Info.bytes;
fprintf('Byte size of .mat files are the same: %i\n', bytesAgree);