Storing a .mat file in mysql database

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 :(

Answer 1

[Darik]

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.

Answer 2

[Shashank Prasanna]

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:

http://www.mathworks.com/help/database/data-export-from-matlab.html

Take a look at some of the full example workflows to get an idea of how to insert the data into a table.

[Gurvinder]

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]

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.

Answer 3

[Simon]

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.