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

[Gabor Grothendieck]

I don't think you have established db1.sqlite as an external sqlite
database yet -- its still only known to the db1 connection.  Try
disconnecting from db1 before attaching it in the db2 connection to
flush it out.

You can likely re-connect to db1 again right after disconnecting db1
even before attaching it in db2 if you wish -- that would be optional.

On Jan 17, 2008 7:56 PM, Herve Pages <hpages using fhcrc.org> wrote:
> 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
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>