Re: [R] ODBC MySql DB: sqlQuery empty
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 wrote:
> Did you register the database with ODBC?
>
> Dennis
>
> On Sun, Jul 17, 2011 at 3:46 PM, Henri Mone 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@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.
>>
>
__
R-help@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.
Re: [R] ODBC MySql DB: sqlQuery empty
Did you register the database with ODBC?
Dennis
On Sun, Jul 17, 2011 at 3:46 PM, Henri Mone 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@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.
>
__
R-help@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.
