Dear all,
I am writing a package that is a collection of queries to be run
against a postgresql database,
so that the users do not have to worry about the structure of the database.
In my package I import dbDriver, dbUnloadDriver, dbConnect,
dbDisconnect from the package DBI
and dbGetQuery from the package RPostgreSQL.
All the function in a function in my package have the same structure:
getFancyData <- function( from, to) {
on.exit( dbDisconnect(con), add=TRUE)
on.exit( dbUnloadDriver(drv), add=TRUE)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv,
user=pkguser,
host=pkghost,
password=pkgpassword,
port = pkgport)
query <- sprintf("select * from fancyTable where dt between '%s'
and '%s'", from, to)
res <- dbGetQuery(con,query)
return(res)
}
The various access details are read from an encrypted profile that the
user has to
create when she installs the package.
Such functions work perfectly fine, but I have to replicate a lot of
times loading and unloading the driver and connecting and
disconnecting from the database.
I am wondering if there is a better way to do this job, like loading
the driver and opening the connection only once when the package is
loaded. However I have to make sure that
if R crashes or the code where the function is called contains an
error then the connection
with the database is closed. How would you implement this?
Also how would you write a functional that would at least allow me to
avoid replicating
the boilerplate code to load and unload the drivers?
I am thinking something on the lines of:
querybuild <- function(query, ....)
on.exit( dbDisconnect(con), add=TRUE)
on.exit( dbUnloadDriver(drv), add=TRUE)
query <- sprintf(query, ... )
res <- dbSendQuery(query)
return(res)
}
and then define
getFancyData <- function(from, to) querybuild("select * from
fancyTable where dt between '%s' and '%s'", from, to)
Do you see a better way?
Thanks a lot in advance for your help and advice on this!
Cheers,
Luca
______________________________________________
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
