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

[Herve Pages]

Hi Seth,

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)")

Note that dbSendQuery() is used instead of dbGetQuery() to create the
table in db2 so now db2 is holding a resultSet:

  > summary(db2)
  <SQLiteConnection:(28981,1)>
    User: NA
    Host: localhost
    Dbname: db2.sqlite
    Connection type: direct
    Loadable extensions: off
      1  <SQLiteResult:(28981,1,1)>

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:

  1. This doesn't work:

       dbGetQuery(db2, "SELECT * FROM db1.t1")

  2. I can run this as many times I want (normally you can't attach a
     db that's already attached):

       dbGetQuery(db2, "ATTACH 'db1.sqlite' AS db1")
       dbGetQuery(db2, "ATTACH 'db1.sqlite' AS db1")
       ... etc ...

  3. I can't detach it either:

       dbGetQuery(db2, "DETACH db1")

so everything tends to indicate that db1 was not attached in the
first place.

Now if I clear 'res', things work as expected:

  dbClearResult(res)
  summary(db2)
  dbGetQuery(db2, "ATTACH 'db1.sqlite' AS db1")
  dbGetQuery(db2, "SELECT * FROM db1.t1")
  dbGetQuery(db2, "DETACH db1")

Any idea what's going on?

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-4 DBI_0.2-4