lalitha viswanath
2007-May-02 15:31 UTC
[R] Query about RODBC to access MySQL from Windows
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
First, try this kind of connection string channel <- odbcConnect("mysqldsn","uname;Password=pwd;Database=default_db") If it doesn't work, in order to understand if it's a permission issue, try to connect with another client (still using the RODBC!!if you use the mysql client, you will not use ODBC but the mysql driver!!!). If it dooesn't solve the problem, give us more details on how you retrieve the data...> 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. >------------------------------------------------------ Passa a Infostrada. ADSL e Telefono senza limiti e senza canone Telecom http://click.libero.it/infostrada