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

The author wishes to acknowledge the following in the creation of this submission:
Access a MySQL database

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

[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.

[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

[Alex Harper]

 

[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

[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]

@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

[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

[Florian Koehler]

 

[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]

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,

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

[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

[Nicolas]

yep, matlab must be at least 7.12

[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.

[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

[Agustin]

 

[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

[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

[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]

Samuel and/or Agustin,

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

Best.
- Jonathan

[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