This vignette outlines how to install common ODBC drivers andconfigure them with a driver manager.
For Unix and MacOS, ODBC drivers should be compiled againstunixODBC. Drivers compiled againstiODBCmay also work, butare not fully supported.
After installation of the driver manager and driver, you will have toregister the driver in aodbcinst.ini file for it to appearinodbc::odbcListDrivers().
Windows is bundled with ODBC libraries. However, drivers for eachdatabase need to be installed separately. Windows ODBC drivers typicallyinclude an installer that needs to be run and will install the driver tothe proper locations.
homebrew can be used to easily installdatabase drivers on MacOS.
The UnixODBC driver manager is required for all databases:
## Install the unixODBC librarybrew install unixodbcMost common database drivers can be installed with homebrew. Somedrivers are only available from the database vendor.
## Microsoft SQL Server ODBC driversbrew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-releasebrew updatebrew install msodbcsql17 mssql-tools## SQL Server ODBC drivers (Free TDS)brew install freetds## PostgreSQL ODBC driversbrew install psqlodbc## MySQL ODBC drivers (and database)brew install mysqlbrew install mariadb-connector-odbc## SQLite ODBC driversbrew install sqliteodbcSome driver installations will not automatically configure the driverwith the driver manager. See the“Connecting to aDatabase” section below to learn more about configuring drivers anddata sources.
apt-get can be used toeasily 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 libraryapt-get install unixodbc unixodbc-devFor SQL Server on Linux it is recommended you use theMicrosoftDrivers. The code below will install some common drivers:
## SQL Server ODBC Drivers (Free TDS)apt-get install tdsodbc## PostgreSQL ODBC ODBC Driverszapt-get install odbc-postgresql## MySQL ODBC Driversapt-get install libmyodbc## SQLite ODBC Driversapt-get install libsqliteodbcOn Windows, driver config is managed by the operating system, and youdon’t generally need to edit any configuration files directly. On MacOSand Linux, however, the driver config is managed by unixODBC, anddepending on how the driver is installed, it may or may not beautomatically configured.
You can see which drivers are configured by runningodbcListDrivers(). If the driver you want is not installed,you will need to editodbcinst.ini. You can useodbcinst -j (provided by unixODBC) to find out where thisfile 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.dylibi.e., a mapping between driver names, and the location of the driverfile. The driver name is what you pass toodbc::odbc() whenyou 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 thedriver library was installed, using something likebrew list {drivername} ordpkg-query -L {drivername}.
It’s also possible to configure named data sources, so instead oftyping 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 theODBCData Source Administrator application is used to manage ODBC datasources on Windows. On Linux and Mac, you’ll need to editodbc.ini (again finding the location withodbcinst -j). Each data source is defined in a separatesection (defined by[name]]), with each paramter going onits own line, e.g.:
[test_db]driver= PostgreSQL Driverdatabase= test_dbusername= postgrespassword= passwordhost=localhostport=5432Thedriver entry represents the name of the driverdefined inodbcinst.ini. You can see all currently defineddata sources by runningodbcListDataSources().
The odbc package may have trouble locating your driver and datasource configurations. If you find thatodbcListDrivers()andodbcListDataSources() report no configurations, checkthe output ofodbcListConfig() next. WhileodbcListDrivers() andodbcListConfig()interface with the unixodbc driver manager through nanodbc—the tool thatthe odbc package uses to interface with the ODBCAPI—odbcListConfig() interfaces with unixODBC directly.odbcListConfig() will show where unixODBC is looking foryour configurations.
If the files listed inodbcListConfig() are at alocation other than where you’ve configured drivers and data sources,either 1) configure the listed files as instructed above, or 2) changethe folder where ODBC should look for configurations using theODBCSYSINI environmental variable. Seethe following section on settingODBCSYSINI.
If the files listed inodbcListConfig() are whereyou expected them to be and appear complete, the odbc package may havehad trouble interfacing with the unixODBC driver manager throughnanodbc. To remedy this, first rundirname(odbcListConfig()[1]) and note its output. Then,follow the instructions inthe section below,replacing"some/folder" with the noted output. If thisdoesn’t resolve the issue, try to build the odbc package from source,e.g. withdevtools::install_github("r-dbi/odbc").
ODBCSYSINITheODBCSYSINI environmental variable controls the pathwhere unixODBC and the odbc package will look for configuration files.SettingSys.setenv(ODBCSYSINI = "some/folder") means thatyour configuration files should be located at"/my/folder/odbc.ini" and"/my/folder/odbcinst.ini". Be sure to setODBCSYSINIbefore loading the odbc package. Thatis:
Sys.setenv(ODBCSYSINI = "some/folder")library(odbc)odbcListDrivers() andodbcListDataSources() againIf settingODBCSYSINI resolves the issue you’ve noted,you may want to set that environmental variable every time you start R.To do so, add an entry likeODBCSYSINI = some/folder toyour~/.Renviron file, which contains environmentalvariables that are set every time an R session is started. To open thatfile using R, runusethis::edit_r_environ().
Once you’ve edited~/.Renviron, you’ll need to save thefile and restart R for changes to take effect.