# odbc

The goal of the odbc package is to provide a
[DBI](https://dbi.r-dbi.org/)-compliant interface to
[ODBC](https://learn.microsoft.com/en-us/sql/odbc/microsoft-open-database-connectivity-odbc)
drivers. This makes it easy to connect databases such as [SQL
Server](https://www.microsoft.com/en-us/sql-server/), Oracle,
[Databricks](https://www.databricks.com/), and Snowflake.

The odbc package is an alternative to
[RODBC](https://cran.r-project.org/package=RODBC) and
[RODBCDBI](https://cran.r-project.org/package=RODBCDBI) packages, and is
typically much faster. See
[`vignette("benchmarks")`](https://odbc.r-dbi.org/articles/benchmarks.md)
to learn more.

## Overview

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

\<img src=“man/figures/whole-game.png” alt=“A diagram containing four
boxes with arrows linking each pointing left to right. The boxes read,
in order,”R interface,” “driver manager,” “ODBC driver,” and “DBMS.” The
left-most box, R interface, contains three smaller components, labeled
“dbplyr,” “DBI,” and “odbc.”” width=“1489” /\>

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](https://docs.posit.co/pro-drivers/).

Drivers are managed by a **driver manager**, which is responsible for
configuring driver locations, and optionally named **data sources** that
describe how to connect to a specific database. Windows is bundled with
a driver manager, while MacOS and Linux require installation of
[unixODBC](https://www.unixodbc.org/). Drivers often require some manual
configuration; see
[`vignette("setup")`](https://odbc.r-dbi.org/articles/setup.md) for
details.

In the **R interface**, the [DBI package](https://dbi.r-dbi.org/)
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](https://nanodbc.github.io/nanodbc/) C++ library. To interface
with DBMSs using R and odbc:

\<img src=“man/figures/r-interface.png” alt=“A high-level workflow for
using the R interface in 3 steps. In step 1, configure drivers and data
sources, the functions odbcListDrivers() and odbcListDataSources() help
to interface with the driver manager. In step 2, the dbConnect()
function, called with the first argument odbc(), connects to a database
using the specified ODBC driver to create a connection object”con.”
Finally, in step 3, that connection object can be passed to various
functions to retrieve information on database structure, iteratively
develop queries, and query data objects.” width=“1302” /\>

You might also use the [dbplyr package](https://dbplyr.tidyverse.org/)
to automatically generate SQL from your dplyr code.

## Installation

Install the latest release of odbc from CRAN with the following code:

``` r

install.packages("odbc")
```

To get a bug fix or to use a feature from the development version, you
can install the development version of odbc from GitHub:

``` r

# install.packages("pak")
pak::pak("r-dbi/odbc")
```

## Usage

To use odbc, begin by creating a database connection, which might look
something like this:

``` r

library(DBI)

con <- dbConnect(
  odbc::odbc(),
  driver = "SQL Server",
  server = "my-server",
  database = "my-database",
  uid = "my-username",
  pwd = rstudioapi::askForPassword("Database password")
)
```

(See [`vignette("setup")`](https://odbc.r-dbi.org/articles/setup.md) for
examples of connecting to a variety of databases.)

[`dbListTables()`](https://dbi.r-dbi.org/reference/dbListTables.html) is
used for listing all existing tables in a database.

``` r

dbListTables(con)
```

[`dbReadTable()`](https://dbi.r-dbi.org/reference/dbReadTable.html) will
read a full table into an R
[`data.frame()`](https://rdrr.io/r/base/data.frame.html).

``` r

data <- dbReadTable(con, "flights")
```

[`dbWriteTable()`](https://dbi.r-dbi.org/reference/dbWriteTable.html)
will write an R [`data.frame()`](https://rdrr.io/r/base/data.frame.html)
to an SQL table.

``` r

dbWriteTable(con, "iris", iris)
```

[`dbGetQuery()`](https://dbi.r-dbi.org/reference/dbGetQuery.html) will
submit a SQL query and fetch the results:

``` r

df <- dbGetQuery(
  con,
  "SELECT flight, tailnum, origin FROM flights ORDER BY origin"
)
```

It is also possible to submit the query and fetch separately with
[`dbSendQuery()`](https://dbi.r-dbi.org/reference/dbSendQuery.html) and
[`dbFetch()`](https://dbi.r-dbi.org/reference/dbFetch.html). This allows
you to use the `n` argument to
[`dbFetch()`](https://dbi.r-dbi.org/reference/dbFetch.html) to iterate
over results that would otherwise be too large to fit in memory.

# Package index

## DBI methods

- [`odbc()`](https://odbc.r-dbi.org/reference/dbConnect-OdbcDriver-method.md)
  [`dbConnect(`*`<OdbcDriver>`*`)`](https://odbc.r-dbi.org/reference/dbConnect-OdbcDriver-method.md)
  : Connect to a database via an ODBC driver
- [`dbListTables(`*`<OdbcConnection>`*`)`](https://odbc.r-dbi.org/reference/dbListTables-OdbcConnection-method.md)
  : List remote tables and fields for an ODBC connection
- [`dbWriteTable(`*`<OdbcConnection>`*`,`*`<character>`*`,`*`<data.frame>`*`)`](https://odbc.r-dbi.org/reference/DBI-tables.md)
  [`dbWriteTable(`*`<OdbcConnection>`*`,`*`<Id>`*`,`*`<data.frame>`*`)`](https://odbc.r-dbi.org/reference/DBI-tables.md)
  [`dbWriteTable(`*`<OdbcConnection>`*`,`*`<SQL>`*`,`*`<data.frame>`*`)`](https://odbc.r-dbi.org/reference/DBI-tables.md)
  [`dbAppendTable(`*`<OdbcConnection>`*`)`](https://odbc.r-dbi.org/reference/DBI-tables.md)
  [`sqlCreateTable(`*`<OdbcConnection>`*`)`](https://odbc.r-dbi.org/reference/DBI-tables.md)
  : Convenience functions for reading/writing DBMS tables

## Database-specific helpers

- [`databricks()`](https://odbc.r-dbi.org/reference/databricks.md)
  [`dbConnect(`*`<DatabricksOdbcDriver>`*`)`](https://odbc.r-dbi.org/reference/databricks.md)
  : Helper for Connecting to Databricks via ODBC
- [`Microsoft SQL Server-class`](https://odbc.r-dbi.org/reference/SQLServer.md)
  [`dbUnquoteIdentifier,Microsoft SQL Server,SQL-method`](https://odbc.r-dbi.org/reference/SQLServer.md)
  [`isTempTable,Microsoft SQL Server,character-method`](https://odbc.r-dbi.org/reference/SQLServer.md)
  [`isTempTable,Microsoft SQL Server,SQL-method`](https://odbc.r-dbi.org/reference/SQLServer.md)
  [`dbExistsTable,Microsoft SQL Server,character-method`](https://odbc.r-dbi.org/reference/SQLServer.md)
  [`dbListTables,Microsoft SQL Server-method`](https://odbc.r-dbi.org/reference/SQLServer.md)
  [`dbExistsTable,Microsoft SQL Server,Id-method`](https://odbc.r-dbi.org/reference/SQLServer.md)
  [`dbExistsTable,Microsoft SQL Server,SQL-method`](https://odbc.r-dbi.org/reference/SQLServer.md)
  [`odbcConnectionSchemas,Microsoft SQL Server-method`](https://odbc.r-dbi.org/reference/SQLServer.md)
  [`sqlCreateTable,Microsoft SQL Server-method`](https://odbc.r-dbi.org/reference/SQLServer.md)
  [`odbcConnectionColumns,Microsoft SQL Server,character-method`](https://odbc.r-dbi.org/reference/SQLServer.md)
  [`odbcConnectionColumns,Microsoft SQL Server,SQL-method`](https://odbc.r-dbi.org/reference/SQLServer.md)
  : SQL Server
- [`sqlCreateTable(`*`<Oracle>`*`)`](https://odbc.r-dbi.org/reference/Oracle.md)
  [`odbcConnectionTables(`*`<Oracle>`*`,`*`<character>`*`)`](https://odbc.r-dbi.org/reference/Oracle.md)
  : Oracle
- [`redshift()`](https://odbc.r-dbi.org/reference/redshift.md)
  [`dbConnect(`*`<RedshiftOdbcDriver>`*`)`](https://odbc.r-dbi.org/reference/redshift.md)
  : Helper for Connecting to Redshift via ODBC
- [`snowflake()`](https://odbc.r-dbi.org/reference/snowflake.md)
  [`dbConnect(`*`<SnowflakeOdbcDriver>`*`)`](https://odbc.r-dbi.org/reference/snowflake.md)
  : Helper for connecting to Snowflake via ODBC

## ODBC configuration

- [`odbc()`](https://odbc.r-dbi.org/reference/dbConnect-OdbcDriver-method.md)
  [`dbConnect(`*`<OdbcDriver>`*`)`](https://odbc.r-dbi.org/reference/dbConnect-OdbcDriver-method.md)
  : Connect to a database via an ODBC driver
- [`dbExistsTableForWrite(`*`<Snowflake>`*`,`*`<character>`*`)`](https://odbc.r-dbi.org/reference/driver-Snowflake.md)
  : Connecting to Snowflake via ODBC
- [`odbcDataType()`](https://odbc.r-dbi.org/reference/odbcDataType.md) :
  Return the corresponding ODBC data type for an R object
- [`odbcListColumns()`](https://odbc.r-dbi.org/reference/odbcListColumns.md)
  : List columns in an object.
- [`odbcListConfig()`](https://odbc.r-dbi.org/reference/odbcListConfig.md)
  [`odbcEditDrivers()`](https://odbc.r-dbi.org/reference/odbcListConfig.md)
  [`odbcEditSystemDSN()`](https://odbc.r-dbi.org/reference/odbcListConfig.md)
  [`odbcEditUserDSN()`](https://odbc.r-dbi.org/reference/odbcListConfig.md)
  : List locations of ODBC configuration files
- [`odbcListDataSources()`](https://odbc.r-dbi.org/reference/odbcListDataSources.md)
  : List Configured Data Source Names
- [`odbcListDrivers()`](https://odbc.r-dbi.org/reference/odbcListDrivers.md)
  : List Configured ODBC Drivers
- [`odbcListObjectTypes()`](https://odbc.r-dbi.org/reference/odbcListObjectTypes.md)
  : Return the object hierarchy supported by a connection.
- [`odbcListObjects()`](https://odbc.r-dbi.org/reference/odbcListObjects.md)
  : List objects in a connection.
- [`odbcPreviewObject()`](https://odbc.r-dbi.org/reference/odbcPreviewObject.md)
  : Preview the data in an object.
- [`odbcSetTransactionIsolationLevel()`](https://odbc.r-dbi.org/reference/odbcSetTransactionIsolationLevel.md)
  : Set the Transaction Isolation Level for a Connection
- [`isTempTable()`](https://odbc.r-dbi.org/reference/isTempTable.md) :
  Helper method used to determine if a table identifier is that of a
  temporary table.
- [`quote_value()`](https://odbc.r-dbi.org/reference/quote_value.md) :
  Quote special character when connecting

# Articles

### Articles

- [Installing and Configuring
  Drivers](https://odbc.r-dbi.org/articles/setup.md):
- [Benchmarks](https://odbc.r-dbi.org/articles/benchmarks.md):
- [Developing odbc](https://odbc.r-dbi.org/articles/develop.md):