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

[Orvalho Augusto]

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