Did you register the database with ODBC? Dennis
On Sun, Jul 17, 2011 at 3:46 PM, Henri Mone <henrim...@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@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.