queryMySQL

Platform independent MySQL database solution with blob support.

Description

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.

Acknowledgements

Access A My Sql Database inspired this file.

• Required Products: MATLAB
• MATLAB release: MATLAB 7.13 (R2011b)
• Other requirements: Requires MATLAB >= 2008a. Note: could be refactored to support MATLAB < 2008a. Does not require the Mathworks' database toolbox.

[Jonathan Karr]

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?

Best
Jonathan

[Carlos]

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
destructor:
Attempt to reference field of non-structure array.
??? Error using ==> MySQLDatabase>MySQLDatabase.open 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(Connection.java:2268)
at com.mysql.jdbc.Connection.initializePropsFromServer(Connection.java:3748)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2585)
at com.mysql.jdbc.Connection.<init>(Connection.java:1485)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.mathworks.jmi.AWTUtilities$Invoker$3.runWithOutput(AWTUtilities.java:443)
at com.mathworks.jmi.AWTUtilities$Invoker$2.watchedRun(AWTUtilities.java:397)
at com.mathworks.jmi.AWTUtilities$WatchedRunnable.run(AWTUtilities.java:360)
at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:209)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:708)
at java.awt.EventQueue.access$400(EventQueue.java:82)
at java.awt.EventQueue$2.run(EventQueue.java:669)
at java.awt.EventQueue$2.run(EventQueue.java:667)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:678)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:296)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:211)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:201)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:196)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:188)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)

Error in ==> MySQLDatabase>MySQLDatabase.MySQLDatabase at 72
this.open();

[Jonathan Karr]

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.

Best.
Jonathan

[AKHMAD FAUZI]

[HELP ME]

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: [e-mail]

Thanks Verry Much for your help

[Joshua]

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'));
exportMysqlData(db,host);

[Joshua]

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.

[Jonathan Karr]

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.

Best.
Jonathan

[Jonathan Karr]

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.

Best.
Jonathan

[George]

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('covertlab.stanford.edu', '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
failure

Last packet sent to the server was 0 ms ago.

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

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

...
"

[Agustin]

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.

Regards.

[Jonathan Karr]

Hi Agustin,

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

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

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.

Best.
- Jonathan

[Agustin]

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?

Thanks

[Jonathan Karr]

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,
PRIMARY KEY (`id`)
) 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)];
end
sql = sql(1:end-2);

db = MySQLDatabase('covertlab.stanford.edu', 'test', 'test', 'test');
db.prepareStatement(sql);
db.query();

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

db.close();

[Jonathan Karr]

Hi Agustin,

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

Best.
- Jonathan

[Agustin]

 

[Agustin]

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>SQLDatabase.open 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),'`');
db.prepareStatement(char(st3(1)));
fieldsqacc = db.query();
assert(~isequal(-1, fieldsqacc));
"

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

"db = MySQLDatabase(...
db.close();"

Do you know where is the problem?

Thanks in advance!

[Benedict]

 

[Jonathan Karr]

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

[Jonathan Karr]

Matteo,

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

[Matteo Broggi]

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.

Best,

Matteo

function open(this)
try
properties = java.util.Properties();
properties.setProperty('user',this.userName);
properties.setProperty('password',this.password);
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)));
end
end

[Pierre]

 

[Jonathan Karr]

Samuel and/or Agustin,

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

Best.
- Jonathan

[Jonathan Karr]

Samuel and/or Agustin,

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

Best.
- Jonathan

[Samuel Hurley]

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.

[Jonathan Karr]

Hi Agustin,

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

Best.
- Jonathan

[Agustin]

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

[Agustin]

 

[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

[Jonathan Karr]

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.

[Nicolas]

yep, matlab must be at least 7.12

[Florian Koehler]

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,
Florian

[Florian Koehler]

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,
cheers,
Florian

[Jonathan Karr]

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 (http://www.mysql.com/products/workbench/). It sounded like you might have a permissions or firewall issue.

Best of luck.
- Jonathan

[Florian Koehler]

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!!!
Cheers,
Florian

[Jonathan Karr]

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.

Best.
- Jonathan

[Florian Koehler]

 

[Florian Koehler]

Hello,
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!
Thanks,
Florian

[Manoj Gudi]

@Jon
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-
________________________________
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

Regards
Manoj G

[Jonathan Karr]

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
db.prepareStatement('<SQL>');
[result, colNames] = db.query();

6. Cleanup
db.close();

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

Best.
- Jonathan

[Manoj Gudi]

Hello,
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]

 

[Syed]

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

Thanks
Sid

[Eric Patterson]

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.