[R-sig-DB] Connect R to MySQL DB installed via XAMPP

Boris Leroy |eroy@bor|@ @end|ng |rom gm@||@com
Tue Jun 26 12:38:32 CEST 2012 

Hi Julien,

yes I managed to connect R to MySQL through XAMPP, on the basis of the
RODBC package, because the other packages did not work properly.
Furthermore, some functions in RODBC did not work properly, like
sqlSave, and I could not obtain help for this.
I therefore wrote my own functions on the basis of "sqlQuery".

It now works relatively well, so I would advise you to give a try to RODBC.

Regards,

--
Boris Leroy,
PhD Student,
Equipe Biodiversité et Gestion des Territoires,
Service du Patrimoine Naturel, MNHN
UMR 7204 CERSP, MNHN
Université de Rennes 1
Campus de Beaulieu, Bât. 25, 1er étage
35042 Rennes cedex

+33 (0) 2 23 23 68 05
+33 (0) 6 66 02 13 27







Thanks Brain for even answering my (too) long email.

I did not mean to ask how to install windows source package - merely
hoping that someone would have sucessfully connect R with MySQL
(through XAMPP) without using RMySQL which I could not install "off
the shelve", while showing I did some research & tried before jumping
to the list.

Most probably my email was messy / too long, apologies for the inconvenience

Re RTools & how to use it, I believe I had simply not read proprely
what it is for / how to use it - mea culpa.



Rgds,
Julien



> Date: Mon, 25 Jun 2012 16:24:38 +0100
> From: ripley using stats.ox.ac.uk
> To: r-sig-db using r-project.org
> Subject: Re: [R-sig-DB] Connect R to MySQL DB installed via XAMPP
>
> On 25/06/2012 15:12, julien cuisinier wrote:
> >
> > Hi List,
> >
> >
> >
> > Just to update, I could find a zip online (I unfortunately lost the address & cannot find it anymore) RMySQL_0.8-0_for_MySQL_5.5.16.
zip
> >
> > which when installed locally under R allowed me to connect... not ideal but at leats it works for the time being.
> >
> > Any feedback on my email / question is more than welcome
>
> You start from mendacious assumptions, and installing Windows source
> packages is way off topic here.
>
> But you should not need administrative rights to install Rtools ....
>
> >
> >
> > Thanks & regards,
> > Julien
> >
> >
> >
> > From: j_cuisinier using hotmail.com
> > To: r-sig-db using r-project.org
> > Subject: FW: Connect R to MySQL DB installed via XAMPP
> > Date: Mon, 25 Jun 2012 13:46:27 +0200
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Hi List,
> >
> >
> > On windows XP & R2.15. Session info below.
> >
> > I installed XAMPP on my machine - I can't install MYSQL on the C:\Program file dir because no admin rights, so I installed XAMPP onto a personal directory...which I guess makes things slightly harder, but I am hopeful I am not thhe first one struggling on this!
> >
> > Any feedback  redirection / hints hugely appreciated
> >
> >
> >
> > (1) I tried to install RMySQL which I used successsfully on Linux in the past but it seems to be a trouble on Windows, no binary available on CRAN (anymore?) & when trying the "type = 'source' " option I get the following:
> >
> >> install.packages("RMySQL",type="source")
> > trying URL 'http://cran.ma.imperial.ac.uk/src/contrib/RMySQL_0.9-3.tar.gz'
> > Content type 'application/x-gzip' length 165363 bytes (161 Kb)
> > opened URL
> > downloaded 161 Kb
> >
> > '\\UKLonHome.HDS.Int\Home$\julien.cuisinier'
> > CMD.EXE was started with the above path as the current directory.
> > UNC paths are not supported.  Defaulting to Windows directory.
> > * installing *source* package 'RMySQL' ...
> > ** package 'RMySQL' successfully unpacked and MD5 sums checked
> > ERROR: configuration failed for package 'RMySQL'
> > * removing 'P:/PersoServer/Programs/R/R-2.15.0/library/RMySQL'
> >
> > The downloaded source packages are in
> >          ‘C:\Documents and Settings\julien.cuisinier\Local Settings\Temp\RtmpszNeip\downloaded_packages’
> > Warning messages:
> > 1: running command 'P:/PersoServer/Programs/R/R-2.15.0/bin/i386/R CMD INSTALL -l "P:/PersoServer/Programs/R/R-2.15.0/library"   C:\DOCUME~1\JULIEN~1.CUI\LOCALS~1\Temp\RtmpszNeip/downloaded_packages/RMySQL_0.9-3.tar.gz' had status 1
> > 2: In install.packages("RMySQL", type = "source") :
> >    installation of package ‘RMySQL’ had non-zero exit status
> >
> >
> > (2) I can't use tips like this one:
> > http://blog.tersmitten.nl/archives/1345
> >
> > simply because I can't install RTools.exe as I do not have admin rights
> > NB: I already changed the env. variable MYSQL_HOME but without success:
> >> Sys.getenv("MYSQL_HOME")
> > [1] "P:/PersoServer/Programs/XAMPP/mysql"
> >
> >
> >
> > (2) So I thought to try & connect directly using DBI package, but I am having an error msge I do not understand (My apologies if this is basic, not expert at all on DB management)
> >> library(DBI)
> >> drv <- dbDriver("MYSQL")
> > Error in do.call(as.character(drvName), list(...)) :
> >    could not find function "MYSQL"
> >
> > googling, I found the following but I do not get the answer from Prof Brian Ripley:
> > http://r.789695.n4.nabble.com/connecting-to-DB2-database-td827626.html
> >
> > I am guessing the "driver package" he refers to is RMySQL which I could not install in the first place... I tried to fiddle with the R files you have access once you extract the tar.gz source package under RMySQL\R, but no luck
> >> source("zzz.R")
> >> source("S4R.R")
> >> source("dbObjectId.R")
> >> source("MySQLSupport.R")
> >> source("MySQL.R")
> >> drv <- dbDriver("MYSQL")
> > Error in do.call(as.character(drvName), list(...)) :
> >    could not find function "MYSQL"
> >
> > weird thing is that in the "MySQL.R" file from the source package downloaded there is a "MYSQL" function declared - I am guessing the error is somewhere else but R is not very helpful here (my own limitation of course...)
> >
> >
> > Any idea of where to go from here would be hugely appreciated!! Sorry for the long email.
> >
> >
> > Thanks & regards,
> > Julien
> >
> >
> >
> >
> >
> >> sessionInfo()
> > R version 2.15.0 (2012-03-30)
> > Platform: i386-pc-mingw32/i386 (32-bit)
> >
> > locale:
> > [1] LC_COLLATE=English_United Kingdom.1252  LC_CTYPE=English_United Kingdom.1252
> > [3] LC_MONETARY=English_United Kingdom.1252 LC_NUMERIC=C
> > [5] LC_TIME=English_United Kingdom.1252
> >
> > attached base packages:
> > [1] stats     graphics  grDevices utils     datasets  methods   base
> >
> > other attached packages:
> > [1] DBI_0.2-5
> >
> >
> >     [[alternative HTML version deleted]]
> >
> >
> >
> > _______________________________________________
> > R-sig-DB mailing list -- R Special Interest Group
> > R-sig-DB using r-project.org
> > https://stat.ethz.ch/mailman/listinfo/r-sig-db
> >
>
>
> --
> Brian D. Ripley,                  ripley using stats.ox.ac.uk
> Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
> University of Oxford,             Tel:  +44 1865 272861 (self)
> 1 South Parks Road,                     +44 1865 272866 (PA)
> Oxford OX1 3TG, UK                Fax:  +44 1865 272595
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-db

More information about the R-sig-DB mailing list