[R] Accessing MySQL Database in R

[Spencer Graves]

       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