[R] Query about RODBC to access MySQL from Windows

[Bruno C.]

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