[R] ODBC MySql DB: sqlQuery empty

[Henri Mone]

Hi Dennis,

Yes the database is registered with ODBC. I posted a copy of my ODBC
setup "~/.odbc.ini".
Still looking for the problem ....

Thanks,
Henri

On Mon, Jul 18, 2011 at 12:51 AM, Dennis Murphy <djmuser at gmail.com> wrote:
> Did you register the database with ODBC?
>
> Dennis
>
> On Sun, Jul 17, 2011 at 3:46 PM, Henri Mone <henriMone at gmail.com> wrote:
>> Dear R Beginners, Experts and Users,
>>
>> I already asked this question on the R-sig-DB mailing list, but sadly
>> didn't got a reply :-( .
>> Keeping fingers crossed that I have more luck on the main R mailinglist :) .
>>
>> I'm trying to connect to a MySQl database via Gnu R using the RODBC
>> interface. The conenction workes but I get an empty result string.
>> Gnu R and the MySql DB are running on a 64 bit server. I installed
>> version 5.1 of the mysql ODBC interface files [1]. My unixODBC setup
>> file is given in [2], the setup was tested with "isql" and works [3].
>> If I connect from GNU R to the database, an empty list of tables is
>> presented and sqlQuery(t.con, "show databases") returns an empty list
>> [4].
>>
>> What am I doing wrong I tried nearly everyting, but nothing seems to work.
>>
>> Thanks,
>> Henri
>>
>>
>>
>> ---1: INSTALL mysqlOdbc libraries
>>  $ wget http://dev.mysql.com/get/Downloads/Connector-ODBC/5.1/mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit.tar.gz/from/http://ftp.gwdg.de/pub/misc/mysql/
>>  $ tar xvzf mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit.tar.gz
>>
>>
>>
>>
>> ---2: unixODBC setup
>> $ more ~/.odbc.ini
>> [MySQL-data]
>> Description     = MySQL test database
>> Trace       = Off
>> TraceFile   = stderr
>> Driver      = /home/me/opt/mysql-connector-odbc/lib/libmyodbc5.so
>> Setup       = /home/me/opt/mysql-connector-odbc/lib/libmyodbc3S.so
>> SERVER      = 10.1.2.10
>> USER        = readOnlyUser
>> PASSWORD    = secret
>> PORT        = 3306
>> DATABASE    = data
>>
>>
>> ---3: unixODBC test
>> $ isql -v MySQL-data
>> +---------------------------------------+
>> | Connected!                            |
>> |                                       |
>> | sql-statement                         |
>> | help [tablename]                      |
>> | quit                                  |
>> |                                       |
>> +---------------------------------------+
>> SQL> show databases;
>> +-----------------------------------------------------------------+
>> | Database                                                        |
>> +-----------------------------------------------------------------+
>> | information_schema                                              |
>> |
>> +-----------------------------------------------------------------+
>> SQLRowCount returns 2
>> 2 rows fetched
>>
>>
>>
>> ---4: GNU R TEST
>>> library(RODBC)
>>> odbcDataSources()
>>                                       MySQL-data
>>  "/home/me/opt/mysql-connector-odbc/lib/libmyodbc5.so"
>>
>>> t.con<-odbcConnect("MySQL-data")
>>> t.con
>>  RODBC Connection 1
>>  Details:
>>   case=nochange
>>   DSN=MySQL-data
>>
>>> sqlTables(t.con)  # no output
>>
>>> sqlQuery(t.con, "show databases")
>>  character(0)
>>
>> ______________________________________________
>> R-help at r-project.org 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.
>>
>