Accessing MySQL Database in R

      The following worked for me recently:


library(RMySQL)
MySQL. <- MySQL()
MySQLcon <- dbConnect(MySQL., user='thisuser', password='thispassword',
                      dbname='desiredDB')


     I have the following suggestions and questions for you:


           1.  Have you tried supplying "dbname" rather than "host"?


           2.  Please provide "sessionInfo()".  Many packages have a 
function named "dbConnect", and I don't know which one you are using.


           3.  I don't know if "MySQL()" is equivalent to dbDriver("MySQL"), 
which you used.  It might be;  I don't know.


           4.  The standard "install.packages('RMySQL')" may not work, 
because this package needs to be built to configure itself properly to your 
local operating system and versions of MySQL and R installed.  Installation 
instructions are available at 
"http://biostat.mc.vanderbilt.edu/wiki/Main/RMySQL".  If you have not already 
followed those instructions, please do so.  There is a good chance that will 
fix your problem, I think.


           5.  If this is not adequate, I suggest you post this question to 
"r-sig-db at stat.math.ethz.ch".  [I suggest you subscribe first.  This list has 
low volume and you can unsubscribe later if you prefer.  And please also 
provide "sessionInfo()".]


           6.  Or use RODBC as suggested by Ptit Bleu.  It comes highly 
recommended (including by Brian Ripley).  However, I had difficulties getting 
positive results from both RMySQL and RODBC.  I tried both, with each 
receiving similar quantities of expletives.  Finally, I got RMySQL to do what 
I wanted and suspended my schoolboy exercises with RODBC.


      Hope this helps.
      Spencer


On 1/20/2011 5:55 AM, PtitBleu wrote:

 Hello,

 I used to use RMySQL but as there is no more package for windows, I
 decided
 to move to RODBC.
 I installed ODBC driver for MySQL (downloaded on the MySQL website) and
 then
 the RODBC package.

 I finally discovered that it was not needed to "register" your database
 with
 ODBC before using it.
 These commands below work for me.

 library(RODBC)
 ch<-odbcDriverConnect(connection="SERVER=localhost;DRIVER=MySQL ODBC 5.1
 Driver;DATABASE=my_database;UID=root;PWD=my_password;case=tolower")
 resultdb<-sqlQuery(ch,"SELECT * from my_table")
 odbcClose(ch)

 Try to modify them for your case.
 I hope it will work for you.
 Good luck,
 Ptit Bleu.


 Re: Accessing MySQL Database in R
 Jan 18, 2011; 12:10am ? by djmuseR [User is online] djmuseR
 Hi:

 Because R does not have a direct interface to MySQL?

 You need to load a communication package - the two most common ones are
 RODBC and RMySQL. The former requires that you register your MySQL
 database
 table(s) with ODBC before using the RODBC package on them, whereas the
 latter works with specific version combinations of MySQL and R. The RODBC
 package has a very informative vignette; for information re the RMySQL
 package, see
 http://biostat.mc.vanderbilt.edu/wiki/Main/RMySQL

 HTH,
 Dennis

 On Mon, Jan 17, 2011 at 1:30 PM, schlafly<[hidden email]>  wrote:

 I have a local installation of MySQL on my computer.

 I enter the following to access MySQL from the command line:
 /Applications/MAMP/Library/bin/mysql -h localhost -u root -p
 I am then prompted for a password, and I use: root
 This connects me to MySQL in the command line.

 I now want to access MySQL databases in R. I enter the following:
 mysql<- dbDriver("MySQL")
 conn<- dbConnect(mysql,user='root',host='localhost', password='root')

 I get the following error message: Error in mysqlNewConnection(drv, ...) 
 :
 RS-DBI driver: (Failed to connect to database: Error: Access denied for
 user
 'root'@'localhost' (using password: YES)

 Does anyone know why these aren't equivalent?
 --
 View this message in context:
 http://r.789695.n4.nabble.com/Accessing-MySQL-Database-in-R-tp3221264p3221264.html
 Sent from the R help mailing list archive at Nabble.com