[R-sig-DB] DBI preferred syntax

[Paul Gilbert]

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