I would simply separate the database connect and disconnect functions from the
query functions.
Mark
R. Mark Sharp, Ph.D.
msh...@txbiomed.org
> On May 28, 2015, at 12:18 PM, Luca Cerone <luca.cer...@gmail.com> wrote:
>
> 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.
______________________________________________
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.
