Connecting to a mySQL database with R on a Mac
I had some difficulty accessing a mySQL db using the RODBC package on my Mac – I’m documenting the steps to follow below:
1) Download the mySQL ODBC connector from http://dev.mysql.com/downloads/connector/odbc/ (the tarball is best)
2) Unpack the archive and copy libmyodbc5w.so to your preferred location (/usr/local/lib in my case)
3) Create ~/Library/ODBC if it doesn’t already exist
4) In the directory above create a file named odbc.ini with the parameters for your data source(s):
[ODBC Data Sources] TESTDB = MyODBC
[TESTDB] Driver = /usr/local/lib/libmyodbc5w.so DATABASE = your_db DESCRIPTION = your_description SERVER = your_server UID = your_username PASSWORD = your_password
5) In the same directory, create odbcinst.ini with parameters for your driver(s):
[ODBC Drivers] MyODBC = Installed /usr/local/lib/libmyodbc5w.so = Installed
[MyODBC] Driver = /usr/local/lib/libmyodbc5w.so Description = mySQL driver Threading = 0
[/usr/local/lib/libmyodbc5w.so] Driver = /usr/local/lib/libmyodbc5w.so Description = mySQL driver Threading = 0
6) Test that you can access your data source(s) using Mac OS X’s built-in command line tool iodbctest:
monolith:~ nicolas$ iodbctest iODBC Demonstration program This program shows an interactive SQL processor Driver Manager: 03.52.0607.1008
Enter ODBC connect string (? shows list): ?
DSN | Driver ------------------------------------------------ TESTDB | MyODBC
Enter ODBC connect string (? shows list): DSN=TESTDB Driver: 05.02.0004 (libmyodbc5w.so) SQL>
7) Connect to your data source in R through RODBC – Voilà!
library(RODBC)
cn <- odbcConnect("TESTDB")
sqlTables(cn)
close(cn)
Thanks for this post!
Could you please elaborate on which software version you exactely to get it working (the OS, R-Studio)? (This blog doesn’t seems to have publication dates… woul dbe helpful, though. Maybe you cann add those?)
I try to re-do your “tutorial” on Mac OS X Mavericks, but unfortunately it does not seem to work. The test scenario with iodbctest you advise to run seems to be impossible to reproducable, because iodbctest is an unknown command to the bash.
Looking forward to your response!
Thanks again!
Best greetings from Vienna!
Robert
Hi Robert,
Sorry for the late reply, your comment was unfortunately stuck in my spam filter. This was indeed done before Mavericks was released. To get all the files you need, you can install the freeTDS open source ODBC driver via Homebrew. Doing this, you end up with a copy of iodbctest as part of the install (mine landed in /Library/Application Support/iODBC/bin/iodbctest).
The steps are covered in a blog post quite similar to mine that I only found recently:
http://blog.nguyenvq.com/blog/2013/04/06/guide-to-accessing-ms-sql-server-and-mysql-server-on-mac-os-x/
Hope this helps!