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