Installing and Configuring Drivers

Installation

For Unix and MacOS, ODBC drivers should be compiled against unixODBC. Drivers compiled against iODBC may also work, but are not fully supported.

After installation of the driver manager and driver, you will have to register the driver in a odbcinst.ini file for it to appear in odbc::odbcListDrivers().

## Install the unixODBC library
brew install unixodbc

## Microsoft SQL Server ODBC drivers
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install msodbcsql17 mssql-tools

## SQL Server ODBC drivers (Free TDS)
brew install freetds

## PostgreSQL ODBC drivers
brew install psqlodbc

## MySQL ODBC drivers (and database)
brew install mysql
brew install mariadb-connector-odbc

## SQLite ODBC drivers
brew install sqliteodbc

## Install the unixODBC library
apt-get install unixodbc unixodbc-dev

## SQL Server ODBC Drivers (Free TDS)
apt-get install tdsodbc

## PostgreSQL ODBC ODBC Driversz
apt-get install odbc-postgresql

## MySQL ODBC Drivers
apt-get install libmyodbc

## SQLite ODBC Drivers
apt-get install libsqliteodbc

Driver configuration

On Windows, driver config is managed by the operating system, and you don’t generally need to edit any configuration files directly. On MacOS and Linux, however, the driver config is managed by unixODBC, and depending on how the driver is installed, it may or may not be automatically configured.

You can see which drivers are configured by running odbcListDrivers(). If the driver you want is not installed, you will need to edit odbcinst.ini. You can use odbcinst -j (provided by unixODBC) to find out where this file lives.

The basic form of this file is something like this:

[PostgreSQL Driver]
Driver          = /usr/local/lib/psqlodbcw.so

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

i.e., a mapping between driver names, and the location of the driver file. The driver name is what you pass to odbc::odbc() when you connect, e,g:

library(DBI)
con1 <- dbConnect(odbc::odbc(), driver = "PostgreSQL Driver")
con2 <- dbConnect(odbc::odbc(), driver = "SQLite Driver")

If installing the driver did not automatically update this file, you’ll need to update it yourself. You’ll need to figure out where the driver library was installed, using something like brew list {drivername} or dpkg-query -L {drivername}.

Data source configuration

It’s also possible to configure named data sources, so instead of typing this:

library(DBI)
con <- dbConnect(
  odbc::odbc(),
  driver = "PostgreSQL Driver",
  database = "test_db",
  username = "postgres",
  password = Sys.getenv("PASSWORD"),
  host = "localhost",
  port = 5432
)

You can type:

con <- dbConnect(odbc::odbc(), dsn = "test_db")

On Windows, you can set up a data source by using the ODBC Data Source Administrator application is used to manage ODBC data sources on Windows. On Linux and Mac, you’ll need to edit odbc.ini (again finding the location with odbcinst -j). Each data source is defined in a separate section (defined by [name]]), with each paramter going on its own line, e.g.:

[test_db]
driver     = PostgreSQL Driver
database   = test_db
username   = postgres
password   = password
host       = localhost
port       = 5432

The driver entry represents the name of the driver defined in odbcinst.ini. You can see all currently defined data sources by running odbcListDataSources().

Debugging driver and data source configurations

The odbc package may have trouble locating your driver and data source configurations. If you find that odbcListDrivers() and odbcListDataSources() report no configurations, check the output of odbcListConfig() next. While odbcListDrivers() and odbcListConfig() interface with the unixodbc driver manager through nanodbc—the tool that the odbc package uses to interface with the ODBC API—odbcListConfig() interfaces with unixODBC directly. odbcListConfig() will show where unixODBC is looking for your configurations.

• If the files listed in odbcListConfig() are at a location other than where you’ve configured drivers and data sources, either 1) configure the listed files as instructed above, or 2) change the folder where ODBC should look for configurations using the ODBCSYSINI environmental variable. See the following section on setting ODBCSYSINI.

• If the files listed in odbcListConfig() are where you expected them to be and appear complete, the odbc package may have had trouble interfacing with the unixODBC driver manager through nanodbc. To remedy this, first run dirname(odbcListConfig()[1]) and note its output. Then, follow the instructions in the section below, replacing "some/folder" with the noted output. If this doesn’t resolve the issue, try to build the odbc package from source, e.g. with devtools::install_github("r-dbi/odbc").