« Making Data Work online conference | Main | Because it's Friday: Stochastic degradation »

June 04, 2010


Feed You can follow this conversation by subscribing to the comment feed for this post.

The "if necessary" phrase keeps evaluating as true, so I'm stuck in an infinite loop in step 6. But it's okay because it's beer.

I just spent all night testing different versions of R, libmySQL.dll, and RMySQL.

My evening of compiling different versions of things from source was unsuccessful until I stumbled across this.

My setup is:
Windows 7 x64
R 2.10
RMySQL-7.4 (from CRAN)

The problem I was having was that I'm using a 64-bit OS with a 32-bit RGUI. I didn't actually need a full MySQL server installed (I'm using a remote MySQL database), but the only way I could find a compatible library (libmySQL.dll) was by installing MySQL server 5.0.91.

I tried the 64 bit version of MySQL first with no success (error message about not being a valid 32-bit library). But I just tried the 32-bit MySQL 5.0.91, copied the libmySQL.dll from that installation and it works like a charm!

Thanks for the pointers!

I have been puzzled by the problems when I try to connect R with MySQL through RMySQL.
It's certain that I have already an environment variable - MYSQL_HOME, but I just can't get the connection work after restarting R...
when I load RMySQL the error occur:
Error : loadNamespace()...
If you know how to solve this tough problem I really appreciate it if you can send an email to me!

The RODBC package will also work in connecting to MySQL on 64-bit Windows. You just have to download the ODBC driver from the MySQL website.

You can avoid the "data source name" hassle of going through ODBC by using the odbcDriverConnect() function found in the RODBC package rather than using the odbcConnect() function. The connection string to use for MySQL (if found on your machine) is

Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=dbname;User=uname;Password=pswrd;Option=3;

My RMySQL is working now.
Thanks for it!

Thanks Yuanwei

RODBC solves the problem of using MySQL with Rev R 4.0 on x64. (Namely that an x64 RMySQL binary isn't available for R 2.11, and it sounds like there may never be one.)

I have just found this post and wonder if you can help.

I would like to read MySQL database tables into R where MySQL is hosted remotely.

In the above post by Eric on July 29 can I substitute the URL localhost in Server=localhost to able to connect to the db.

Also is RODBC available on CRAN.

Any help would be appreciated

i have a database called apple in mysql
and table name applecost
below is the table name applecost
| quantity | price | totprice |
| 40 | 90 | NULL |
| 50 | 60 | NULL |
| 60 | 30 | NULL |
I am calculating totprice by multiplying quantity and price in R.
i got the values 3600 3000 1800=price5
now i want to send values 3600 3000 1800 from R to the column totprice.
i have written the update query in R as
query.str1 = 'update applecost1 set totprice=price5[1,1] where quantity=40'
i got error as in R
"42000 1064 [MySQL][ODBC 3.51 Driver][mysqld-5.1.48-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[1,1] where quantity=40' at line 1"
[2] "[RODBC] ERROR: Could not SQLExecDirect 'update applecost1 set totprice=price5[1,1] where quantity=40'" .
how to fix this error? I need help on this to solve this problem....can anyone help me

I have had nothing but problems trying to get RmySql to install on several machines. I always get the same error. "In install.packages("RMySQL", type = "source") :
installation of package 'RMySQL' had non-zero exit status"

The comments to this entry are closed.

Search Revolutions Blog

Got comments or suggestions for the blog editor?
Email David Smith.
Follow revodavid on Twitter Follow David on Twitter: @revodavid
Get this blog via email with Blogtrottr