8

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?

Floris
  • 637
  • 1
  • 8
  • 17
  • Thanks for the suggestion, I updated my post to add these in – Floris Sep 13 '18 at 15:27
  • 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`. – Parfait Sep 13 '18 at 15:36
  • 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` – Floris Sep 13 '18 at 15:44
  • Where is the `export` directive? Try doing this in command line outside R first. – Parfait Sep 13 '18 at 15:45
  • 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. – Floris Sep 13 '18 at 15:53
  • 1
    This can be a permissions issue in the `/etc`. Try running R as root or the owner of the folder and test odbc connection. – Parfait Sep 13 '18 at 18:37
  • 1
    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. – Floris Sep 13 '18 at 19:33
  • Check permissions and ownership with `ls -l /etc`. If root, try changing to your user `sudo chown -R $USER:$USER /etc` – Parfait Sep 13 '18 at 20:26
  • 2
    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. – Floris Sep 14 '18 at 14:43
  • 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](https://db.rstudio.com/best-practices/drivers/#odbcinst.ini) and the connection worked. – larnsce Aug 07 '19 at 09:08

1 Answers1

5

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().

BilboBaagins
  • 66
  • 1
  • 4