Marcus, Jeffrey
2010-Jan-12 23:08 UTC
[R] 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