DBI preferred syntax
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 at 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 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