How to Connect R to MySQL Database Using ODBC on Mac OS 10.10.3 (Yosemite)

0
votes

Disclaimer upfront: I'm not a programmer, so...be gentle.

I've spent the past couple of days trying to get R studio on my Mac to connect to a MySQL database. The first hurdle faced was when running the command to install the ODBC package:

configure: error: "ODBC headers sql.h and sqlext.h not found" ERROR: configuration failed for package 'RODBC' ** Removing '/usr/lib64/R/library/RODBC'

After a lot of googling, I figured out this was solved by installing and updating something called brew, by running this command:

brew update && brew install unixODBC && \ wget "http://cran.r-project.org/src/contrib/RODBC_1.3-10.tar.gz" && \ R CMD INSTALL RODBC_1.3-10.tar.gz

That got RODBC installed. Now, I'm trying to connect to the database by running this syntax (as described online and in the package's documentation):

odbcConnect(dsn, uid = "", pwd = "", ...)

When I do that, I get the following error:

Warning messages: 1: In odbcDriverConnect("DSN=xyz;UID=xyz;PWD=xyz") : [RODBC] ERROR: state IM012, code 0, message [unixODBC][Driver Manager]DRIVER keyword syntax error 2: In odbcDriverConnect("DSN=xyz;UID=xyz;PWD=xyz") : ODBC connection failed

So, it seems like the syntax is wrong, but I don't see where. R documentation says that Error IM012 is a syntax error (note: replaced 'XYZ' for actual credentials) I have a few questions:

First:

On MySQL Workbench, I input the following parameters to access the database: 1. Connection Name: a made up name for the database 2. Hostname: an amazon cloud database link 3. Port (leave default of 3306) 4. Username 5. Password

Where in the RODBC syntax of odbcConnect(dsn, uid = "", pwd = "", ...) do you put the location of the database, presumably an ip or link? I tried that for the DNS, and it didn't work (i.e. got the same syntax error).

Second:

and most importantly to answer my question:

Why am I getting this IM012 syntax error? What would the correct syntax be? Where in this syntax is there room to tell the program where the database is located?

Thanks in advance for your help!

2
mysqlrdatabasemacosrodbc
brew puts packages in different directories than are expected by the default OSX install of R. You probably need to either add that directory to your system environment variables or build a symbolic link and put it in where R thinks it should be. – IRTFM

2 Answers

2
votes

If the documentation says:

odbcConnect(dsn, uid = "", pwd = "", ...)

and you supplied:

odbcDriverConnect("DSN=xyz;UID=xyz;PWD=xyz")

That is not the same syntax. Notice the quotations around each parameter's value only. Also, it seems each key-value pair should be separated by commas, not semicolons.

It also does not say dsn="", it says just 'dsn' so maybe you replace that with your DSN name from the ODBC configuration, which its sounds like for you comes from MySQL Workbench? I use an ODBC Administrator to create DSNs, but on a Mac they often wind up in /Library/ODBC/odbc.ini or a similar location in your user account.

The ellipses suggest you may supply further parameters. Often in your ODBC configuration, you supply DATABASE="192.168.254.85",PORT="3306", etc. within the DSN, so you do not need to supply the parameters in your call to that method. The UID and PWD are provided 'at run time' so you're not storing credentials in a config file.

0
votes

Sometimes you can add the database at the beginning of the url/IP .. so it would be db_name.url_of_databse