ODBC?
Yep ODBC.. Normally I would use the RMySQL package for connecting to a MySQL-server, but since I wanted to do my analysis on an old laptop lying around running Windows XP and no RMySQL package was available in CRAN for my version of R (2.15.2), RODBC was the quickest one to setup.
For this being cleared up, let's give it a go.
R
Before setting everything else up we first install R. R for Windows can be downloaded from http://cran.r-project.org/bin/windows/base/, the version I use is 2.15.2. Follow the installation instructions, choosing for a "Full installation" and a customized startup at the "Startup options" screen to setup your preferred way of display, for me that was: "MDI", "HTML help" and "Internet2" while having "C:\Program Files\R\R-2.15.2" chosen as installation directory.
That was it, now let's install the RODBC package..
RODBC
Having installed R, we can launch RGui which is shipped with R. Depending on your installation preferences, it can be found on your Desktop, Start menu, and as "C:\Program Files\R\R-2.15.2\bin\i386\Rgui.exe".
For simplicity of installing packages in the future, I recommend selecting a CRAN mirror beforehand. You can do this be entering the following in the console RGui opens up by default:
chooseCRANmirror()
This launches a large list of available mirrors. Select the one you want (mine is "Utrecht University") and hit "OK".
Now for installing the RODBC package, enter the command:
utils:::menuInstallPkgs()
Scroll down and select "RODBC", finish by clicking "OK". The following should now show up in the console:
trying URL 'http://cran-mirror.cs.uu.nl/bin/windows/contrib/2.15/RODBC_1.3-6.zip'
Content type 'application/zip' length 759673 bytes (741 Kb)
opened URL
downloaded 741 Kb
package ‘RODBC’ successfully unpacked and MD5 sums checked
The downloaded binary packages are in
C:\Documents and Settings\Iwan Luijks\Local Settings\Temp\RtmpmW37k7\downloaded_packages
Now you have RODBC installed. Let's configure a MySQL data source we can use.
MySQL Connector/ODBC
Before can configure our MySQL data source however, we need the have the MySQL/Connector ODBC installed so we can actually connect to a MySQL-server via ODBC.
Go to http://dev.mysql.com/downloads/connector/odbc/ and start your download (I used the MSI-installer, version 5.2.4-ansi). The installation is pretty straight-forward so I won't dive into that one.
Adding the data source
Windows has many shortcuts for accessing the Control Panel Files, for configuring ODBC data sources there is "Odbccp32.cpl". So press "Windows+R" and enter "Odbccp32.cpl" in the input field followed by clicking "OK". This launches the "ODBC Data Source Administrator". Being in the "ODBC Data Source Administrator", the default tab opened is "User DSN" where DSN stands for Data Source Name used for describing the connection to a Data Source. The name of the Data Source is used as first parameter when setting up a connection using RODBC, so remember the one you enter next.
Hit "Add..." and select "MySQL ODBC 5.2a Driver". Click "Finish" to open the "MySQL Connector/ODBC Data Source Configuration" dialog. Enter the credentials for connecting to your MySQL-server and optionally hit the "Test"-button for testing the connection.
Note that you might want to set additional connection options, these can be found under the "Details >>"-button.
When you're done click "OK" and close the "ODBC Data Source Administrator" dialog.
Back to RGui
Find your way back to RGui and in the console enter the following:
library(RODBC)
ch <- odbcConnect("THE_DSN_YOU_ENTERED_BEFORE")
sqlTables(ch)
odbcClose(ch)
Where these functions above do the following (line-by-line):
- load the RODBC library
- connect to your Data Source using the DSN you specified before and assign that connection to the variable "ch"
- list all tables
- close the connection you setup using odbcConnect()
That's actually all.. Now go analyse your data! ☺