MySQL Database Connector

Connect to MySQL database.

Description

This C++ Mex-function lets you connect to a MySQL database server. The results of SELECT queries are converted to MATLAB native datatypes. Data can be inserted into the database (slowly) by construcing the INSERT string in MATLAB. It can connect to several servers simultaneously (or multiple connections to one server). See mysql.m for usage instructions. Must be compiled before use; brief instructions are in the C++ file or try http://mmf.utoronto.ca/resrchres/mysql/

• Acknowledgements: This submission has inspired the following:; A Powerful Sidekick: Using MySQL for High-Volume Data Manipulation in Matlab, Access a MySQL database, Myblob
• MATLAB release: MATLAB 7.0.1 (R14SP1)
• Other requirements: Need the MySQL client libraries and a standard C++ compiler (MSVC++ or GCC). Tested on Windows and Linux but should work on other platforms.

[a user]

Works very well. Mainly useful for retrieving data from an existing MySQL database. Inserting MATLAB created data into mySQL set is somewhat harder to program and can be very slow. MySQL experts would probably want to create text files and load thes into mySQL in the normal mySQL manner.

[a user]

work very well, easy to use. For a version supporting Matlab object as well, you can try mYm (http://itswww.epfl.ch/~maret/software.php).

[Adam A]

So simple to use. You need the MySQL 'include' files to install tho.

[Antonio Sabino]

Hi, I follow the instructions that be in the installer file, and I have the error:

">>mex mysql.cpp
lcc preprocessor error: mysql.cpp:10 Could not find include file <mysql.h>
Error mysql.cpp: 57 illegal initialization for parameter `n'
Error mysql.cpp: 98 syntax error; found `t' expecting `)'
Error mysql.cpp: 98 skipping `t'
Error mysql.cpp: 100 undeclared identifier `t'
Error mysql.cpp: 103 undeclared identifier `FIELD_TYPE_DECIMAL'
Error mysql.cpp: 103 case label must be a constant integer expression
Error mysql.cpp: 104 undeclared identifier `FIELD_TYPE_TINY'
Error mysql.cpp: 104 case label must be a constant integer expression
Error mysql.cpp: 105 undeclared identifier `FIELD_TYPE_SHORT'
Error mysql.cpp: 105 case label must be a constant integer expression
Error mysql.cpp: 106 undeclared identifier `FIELD_TYPE_LONG'
Error mysql.cpp: 106 case label must be a constant integer expression
Error mysql.cpp: 107 undeclared identifier `FIELD_TYPE_FLOAT'
Error mysql.cpp: 107 case label must be a constant integer expression
Error mysql.cpp: 108 undeclared identifier `FIELD_TYPE_DOUBLE'
Error mysql.cpp: 108 case label must be a constant integer expression
Error mysql.cpp: 109 undeclared identifier `FIELD_TYPE_NULL'
Error mysql.cpp: 109 case label must be a constant integer expression
Error mysql.cpp: 110 undeclared identifier `FIELD_TYPE_LONGLONG'
Error mysql.cpp: 110 too many errors
 
  C:\MATLAB7\BIN\WIN32\MEX.PL: Error: Compile of 'mysql.cpp' failed.
 
??? Error using ==> mex
Unable to complete successfully

Could you help me?. Please!!!!!!!!.

[Abhishek Tomar]

Through this method we can simply connect them. I need mysql.h header file. Please give me the address for download the file or send me the file. Thanks

[Paul Probert]

Very nice and handy program. Much lighter and easier to maintain than going through ODBC, especially when you've got 20 networked users who can't install stuff on their own. To make this even easier, I managed to statically link with mysqlclient.lib rather than requiring libmysql.dll to be in the windows search path. Here's my notes:

10/3/06 successfully built mysql.dll for matlab statically linked to mysqlclient, to
avoid having to have all users install libmysql.dll. Using mysql 4.1.21, matlab 6.5.

edit mexopts.bat:

compiler optimflags should be /MT, not /MD
linker linkflags should have /NODEFAULTLIB:MSVCRT, also ws2_32.lib advapi32.lib

build command (in matlab):

mex -I"c:\program files\mysql\mysql server 4.1\include" -DWIN32 -D_WINDOWS -D__WIN__ mysql.cpp ...
"c:\program files\mysql\mysql server 4.1\lib\opt\zlib.lib" ...
"d:\websave\mysql\src\mysql-4.1.21\lib_release\mysqlclient.lib"

(not sure if the __WIN__ and _WINDOWS are needed)

Where in d:\websave\... I had downloaded the mysql windows source distribution and
rebuilt the mysqlclient.lib using VC6. In the source tree client directory there is a nice mysqlclient.dsw
workspace that you can open and build. If I didn't build my own mysqlclient then I'd
get link errors of undefined externals like ftol2 and aulldvrm that I couldn't find anywhere.

[Arva Hsu]

 

[Carl Fischer]

Line 769 (ish) should read:
   else if (f[j].type==FIELD_TYPE_BLOB && f[j].charsetnr == 63)
rather than
   else if (f[j].type==FIELD_TYPE_BLOB)
So that TEXT columns are formatted as text, not arrays of uint8. Also, comment out lines:
                  const uLongf max_len = compressBound(plen[i]);
                    if (max_len>cmp_buf_len){
                    pcmp = (Bytef*)mxRealloc(pcmp, max_len);
                    cmp_buf_len = max_len;
                    }

To get it to compile on Linux x64 without the whole libz fiasco. Not sure if this breaks blob insertions, but I don't think it will.

[R.K. R.K.]

Everything works great under mac osx, win32 and linux.
When I try to compile the mysql.cpp under win 64bit it fails.
I have just replaced -DWIN32 with -DWIN64 flag when trying to build it but I get many errors.

Can someone please help?

[Frank Hutter]

Since some people had problems with the header file mysql.h:
you need to download MySQL, use the custom installation, and tell it to install lib and include files; then use the appropriate directory in the mex command (for me it's mex -I"C:\Program Files\MySQL\MySQL Server 4.1\include" ...)

Also, compilation didn't work for me with the Matlab lcc compiler, but with the following one (configurable with mex -setup)
Microsoft Visual C/C++ version 7.1 in C:\Program Files\Microsoft Visual Studio .NET 2003

[Happy User]

Very useful program, Work with WindowsXp and MySql 5.

[Confused More confused]

I have used this for a long time in Matlab 2006b. When upgrading to Matlab 2008, data from mysql is trunkated to integers!?!?

[Another Happy User]

After compiling this on OS X 10.5.4 (intel) against mysql 5.0.67 i86_64 libraries, running mysql('open', 'localhost','user_name', 'password') caused MATLAB to crash at first mysql_init(conn) call.

fixed problem by reinstalling mysql i386 version, then compiling using

mex -v -maci -I/usr/local/mysql/include -L/Applications/MATLAB_R2008a/bin/maci -lmysqlclient_r -L/usr/local/mysql/lib -lpthread mysql.cpp

Would have saved time if -lmysqlclient_r and -lpthread were mentioned in http://mmf.utoronto.ca/resrchres/mysql/

No problem with fractions using data type double.

[Adam Auton]

 

[koubaa kamal]

 

[Ryan murphy]

 

[Adam Auton]

This function is excellent... Easy to use and fast. My only gripe is that it doesn't work with the 64bit OS X version of MySQL. It compiles using the 64bit libraries, but crashes MATLAB when you use the 'open' command. As outlined in a previous comment, you need to uninstall the 64bit version of MySQL and replace it with the 32bit version.

[Markus Pylvanen]

I have found following problem with Matlab 7.8.0.347 (R2009a), 32-bit win32 and mysql-5.1.32.

For example when I type this command:

"clear A B; [A B] = mysql('SELECT 1, 2 FROM TableX;'); iscell(B)"

the result is true. I haven't found a reason why the output arguments of mysql-function are sometimes in a wrong format.

I have tried self-compiled version and compiled version which is found here.

[Rick Mediavilla]

I have also found a problem with incorrect types. Variables are inconsistently returned (commonly a string for a numeric) when queries include multiple columns. My workaround is to restructure query to return values for only one column. Alternatively, do a type check (isnumeric) and convert if necessary.

[Matt]

I setup Matlab to use the Microsoft Visual C++ 2008 Express compiler
    >> mex -setup

Then I compiled the file
    >> mex -DWIN32 -I'C:\Program Files\MySQL\MySQL Server 5.0\include' -L'C:\Program Files\MySQL\MySQL Server 5.0\lib\opt' -llibmysql mysql.cpp

Works great!!!

[Todd Welti]

In the description, it says you can insert data into the database by constructing the INSERT string, but in the m file, no mention of it...?!?

[Pete]

After much, much hair pulling, I got this working on my windows XP machine as follows:

First it wouldn't compile ussing lcc, and so I was getting the previously reported error message 'lcc preprocessor error: mysql.cpp:10 Could not find include file <mysql.h> '. To get round this I needed an alternative C/C++ compiler (cl.exe). As such I downloaded Microsoft VC2005 Express.

Then, after running mex -setup and changing the compiler to VC2005 Express it still wouldn't compile, complaining that cl.exe needed access to windows.h. Since I had the Microsoft PlatformSDK on my computer I followed the standard advice and: (i) created the directory "PlatformSDK" under "%ProgramFiles%\Microsoft Visual Studio 8\VC" (ii) copied the "bin", "include" and "lib" directory from the PSDK-installation into this directory.

Then I successfully compiled from matlab, using: mex -I'C:\Program Files\MySQL\MySQL Server 5.5\include' -DWIN32 mysql.cpp 'C:\Program Files\MySQL\MySQL Server 5.5\lib\opt\libmysql.lib'

Finally, when I tried to use the basic command "mysql('status')" it I was getting the error "??? Invalid MEX-file 'N:\Matlab Toolboxes\mym utilities\mysql.mexw32': The specified module could not be found." This was caused by an inability to access the file libmysql.dll. As such, I moved the file libmysql.dll from 'C:\Program Files\MySQL\MySQL Server 5.5\lib\opt' to 'C:\WINDOWS\system32' [this quite possibly wasn't the best thing to do, but I know no better]

It works, huzzah!

[albrecht]

very useful program! i'm running it on my mac (snow leopard) and with Matlab R2009b and it is very handy.

however, i have problems with special characters such as the french 'é' with the fancy slash on top (accent aigu); these letters are just skipped by the program when reading a text entry from the database.

anyone knows how to solve this issue? thanks in advance.

[CRP]

I have been able to compile this on mac 10.6.2 with the following command line:

/Applications/MATLAB_R2009b.app/bin/mex -v -maci64 -I../mysql-connector-c-6.0.2-osx10.5-x86-64bit/include/ -I/Applications/MATLAB_R2009b.app/extern/include/ -L/Applications/MATLAB_R2009b.app/bin/maci64/ -L../mysql-connector-c-6.0.2-osx10.5-x86-64bit/lib/ -lz -lmysqlclient_r -lSystemStubs -lpthread mym.cpp

Unfortuntely, upon running mym from matlab I get:

>> mym
??? Invalid MEX-file '/Users/cp/Downloads/mym_src_v1.0.9/mym.mexmaci64': dlopen(/Users/cp/Downloads/mym_src_v1.0.9/mym.mexmaci64, 1):
Library not loaded: libmysql.16.dylib
  Referenced from: /Users/cp/Downloads/mym_src_v1.0.9/mym.mexmaci64
  Reason: image not found.

I think this is due to the whole @loader mechanism in OS X. I think this could be solved by linking the libraries statically. How do I do that with mex?

Thanks
CRP

[Can Baran]

So, this function written is pretty useful (almost life saving). I have been using it since August 2009.
One comment.
Don't start your function with clear all. why?
so, let s say you opened a connection with mysql('open',....)
and u are debugging with the matlab debugger. you found the mistake and you quit debugging without closing the connection.
then if u start your script with clear all, you think you closed the connection but actually that is not the case! Start with mysql('closeall') and clear variables commands. that will ensure that you close all of your connections before your script/program runs and the memory is cleared.

[Omar]

 

[Omar]

Very useful. Thanks!

[Yanal WAZAEFI]

 

[Felix Lam]

Finally upgraded to Mac OS X 10.6.4 along with MySQL 5.1.48 and MATLAB R2010a, everything running in Intel 64 bit. I had no problems compiling Almgren's original C++ source code (have not tried the more recent mYm from SourceForge):

>> mex -v -maci64 -I/usr/local/mysql/include -L/usr/local/mysql/lib -lmysqlclient_r -lSystemStubs -lpthread mysql.cpp

and everything tested fine afterwards from within MATLAB: was able to connect to MySQL, submit queries, and retrieve data. Perhaps the problems people were having was with not matching the bit lengths of MATLAB and MySQL? (e.g., 64-bit MySQL with 32-bit MATLAB, or vice versa).

[Robin den Dekker]

Very good!
I had some problems with compiling and stuff but in the end it is working!
Don't forget to run the install and install the Include files aswell.
I had to download MS visual c++ 2008 express because lcc wasn't working out.
Read the website carefully and it will work out

[Oliver Ross]

Since upgrading to Win 7 and 64 bit the old DLL no longer works and I keep getting the "??? Attempt to execute SCRIPT mysql as a function:" error. Could someone maybe post a compiled version as not everyone has a compiler. I would need the dll for Matlab R2010b (64 bit) and Mysql 5.1 (64bit) on a Windows 7 (64 bit machine). Thanks.

[Nicolas]

Same here:
Need .dll for Matlab R2010b (64 bit) and Mysql 5.1 (64bit) running Win 7.

currently i'm receiving the following error:
************************************************************
>> mex -I"C:\Program Files (x86)\MySQL\MySQL Server 5.1\include" -DWIN32 mysql.cpp "C:\Program Files (x86)\MySQL\MySQL Server 5.1\lib\opt\libmySQL.lib"
   Bibliothek "C:\Users\alln\AppData\Local\Temp\mex_ZevDM0\templib.x" und Objekt "C:\Users\alln\AppData\Local\Temp\mex_ZevDM0\templib.exp" werden erstellt.
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_fetch_row" in Funktion ""void __cdecl fancyprint(struct st_mysql_res *)" (?fancyprint@@YAXPEAUst_mysql_res@@@Z)".
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_data_seek" in Funktion ""void __cdecl fancyprint(struct st_mysql_res *)" (?fancyprint@@YAXPEAUst_mysql_res@@@Z)".
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_fetch_fields" in Funktion ""void __cdecl fancyprint(struct st_mysql_res *)" (?fancyprint@@YAXPEAUst_mysql_res@@@Z)".
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_num_fields" in Funktion ""void __cdecl fancyprint(struct st_mysql_res *)" (?fancyprint@@YAXPEAUst_mysql_res@@@Z)".
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_num_rows" in Funktion ""void __cdecl fancyprint(struct st_mysql_res *)" (?fancyprint@@YAXPEAUst_mysql_res@@@Z)".
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_select_db" in Funktion "mexFunction".
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_free_result" in Funktion "mexFunction".
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_affected_rows" in Funktion "mexFunction".
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_field_count" in Funktion "mexFunction".
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_store_result" in Funktion "mexFunction".
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_query" in Funktion "mexFunction".
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_real_escape_string" in Funktion "mexFunction".
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_get_host_info" in Funktion "mexFunction".
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_get_server_info" in Funktion "mexFunction".
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_ping" in Funktion "mexFunction".
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_stat" in Funktion "mexFunction".
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_error" in Funktion "mexFunction".
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_real_connect" in Funktion "mexFunction".
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_init" in Funktion "mexFunction".
mysql.obj : error LNK2019: Verweis auf nicht aufgelöstes externes Symbol "mysql_close" in Funktion "mexFunction".
mysql.mexw64 : fatal error LNK1120: 20 nicht aufgelöste externe Verweise.
 
  C:\PROGRA~1\MATLAB\BIN\MEX.PL: Error: Link of 'mysql.mexw64' failed.
 
??? Error using ==> mex at 208
Unable to complete successfully.
************************************************************

any help would be appreciated!
THX!

[Nicolas]

sorry for the previous post.
problem was that i installed a 32 bit version of mysql.
now (with 64 bit) it works fine.

however a .dll has not been created. instead a file mysql.mexw64 was created by using
mex -I"C:\Program Files\MySQL\MySQL Server 5.1\include" -DWIN32 mysql.cpp "C:\Program Files\MySQL\MySQL Server 5.1\lib\opt\libmySQL.lib"

in case anyone is insterested:
MySQL and Matlab for Win7 with Matlab 7.11.0 (R2010b) [64 bit] and Mysql 5.1.53 [64bit]
http://www.megaupload.com/?d=UGNG4F13

[Nicolas]

Hi Alex Harper
I have no idea how respond to your PM, so i'm writing it here:

#quote Alex Harper:
Hi, did you manage to get Matlab and mySQL talking using the connector? I have a similar set up to you but I'm unsure what to do with the mysql.mexw64 file which you uploaded. Any advice would be much appreciated!
#end quote

The mysql.mexw64 file is the .dll. this seams to be a more or less new 'invention' of matlab.
compiled for a 32bit-system the ending would be .mexw32.
Acording to Robert Almgren's page all you have to do is:

#quote Robert Almgren
mysql.dll
This is compiled against Matlab Version 4 (Release 14) and MySQL 4.1.11, on x86 using Microsoft Visual C++ 2003. Put this and mysql.m into $MATLAB\toolbox\local, or keep them in your working directory. ($MATLAB means Matlab's home directory, usually something like C:\Program Files\MATLAB704.)
#end quote

He simply hasn't updated his page to the new matlab standards (.mexw32/.mexw64 instead of .dll).

I simply added the path containing the files in my code by:
   addpath(path);
and it worked.

I hope i could help.
Greetings!

[Antonio]

Hi All,

I have been having a problem to run the file in ubuntu x86_64.

I downloaded the libraries and I compile it with the following command:

mex -v -g -Ipath/include -Lpath/lib -lmysqlclient mysql.cpp

It works and creates the file: mysql.mexa64.

However, when I try to run the file, I get the following error:

??? Invalid MEX-file 'mysql.mexa64': libmysql.so.16: cannot
open shared object file: No such file or directory.

My linux version is:
Linux 2.6.35-25-generic #44-Ubuntu SMP Fri Jan 21 17:40:44 UTC 2011 x86_64 GNU/Linux
and my matlab version is:
MATLAB Version 7.9.0.529 (R2009b)

Can anyone help me?

[Antonio]

I got it working, but forgot to post in here.

It was simpler than I thought.

I just had to type on the terminal

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:my_path

where my_path is the path for mysql library.

[Avtandil Tkabladze]

Please help.
I have MatLab 2008a (it is supposed to be 32 bit) on Windows 7 64 bit machine. I installed 32 bit MySQL.
I have Visual Studio 2008.

Old mysql.dll library does not work.
mex gives compilation error
Error using ==>mex at 2007.

Can somebody explain me where is the problem?
Did you try to install 32 bit MatLab and MySQl on 64 bit platfform succesfully?

[Avtandil Tkabladze]

 O.K. here is a my story. It can be useful
for somebody else.
I did everything as it is described above.
System, Windows 7, MatLAb2008a (64 bit), Mysql 5.5 (64bit).
I compiled the mex library. The output was libmyql.mexw64.
Matlab did not understand mysql command, tried to run mysql as a function.
Irenamed the library libmysql.mexw64 to mysql.mexw64 and everything works.

[Robert]

Everything works great. I was wondering if there is a way to avoid giving an output for each column of data. I have 137 columns and individually writing out [A(:,1),A(:,2),...,A(:,137)] seems pointless. Suggestions?

[yang yang]

Obviously this file works great and received excellent review. But I can't even get it work. I downloaded "all the files", including 'mysql.m' and 'mysql.cpp' and included them in the path. When I type in mysql('open',...,...,...), i got the message basically saying that this is a script instead a function. Can anyone give me a clue what is going on here?

[Kenji]

Thank you for great information anytime.
I wanna interact with MySQL database(MDB) from MATLAB.
Examples)
・Input or output data by accessing the MDB from MATLAB.

I have some question.

Are these thinking correct ?

Question.
1. What is function of 'mysql.m' and 'mysql.cpp' .
2. How to use these files.

About 1.
I guess that contents of these files are "how to access the MDB from Matlab".
・mysql.m shows how to describe source of Accessing the MDB.
・I couldn't understand function of mysql.cpp.

If my idea is incorrect, please tell me intuitively.

About 2.
・Only confirm syntax of source description using mysql.m.
    thus mysql.m is comment file.
・What is purpose of running mysql.cpp?

Please teach me correct opinion to my question.

[MATLAB:R2011b OS:Windows 7]

[Uwe Zimmermann]

yes, it is troublesome to get this working!

First of all, the external link is no longer valid. The correct link is now http://www.cims.nyu.edu/~almgren/mysql/ (why doesn't Mathworks provide a permanent webspace for such contributions?)

Secondly as pointed out by others, the package cannot be compiled using the Mathlab compiler!

Under Windows install the free MS Visual C++ Express from http://www.microsoft.com/visualstudio/en-us/products/2010-editions/express

You also need the MySQL connector library package (libmysql) from http://www.mysql.com/downloads/connector/

Then, finally you can compile the package and get it to run!!!

Uwe.

P.S.: I think there should be a trouble-free built-in access to MySQL and other databases in Matlab!

[Kenji]

Thank you for your advise.

I reffered to external link and other explanations and could connect MySQL in Matlab.

These works is really useful!