[R] Closing unreferenced result sets in dbi / RSQLite

[Michael Bedward]

Hi Andreas,

Try this...

# forget to assign result set
dbSendQuery(con, "select * from df")

# retrieve the result set just created
rs <- dbListResults(con)[[1]]

Then you can do dbClearResult or whatever.

Michael


On 4 November 2010 19:56, Andreas Borg <andreas.borg at unimedizin-mainz.de> wrote:
> Hello R-help members,
>
> I have one problem with the database interface dbi (more specifically, I
> work with RSQLite). Consider the following example, which writes a test
> table to a temporary SQLite database and sends a query to read from it:
>
> library(RSQLite)
> df <- as.data.frame(matrix(runif(4), nrow=2, ncol=2))
> drv <- dbDriver("SQLite")
> con <- dbConnect(drv)
> dbWriteTable(con, "df", df)
> dbSendQuery(con, "select * from df")
>
>
> In the last line I "forgot" to assign the DBIResult object returned by
> dbSendQuery() to a variable, which happens from time to time when I work
> interactively. The following attempt to correct the mistake:
>
> res <- dbSendQuery(con, "select * from df")
>
> fails because the orphaned result set from the preceeding call is still
> active. Consequently, I have to close the connection to keep on working,
> which is especially annoying when working with a temporary data base where
> everything is discarded on disconnection. Is there any way to create a new
> reference to the pending result set or to close result sets which are not
> bound to a variable?
>
> Thanks for any suggestion,
>
> Andreas
>
>
> --
> Andreas Borg
> Medizinische Informatik
>
> UNIVERSITÄTSMEDIZIN
> der Johannes Gutenberg-Universität
> Institut für Medizinische Biometrie, Epidemiologie und Informatik
> Obere Zahlbacher Straße 69, 55131 Mainz
> www.imbei.uni-mainz.de
>
> Telefon +49 (0) 6131 175062
> E-Mail: borg at imbei.uni-mainz.de
>
> Diese E-Mail enthält vertrauliche und/oder rechtlich geschützte
> Informationen. Wenn Sie nicht der
> richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
> informieren Sie bitte sofort den
> Absender und löschen Sie diese Mail. Das unerlaubte Kopieren sowie die
> unbefugte Weitergabe
> dieser Mail und der darin enthaltenen Informationen ist nicht gestattet.
>
> ______________________________________________
> R-help at r-project.org mailing list
> 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.
>