ODBC Driver Issues using Presto ODBC Drivers on Mac OS

odbc

#1

When using simba presto drivers on a Mac OS the DSN found at /usr/local/etc/odbc.ini does not work.

isql "DSN Name" 

works so I know the definition is correct,

However, when using DBI::dbConnect(odbc::odbc(), “DSN Name”) the i get the following error Error:

nanodbc/nanodbc.cpp:950: HY000: [Simba][ODBC] (11560) Unable to locate SQLGetPrivateProfileString function.

From my research this seems to be an issue with the drivers finding an appropriate ini file.

I just can’t figure out what RStudio is or is not doing…

the simbda driver info is here: Simba Driver Docs

$ odbcinst -j
unixODBC 2.3.4
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /usr/local/etc/odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

And so you know it actually does work from the terminal…

$ isql "EIM Test"
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> 

#2

Do you know about http://db.rstudio.com ? You will find some info on how things are working with DBI and odbc and drivers

I am not on mac and not using presto but when I want to check what odbc does I am using:

  • odbc::odbcListDrivers() to see if the driver are found
  • odbc::odbcListDataSources() to see if DSN are found.

as it works with isql, should be ok. But we never know…


#3
> odbc::odbcListDataSources()
      name                                  description
1      HAD /Applications/sap/hdbclient/libodbcHDB.dylib
2 EIM Test                     Simba Presto ODBC Driver

It shows up, but it doesn’t work. I feel it is some system environmental variable that isn’t getting picked up by RStudio or the odbc package.


#4

Have you tried running the connection from inside just R (outside of RStudio)? It may not be an RStudio issue, but an issue with the odbc package itself. I would recommend giving it a shot in the terminal, just to be sure where the issue lies. I suspect that the issue is in the odbc package utilizing the driver.

Also, have you tried connecting with the Driver directly? i.e. specifying connection parameters directly and not using the DSN? That might be another approach to whittle down and be sure that the Driver is the issue.


#5

I can’t use the DSN in either the R console or RStudio Session. Same error, guess it is odbc package related.

I am able to connect with the driver and my credentials, but there is some encoding issue and the table names all have one character like h for hive and s for system.

Same setup works great in Windows :smiley:


#6

@MattL Sorry for the delay in responding back. I definitely recommend hitting “Reply” or “@-mentioning” people to be sure they get a notification of your update!

In any case, this sounds like an issue that might be worth raising over at the odbc repo. I have seen some encoding issues over there and it would be great to get them documented / resolved!