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.
