BLOB and CLOB using SQL query

35 次查看（过去 30 天）
Debejyo Chakraborty
回答： Chris P 2019-10-3
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.
  1 个评论
Ed Yu
Ed Yu 2015-9-16
编辑：Ed Yu 2015-9-16
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!

请先登录，再进行评论。

请先登录，再回答此问题。

回答（2 个）

James Anderson
James Anderson 2017-11-1
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
Chris P
Chris P 2019-10-3
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);

类别

在 Help Center 和 File Exchange 中查找有关 Database Toolbox 的更多信息

标签

产品

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by