0

My working laptop is Mac OS X 10.11.3 El Capitan. I am currently working on a project in which I am developing a shiny app to visualize the data in our company's MySQL database. I set up the SSH Tunnel via 'SSH Tunnel Manager' and was able to connect MySQL data via SquelPro. However, I am constantly having problems when I am trying to connect MySQL data in R (R-version3.2.3 / Rstduio-version 0.99.491) using 'RODBC' package.

Initially, I am following this blog: http://blog.nguyenvq.com/blog/2013/04/06/guide-to-accessing-ms-sql-server-and-mysql-server-on-mac-os-x/ to downloaded and created the connector and 'odbc.ini' files. Then move them to the right locations.

Yet, when I try to install package 'RODBC' in R, the installation process fails and returns the following error message - "configure: error: "ODBC headers sql.h and sqlext.h not found". Then I follow the steps in this link to downloaded the iODBC zip files: Installation of RODBC/ROracle packages on OS X Mavericks Yet, I couldn't identify the file 'libiodbc.a' in the archball. So, just moving the sql.h and sqlext.h files are not helping. I still have that issue.

So, I found another link: Installation of RODBC on OS X Yosemite which says installing unixODBC will also solve this issue. So I copied the commands and successfully downloaded unixODBC. This time it works. I successfully called the library(RODBC) in R. However, when I run the following R commands in Rstudio:

connect1 <- odbcConnect(dsn="mysql01", 
                uid="user_name", 
                pwd="pass_words")

Then I got error message like:

    Warning messages:
1: In odbcDriverConnect("DSN=mysql01;UID=user_name;PWD=pass_words") :
  [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specified
2: In odbcDriverConnect("DSN=mysql01;UID=user_name;PWD=pass_words") :
  ODBC connection failed

It seems that I need to configure the driver manager for unixODBC on R. However, I am not sure what exactly gone wrong in the process above. What should I do next to get R connected to MySQL?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Mark Li
  • 429
  • 1
  • 7
  • 21
  • With ODBC, you can connect either by a generalized Driver/Provider or DSN (a pre-built, customized driver/provider connection). Did you create `mysql01` with server credentials? In R after loading `library(RODBC)` run `odbcDataSources()` to see current sources. See if any MySQL Driver/DSN appear. – Parfait Feb 13 '16 at 14:54
  • Hey Parfait, thank you so much for the help! Yes, I have double checked that I created the file 'odbc.ini' under directory - '~/Library/ODBC/' on my laptop. The file contains information as below: "[mysql01] Driver = /usr/local/lib/libmyodbc5w.so Server = [server.address] Port = 3306 charset = UTF8 User = [user_name] Password = [passwords] Database = [some_tables]" Also, I have run "odbcDataSources()" in R. It returns "named character(0)". Does it mean R can't locate the driver or DSN? – Mark Li Feb 15 '16 at 20:20
  • This suggests no ODBC driver or DSN available. Did you ever manually download the [MySQL driver](https://dev.mysql.com/downloads/connector/odbc/) for Mac OS? I work only on Windows so may not be able to fully help. Consider [RJDBC](http://stackoverflow.com/questions/13820745/rodbc-not-recognizing-my-odbc-settings) which Unix and Linux users have better luck. Additionally, use native [RMySQL](https://cran.r-project.org/web/packages/RMySQL/index.html) library with no driver install needed. – Parfait Feb 15 '16 at 21:59
  • Yes, I have downloaded the Tar Archive of MySQL driver for Mac and copied driver file 'libmyodbc5w.so' from the arch-ball to the directory '/usr/local/lib/' as this blog suggested - http://blog.nguyenvq.com/blog/2013/04/06/guide-to-accessing-ms-sql-server-and-mysql-server-on-mac-os-x/. It didn't help. I have also tried 'RMySQL' library. It returns me 'error message(60)' when I called 'dbConnect' with all login parameters. ;( I used to be developing on PC and ubuntu, Mac is new to me as well.. Thank you though Parfait! Much appreciated! – Mark Li Feb 16 '16 at 00:35

0 Answers0