Storing a .mat file in mysql database

26 views (last 30 days)
Gurvinder
Gurvinder on 6 Aug 2013
Answered: Simon on 13 May 2023
Hello again, I have a database in which i am storing certians bits of information, however i would like to store the whole .mat file related to this data.Mainly for the purpose if further analysis needs to be preformed on the dataset at a later point.
I have seen some research journals where people have done this and was hoping if someone could point me in the right direction please.
FYI running matlab 7.6.0(R2008a) and query builder is not working for me :(

Accepted Answer

Darik
Darik on 8 Aug 2013
If you absolutely have to do this, you could create a BLOB column in your database table, then in matlab use the fread function to grab the mat file as raw binary data, and then just insert that into the database. To get the data back out, you would then just fwrite the binary data to disk, and use the load function to read it into matlab.
The huge downside of this whole plan is of course the fact that you won't be able to query in any of the data in the matfile, which is usually the whole reason for storing stuff in a relational database in the first place.

More Answers (2)

Shashank Prasanna
Shashank Prasanna on 6 Aug 2013
Since MATLAB supports so many different types of data, you will have to load it into MATLAB first before committing it into a database.
You can start by going through some examples in the Database Toolbox:
Take a look at some of the full example workflows to get an idea of how to insert the data into a table.
  2 Comments
Gurvinder
Gurvinder on 6 Aug 2013
Edited: Gurvinder on 6 Aug 2013
Thank you for your reply,
ah so storing a whole .mat file is not possible instead only sotring parts of the data within it are?
as currently i am doing what your saying, as i am storing the important snippets of data such as:
myData = [MATDATA.StatsSetHistory.data];
for i = 1:150 RESNAME = ['Result' int2str(i)]; colnames{1,i} = RESNAME; end
insert(conn, 'data', colnames, myData);
however for future purposes if i wish to come back to the analysis performed i would like to access the whole .mat file, as it is pointless sotring everything into a database as 1) it will not be needed on a routine bases 2) lengthly procedure
so if the whole .matfile cannot be stored is there a way to store the location into the database instead?
Shashank Prasanna
Shashank Prasanna on 8 Aug 2013
MAT file is very generic data container therefore I cannot answer your question with an Yes or No. It depends on what data you have within your mat file as I already mentioned.
You can simply write a function or script once to load the matfile and write the data into the specified database. And subsequently all you need to do is call that function again. Otherwise please clarify exactly what your workflow is.

Sign in to comment.


Simon
Simon on 13 May 2023
In my own experience I have tried three different approaches to store tables in MySQL. I also use Workbench (UI for MySQL commands) for fine-tuning variable types and quick selection and visual inspection of data.
I would suggest you to restructurd your .mat data into tables. Saving tables to MySQL (and other databases) is simpler.
approach I.) When the table has less than 19,000 rows, sqlwrite(conn, tablename, tabledata). For new rows of data, sqlwrite( ) will automatically append to the existing MySQL table.
approach II.) It seems that MySQL has problems with inserting many rows at one time. So when my codes would generate a very tall table, I would run the for-looop to get that talll table. And then I run a separate for-loop to sqlwrite( ) the table rows in chunks.
approach III.) I had also tried embeding sqlwrite( ) to save result in each step in a for-loop bypassing the end-result tall table. For example, if you want to convert and merge many files into a huge table, you could do this way:
for file = 1:number of files
datarows = convert the file( );
make the data rows into a table T.
sqlwrite(conn, 'SQLtablename', T)
end
------
For the moment, I use approach II, because it allows me to use parfor, which is much faster:
parfor i=1:number of files
datarows ....
T = vertcat(datarows) made into a table
end
Next, I would run a separate for-loop for saving to sql.
for row=1:rows
got cor = chunks of rows
sqlwrite(conn, 'SQLtablename', cor)
end
---------
Those are just my personal experiences learned by strugglings with buntch of sql stuff. Hope this might be useful for you.

Products

Community Treasure Hunt

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

Start Hunting!