Marcus, Jeffrey
2010-Jan-12 23:48 UTC
[R] FW: Problems connecting with MySQL using odbcDriverConnect (RODBC package) on Linux
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 at 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
Orvalho Augusto
2010-Jan-14 03:56 UTC
[R] FW: Problems connecting with MySQL using odbcDriverConnect (RODBC package) on Linux
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.Marcus at 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 at 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 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. >-- OpenSource Software Consultant CENFOSS (www.cenfoss.co.mz) SP Tech (www.sptech.co.mz) email: orvaquim at cenfoss.co.mz cell: +258828810980