Setting up ODBC Drivers
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
Windows is bundled with ODBC libraries; however, drivers for each database need to be installed separately. Windows ODBC drivers typically include an installer that must be run to install the drivers in the proper locations.
The ODBC Data Source Administrator application is used to manage ODBC data sources on Windows.
Install homebrew to install database drivers easily on MacOS
Install UnixODBC, which is required for all databases
# Install the unixODBC library brew install unixodbc
Install common DB drivers (optional)
# SQL Server ODBC Drivers (Free TDS) brew install freetds --with-unixodbc # PostgreSQL ODBC ODBC Drivers brew install psqlodbc # MySQL ODBC Drivers (and database) brew install mysql # SQLite ODBC Drivers brew install sqliteodbc
Setting up database connections
See the section with the same name in the Linux section.
Linux Debian / Ubuntu
The apt-get command can be used to install database drivers easily on Linux distributions that support it, such as Debian and Ubuntu.
- Install UnixODBC, which is required for all databases
# Install the unixODBC library apt-get install unixodbc unixodbc-dev
- Install common DB drivers (optional)
# SQL Server ODBC Drivers (Free TDS) apt-get install tdsodbc # PostgreSQL ODBC ODBC Drivers apt-get install odbc-postgresql # MySQL ODBC Drivers apt-get install libmyodbc # SQLite ODBC Drivers apt-get install libsqliteodbc
Setting up database connections
On MacOS and Linux, there are two separate text files that need to be edited. UnixODBC includes a command-line executable called
odbcinst, which can be used to query and modify the DSN files. However, these are plain text files you can also edit by hand if desired.
There are two different files used to set up the DSN information:
odbcinst.inidefines driver options
odbc.inidefines connection options
This file contains the driver information, particularly the name of the driver library. Multiple drivers can be specified in the same file.
[PostgreSQL Driver] Driver = /usr/local/lib/psqlodbcw.so [SQLite Driver] Driver = /usr/local/lib/libsqlite3odbc.dylib
This file contains the connection information, particularly the username, password, database and host information. The Driver line corresponds to the driver defined in
[PostgreSQL] Driver = PostgreSQL Driver Database = test_db Servername = localhost UserName = postgres Password = password Port = 5432 [SQLite] Driver = SQLite Driver Database=/tmp/testing
See also: unixODBC without the GUI for more information and examples.
The DSN configuration files can be defined globally for all users of the system, often at
/opt/local/etc/odbc.ini. The file location depends on what option was used when compiling unixODBC;
odbcinst -j can be used to find the exact location. Alternatively, the
ODBCSYSINI environment variable can be used to specify the location of the configuration files. Ex.
A local DSN file can also be used with the files
Connecting to a Database in R
Databases can be connected by specifying a connection string directly, or with DSN configuration files.
# Install the latest odbc release from CRAN: install.packages("odbc") # Or the the development version from GitHub: # install.packages(devtools) devtools::install_github("rstats-db/odbc")
Pass the connection parameters as arguments to the
con <- DBI::dbConnect(odbc::odbc(), driver = "PostgreSQL Driver", database = "test_db", UID = rstudioapi::askForPassword("Database user"), PWD = rstudioapi::askForPassword("Database password"), host = "localhost", port = 5432)
For database-specific settings, go to the Databases section in the menu and look for the page that matches the desired database type.
DSN Configuration files
ODBC configuration files are another option to specify connection parameters; they allow you to use a Data Source Name (DSN) to make it easier to connect to a database.
con <- dbConnect(odbc::odbc(), "PostgreSQL")
For more information about how DSN can be used, check out the following articles in this site: