R help - Nov 2010 - Closing unreferenced result sets in dbi / RSQLite

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.

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.
>