[R] Query about RODBC to access MySQL from Windows

[Prof Brian Ripley]

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 at 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