Search code examples
rodbcteradatar-dbi

How to connect to Teradata Database using R + DBI +ODBC with osx (and windows VM)

I work on a Mac, but also have a windows VM.

I am trying to connect to a Teradata Database on both my Mac and Windows VM using R with the DBI library (so I can use dbplyr). Everything is working great on my Windows VM (see my previous question: Connect to Teradata Database using R + DBI library + ODBC).

Again I suspect a driver issue, but I am not sure. I'm sure I had this working last week, but I can't remember. I know I have updated my Mac.

Background: First here's my ODBC info if I look it the ODBC Administrator:

  • Name = name_name
  • Driver = Teradata
  • Name or IP address = address.here.ok
  • Mechanism = ldap
  • Username = my_username

See an screenshot of my ODBC administrator ODBC administrator and the drivers the drivers

I downloaded the ODBC driver for Mac from Teradata

I use R version 3.5.1 (2018-07-02) and the teradata driver 16.20.

What works (on Mac and Windows): library(RODBC) 

con = odbcConnect(dsn = "name_name"
                  ,uid = rstudioapi::askForPassword("Username")
                  ,pwd = rstudioapi::askForPassword("Password")
                  )

What doesn't work on Mac (but works on Windows)

con = DBI::dbConnect(odbc::odbc()
                  ,dsn = "name_name"
                  ,uid = rstudioapi::askForPassword("Username")
                  ,pwd = rstudioapi::askForPassword("Password")
                  )

On Mac I get the error Error: nanodbc/nanodbc.cpp:950: IM002: [unixODBC][Driver Manager]Data source name not found, and no default driver specified.

I have also tried other values in my connection string with no luck as described here, e.g:

con = DBI::dbConnect(odbc::odbc()
                  ,Driver = "Teradata"
                  ,Host = "address.here.ok"
                  ,DBName = "name_name"
                  ,uid = rstudioapi::askForPassword("Username")
                  ,pwd = rstudioapi::askForPassword("Password")
                  )

Thankyou in advance.

Edit: update

Based on suggestions from @TallTed I checked the two locations where DBI will “look” for the odbc file.

  • /usr/local/etc/ directory has files odbc.ini and odbcinst.ini but they’re completely empty
  • /etc/ had neither odbc.ini or odbcinst.ini

I also looked in the locations @TallTed suggested the DBI library would look:

  • /Users/*/Library/ODBC has both odbc.ini and odbcinst.ini. odbc.ini is filled in with information and odbcinst.ini doesn't have much.
  • /Library/ODBC/ only has odbcinst.ini which is filled with information and an empty folder called ODBCDataSources (not sure if there are hidden files in there).
  • /Users/*/.odbc.ini exists with nothing in it, I couldn’t open /Users/*/.odbcinst.ini

So far, in /usr/local/etc/ I removed odbcinst.ini and odbc.ini and crated the following symlinks:

  • (link to odbc.ini file)ln -s /Users/*/Library/ODBC/odbc.ini /usr/local/etc
  • (link to odbcinst.ini file)ln -s /Library/ODBC/odbcinst.ini /usr/local/etc
  • (link to ODBCDataSources folder) ln -s /Library/ODBC/ODBCDataSources /usr/local/etc

I couldn't create symlinks into /etc/ folder for example I got the error:

  • ln: /etc/odbc.ini: Permission denied

Now I get Error: nanodbc/nanodbc.cpp:950: HY000: [Teradata][ODBC] (11560) Unable to locate SQLGetPrivateProfileString function.

Edit 2: solution

con = DBI::dbConnect(odbc::odbc()
               ,driver = "/Library/Application Support/teradata/client/16.20/lib/tdataodbc_sbu.dylib"
               ,DBCName = "address.goes.here"
               ,MechanismName = "ldap"
               ,uid = rstudioapi::askForPassword("Username")
               ,pwd = rstudioapi::askForPassword("Password"))

The path /Library/Application Support/teradata/client/16.20/lib/tdataodbc_sbu.dylib is just from ODBC Administrator > Drivers tab

Solution

  • The ODBC Administrator you're running is working with the iODBC Driver Manager (which Apple ships with macOS). iODBC looks to specific default locations for the ODBC configurations --

    /Library/ODBC/odbc.ini
/Library/ODBC/odbcinst.ini
/Users/*/Library/ODBC/odbc.ini
/Users/*/Library/ODBC/odbcinst.ini
/Users/*/.odbc.ini -> /Users/*/Library/ODBC/odbc.ini
/Users/*/.odbcinst.ini -> /Users/*/Library/ODBC/odbcinst.ini

    Based on the success and failure you report, RODBC is apparently built against iODBC, but DBI is apparently built against the unixODBC Driver Manager, which looks to different default locations --

    /etc/odbc.ini
/etc/odbcinst.ini
/usr/local/etc/odbc.ini
/usr/local/etc/odbcinst.ini

    You may find success by using the full [name_name] DSN definition as found in the relevant odbc.ini file to construct a DSN-less ODBC connect string for your DBI::dbConnect() call.

    Alternatively, you might move (most of) the content of the latter files into the former files, and replace the latter files with symlinks to the others, such that both Driver Managers use the same configuration files -- even though some apps and some drivers will only work with one Driver Manager.