Re: [R] Query about RODBC to access MySQL from Windows On Thu, 3 May 2007, Bruno C. wrote: > First, try this kind of connection string > channel <- odbcConnect("mysqldsn","uname;Password=pwd;Database=default_db") I presume the DSN was set up in a dialog box under Windows, and tested  there (which provides a different client). This makes me suspect the DSN is valid, but pointing at the wrong  database.  I noticed (s)he did not use 'use default_db; show tables;" when running from RODBC, which would have been a fairer test. Simple diagnostic output like that from odbcGetInfo() is missing from the  report. > 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... (And read the posting guide and try to ask a question that we have some  hope of answering, for example, one about R!) >> 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 --  Brian D. Ripley,                  ripley@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-help@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.