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

[Seth Falcon]

Hi Herve,

A quick response now and I will try to take a closer look later on...

Herve Pages <hpages using fhcrc.org> writes:
> Here is how to reproduce the problem.
>
> First create 2 databases with a single empty table in each:
>
>   library(RSQLite)
>
>   db1 <- dbConnect(SQLite(), "db1.sqlite")
>   dbGetQuery(db1, "CREATE TABLE t1 (a integer, aa text)")
>
>   db2 <- dbConnect(SQLite(), "db2.sqlite")
>   res <- dbSendQuery(db2, "CREATE TABLE t2 (b integer, bb text)")
[snip]
>
> Second, try to attach db1 to db2:
>
>   > dbGetQuery(db2, "ATTACH 'db1.sqlite' AS db1")
>   NULL
>
> No errors so it looks like it got attached but:

I'm pretty sure that SQLite only allows one result set per connection.
Hence it does not surprise me that the above does not work -- but an
error message would be nice :-)

Do you have a use-case where you really want to be able to have open
resultSets and keep doing things (which I'm not sure is possible)?  I
think the fix here is going to be to raise an error when this is
attempted.

For example, if you try to create two result sets that have pending
rows, you do get an error:

    > res <- dbSendQuery(db2, "select * from t2")
    > dbListResults(db2)
    [[1]]
    <SQLiteResult:(7183,1,4)> 
    
    > res <- dbSendQuery(db2, "select * from t2")
    Error in sqliteExecStatement(conn, statement, ...) : 
      RS-DBI driver: (connection with pending rows, close resultSet before continuing)

For result sets that don't return rows, there isn't much good that you
can do with the result set.  Perhaps these should be invalidated on
return so that you can open another resultSet?

Other ideas?

+ seth

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