[R] ODBC MySql DB: sqlQuery empty

[Henri Mone]

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)