DBI preferred syntax
Hi Paul,
Here are my thoughts:
* dbDriver("SQLite") - never do this. dbDriver initialisation should
only ever be performed by the package (that's the whole point of
RSQLite::SQLite).
* dbConnect("SQLite", "test") - this used to work, but I think it's a
bad idea in general, because it interfaces poorly with namesspaces
* dbConnect(SQLite(), "test") - this will work but only if RSQLite is
attached (i.e. on the search path)
* dbConnect(RSQLite::SQLite(), "test") - this is the best way in my
opinion. It doesn't require that RSQLite be attached, just DBI, which
I think is the right approach. A DBI backend (in general) should only
ever provide methods, not new generics, so it shouldn't need to be
attached. In other words, you should _never_ need require("RSQLite").
* dbConnect(getExportedValue("RSQLite", "SQLite")()) - equivalent to
above, but use strings.
Hadley
On Sat, Oct 11, 2014 at 12:18 PM, Paul Gilbert <pgilbert902 at gmail.com> wrote:
I am trying to understand what is the new preferred way to establish a db
connection. (And there seems to be an error in SQLite.)
There also seems to be, from a user's perspective, an inconsistency in the
call dbConnect() relative to dbDriver(), in that the first works with, for
example, RSQLite::SQLite() and "SQLite", whereas the latter wants only the
character string.
I am using
R version 3.1.1 (2014-07-10) -- "Sock it to Me"
on Linux Mint (3.11.0-12-generic #19-Ubuntu SMP x86_64 x86_64 x86_64
GNU/Linux)
installed.packages()[c("DBI","RSQLite", "RMySQL",
"RPostgreSQL"),c("Package","Version")]
Package Version
DBI "DBI" "0.3.1"
RSQLite "RSQLite" "0.11.4"
RMySQL "RMySQL" "0.9-3"
RPostgreSQL "RPostgreSQL" "0.4"
For easy comparison the commands are shown here. The R session was restarted
between testing the different packages. The sessions with error messages are
further below.
##########
require("RSQLite")
z <-dbConnect("SQLite", dbname="test") #works but see below
z <-dbConnect(SQLite, dbname="test") #fails
z <-dbConnect(RSQLite::SQLite(), dbname="test") #works
z <-dbConnect(RSQLite::SQLite, dbname="test") #fails
m <- dbDriver("SQLite")
z <-dbConnect(m, dbname="test") #works
m <- dbDriver(RMySQL::MySQL()) #fails
z <-dbConnect("SQLite", dbname="test") # worked above but now fails
##########
require("RMySQL")
z <-dbConnect("MySQL", dbname="test") #works
z <-dbConnect(MySQL, dbname="test") #fails
z <-dbConnect(RMySQL::MySQL(), dbname="test") #works
z <-dbConnect(RMySQL::MySQL, dbname="test") #fails
m <- dbDriver("MySQL")
z <-dbConnect(m, dbname="test") #works
m <- dbDriver(RMySQL::MySQL()) #fails
##########
require("RPostgreSQL")
z <-dbConnect("PostgreSQL", dbname="test") #works
z <-dbConnect(PostgreSQL, dbname="test") #fails
z <-dbConnect(RPostgreSQL::PostgreSQL(), dbname="test") #works
z <-dbConnect(RPostgreSQL::PostgreSQL, dbname="test") #fails
m <- dbDriver("PostgreSQL")
z <-dbConnect(m, dbname="test") #works
m <- dbDriver(RPostgreSQL::PostgreSQL()) #fails
##############################
Sessions showing errors. R was restarted for each package test.
##############################
require("RSQLite")
Loading required package: RSQLite Loading required package: DBI
z <-dbConnect("SQLite", dbname="test") #works but see below
z <-dbConnect(SQLite, dbname="test") #fails
Error in (function (classes, fdef, mtable) : unable to find an inherited method for function \u2018dbConnect\u2019 for signature \u2018"function"\u2019
z <-dbConnect(RSQLite::SQLite(), dbname="test") #works z <-dbConnect(RSQLite::SQLite, dbname="test") #fails
Error in (function (classes, fdef, mtable) : unable to find an inherited method for function \u2018dbConnect\u2019 for signature \u2018"function"\u2019
m <- dbDriver("SQLite")
z <-dbConnect(m, dbname="test") #works
m <- dbDriver(RMySQL::MySQL()) #fails
Error in (function (classes, fdef, mtable) : unable to find an inherited method for function \u2018dbDriver\u2019 for signature \u2018"MySQLDriver"\u2019
z <-dbConnect("SQLite", dbname="test") # now fails
Error in as.integer(from) : cannot coerce type 'externalptr' to vector of type 'integer'
require("RMySQL")
Loading required package: RMySQL Loading required package: DBI
z <-dbConnect("MySQL", dbname="test") #works
z <-dbConnect(MySQL, dbname="test") #fails
Error in (function (classes, fdef, mtable) : unable to find an inherited method for function \u2018dbConnect\u2019 for signature \u2018"function"\u2019
z <-dbConnect(RMySQL::MySQL(), dbname="test") #works z <-dbConnect(RMySQL::MySQL, dbname="test") #fails
Error in (function (classes, fdef, mtable) : unable to find an inherited method for function \u2018dbConnect\u2019 for signature \u2018"function"\u2019
m <- dbDriver("MySQL")
z <-dbConnect(m, dbname="test") #works
m <- dbDriver(RMySQL::MySQL()) #fails
Error in (function (classes, fdef, mtable) : unable to find an inherited method for function \u2018dbDriver\u2019 for signature \u2018"MySQLDriver"\u2019
require("RPostgreSQL")
Loading required package: RPostgreSQL Loading required package: DBI
z <-dbConnect("PostgreSQL", dbname="test") #works
z <-dbConnect(PostgreSQL, dbname="test") #fails
Error in (function (classes, fdef, mtable) : unable to find an inherited method for function \u2018dbConnect\u2019 for signature \u2018"function"\u2019
z <-dbConnect(RPostgreSQL::PostgreSQL(), dbname="test") #works z <-dbConnect(RPostgreSQL::PostgreSQL, dbname="test") #fails
Error in (function (classes, fdef, mtable) : unable to find an inherited method for function \u2018dbConnect\u2019 for signature \u2018"function"\u2019
m <- dbDriver("PostgreSQL")
z <-dbConnect(m, dbname="test") #works
m <- dbDriver(RPostgreSQL::PostgreSQL()) #fails
Error in (function (classes, fdef, mtable) :
unable to find an inherited method for function \u2018dbDriver\u2019 for
signature \u2018"PostgreSQLDriver"\u2019
In addition to the above, I am sometimes faced with getting from a string to
a driver. Is there any preference for one of these rather than the other?
z <-dbConnect(do.call("SQLite", list()), dbname="test") #works
z <-dbConnect(get("SQLite")(), dbname="test") #works
z <-dbConnect("SQLite", dbname="test") #when it works
or is there a better way to do this?
Thanks,
Paul