This vignette outlines how to install common ODBC drivers and configure them with a driver manager.
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()
.
Windows is bundled with ODBC libraries. However, drivers for each database need to be installed separately. Windows ODBC drivers typically include an installer that needs to be run and will install the driver to the proper locations.
homebrew can be used to easily install database drivers on MacOS.
The UnixODBC driver manager is required for all databases:
## Install the unixODBC library
brew install unixodbc
Most common database drivers can be installed with homebrew. Some drivers are only available from the database vendor.
## 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
Some driver installations will not automatically configure the driver with the driver manager. See the “Connecting to a Database” section below to learn more about configuring drivers and data sources.
apt-get can be used to easily install database drivers on Linux distributions which support it, such as Debian and Ubuntu.
The UnixODBC driver manager is required for all databases:
## Install the unixODBC library
apt-get install unixodbc unixodbc-dev
For SQL Server on Linux it is recommended you use the Microsoft Drivers. The code below will install some common drivers:
## 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
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}
.
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:
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()
.