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

[Herve Pages]

Hi Seth,

Seth Falcon wrote:
> Hi all,
> 
> Seth Falcon <seth using userprimary.net> writes:
>> I think there are two ways to go here:
>>
>> 1. Fix dbGetQuery to have the same auto-close semantics.
>>
>> 2. Remove the auto-close feature entirely.  Should dbGetQuery change
>>    too such that it fails when a ResultSet is open instead of opening
>>    a new connection?  That is easier to explain, but not at all
>>    backwards compatible.
> 
> I've just uploaded a new RSQLite 0.6-7 with the following changes:
> 
> * dbGetQuery now has the same auto-close semantics as dbSendQuery
> 
> * dbGetQuery issues a warning message when it opens a temporary
>   connection because there is an incomplete result set.


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.
Even if there is a warning now, I still find the "temporary connection"
feature confusing...

Thanks!
H.


> sessionInfo()
R version 2.7.0 Under development (unstable) (2007-12-20 r43747)
x86_64-unknown-linux-gnu

locale:
LC_CTYPE=en_US;LC_NUMERIC=C;LC_TIME=en_US;LC_COLLATE=en_US;LC_MONETARY=en_US;LC_MESSAGES=en_US;LC_PAPER=en_US;LC_NAME=C;LC_ADDRESS=C;LC_TELEPHONE=C;LC_MEASUREMENT=en_US;LC_IDENTIFICATION=C

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base

other attached packages:
[1] RSQLite_0.6-7 DBI_0.2-4
>


> 
> + seth
>