[Rd] How to connect R to Mysql?

[Prof Brian Ripley]

On Fri, 17 Sep 2010, Spencer Graves wrote:

> Hi, Thomas:
>
>      You use RODBC to connect to MySQL?

Well, I do, and it is faster and more stable than RMySQL on all my 
platforms.

However, I have no idea why this is thought to be the appropriate 
list.  See R-sig-db !

For Windows, we need to know (see the posting guide) not just the 
version of the OS but also the version of R.  If you are running x64 
Win 7, you might be running 32- or 64-bit Windows.  Whichever, the the 
ODBC driver or MySQL client dll has to be of the same architecture as 
R *and not MySQL nor the OS*.  This is covered in detail in the RODBC 
manual.  For RMySQL it is quite recent that it has worked under x64 
Windows and I am not sure the documentation has yet caught up.

Follow-up to R-sig-db (which requires you to join to post) would be 
appropriate, so I am copying this there for future reference.

>
>
>      Thanks, Spencer
>
>
> On 9/17/2010 9:26 AM, Thomas Etheber wrote:
>> I also had problems connecting via RMysql on Windows several weeks ago.
>> I decided to skip the package and now use RODBC, which runs stable out of 
>> the box. Perhaps you should have a look at this package.
>> 
>> Hth
>> Thomas
>> 
>> Am 17.09.2010 17:50, schrieb Spencer Graves:
>>> 
>>>
>>>       I've recently been through that with some success.  I don't remember 
>>> all the details, but I first looked at "help(pac=RMySQL)".   This told me 
>>> that the maintainer was Jeffrey Horner.  Google told me he was at 
>>> Vanderbilt.  Eventually I found 
>>> "http://biostat.mc.vanderbilt.edu/wiki/Main/RMySQL", which told me that I 
>>> needed to build the package myself so it matches your version of MySQL, 
>>> operating system, etc.  I did that.
>>> 
>>>
>>>       Does the MySQL database already exist?  I created a MySQL database 
>>> and tables using MySQL server 5.1.50-win32.  (Which version of MySQL do 
>>> you have?)
>>> 
>>>
>>>       help('RMySQL-package') includes "A typical usage".  That helped me 
>>> get started, except that I needed to write to that database, not just 
>>> query it.  For this, I think I got something like the following to work:
>>> 
>>> 
>>> d <- dbReadTable(con, "WL")
>>> dbWriteTable(con, "WL2", a.data.frame)  ## table from a data.frame
>>> dbWriteTable(con, "test2", "~/data/test2.csv") ## table from a file
>>> 
>>>
>>>       Hope this helps.
>>>       Spencer
>>> 
>>> 
>>> On 9/17/2010 7:55 AM, Arijeet Mukherjee wrote:
>>>> I installed the RMySql package in R 2.11.1 64 bit
>>>> Now how can I connect R with MySql?
>>>> I am using a windows 7 64 bit version.
>>>> Please help ASAP.

-- 
Brian D. Ripley,                  ripley at 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