R and RStudio not detecting installed PostgreSQL (or any other) ODBC drivers

Question

I am trying to connect to a PostgreSQL database using the R ODBC drivers in RStudio. For some reason, the drivers are not being detected by R:

$ Rscript -e 'odbc::odbcListDrivers()'
[1] name      attribute value    
<0 rows> (or 0-length row.names)

Even though as far as I can tell, they are correctly installed (using homebrew):

$ brew list
freetds     gettext     git     icu4c       libtool     openssl     pcre2       pkg-config  
postgresql  psqlodbc    readline    sqlite      sqliteodbc  unixodbc

$ odbcinst -j
unixODBC 2.3.6
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /Users/barthf/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

$ cat /etc/odbcinst.ini
[PostgreSQL Driver]
Driver          = /usr/local/lib/psqlodbcw.so

[SQLite Driver]
Driver          = /usr/local/lib/libsqlite3odbc.dylib

$ ls /usr/local/lib/ | grep odbc.*so$
libsqlite3odbc.so
libtdsodbc.0.so
libtdsodbc.so
psqlodbca.so
psqlodbcw.so

I am running on:

R version 3.4.2 (2017-09-28)
Platform: x86_64-apple-darwin15.6.0 (64-bit)
Running under: OS X El Capitan 10.11.6

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] DBI_1.0.0  odbc_1.1.6

loaded via a namespace (and not attached):
 [1] Rcpp_0.12.18      dbplyr_1.1.0      compiler_3.4.2    pillar_1.2.3      later_0.7.1       plyr_1.8.4       
 [7] bindr_0.1.1       base64enc_0.1-3   tools_3.4.2       digest_0.6.12     bit_1.1-12        jsonlite_1.5     
[13] tibble_1.4.2      nlme_3.1-131      lattice_0.20-35   pkgconfig_2.0.2   rlang_0.2.2       psych_1.7.8      
[19] shiny_1.0.5       rstudioapi_0.7    parallel_3.4.2    RPostgreSQL_0.6-2 yaml_2.1.14       bindrcpp_0.2.2   
[25] stringr_1.2.0     httr_1.3.1        dplyr_0.7.5       sparklyr_0.8.4    hms_0.4.2         rprojroot_1.2    
[31] bit64_0.9-7       grid_3.4.2        tidyselect_0.2.4  glue_1.2.0        R6_2.2.2          foreign_0.8-69   
[37] tidyr_0.7.2       reshape2_1.4.2    purrr_0.2.5       blob_1.1.1        magrittr_1.5      backports_1.1.1  
[43] promises_1.0.1    htmltools_0.3.6   mnormt_1.5-5      assertthat_0.2.0  mime_0.5          xtable_1.8-2     
[49] httpuv_1.4.0      stringi_1.1.5     lazyeval_0.2.1    broom_0.4.2    

For some reason unknown to me, the RPostgreSQL::PostgreSQL() driver works fine. However I want to use the ODBC driver so I can make use of the RStudio Connections feature. When I try to use RStudio to create a New Connection the dialog window only shows options for connecting to Livy and Spark.

As suggested, here is the attempted connection code:

Using ODBC:

> con <- DBI::dbConnect(odbc::odbc(),
+                       driver = "PostgreSQL Driver",
+                       database = "postgres",
+                       UID    = rstudioapi::askForPassword("Database user"),
+                       PWD    = rstudioapi::askForPassword("Database password"),
+                       host = "localhost",
+                       port = 5432) ## returns error below
Error: nanodbc/nanodbc.cpp:950: 01000: [unixODBC][Driver Manager]Can't open lib 'PostgreSQL Driver' : file not found 

Using RPostgreSQL:

con <- DBI::dbConnect(RPostgreSQL::PostgreSQL(),
+   dbname = "postgres",
+   user    = rstudioapi::askForPassword("Database user"),
+   password    = rstudioapi::askForPassword("Database password"),
+   host = "localhost",
+   port = 5432) ## works fine

Any thoughts why the R ODBC drivers do not seem to work? Any suggestions on things that I can try here?

Purview

Thanks for the suggestion, I updated my post to add these in – Purview

Please output the environment variable, LD_LIBRARY_PATH. You need to set the odbc lib directory to it: export LD_LIBRARY_PATH=/usr/local/lib. – Vanir

So I checked LD_LIBRARY_PATH using Sys.getenv and it seems to be empty. Then I set it using Sys.setenv but am still getting: Can't open lib 'PostgreSQL Driver' : file not found – Purview

Where is the export directive? Try doing this in command line outside R first. – Vanir

No luck, tried using export LD_LIBRARY_PATH... in shell before starting R, using system(), using Sys.setenv, adding the export statement to ~/.Renviron and using the terminal tab in RStudio but it doesn't change the outcome. – Purview

This can be a permissions issue in the /etc. Try running R as root or the owner of the folder and test odbc connection. – Vanir

OK awesome thanks it looks like you're correct and it's a permissions issue! I first tried sudo Rscript -e 'odbc::odbcListDrivers()' which (for some unknown reason) does not work BUT then I just tried cp /etc/odbcinst.ini ~/.odbcinst.ini && Rscript -e 'odbc::odbcListDrivers() and that shows two drivers. Awesome. – Purview

Check permissions and ownership with ls -l /etc. If root, try changing to your user sudo chown -R $USER:$USER /etc – Vanir

Actually using ~/.odbcinst.ini solved my issue, showing that it was indeed a permissions issue as you suggested. I'd rather not touch the /etc permissions. Happy to accept your answer if you want to post it as an answer. – Purview

I was following through the same issues and I had missed one crucial step. I did not edit the odbcinst.ini. I followed through the steps here and the connection worked. – Nigeria

Answer 1

For anyone else still stuck on this issue - I spent a couple of hours trying to get the odbc::odbcListDrivers() command to print out the drivers I knew were on my Mac.

I normally use a windows machine and never had this issue before, so was kind of out of my depth trying to figure it out.

There are a couple of questions on StackOverflow and in the issues section of r-dbi/odbc github with various answers - but none worked for me.

I thought I’d post a detailed answer to how I solved it for my Mac.

RStudio has a guide on setting up drivers for Windows/Mac/Linux machines. After installing unixODBC and your drivers (PostgreSQL for example) you are meant to edit the following two files odbcinst.ini (defines driver options) and odbc.ini (defines connection options).

On my Mac, I didn’t know where to look so I went searching and found it at /Library/ODBC/odbcinst.ini.

This file had the driver information in it that I assumed would show up using odbc::odbcListDrivers() - but wasn't.

After a lot of googling, I found the following command to type into mac terminal odbcinst -j to (i think) show the file location of driver and data source name information. This displayed the location of the odbc.ini file at the following path /Users/myusername/.odbc.ini.

Note the leading dot in .odbc.ini.

The dot denotes hidden files. Type SHIFT + CMD + . to display them.

After navigating to /Users/myusername and displaying hidden files, I saw .odbcinst.ini also. I opened this file in a text editor and surprise, it was empty.

I opened up /Library/ODBC/odbcinst.ini, copied the contents and pasted them into /Users/myusername/.odbcinst.ini.

I jumped back into the R console and finally, I could return values using odbc::odbcListDrivers().

Coniology

Answer 2

If using Windows, you could install the driver and add your data source in "ODBC Data Source Administrator", e.g. with the name of "DATA_SOURCE_NAME".

The following code will work if you have saved the password in your ODBC Data Source Administrator.

install.package('DBI')
install.package('odbc')
con <- DBI::dbConnect(odbc::odbc(), dsn='DATA_SOURCE_NAME', driver="PostgreSQL Unicode")

Distiller