RStudio Connection Tab Error MacOS (Error: nanodbc/nanodbc.cpp:1021: 00000 - file not found)

RStudio IDE
, ,
1

Hello everyone! I've been struggling a lot lately to have RStudio connection tab working on MacOS. Whenever i use the ListDrivers function it finds the driver:

odbc::odbcListDrivers()
name attribute                                         value
1 PostgreSQL Driver    Driver /Library/PostgreSQL/psqlODBC/lib/psqlodbcw.so

But then when trying to finish the connection I'm receiving the following error:

Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib '/Library/PostgreSQL/psqlODBC/lib/psqlodbcw.so' : file not found

Can someone help me figure out how to make this connection work?

2

Hi @felipe_dalton and welcome!

There are a couple of options you can take. The error message is telling you that you're missing a critical psqlodbc driver file.

Step 1. The easiest way is to first install psqlodbc using homebrew:
In a terminal: brew install psqlodbc

Method #1 (Preferred)

To get rid of that error message, remove the current PostgreSQL Driver which is pointing to an location referencing the missing driver using an ODBC Manager app (ex. Applications/Utilities/ODBC Manager).

Step 2. In your odbc manager app, create a new driver with the location of driver file pointing to your homebrew psqlodbc installation:
/usr/local/opt/psqlodbc/lib/psqlodbcw.so

Step 3. Create a user-level or system level DSN using that driver. From there, RStudio should be able to pick it up.

Method #2
Step 2.
In a terminal, create a symbolic from your homebrew installation to where odbc is currently looking for it.

sudo mkdir /Library/PostgreSQL

sudo ln -s /usr/local/opt/psqlodbc /Library/PostgreSQL/psqlODBC

HTH

3

Thank you very much for the information @jrmuirhead ! Please bear with me as I have a limited knowledge on these configurations (and english is not my native language so there might be some mistakes in there :smile: )

I'll give more detail on the issue that I forgot to point out initially. I've actually run Step 1 and followed up all the instructions on setting up the odbc as instructed here: 'Setting up ODBC Drivers'

I've also downloaded the odbc driver manager 'odbcmanager.net/' as MacOS stopped providing a native application after Leopard and installed Postgres SQL Driver and also an AWS redshift from their websites. After those were installed they created the files on the mentioned locations.

I've used the odbc Manager to setup the drivers, although I haven't setup anything on User/System DSN (i thought by using odbc.ini it would already work, is it wrong?)

When using Method 2 to create a symbolic from homebrew installation I 've got the following result confirming that the driver does exist in the location specified

mkdir: /Library/PostgreSQL: File exists
mkdir: /usr/local/opt/psqlodbc: File exists
mkdir: /Library/PostgreSQL/psqlODBC: File exists

So it seems that both drivers exist on the correct location and are configured on the ODBC Manager. They also appear correctly on RStudio Connection pane but each gives a different error prompt message although all appear to be configured right as setup by odbcinst.ini. Do you know if there is anything that I might be missing?

Error: nanodbc/nanodbc.cpp:1021: 00000: [Amazon][ODBC] (11560) Unable to locate SQLGetPrivateProfileString function: [Amazon][DSI] An error occurred while attempting to retrieve the error message for key 'LibsLoadErr' with message parameters ['""'] and component ID 3: Message not found in file "/opt/amazon/redshift/ErrorMessages/en-US/ODBCMessages.xml"

Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib '/Library/PostgreSQL/psqlODBC/lib/psqlodbcw.so' : file not found

4

@jrmuirhead there are some restrictions for screenshot/links posting so let me know if you need additional information to be provided :slight_smile:

5

  1. For method #2, there should just be one line to create the symbolic link:
    sudo ln -s /usr/local/opt/psqlodbc /Library/PostgreSQL/psqlODBC

  2. The only other thing I can think of is that you might have multiple odbc.ini files on your computer.

  3. Finally, you can specify the driver in your ~/.odbcinist.ini file to either /usr/local/lib/psqlodbcw.so or /usr/local/opt/psqlodbc/lib/psqlodbcw.so.

PerfMon    = 1
Retry Wait = 1800

[ODBC Drivers]
psql = Installed

[PostgreSQL]
Driver  = /usr/local/opt/psqlodbc/lib/psqlodbcw.so
Setup   = 
Keyword = Value
1 Like
6

Thank you very much @jrmuirhead! It worked! I've used the symbolic link (1st option and adjusted odbcinst.ini file to show the driver as you've stated and it connected! :smile:

I indeed had multiple odbc.ini files (I believe it happened after downloading from multiple sources) but found which one was working for R and fixed it

Once again, thank you very much for taking the time to help me here!

7

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.