1

I found no explicit guidance in DBI documentation on how to load the front-end (i.e. package:DBI) and the back-end (e.g. package:odbc).

Most examples in the documentation (but not all) attach package:DBI to the search path and do not attach the back-end, e.g.:

library(DBI)
con <- dbConnect(odbc::odbc(), ...)

There was also a commit to favor this.

Why does the documentation use that method rather than e.g.:

# attaching only the back-end
library(odbc)
con <- dbConnect(odbc(), ...)

# attaching the back-end before attaching package:DBI
library(odbc)
library(DBI)
con <- dbConnect(odbc(), ...)

# attaching the back-end after attaching package:DBI
library(DBI)
library(odbc)
con <- dbConnect(odbc(), ...)

?

So far, I only found that the first alternative may cause issues, e.g. for package:duckdb.

Thomas
  • 457
  • 2
  • 12
  • 1
    `odbc` (and other driver-packages) provide the internals required but may not implement every single method that `DBI` provides. I've seen people use `odbc::dbConnect`, and it still seems to work for them. I have not seen anybody complain that using the `odbc::`-variants of `DBI`'s functions has directly caused a problem. I use `DBI`, period. – r2evans Mar 07 '23 at 13:15
  • 1
    **Q1**: I've not seen any issues (yet) with attaching _only_ the driver-backend package. I don't do it. **Q2**: No. There is no masking, just S3 method dispatch, which is different. **Q3**: If you load the backend driver only, then your command will either clearly fail or send possibly-dialectually-inappropriate SQL to the DBMS. **Etc**: I've not seen a bug-report to suggest that loading the driver-only has caused failures, though I do not stalk the repos. Perhaps a rare issue: if you ever change backends, and if you hard-code `odbc::`, then you'll need to rewrite _everything_. – r2evans Mar 07 '23 at 23:33
  • 1
    YES, DBI provides low-level connectivity to the database. Package odbc does not implement all the methods of DBI. Check this gist (https://gist.github.com/arora123/9af5e2d8366ad62017d4a87caeac0f7a) that supports this argument. I prefer DBI – Dr Nisha Arora Jun 24 '23 at 08:16
  • @DrNishaArora Nice example, thanks. Could you post it as an answer so I can accept it? – Thomas Aug 15 '23 at 09:16

1 Answers1

0

According to comments from @r2evans, package:odbc (and other back-end packages) provide the internals required but may not implement every single method that package:DBI provides.

Here is an example from @DrNishaArora where attaching only the back-end fails:

library(odbc)
conn <- dbConnect(odbc(), "xxx")
dbReadTable(conn, "A_TABLE_IN_THE_DB")
# Error in dbReadTable(conn, "A_TABLE_IN_THE_DB") : 
#   could not find function "dbReadTable"

It works as expected when attaching only package:DBI:

library(DBI)
conn <- dbConnect(odbc::odbc(), "xxx")
dbReadTable(conn, "A_TABLE_IN_THE_DB")
Thomas
  • 457
  • 2
  • 12