R (RODBC and odbc)

Connect from R via either RODBC (legacy, simpler) or odbc (modern, recommended).

Syntax

DBI::dbConnect(odbc::odbc(), dsn = "DeltaForge")

Description

## Two packages | Package | Status | Notes | |---|---|---| | `odbc` (with `DBI`) | Recommended | Modern, type-aware, integrates with the tidyverse. Built on top of nanodbc. | | `RODBC` | Legacy, still works | Older API, simpler but limited type handling. Maintained but no longer actively developed. | New code should use `odbc`. RODBC is documented here because many existing scripts still use it. ## Install ``` install.packages("odbc") install.packages("DBI") ``` On Linux the package needs unixODBC headers (`libodbc1-dev` on Debian-family, `unixODBC-devel` on RHEL-family). On macOS the `odbc` package builds against unixODBC by default; if you have iODBC instead, set the environment variable `ODBC_CONFIG=/path/to/iodbc-config` before installing. ## Connect `dbConnect` takes the same connection-string keys you would put in any other ODBC client: `dsn`, `uid`, `pwd`, `driver`, `server`, `encrypt`, etc. Connection-string keys arrive at the driver verbatim; differences in capitalisation between R's preference (lowercase) and the driver's (PascalCase) are handled by the parser's case-insensitive match. ## Reading into a tibble ``` library(odbc) library(dplyr) cn <- dbConnect(odbc::odbc(), dsn = "DeltaForge") fact_orders <- tbl(cn, in_schema("analytics.marts", "fact_orders")) fact_orders %>% filter(order_date >= as.Date("2026-01-01")) %>% collect() ``` `dbplyr` translates the R verbs to SQL on the engine; `collect()` pulls the result into a local tibble. For analytical work this push-down avoids materialising large intermediates in R. ## Parameter binding ``` dbGetQuery(cn, "SELECT * FROM customers WHERE id = ? AND status = ?", params = list(42L, "active")) ``` R's `odbc` package uses positional `?` placeholders. For Decimal parameters with high precision, supply them as character strings to preserve precision; coercion through `numeric` truncates. ## Performance The `odbc` package fetches in batches and assembles into a tibble using R's vector primitives. For wide / long results, this is significantly faster than RODBC's row-by-row build. If the query result is very large (millions of rows), prefer Arrow: ``` library(arrow) result <- as_arrow_table(dbGetQuery(cn, "SELECT ...")) ``` This keeps the data columnar all the way to downstream analytics.

Examples

# Modern: DBI + odbc package
library(DBI)
library(odbc)
cn <- dbConnect(odbc::odbc(),
                dsn = "DeltaForge",
                uid = "alice@example.com",
                pwd = rstudioapi::askForPassword("Database password:"))
df <- dbGetQuery(cn, "SELECT * FROM analytics.marts.fact_orders LIMIT 1000")
dbDisconnect(cn)

# Legacy: RODBC
library(RODBC)
ch <- odbcConnect("DeltaForge", uid = "alice@example.com", pwd = "hunter2")
df <- sqlQuery(ch, "SELECT * FROM analytics.marts.fact_orders LIMIT 1000")
odbcClose(ch)

Pitfalls

• RODBC's character handling on Windows can mangle non-ASCII strings if the locale is not set correctly. The `odbc` package handles UTF-8 end-to-end and avoids this entirely; another reason to prefer it.
• On macOS, the default install of the `odbc` package may link against unixODBC even if iODBC is also present. If you installed the Delta Forge driver via the `.pkg` (which registers with iODBC), the R side will not see it. Either reinstall the R package against iODBC or symlink the driver into unixODBC's `odbcinst.ini`.
• Long-running R sessions hold an open connection. If the auth provider's session expires, the next query fails with `28000`. Wrap dbGetQuery in `tryCatch` and reconnect on failure for unattended scripts.

See Also

• ODBC_BI_PYTHON
• ODBC_DATATYPE_MAPPING