[R] Query about RODBC to access MySQL from Windows

From: lalitha viswanath <lalithaviswanath_at_yahoo.com>
Date: Wed, 02 May 2007 08:31:28 -0700 (PDT)


Hi
I am trying to use RODBC in R installed on Windows to access MySQL database (on a linux box).
I set up a DSN and specified this DSN in R as follows library(RODBC);
channel <- odbcConnect("mysqldsn");
RODB Connection 5
Details:
  case=nochange
  PORT=3306
........
Although this seems to connect properly, running any command yields NO results.
i.e. sqlQuery(channel, "show tables") yields 0 rows when there are close to 500 tables in the database. Ditto with any other query. It does not cause an error, but it returns 0 rows.

The USER DSN "mysqldsn" is set up as follows :- host : zion.xxx.xxx.xxx
default database : default_db
port : 3306
username : uname
password : pwd

Running " use default_db; show tables;" command from the command prompt on the db server returns 500 rows.

I find this problem while running any query. Running "select * from tname limit 100" returns 0 rows whereas tname has around a million records.

In the past, I have used MySQL clients for Windows to access the database without encountering any such problem

I even tried setting up the "mysqldsn" DSN as a system DSN instead of a user DSN.

I would like to know
a) whether this is a permissions issue at some level b) whether there is any solution for this problem in R

Thanks
Lalitha



R-help_at_stat.math.ethz.ch mailing list
https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code. Received on Wed 02 May 2007 - 15:38:22 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Thu 03 May 2007 - 09:31:43 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.