connect R to MySQL with RODBC using dsn

Question

Short version: I'm trying to figure out how to use the dsn argument in the odbcConnect() function to connect to a MySQL database.

Longer version: I appologize if this is an ignorant question. I didn't find an answer on SO searching under the tags. I'm not new to R, although I'm not the world's foremost expert. I am new to MySQL. I have been trying to learn it on my own.

I would like to be able to create tables and such in R and write them to a database in MySQL and then be able to select from them later and read the selected records back into R for analysis. I can create a database in MySQL, select, export a text file, & read that file into R now, but I would rather be able to do everything from R. For this purpose, I installed RODBC to be able to connect R to MySQL directly. I understand that I need to use the odbcConnect() function, but I do not understand what to put for the dsn argument to do this, and haven't been able to figure it out from the wiki or the help file.

Details (if needed):

I did not establish a uid or password when I set up MySQL

Versions used:          Windows XP;     R 2.10.1;     MySQL 5.5
Locations of exe files: C:\Program Files\MySQL\MySQL Server 5.5\bin  
                        C:\Program Files\R\R-2.10.1\bin  

I expect I need fairly basic advice--e.g., it might be best to assume I don't even know what dsn stands for (even though that's not actually true). Any help is appreciated. Thanks in advance.

Answer 1

The simplest solution I can suggest would be installing RMySQL. This will give you native connectivity to the MySQL database that you are using.

If you want to use ODBC besides needing to have the MySQL ODBC drivers you will need to set up the ODBC connection in the ODBC manager on Windows XP. When you go through the connection it will ask you for a name, which will be your DSN name, which then you should be able to use in odbcConnect()