odbc {odbc}

Connect to a database via an ODBC driver

Description

The dbConnect() method documented here is invoked when DBI::dbConnect() is called with the first argument odbc(). Connecting to a database via an ODBC driver is likely the first step in analyzing data using the odbc package; for an overview of package concepts, see the Overview section below.

Usage

odbc()

## S4 method for signature 'OdbcDriver'
dbConnect(
  drv,
  dsn = NULL,
  ...,
  timezone = "UTC",
  timezone_out = "UTC",
  encoding = "",
  bigint = c("integer64", "integer", "numeric", "character"),
  timeout = 10,
  driver = NULL,
  server = NULL,
  database = NULL,
  uid = NULL,
  pwd = NULL,
  dbms.name = NULL,
  attributes = NULL,
  interruptible = getOption("odbc.interruptible", interactive()),
  .connection_string = NULL
)

Arguments

drv

An OdbcDriver, from odbc().

dsn

The data source name. For currently available options, see the name column of odbcListDataSources() output.

...

Additional ODBC keywords. These will be joined with the other arguments to form the final connection string.

Note that ODBC parameter names are case-insensitive so that (e.g.) DRV and drv are equivalent. Since this is different to R and a possible source of confusion, odbc will error if you supply multiple arguments that have the same name when case is ignored.

Any values containing a leading or trailing space, a =, ⁠;⁠, ⁠{⁠, or ⁠}⁠ are likely to require quoting. Use quote_value() for a fairly standard approach or see your driver documentation for specifics.

timezone

The server time zone. Useful if the database has an internal timezone that is not 'UTC'. If the database is in your local timezone, set this argument to Sys.timezone(). See OlsonNames() for a complete list of available time zones on your system.

timezone_out

The time zone returned to R. If you want to display datetime values in the local timezone, set to Sys.timezone().

encoding

The text encoding used on the Database. If the database is not using UTF-8 you will need to set the encoding to get accurate re-encoding. See iconvlist() for a complete list of available encodings on your system. Note strings are always returned UTF-8 encoded.

bigint

The R type that SQL_BIGINT types should be mapped to. Default is bit64::integer64, which allows the full range of 64 bit integers.

timeout

Time in seconds to timeout the connection attempt. Setting a timeout of Inf indicates no timeout. Defaults to 10 seconds.

driver

The ODBC driver name or a path to a driver. For currently available options, see the name column of odbcListDrivers() output.

server

The server hostname. Some drivers use Servername as the name for this argument. Not required when configured for the supplied dsn.

database

The database on the server. Not required when configured for the supplied dsn.

uid

The user identifier. Some drivers use username as the name for this argument. Not required when configured for the supplied dsn.

pwd

The password. Some drivers use password as the name for this argument. Not required when configured for the supplied dsn.

dbms.name

The database management system name. This should normally be queried automatically by the ODBC driver. This name is used as the class name for the OdbcConnection object returned from dbConnect(). However, if the driver does not return a valid value, it can be set manually with this parameter.

attributes

A list of connection attributes that are passed prior to the connection being established. See ConnectionAttributes.

interruptible

Logical. If TRUE calls to SQLExecute and SQLExecuteDirect can be interrupted when the user sends SIGINT ( ctrl-c ). Otherwise, they block. Defaults to TRUE in interactive sessions, and FALSE otherwise. It can be set explicitly either by manipulating this argument, or by setting the global option odbc.interruptible.

.connection_string

A complete connection string, useful if you are copy pasting it from another source. If this argument is used, any additional arguments will be appended to this string.

Connection strings

Internally, dbConnect() creates a connection string using the supplied arguments. Connection string keywords are driver-dependent; the arguments documented here are common, but some drivers may not accept them.

Alternatively to configuring DSNs and driver names with the driver manager, you can pass a complete connection string directly as the .connection_string argument. The Connection Strings Reference is a useful resource that has example connection strings for a large variety of databases.

Overview

The odbc package is one piece of the R interface to databases with support for ODBC:

The package supports any Database Management System (DBMS) with ODBC support. Support for a given DBMS is provided by an ODBC driver, which defines how to interact with that DBMS using the standardized syntax of ODBC and SQL. Drivers can be downloaded from the DBMS vendor or, if you're a Posit customer, using the professional drivers. To manage information about each driver and the data sources they provide access to, our computers use a driver manager. Windows is bundled with a driver manager, while MacOS and Linux require installation of one; this package supports the unixODBC driver manager.

In the R interface, the DBI package provides a front-end while odbc implements a back-end to communicate with the driver manager. The odbc package is built on top of the nanodbc C++ library.

Interfacing with DBMSs using R and odbc involves three high-level steps:

1. Configure drivers and data sources: the functions odbcListDrivers() and odbcListDataSources() help to interface with the driver manager.

2. Connect to a database: The dbConnect() function, called with the first argument odbc(), connects to a database using the specified ODBC driver to create a connection object.

3. Interface with connections: The resulting connection object can be passed to various functions to retrieve information on database structure (dbListTables()), iteratively develop queries (dbSendQuery(), dbColumnInfo()), and query data objects (dbFetch()).

Learn more

To learn more about databases:

• "Best Practices in Working with Databases" documents how to use the odbc package with various popular databases.

• The pyodbc "Drivers and Driver Managers" Wiki provides further context on drivers and driver managers.

• Microsoft's "Introduction to ODBC" is a thorough resource on the ODBC interface.