Orvalho:
?Thanks for pointing our RMySQL. The reason is that we have similar data stored in both SQL server and MySQL databases and I want to reuse code as much as possible.
?Jeff
-----Original Message-----
From: Orvalho Augusto [mailto:orvaquim at gmail.com]
Sent: Wednesday, January 13, 2010 10:57 PM
To: Marcus, Jeffrey
Cc: r-help at r-project.org
Subject: Re: [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
? ? 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