[R] FW: Problems connecting with MySQL using odbcDriverConnect (RODBC package) on Linux

[Marcus, Jeffrey]

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