Thanks you solved and share with us. But, why don't you use the RMySQL, which connects to MySQL without the need of ODBC?
Caveman On Wed, Jan 13, 2010 at 1:48 AM, Marcus, Jeffrey <jeffrey.mar...@nuance.com> wrote: > I think I figured this out. I should not have put the Driver name in > braces. Changing it from {MySQL} to MySQL seems to work. > > -----Original Message----- > From: Marcus, Jeffrey > Sent: Tuesday, January 12, 2010 6:09 PM > To: 'r-help@r-project.org' > Subject: Problems connecting with MySQL using odbcDriverConnect (RODBC > package) on Linux > > I am sure I'm doing something wrong here but not sure what. > > Our system administrator recently installed UnixODBC and the MyODBC > driver on a Linux box running Linux version 2.6 x86_64. > > I have an .odbc.ini file in my home directory with following lines: > > [mydb] > Description = MySQL server on my-server > Driver=/usr/lib64/libmyodbc3.so > SERVER=my-server > > I can successfully do the following: > > library(RODBC) > channel <- odbcConnect("mydb") > sqlQuery(channel, "show databases") > > And in general, I have no problems using odbcConnect to connect to the > mydb DSN. > > However, for various reasons I want to make a "DSN-less" connection > using odbcDriverConnect. However, everything I've tried generated a > "data source not found" message (see below for details) > > After reading through various documents, I tried doing following. > > (1) Put an odbcinst.ini file in my home directory with following lines > [MySQL] > Description = ODBC for MySQL > Driver=/usr/lib64/libmyodbc3.so > Setup = /usr/lib/libodbcmyS.so > FileUsage = 1 > > (2) Install it with odbcinst -i -f. This seems to work as when I type > odbcinst -j I get > > DRIVERS............: /home/jmarcus/odbcinst.ini > SYSTEM DATA SOURCES: /home/jmarcus/odbc.ini > USER DATA SOURCES..: /home/jmarcus/.odbc.ini > > > (2) Set the environment variable to point to this file: > > bash-3.2$ ODBCSYSINI=/home/jmarcus > bash-3.2$ export ODBCSYSINI > > (3) Start R > > Note that R has inherited environment variable >> Sys.getenv("ODBCSYSINI") > > ODBCSYSINI > "/home/jmarcus" > > (4) Try to connect to the MySQL server > > > conn <- > odbcDriverConnect(connection="Driver={MySQL};Server=my-server;Database=m > y_database;Uid=my_username;Pwd=my_password") > > This generates following: > > Warning messages: > 1: In odbcDriverConnect(connection = > "Driver={MySQL};Server=my-server;Database=my_database;Uid=my_username;Pw > d=my_password") : > [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver > Manager]Data source name not found, and no default driver specified > 2: In odbcDriverConnect(connection = > "Driver={MySQL};Server=my-server;Database=my_database;Uid=my_username;Pw > d=my_password") : > ODBC connection failed > > > Can anyone see what I'm doing wrong? Thanks. > > Jeff > > ______________________________________________ > 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. > -- OpenSource Software Consultant CENFOSS (www.cenfoss.co.mz) SP Tech (www.sptech.co.mz) email: orvaq...@cenfoss.co.mz cell: +258828810980 ______________________________________________ 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.