Code covered by the BSD License  

Highlights from
queryMySQL

4.58333

4.6 | 14 ratings Rate this file 53 Downloads (last 30 days) File Size: 672 KB File ID: #28237

queryMySQL

by

 

19 Jul 2010 (Updated )

Platform independent MySQL database solution with blob support.

| Watch this File

File Information
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.
Tags for This File   Please login to tag files.
Please login to add a comment or rating.
Comments and Ratings (49)
10 Jun 2014 Romain

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 !

10 Jun 2014 Romain

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 !

23 Oct 2013 John

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?
Thanks

12 Oct 2013 Hanan Shteingart

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

mkconstarray.m
padarray.m
checkstrs.m

You can find them over the web.

26 Jul 2013 Jonathan Karr

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.

Best.
Jonathan

26 Jul 2013 Carlos

Hi Jonathan,

Thank you for your reply. The error is in:

Error in ==> MySQLDatabase>MySQLDatabase.open 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.

09 May 2013 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

09 May 2013 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();

01 May 2013 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

27 Apr 2013 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: akhfauz.sisfo@gmail.com

Thanks Verry Much for your help

01 Dec 2012 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);

01 Dec 2012 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.

06 Nov 2012 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

06 Nov 2012 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

06 Nov 2012 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)

...
"

19 Jul 2012 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.

18 Jul 2012 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

18 Jul 2012 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

17 Jul 2012 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();

17 Jul 2012 Jonathan Karr

Hi Agustin,

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

Best.
- Jonathan

17 Jul 2012 Agustin  
17 Jul 2012 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!

28 Jun 2012 Benedict  
12 Jun 2012 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

12 Jun 2012 Jonathan Karr

Matteo,

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

12 Jun 2012 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

07 Jun 2012 Pierre  
30 May 2012 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

30 May 2012 Jonathan Karr

Samuel and/or Agustin,

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

Best.
- Jonathan

29 May 2012 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.

08 May 2012 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

08 May 2012 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

08 May 2012 Agustin  
27 Apr 2012 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

06 Mar 2012 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.

01 Feb 2012 Nicolas

yep, matlab must be at least 7.12

24 Jan 2012 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

23 Jan 2012 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

13 Jan 2012 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

13 Jan 2012 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

12 Jan 2012 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

12 Jan 2012 Florian Koehler  
12 Jan 2012 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

21 Aug 2011 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

20 Aug 2011 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

20 Aug 2011 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

09 Feb 2011 Alex Harper  
28 Sep 2010 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

23 Sep 2010 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.

Updates
19 Jul 2010

Added tags.

16 Aug 2010

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

25 May 2011

Now uses MATLAB classes

06 Jun 2011

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.

30 May 2012

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

12 Jun 2012

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.

22 Jun 2012

Adding email for contributor Matteo Broggi

Contact us