1

I'm trying to use RODBC to connect to a MySQL db on my computer (I'm assuming it's localhost). I've read the package reference manual and can't figure out how to do anything (connect, set default driver, open channel, etc). Any suggestions?

EDIT:

> install.packages("RMySQL", type="source")
Installing package(s) into ‘C:/Users/backupSam/Documents/R/win-library/2.13’
(as ‘lib’ is unspecified)
trying URL 'http://lib.stat.cmu.edu/R/CRAN/src/contrib/RMySQL_0.8-0.tar.gz'
Content type 'application/x-gzip' length 160735 bytes (156 Kb)
opened URL
downloaded 156 Kb

* installing *source* package 'RMySQL' ...
ERROR: configuration failed for package 'RMySQL'
* removing 'C:/Users/backupSam/Documents/R/win-library/2.13/RMySQL'
* restoring previous 'C:/Users/backupSam/Documents/R/win-library/2.13/RMySQL'

The downloaded packages are in
        ‘C:\Users\backupSam\AppData\Local\Temp\RtmpitXEFu\downloaded_packages’
Warning messages:
1: running command 'C:/PROGRA~1/R/R-213~1.2/bin/x64/R CMD INSTALL -l "C:/Users/backupSam/Documents/R/win-library/2.13"   C:\Users\BACKUP~1\AppData\Local\Temp\RtmpitXEFu/downloaded_packages/RMySQL_0.8-0.tar.gz' had status 1 
2: In install.packages("RMySQL", type = "source") :
  installation of package 'RMySQL' had non-zero exit status
screechOwl
  • 27,310
  • 61
  • 158
  • 267
  • 1
    You might be interested in this post [http://stackoverflow.com/questions/7804411/rmysql-installation-issue-with-mysql-5-5] on the same topic. There are two useful links in the original post, but they likely won't be enough to get you set up. You can continue by working through the comments. – Josh O'Brien Nov 02 '11 at 16:30
  • which platform are you running? (mac, windows, linux) Is MySQL up and running properly and can you connect to your MySQL outside of R? – JD Long Nov 02 '11 at 16:31
  • 1
    This question needs a lot more detail and specifics to be constructive. – joran Nov 02 '11 at 16:40
  • I'm running a windows 7 box. I recently "upgraded" from R-2.11 to R-2.13 and can't get RMySQL to work now, so I'm trying to figure out RODBC. MySQL is running fine, it's 5.1. – screechOwl Nov 02 '11 at 18:25
  • 2
    Do you know what a 'DSN' is in the context of ODBC? You need too. Learn about ODBC first, "prove" an ODBC connection with something else (even Excel ...) and then try RODBC. – Dirk Eddelbuettel Nov 02 '11 at 19:19
  • @Dirk Eddelbuettel: Thank you, that was what I was looking for. Got everything up and running. Cheers! – screechOwl Nov 02 '11 at 20:14

1 Answers1

6

First set up a connection.

1) For me I had to download a driver on MySql's website, which will vary by system and version, I used this page:

Windows ODBC Drivers

2) Once this is downloaded run the setup utility.

3) Next setup up the DSN. Instructions for windows are here: MySQL ODBC DSN Setup

4) Important: Remember the name of the DSN as it is used when you create the channel in RODBC to connect to your database.

5) Finally, once this is setup you install and load the RODBC package.

6) To connect to your database use something like this:

channel <- odbcConnect("mysql 2", uid="root")

where 'mysql 2' is the name of your DSN connection, NOT the name of the database.

7) Finally you can send a query like this:

result1 <- sqlQuery(channel, paste("SELECT * from db1"))
screechOwl
  • 27,310
  • 61
  • 158
  • 267