In my previous tutorial, I have shown you how to use R’s RMySQL to connect to MySQL Server and manipulate with data.
In this tutorial, I will show you an alternate way to connect with MySQL server using package ‘RODBC’ (please install it on your system from CRAN before using this tutorial). You are also required to install the latest ODBC connector for MySQL, which you can freely download from Oracle’s MySQL website. Once you have installed these files, we are good to go…
First type ODBC in search box.. and click on setup data sources link as shown in picture:
This Pops up the following windows. Click on “Add” button.
You should be able to see MySQL ODBC Drivers as follows.. (if not, please reinstall ODBC Connector after downloading it from MySQL website)..
Select MySQL Driver (ANSI or Unicode) depending upon your requirements… and click finish.. this would pop up the following configuration window:
Type name are “localhost” if you are working on your system or the host name you want to access. In TCP/IP source type either “localhost”, if MySQL you are accessing is installed on your computer or, otherwise, type host IP address and Port No.
You can type user name and password and set default database you want to access from R.
But my suggestion is you leave user name, password as blank for safety reasons. We can access MySQL Server and Database using these details in R on later course.
Click on Finish and then OK.. Ola!! We are done.Now open up R and type the following commands:
# Connect to MySQL using RODBClibrary(RODBC)
# Open connection, replace uid with your user id such as “root” and “password” with your own password that you did set while installing MySQL (or later working).
mycon <- odbcConnect(“localhost”, uid=”root”, pwd=”password”);
# if all is good, you should get connected to MySQL server and no errors should pop up…
# closing connection using:
That’s all folks…