ODBC Driver Issues using Presto ODBC Drivers on Mac OS

Posit Professional & Hosted Products
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!

7

Hey @MattL,
I am having the same issue after completely resetting my machine.
I didn't have this problem before, and everything worked fine. Now I have it, which tells me there's something about my configuration that is different from last time.

If I get to fix it, I'll post my solution here.

8

Alright, here is a brief update.
I still get this error:

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

However, I only get it when I connect using an .odbc.ini file where I have my connection string written out.

To elaborate, here is my .odbc.ini file:

[ODBC Data Sources]
connection1=[Simba SQL Server ODBC Driver]

[connection1]
Driver=/Library/simba/sqlserverodbc/lib/libsqlserverodbc_sbu.dylib
Server=myserver
Database=mydb
UID=mylogin
PWD=mypassword
Trusted_Connection=NTLM

This connection fails:

con <- dbConnect(odbc::odbc(), "connection1")

(it used to work in the past. connection1 is still found in the list of available connections, as it is found via .odbc.ini file)

But this connection succeeds:

con2 <- dbConnect(odbc::odbc(), .connection_string = "Driver={Simba SQL Server ODBC Driver};
                 Server={myserver};
                 Database=mydb;
                 UID=mylogin;
                 PWD=mypassword;
                 Integrated Security=NTLM")

Any hunch on why is this?

9

Hey @MattL - I know this is an old thread but did you ever figure out a solution for the one-character table name issue?