R help - May 2007 - 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