odbcListDrivers does not show list

[eylenth]

Issue Description and Expected Result

Hello, odbc::odbcListDrivers() does not show any drivers in my R session

I have installed the following packages:

• unixODBC
• mysql-connector-odbc
• postgresql-odbc
• unixODBC-devel

then I am able to see the underlying odbc drivers:

odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/eylenbt/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

cat /etc/odbcinst.ini
# Example driver definitions
 
# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description	= ODBC for PostgreSQL
Driver		= /usr/lib/psqlodbcw.so
Setup		= /usr/lib/libodbcpsqlS.so
Driver64	= /usr/lib64/psqlodbcw.so
Setup64		= /usr/lib64/libodbcpsqlS.so
FileUsage	= 1
 
 
# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description	= ODBC for MySQL
Driver		= /usr/lib/libmyodbc5.so
Setup		= /usr/lib/libodbcmyS.so
Driver64	= /usr/lib64/libmyodbc5.so
Setup64		= /usr/lib64/libodbcmyS.so
FileUsage	= 1

But when I start a R session, I can not see the listed drivers:

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

Database

mysql-connector
postgresql

Reproducible Example

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

Session Info

> sessionInfo()
R version 3.5.1 (2018-07-02)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Red Hat Enterprise Linux Server 7.6 (Maipo)

Matrix products: default
BLAS/LAPACK: ${SOFTWARELOCATION}/OpenBLAS/0.3.1-GCC-7.3.0-2.30/lib/libopenblas_haswellp-r0.3.1.so

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C              
 [3] LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8    
 [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8   
 [7] LC_PAPER=en_US.UTF-8       LC_NAME=C                 
 [9] LC_ADDRESS=C               LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

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

other attached packages:
[1] odbc_1.2.1

loaded via a namespace (and not attached):
 [1] Rcpp_1.0.3      withr_2.1.2     digest_0.6.15   zeallot_0.1.0  
 [5] crayon_1.3.4    DBI_1.0.0       backports_1.1.5 pillar_1.4.2   
 [9] rlang_0.4.1     rstudioapi_0.7  blob_1.2.0      vctrs_0.2.0    
[13] devtools_1.13.6 bit64_0.9-7     bit_1.1-14      hms_0.4.2      
[17] compiler_3.5.1  pkgconfig_2.0.2 memoise_1.1.0   pbdZMQ_0.3-3   
[21] tibble_2.1.3   

> devtools::session_info()
Session info ------------------------------------------------------------------
 setting  value                       
 version  R version 3.5.1 (2018-07-02)
 system   x86_64, linux-gnu           
 ui       X11                         
 language (EN)                        
 collate  en_US.UTF-8                 
 tz       Europe/Brussels             
 date     2020-01-10                  

Packages ----------------------------------------------------------------------
 package    * version date       source        
 backports    1.1.5   2019-10-02 CRAN (R 3.5.1)
 base       * 3.5.1   2019-08-28 local         
 bit          1.1-14  2018-05-29 CRAN (R 3.5.1)
 bit64        0.9-7   2017-05-08 CRAN (R 3.5.1)
 blob         1.2.0   2019-07-09 CRAN (R 3.5.1)
 compiler     3.5.1   2019-08-28 local         
 crayon       1.3.4   2017-09-16 CRAN (R 3.5.1)
 datasets   * 3.5.1   2019-08-28 local         
 DBI          1.0.0   2018-05-02 CRAN (R 3.5.1)
 devtools     1.13.6  2018-06-27 CRAN (R 3.5.1)
 digest       0.6.15  2018-01-28 CRAN (R 3.5.1)
 graphics   * 3.5.1   2019-08-28 local         
 grDevices  * 3.5.1   2019-08-28 local         
 hms          0.4.2   2018-03-10 CRAN (R 3.5.1)
 memoise      1.1.0   2017-04-21 CRAN (R 3.5.1)
 methods    * 3.5.1   2019-08-28 local         
 odbc       * 1.2.1   2019-12-05 CRAN (R 3.5.1)
 pbdZMQ       0.3-3   2018-05-05 CRAN (R 3.5.1)
 pillar       1.4.2   2019-06-29 CRAN (R 3.5.1)
 pkgconfig    2.0.2   2018-08-16 CRAN (R 3.5.1)
 Rcpp         1.0.3   2019-11-08 CRAN (R 3.5.1)
 rlang        0.4.1   2019-10-24 CRAN (R 3.5.1)
 rstudioapi   0.7     2017-09-07 CRAN (R 3.5.1)
 stats      * 3.5.1   2019-08-28 local         
 tibble       2.1.3   2019-06-06 CRAN (R 3.5.1)
 utils      * 3.5.1   2019-08-28 local         
 vctrs        0.2.0   2019-07-05 CRAN (R 3.5.1)
 withr        2.1.2   2018-03-15 CRAN (R 3.5.1)
 zeallot      0.1.0   2018-01-28 CRAN (R 3.5.1)

[jimhester]

Is it possible that the odbc package was compiled against iODBC rather than unixODBC? Try reinstalling it and look at the PKG_CFLAGS and PKG_LIBS lines and make sure they are using unixODBC.

The other thing you might try is putting the diver information in a user level config rather than a server one.

Another thing would be updated your version of unixODBC. 2.3.1 is over 8 years old, it was released in November of 2011.

[eylenth]

I see the following PKG_CFLAGS and PKG_LIBS when reinstalling odbc

Found odbc_config cflags and libs!
PKG_CFLAGS=-DHAVE_UNISTD_H -DHAVE_PWD_H -DHAVE_SYS_TYPES_H -DHAVE_LONG_LONG -DSIZEOF_LONG_INT=8 -I/usr/include 
PKG_LIBS=-L/usr/lib64 -lodbc

Putting the driver information at user level did not work.

I will try to update the unixODBC driver, to see if that works

[eylenth]

I found a solution.
using unixODBC/2.3.1 or unixODBC/2.3.7 didn't matter.

I have set the following environment variable:
ODBCSYSINI=/
and the odbc::odbcListDrivers() function is now be able to find the drivers in my R session.

[jimhester]

Glad you have worked out the solution!

[BilboBaagins]

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

[driapitek]

Thank you a lot, BilboBaagins!

[lollo-higg]

I tried all the above solutions and more, to no avail. When I installed the development version using devtools::install_github("r-dbi/odbc") it worked. Hooray!

[c0chi]

It worked for me! Thanks!
(I'm using MacOS Ventura 13.3.1, M2 chip)

I'd tried everything but the only solution for me was to set ODBCSYSINI="/"

It wasn't necessary to create or edit /Library/ODBC/odbcinst.ini or /Library/ODBC/odbc.ini.
It wasn't necessary to "sudo ln -s /opt/homebrew/etc/odbcinst.ini /usr/local/etc/odbcinst.ini"
It doesn't matter openssl version.
Of course, make sure the homebrew path points to "/opt/homebrew". (You can simply check in the terminal with > printenv )

Here are two other interesting solutions that didn't work for me. I leave it just in case.
https://solutions.posit.co/connections/db/best-practices/drivers/?_gl=1*10rwzxh*_ga*OTExMjE4MzQ0LjE2ODA4NzA4NjM.*_ga_2C0WZ1JHG0*MTY4MDk2MDI1NS4yLjEuMTY4MDk2MDMxMS4wLjAuMA..#macos-aarch64

[baderstine]

For MSSQL driver install on Intel Mac (Ventura) and R 4.3.1, I created the symlinks mentioned under Troubleshooting, but odbc::odbcListDrivers() still returned an empty table.

My solution:

1. Find installed paths.
   Run odbcinst -j to list the paths for installed drivers:

<username@machine> % odbcinst -j
unixODBC 2.3.12
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/<username>/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

2. Override R's ODBC Driver Path:
   Following the POSIT Instructions for MacOS aarch64 I edited my .Renviron file (nano /Users/<username>/.Renviron) to add the line: ODBCSYSINI=/etc I know that the /etc folder contains a symlink to /usr/local/etc/odbcinst.ini because I created the symlink following the Troubleshooting steps in the original article.

3. Restart RStudio.
   Now odbc::odbcListDrivers() lists the same drivers as are found in /usr/local/etc/odbcinst.ini.

[yaydede]

@baderstine Thanks for this post, after searching the answer everywhere, your solution worked very well in my new M3 Max.