File Exchange

image thumbnail


version (672 KB) by Jonathan Karr
Platform independent MySQL database solution with blob support.


Updated 22 Jun 2012

View License

Platform independent MySQL database solution with blob support.

Only requires MATLAB >= 2008a (to support classes). Note: the code could be refactored to work with MATLAB < 2008a. Doesn't require any MathWorks toolboxes.

Comments and Ratings (51)

I think florianisopp meant:
%absByteArray = padarray(absByteArray, 8 - numel(absByteArray), 0, 'pre')';
padArr = zeros(1,(8 - numel(absByteArray)));
absByteArray = cat(2, padArr, absByteArray);

I am very happy with your solution, but perhaps you want to supersede the padarray() function call in MySQLDatabase.m cause one don't want to license the Image Processing Toolbox.
%absByteArray = padarray(absByteArray, 8 - numel(absByteArray), 0, 'pre')';
padArr = zeros(1,(8 - numel(absByteArray)));
absByteArray = cat(2, absByteArray, padArr);


Hi again,

I find my problem with '\n', it's just that Excel generate some CSV file with FIELDS TERMINATED BY '\r\n' and not just '\n'!

Sorry about that !


Nice work here !
Only issue I have with these files is to load a CSV file into a database with lines terminated by '\n' (example : LOAD DATA INFILE 'ExampleFile.csv' INTO TABLE example FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n'.
It looks like there are some problems to find this line return. Indeed the same request works on mysql, and the same request with lines terminated by '!' (for example) works with your package.
I've try to fix this problem but without succes for now.

Anyway thanks for this package !


Hi Jonathan- thanks for the useful utility. I've noticed that with these tools, retrieving data from a database takes significantly longer than writing data, even if you try to grab large amounts at once (such as selecting everything in an entire table, which I would think is the most efficent). Running a Matlab profile on the code, it seems that in the function MySQLDatabase.m, line 190 is taking most of the time:
fieldName = this.getValidFieldName(char(metaData.getColumnLabel(i)));

Have you experienced this? Any thoughts on how to speed it up?

files which are not part of clean matlab installation are missing:


You can find them over the web.

Hi Carlos,

This is an error with the MySQL Java driver.

Can you share the error? Without seeing the error its hard to say what the problem is.



Hi Jonathan,

Thank you for your reply. The error is in:

Error in ==> MySQLDatabase> at 409
this.dbConn = driver.connect(url, properties);

Let me know how I can go about fixing this. It seems that it is the Java version used. After I updated my Java, this error takes place.

Many thanks.

Hi Carlos,

I've never seen this error before.

First, comment out the try/catch block on lines 402, 410-412. Which line throws the error?



This has been working great on my computer. However, on a newer Mac mini, it gives the following error (I substituted the server details for dummies).

Any ideas?

>> addpath(genpath('MatlabQueryMySQL'))
>> javaaddpath(which('mysql-connector-java-5.1.6-bin.jar'));
import edu.stanford.covert.db.MySQLDatabase;
>> db = MySQLDatabase('server:port', 'dbname', username, password)
Warning: The following error was caught while executing 'edu.stanford.covert.db.MySQLDatabase' class
Attempt to reference field of non-structure array.
??? Error using ==> MySQLDatabase> at 411
Java exception occurred:
java.sql.SQLException: Unknown initial character set index '192' received from server. Initial client
character set can be forced via the 'characterEncoding' property.
at com.mysql.jdbc.Connection.configureClientCharacterSet(
at com.mysql.jdbc.Connection.initializePropsFromServer(
at com.mysql.jdbc.Connection.createNewIO(
at com.mysql.jdbc.Connection.<init>(
at com.mysql.jdbc.NonRegisteringDriver.connect(
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(
at sun.reflect.DelegatingMethodAccessorImpl.invoke(
at java.lang.reflect.Method.invoke(
at com.mathworks.jmi.AWTUtilities$Invoker$3.runWithOutput(
at com.mathworks.jmi.AWTUtilities$Invoker$2.watchedRun(
at com.mathworks.jmi.AWTUtilities$
at java.awt.event.InvocationEvent.dispatch(
at java.awt.EventQueue.dispatchEventImpl(
at java.awt.EventQueue.access$400(
at java.awt.EventQueue$
at java.awt.EventQueue$
at Method)
at java.awt.EventQueue.dispatchEvent(
at java.awt.EventDispatchThread.pumpOneEventForFilters(
at java.awt.EventDispatchThread.pumpEventsForFilter(
at java.awt.EventDispatchThread.pumpEventsForHierarchy(
at java.awt.EventDispatchThread.pumpEvents(
at java.awt.EventDispatchThread.pumpEvents(

Error in ==> MySQLDatabase>MySQLDatabase.MySQLDatabase at 72;

Hi Akhmad,

For security reasons we had to lockup our MySQL server, so unfortunately you can't use that test database anymore. The schema is described in test.sql. You can set this up on your own machine. See the examples on line 37-53 for how to work with blobs.



How i can save my image from Matlab to MySQL database in Type Data BLOB??

Please give me some tutorial with this Code in here,

my email:

Thanks Verry Much for your help


Edit: as a workaround, I nested the function call and added the path before calling the function. For example:

function dataExporter(db,host)
addpath(fullfile(pwd, 'src'));


Works great but I'm having a similar problem. I have to manually add the "src" directory to get it to work. It hasn't been a problem but now that I'm trying to execute it from a command line, I need to modify the path within the function.

I think it may be a problem with MATLAB. It throws a "syntax error" for the import command before running the code, assuming that the path is static.

To see what I'm talking about, try to enable a breakpoint while "src" directory is not in the path.

Hi George,

Unfortunately our server was attacked recently and I had to lock it down. This means the examples won't work anymore. You'll need to run them on your own MySQL server. The necessary schema is in test.sql.


Hi George,

I should also add that you are correct that MATLAB requires that classes be imported after addpath statements are executed. MATLAB will complain if you run the commands together until you've set the path. I usually work around this by setting the MATLAB path at the beginning of the session and then never setting the path inside any other function.



This fails for me.
Running Matlab R2012a, Win64.
When I run the "queryExample.m" code, it returns error:

>> queryExample
"Error: File: queryExample.m Line: 6 Column: 8
Arguments to IMPORT must either end with ".*"
or else specify a fully qualified class name:
"edu.stanford.covert.db.MySQLDatabase" fails this test."

If I run each of the command separately, e.g.
>> addpath(fullfile(pwd, 'src'))
>> javaaddpath('lib/mysql-connector-java-5.1.6/mysql-connector-java-5.1.6-bin.jar')
>> import edu.stanford.covert.db.MySQLDatabase

I do not get the error above. However, then the code fails on the subsequent step:
>> db = MySQLDatabase('', 'test', 'test', 'test')
Warning: The following error was caught while executing
'edu.stanford.covert.db.MySQLDatabase' class destructor:
Attempt to reference field of non-structure array.
Error using edu.stanford.covert.db.MySQLDatabase/open (line 411)
Java exception occurred:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link

Last packet sent to the server was 0 ms ago.

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)



Hi Jonathan,

Finally, I have fixed the issue. I's was my fault.

I was doing the import of the java class in each iteration. And the java memory in Matlab was running out.

Now, It's running fine.

Many thanks. It's a great job.


Hi Agustin,

I just tried this example where I repeatedly open and close connections without any issues.

for i = 1:1000
db = MySQLDatabase('', 'test', 'test', 'test');
db.prepareStatement('select * from tinytest where id=1');
result = db.query();
assert(~isequal(-1, result));

Are you working with the most recent version of the code (downloaded after June 22)? I recently changed the way database connections are created based on a suggestion from Matteo. Perhaps you won't have the issue with the newer version.

I'm not sure what the issue is. What happens if you keep the connection open rather than reopening it for each query? I can try to help if you can provide a more complete example of the problem.

- Jonathan


Hi Jonathan,

It's no problem of machine memory. We have enough.

I have a database with data from hundreds of users and I do 27 queries on each of them. All the queries are 'Select'.

For each user, I open and close the connection to the database.

When I am in the 25th user, I get this error and Matlab crashes. My Matlab version is 7.11 (R2010b).

Do you have any idea?


Hi Agustin,

I just successfully tried this example with a relatively table result set containing 10,000 rows. Are you working with a very large table or on a machine without much memory?

- Jonathan

CREATE TABLE `tinytest` (
`id` int(11) NOT NULL,
`datatiny` tinyint(4) default NULL,
`datasmall` smallint(6) default NULL,
`datamedium` mediumint(9) default NULL,
`dataint` int(11) default NULL,
`databigint` bigint(20) default NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

sql = 'INSERT INTO `test`.`tinytest` (`id`, `datatiny`, `datasmall`, `datamedium`, `dataint`, `databigint`) VALUES ';
for i = 2:10000
sql = [sql sprintf('(%d, %d, %d, %d, %d, %d), ', i, i, i, i, i, i)];
sql = sql(1:end-2);

db = MySQLDatabase('', 'test', 'test', 'test');

for i = 1:10
db.prepareStatement('select * from tinytest');
result = db.query();
assert(~isequal(-1, result));


Hi Agustin,

I can try to help. Can you please clarify your example. What should the result set look like?

- Jonathan



Hi Jonathan

Thanks for your help. The problem with the tinyint issue is fixed.

But, now, there is a problem of lack of memory.

After several queries I get the following error:

"Error using ==> MySQLDatabase> at...
Java exception occurred:
java.sql.SQLException: java.lang.OutOfMemoryError: PermGen space..."

This is an example of the queries:

"st3= strcat('SELECT * FROM',' `',tablesq_sm(14),'`');
fieldsqacc = db.query();
assert(~isequal(-1, fieldsqacc));

Every time that I change the subject, I close and reopen the database.

"db = MySQLDatabase(...

Do you know where is the problem?

Thanks in advance!


Matteo post deserves a shoutout. I originally tried to create a connection without a java wrapper, but couldn't find a way to achieve this. Great work Matteo for figuring this out and sharing it.
- Jonathan


Thanks for the suggestion. I'll incorporate this into the next release.
- Jonathan

Great toolbox!

I have a suggestion for a possible modification.
I managed to avoid the use of the external java wrapper and use only Matlab calls. I modified the private method open() to do this. In this way, you need to set only the mysql jdbc driver in the classpath.



function open(this)
properties = java.util.Properties();
driver = javaObjectEDT('com.mysql.jdbc.Driver');
url = ['jdbc:mysql://' this.hostName '/' this.schema];
this.dbConn = driver.connect(url,properties);
catch exception
throw(MException('MySQLDatabase:mysqlError', char(exception.message)));


Samuel and/or Agustin,

I just posted an update which should fix the tinyint issue. Let me know if you have other datatype issues.

- Jonathan

Samuel and/or Agustin,

I can try to help you if you can provide an example of your SQL code.

- Jonathan

This is excellent! I spent all night trying to get Oracle's MySQL Connector/J working with no luck, and this package took literally 5 minutes to set up!

I also had a problem, like Agustin, with trying to query single-character fields from a database (getTiny). I fortunately did not need to query that particular table for my purposes, so everything is great for me.

Hi Agustin,

I can try to help. Can you provide more information about the problem? An example query would be helpful.

- Jonathan


Hi Jonathan,

I had a problem trying to import data from a table. The data is type number with a single character. The problem is with the method getTiny for com.mysql.jdbc.JDBC4ResultSet


Joe Kuehl

Jonathan or whom ever, I am very new to mysql and working with databases. Can anyone please provide an alternative example of querymysql which accesses some public data base? It would be a great benefit to me. Thank you

The library requires MATLAB R2009B (7.11) or newer. MATLAB 2008a-2009a can be used if you replace the "~" on lines 379 and 391 of MySQLDatabase.m with "junk". To use the code with yet older versions of MATLAB you would need to restructure the code to not use modern style classes.


yep, matlab must be at least 7.12

An addition to my last comment. I think I know better where the error has to be:
queryMySQl works for me with Matlab 7.13
but not with Matlab 7.05. I think it depends on the import function which is different in Matlab 7.05.
Does anyone know how to use queryMySQL with Matlab 7.05? How does the import ... command look like?
A solution would be great.
Thank you,

Hi Jonathan,
thank you very much for your help. Everything is working now on my pc. Now I want to install this function also on another pc. Unfortunatelly there I have another problem:
When I finally do:
>> import edu.stanford.covert.db.MySQLDatabase;
I get an error:
??? Error using ==> import
Arguments to IMPORT must either end with '.*' or else specify
a fully qualified class name: edu.stanford.covert.db.MySQLDatabase

I did everything in the same way as I did it on the other pc. But somehow this error occurs. It would be great if you could help me once more.

Thank you very much for everything,

Hi Florian,

I'm glad you were able to get MySQL to work. If you weren't I was going to suggest first making sure you can connect with a program like MySQL workbench ( It sounded like you might have a permissions or firewall issue.

Best of luck.
- Jonathan

Hi Jonathan,
sorry for my last comment! It worked! :-)
When I repeated exactly what I wrote it worked! Yipee!
Thank you very much for your help and for your program!!!

Hi Florian,

Its definitely possible to connect to another machine. Just replace the first argument to MySQLDatabase with the IP address or hostname of your server. On Windows you can get a server's ip address by running the command ipconfig. On Linux you can get your IP address by running ifconfig.

- Jonathan

thank you very much for this very nice program! It's great.

Nevertheless I have a question:
Is it possible (with this program) to connect to a MySQL database which is located on another pc (it's not a special server but a common pc)?
If yes, what do I have to write for the hostname in:
db = MySQLDatabase('???','test_schema','root','rootPasswordBla');

Would be great if you could help me!

Manoj Gudi

Thanks for the prompt and detailed reply,
I did this exactly in my previous attempt too but I am still getting this error in command window when I run queryExample-

Warning: Directory access failure: C:\Users\manoj\C:\Users\manoj\Desktop\queryMySQL\src.

> In path at 110

In addpath at 87

In queryExample at 2

??? Error using ==> chkprops at 18

Invalid database property: ' prepareStatement '.

Error in ==> database.get at 36

p = chkprops(c,p,prps);

Error in ==> database.subsref at 17

B = get(A,S(idx).subs);

Error in ==> queryExample at 38

db.prepareStatement('UPDATE test SET DATA = "{F}" WHERE id = "{Si}"', fname, 10001);


I am using WAMP for MYSQL; my problems may be silly, but I'll be glad if you can solve them

Manoj G

Hi Manoj,

Here are instructions on how to use this library. I've tested these with MATLAB 2010b on Windows and linux.

1. Unzip the files and save them to your computer eg, 'C:\Users\jkarr\Documents\queryMySQL'

2. Open MATLAB, add queryMySQL to MATLAB path
>> addpath('C:\Users\jkarr\Documents\queryMySQL\src');
>> javaaddpath('C:\Users\jkarr\Documents\queryMySQL\src\+edu\+stanford\+covert\+db');
>> javaaddpath('C:\Users\jkarr\Documents\queryMySQL\lib\mysql-connector-java-5.1.6\mysql-connector-java-5.1.6-bin.jar');

3. Import query class
>> import edu.stanford.covert.db.MySQLDatabase;

4. Create database connection
>> db = MySQLDatabase('<hostname>', '<schema>', '<username>', '<password>');

5. Run Queries
[result, colNames] = db.query();

6. Cleanup

queryExample.m outlines all of these steps and provides examples of how to run queries.

- Jonathan

Manoj Gudi

I m quite new to MATLAB and I want to use this library,

Please can somebody explain in detail how to setup/install this, (I am using MATLAB 7.11 2010b with Windows 7)

Thanks in advance

Alex Harper


Hi Jonathan,

I ran this on my Mac and I am getting error saying:

??? Reference to non-existent element of a cell array.
Error in ==> queryMySQL at 98
switch columTypes{i}{1}

any idea what could than be.

Another thing is that it is connecting to Database and giving me the right column names for the table but it is giving error on this particular line. i tried to fix it but not working so I though you might know the problem


Much easier to get set up than mym.m. It worked well for my queries that returned few results, but it did not scale well for larger return sets. For example, one query I tried runs in 1.5 seconds in the MySQL browser, but using queryMySQL.m it took over 2 minutes. As many of my queries return millions of results, I'll have to stick with mym.m.

I assume there's some java overhead somewhere, but that's just naive speculation.


Adding email for contributor Matteo Broggi

1) Now returned bigint as (u)int64 rather than double -- thanks Pierre Martineau for the suggestion.
2) Now creating database connection with using an external java wrapper -- thanks Matteo Broggi.

Fixed support for date, time, datetime, and tiny data types.

Adding definitions of example tables and stored procedures. Properly casting returned blobs -- thanks to Jonathon parish. Added support for column names with non-alphanumeric characters -- thanks to Hachi Manzur.

Now uses MATLAB classes

Corrected javaaddpath for MySQLLoader class in example usage in queryMySQL.m

Added tags.

MATLAB Release Compatibility
Created with R2011b
Compatible with any release
Platform Compatibility
Windows macOS Linux

Inspired by: Access a MySQL database