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

[Orvalho Augusto]

Ok.

Caveman


On Thu, Jan 14, 2010 at 5:19 PM, Marcus, Jeffrey
<Jeffrey.Marcus at nuance.com> wrote:
> 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
>>> 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
>



-- 
OpenSource Software Consultant
CENFOSS (www.cenfoss.co.mz)
SP Tech (www.sptech.co.mz)
email: orvaquim at cenfoss.co.mz
cell: +258828810980