[R] ODBC MySql DB: sqlQuery empty

[Dennis Murphy]

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