[R-sig-DB] RSQLite: ATTACH statement not executed when the db connection is holding a resultSet

[Seth Falcon]

Hi Herve,

Herve Pages <hpages using fhcrc.org> writes:
> OK I get the warning now:
>
>   library(RSQLite)
>   db1 <- dbConnect(SQLite(), "db1.sqlite")
>   dbGetQuery(db1, "CREATE TABLE t1 (a integer, aa text)")
>   db2 <- dbConnect(SQLite(), "db2.sqlite")
>   dbGetQuery(db2, "CREATE TABLE t2 (b integer, bb text)")
>   dbGetQuery(db2, "ATTACH 'db1.sqlite' AS db1")
>   dbSendQuery(db2, "SELECT * FROM db1.t1")
>
>   > dbGetQuery(db2, "SELECT * FROM db1.t1")
>   Error in sqliteExecStatement(new.con, statement, bind.data) :
>     RS-DBI driver: (error in statement: no such table: db1.t1)
>   In addition: Warning message:
>   In sqliteQuickSQL(conn, statement, ...) :
>     There is an open, incomplete result set; executing query on a temporary connection
>
>   > dbGetQuery(db2, "DETACH db1")
>   Error in sqliteExecStatement(new.con, statement, bind.data) :
>     RS-DBI driver: (RS_SQLite_exec: could not execute1: no such database: db1)
>   In addition: Warning message:
>   In sqliteQuickSQL(conn, statement, ...) :
>     There is an open, incomplete result set; executing query on a temporary connection
>
> but dbGetQuery() doesn't seem to be reporting what one would expect.

I'm not sure what you mean.  What do you expect dbGetQuery to report?

The above looks "right" to me.  You got a warning that told you that
your ATTACH occured on a temp connection.  Since ATTACH is called for
its side-effect on the _connection_ this means your ATTACH was
useless.

> Even if there is a warning now, I still find the "temporary connection"
> feature confusing...

Yes, the temporary connection "feature" _is_ confusing.  I would like
to remove this feature and have dbGetQuery behave like dbSendQuery:
error if there is an open and incomplete result set, close and process
a complete, but open result set, and encourage users to explicitly
close all result sets that they open.

I haven't done this yet, because making non-backwards compatible
changes should be done with some thought ... and some time for
interested parties to weigh in... anyone?

+ seth

-- 
Seth Falcon | seth using userprimary.net | blog: http://userprimary.net/user/