Troubleshooting FreeTDS/ODBC --> SQL Connection issues in R

Question

I recently returned to work from maternity leave and some ODBC+R functionality that was running fine before is no longer working. I’ve tried for several days to try and figure out what’s going on with no luck, so I’m turning to Stackoverflow for help.

I’ve followed these instructions for settings up the FreeTDS driver and ODBC Connections:

• https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Mac-OSX
• https://github.com/r-dbi/odbc#installation

Running obdc -j in terminal reveals the following information:

unixODBC 2.3.11
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/langford/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

The following configuration files are located in the /usr/local/etc folder (some information has been obsfuscated):

freetds.conf:

[MYMSSQL]
host =  xxx.xxx.xxx.com
port = 1443
tds version = 7.3

odbc.ini:

[MYMSSQL]
Description=Test to SQLServer
Driver=FreeTDS
Servername= MYMSSQL

odbcinst.ini:

[FreeTDS]
Description=FreeTDS Driver for Linux & MSSQL
Driver=/usr/local/lib/libtdsodbc.so
Setup=/usr/local/lib/libtdsodbc.so
UsageCount=1

I feel pretty confident that things are working correctly. I run isql MYMSSQL myuser mypassword from the terminal and get the expected output:

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| echo [string]                         |
| quit                                  |
|                                       |
+---------------------------------------+

I am also able to run queries and get the correct output from my data base source. The issues arise when I try to connect via R (Rstudio, more specifically). For whatever reason, R is not identifying the FreeTDS driver.

install.packages('odbc')
library(odbc)
odbcListDrivers()

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

The connection string I'm attempting to use is as follows (with obfuscations):

connection <- dbConnect(odbc(),
                        server = 'xxx.xxx.xxx.com',
                        Driver = "FreeTDS",
                        Port = 1443,
                        UID = 'username', 
                        PWD = '1234abcd'
                        database = 'MYDATABASE')

The error in R is:

Error: nanodbc/nanodbc.cpp:1135: 00000: [unixODBC][Driver Manager]Can't open lib 'FreeTDS' : file not found

Computer Hardware Information:

• Processor: 2.3 GHz 8-Core Intel Core i9
• macOS: 13.5 (22G74)

R Environment Information:

• R Version: 4.3.1
• RStudio Version: 2023.06.1+524
• odbc Package Version: 1.3.5
• RODBC Package Version: 1.3-20
• DBI Package Version: 1.1.3

At this point, I’m not sure what other information would be helpful to know. Happy to include any environment variables etc. that you believe might be pertinent.

Sincerely, Disconnected

Answer 1

Ok, I got an answer thanks to a colleague! The key is to install the RODBC and odbc packages from source.

remove.packages("RODBC")
install.packages("RODBC",type = "source")

remove.packages("odbc")
install.packages("odbc",type = "source")

I'm having trouble tracking down an verified document anywhere, but the (new?) default ODBC manager for RODBC and odbc is iODBC (which I don't even have installed). Setting type = "source" sets the manager to unixODBC.

Perhaps someone can help document/clarify this behavior.