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 and 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 filesodbc.ini
andodbcinst.ini
but they’re completely empty/etc/
had neitherodbc.ini
orodbcinst.ini
I also looked in the locations @TallTed suggested the DBI library would look:
/Users/*/Library/ODBC
has bothodbc.ini
andodbcinst.ini
.odbc.ini
is filled in with information andodbcinst.ini
doesn't have much./Library/ODBC/
only hasodbcinst.ini
which is filled with information and an empty folder calledODBCDataSources
(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
Error: nanodbc/nanodbc.cpp:950: HY000: [Teradata][ODBC] (11560) Unable to locate SQLGetPrivateProfileString function.
then "the name of the library file for the driver manager is different from the default". Please see Teradata - Specifying ODBC Driver Managers's "Troubleshooting" section – jayBana