odbc 1.2.0 is now on CRAN! The odbc package provides a DataBase Interface (DBI) to Open DataBase Connectivity (ODBC) drivers. ODBC drivers exist for nearly all widely used databases, including SQL Server, Oracle, MySQL, PostgreSQL, SQLite and others. RStudio also provides a set of Professional ODBC Drivers free of charge for customers who have RStudio Server Pro, RStudio Connect, or Shiny Server Pro. In addition, db.rstudio.com has extensive resources on connecting to and working with databases from R. The odbc package allows you to query these databases from within R. Get the latest version with:
install.packages("odbc")
Generally you will not need to load the odbc package with library(), instead you can load the DBI package and use odbc::odbc() to reference the driver when connecting. e.g.
library(DBI)
con <- dbConnect(odbc::odbc(), "MicrosoftSQLServer", UID = "SA", PWD = "Password12")
The biggest improvements in this release are related to querying within schemas, an API for immediate execution, and a new timezone_out parameter to control the displayed time of timezones returned by the query.
See the
change log for the full set of changes for this release.
Querying schemas
Use DBI::Id() reference a table within a schema.
This will allow odbc to handle any quoting necessary for your particular database. e.g. you can write to a table named my.iris even if there is a schema named my.
library(DBI)
con <- dbConnect(odbc::odbc(), "MicrosoftSQLServer", UID = "SA", PWD = "Password12")
my_tbl <- Id(schema = "my", table = "my.iris")
dbWriteTable(con, my_tbl, iris)
tbl <- dbReadTable(con, my_tbl)
# The quoting for "my"."my.iris" is done automatically when
# using DBI::Id()
tbl2 <- dbGetQuery(con, 'SELECT * FROM "my"."my.iris"')
all.equal(tbl, tbl2)
#> TRUE
This feature has actually existed for a number of odbc releases, but due to driver inconsistencies was not working properly on SQL Server, which is now fixed.
Immediate/direct execution
The odbc package uses
Prepared Statements to compile the query once and reuse it, allowing large or repeated queries to be more efficient.
However, prepared statements can actually perform worse in some cases, such as many different small queries that are all only executed once.
Because of this the odbc package now also supports direct queries by specifying immediate = TRUE.
# This will use a prepared statement
dbGetQuery("SELECT * from iris")
# This will execute the statement directly
dbGetQuery("SELECT * from iris", immediate = TRUE)
Timezone display
The odbc package has historically imported date times with a UTC timezone.
This ensures that the same code will produce the same output regardless of the local time.
However this can confuse users, particularly if the server timezones are stored or displayed in a non-UTC timezone.
Because of this, the odbc package now supports a timezone_out parameter, which allows users to set the timezone the times should be displayed in.
Setting this to your local timezone, or the timezone of the database may reduce this confusion.
library(DBI)
# Create a new connection, specifying a timezone_out of UTC (this is the default)
con_utc <- dbConnect(odbc::odbc(), "MicrosoftSQLServer", UID="SA", PWD="Password12", timezone_out = "UTC")
# Create a table with the current timestamp as a value
dbExecute(con_utc, "SELECT CURRENT_TIMESTAMP AS x INTO now")
#> [1] 1
# Read that table, the time is displayed in UTC
res_utc <- dbReadTable(con_utc, "now")
res_utc
#> x
#> 1 2019-11-29 15:03:59
# Create another connection, this time with the timezone in United States eastern time
con_est <- dbConnect(odbc::odbc(), "MicrosoftSQLServer", UID="SA", PWD="Password12", timezone_out = "US/Eastern")
# Read the same table again, this time the time is displayed in EST
res_est <- dbReadTable(con_est, "now")
res_est
#> x
#> 1 2019-11-29 10:03:59
# These two times equal the same time point, the only difference is the display
res_utc == res_est
#> Warning in check_tzones(e1, e2): 'tzone' attributes are inconsistent
#> x
#> [1,] TRUE
# You can convert res_utc to res_est by changing the `tzone` attribute
attr(res_utc$x, "tzone") <- "US/Eastern"
res_utc
#> x
#> 1 2019-11-29 10:03:59
There were a number of additional features and bug fixes in this version, see the change log for details.
Acknowledgements
Thanks to Xianying Tan, James Blair and Kirill Müller who all submitted multiple pull requests with improvements, and to all the 114 GitHub contributors who have opened issues or submitted code improvements to help make this release happen!
@ajholguin, @ammarelsh, @anchal02, @andreaspano, @andrewsali, @arestrom, @aryoda, @berkorbay, @blairj09, @blmayer, @cboettig, @cdumoulin-usgs, @CerebralMastication, @cfisher5, @chrishaug, @ChristianAlvaradoAP, @cnolanminich, @colearendt, @copernican, @crossxwill, @david-cortes, @davidchall, @DavisVaughan, @detule, @dhaycraft, @dirkschumacher, @dpprdan, @duncanrellis, @edgararuiz, @elbamos, @elben10, @etienne-s, @etiennebr, @felipegerard, @foundinblank, @Freekers, @ghost, @hadley, @halpo, @happyshows, @harrismcgehee, @hiltonmbr, @hoxo-m, @iamsaini87, @its-gazza, @JarkoDubbeldam, @jasperDD, @javierluraschi, @jeroenhabets, @jimhester, @jrisi256, @jsonbecker, @jtelleria, @jtelleriar, @juliasilge, @kbzsl, @kerry-ja, @kevinushey, @khotilov, @KimmoMW, @kjaanson, @kohleth, @krlmlr, @lee170, @leungi, @madlogos, @martindut, @mateusz1981, @matthijsvanderloos, @maverix13, @maxPunck, @mbfsdatascience, @md0u80c9, @meztez, @mhsilvav, @mingwandroid, @mlaviolet, @mmastand, @move[bot], @muranyia, @nwstephens, @nzbart, @patperu, @PatWilson, @pchiacchiari-coatue, @pgensler, @pythiantech, @quartin, @r2evans, @ralsouza, @renkun-ken, @revodavid, @ronblum, @rtgdk, @s-fleck, @satvenkat, @scmck17, @sebschub, @shapenaji, @shizidushu, @shrektan, @smingerson, @stlouiso, @timabe, @totalgit74, @TTudino, @UpsideDownRide, @versipellis, @vh-d, @vpanfilov, @warnes, @washcycle, @wibeasley, and @yutannihilation