write a blob inside a cell of sqlite database

Guyse, I need help to write a blob inside one cell of a sqlite database.

Using DB browser for SQLite (it's a free program which everyone can download) it's simple, because i can simply select the cell of my database where i want to write blob, I click on import from file and that's all (the file I load it's a .mat).

Now, in matlab I can also see that blob file. I simply do a fetch to the database and then writing for example:

file = testDatabase{1} (if it is on first row the blob) I can see the blob file. (which is something like 230299x1 uint8

Now, what I need to do (and that's why I'm asking for your help) it's doing all this process with matlab, instead of using that external program DB browser to write the blob inside the database. I suppose if I can read that cell of my sqlite database in matlab to show the content, quite surely there must also be a way to write a blob file inside another cell for example. I don't know what to do.

[Rik]

You can edit my sqlite3 submission in the File Exchange to support BLOBs. If you don't want to go through all that trouble (I would understand), you will need to find out whether the native Matlab tools support the BLOB data type. The documentation would be the best place to start. For which functions did you read the documentation?

[Kautuk Raj]

Have a look at the Database Toolbox.

Answer 1

[Sandeep Mishra]

Hi Massimo,

You can insert a blob data (such as a .mat file) in an SQLite database by first loading the mat file and extracting the required array. After that, you can convert the array into ‘uint8’ format and finally, use the MATLAB ‘sprintf’ function to transform the ‘uint8’ data into a ‘hex’ string, which can then be inserted into the database.

For demonstration, I have created an SQLite database as follows:

conn = sqlite(dbFileName, 'create'); 
createTableSQL = ['CREATE TABLE people (id INTEGER PRIMARY KEY, ','name TEXT, picture BLOB)']; 
execute(conn, createTableSQL); 

You can refer to the following example code to store ‘data.mat’ file as blob into the database:

% Converting mat file into uint8 format 
dataStruct = load('data.mat'); 
dataArray = dataStruct.arr; 
blobData = uint8(dataArray); 

% Convert blobData to a hex string 
hexString = sprintf('%.2x', blobData); 

% Insert data into the table 
insertDataSQL = ['INSERT INTO people (name, picture) VALUES', sprintf('("Alice",  x''%s''), ("Charlie", x''%s'')', hexString,hexString)]; 
execute(conn, insertDataSQL); 

For more information, refer to the following MathWorks Documentation:

1. ‘execute’ function: https://www.mathworks.com/help/releases/R2024b/database/ug/sqlite.execute.html
2. ‘sprintf’ function: https://www.mathworks.com/help/releases/R2024b/matlab/ref/string.sprintf.html
3. ‘uint8’ function: https://www.mathworks.com/help/releases/R2024b/matlab/ref/uint8.html

I hope this help you in solving your query!