[R-sig-DB] DBI preferred syntax

[Paul Gilbert]

I reported earlier in this thread that this works:

Type 'q()' to quit R.
 > require("RPostgreSQL")
Loading required package: RPostgreSQL
Loading required package: DBI
 > require("DBI")
 > z <-dbConnect(RPostgreSQL::PostgreSQL(), dbname="test")   #works

but there are some circumstance I have not isolated where if fails with 
the same problem as this (which fails reliably):

Type 'q()' to quit R.
 > requireNamespace("RPostgreSQL")
Loading required namespace: RPostgreSQL
 > require("DBI")
Loading required package: DBI
 > z <- dbConnect(RPostgreSQL::PostgreSQL(), "test")
Error in postgresqlNewConnection(drv, ...) :
   RS-DBI driver: (could not connect test using local on dbname "test"
)

For some reason, done this way it does not find my setting of 
environment variable PGHOST, which is not localhost, and picks up test 
as my user name, which is not correct.

(As before
 >> R version 3.1.1 (2014-07-10) -- "Sock it to Me"
 >> DBI         "DBI"         "0.3.1"
 >> RPostgreSQL "RPostgreSQL" "0.4"
)

Paul

On 10/11/2014 01:30 PM, Hadley Wickham wrote:
> 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 using 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
>
>
>