RSQLite: ATTACH statement not executed when the db connection is holding a resultSet

Hi Seth,

Hi Herve,

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

Herve Pages <hpages at 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.

No use-case. But we have no way no prevent our users to make improper
use of dbSendQuery() (we'll try to advertise the use of much safer
dbGetQuery() instead but that's all we can do). So, yes, raising an
error that tells them that the ATTACH command failed (or could not be
sent) or, more generally, that any SQL statement failed, seems like
the right thing to have.

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)

Yes I've seen this. And if the first result set does not correspond to a
SELECT statement, then it is silently replaced by the next result set:

  library(RSQLite)
  db <- dbConnect(SQLite(), "test.sqlite")
  dbSendQuery(db, "CREATE TABLE test (a integer, aa text)")
  dbSendQuery(db, "SELECT * FROM test")

First result has disappeared:

  > dbListResults(db)
  [[1]]
  <SQLiteResult:(31978,0,2)>

Personally I find this "automatic result set clearing" feature a little
bit confusing and it raises some concerns about compatibility with other
RDBMSs (see below).

Back to the ATTACH problem: if I try to ATTACH with dbSendQuery() instead
of dbGetQuery() then the "automatic result set clearing" feature enter in
action:

  library(RSQLite)
  db1 <- dbConnect(SQLite(), "db1.sqlite")
  dbGetQuery(db1, "CREATE TABLE t1 (a integer, aa text)")
  db2 <- dbConnect(SQLite(), "db2.sqlite")
  dbSendQuery(db2, "CREATE TABLE t2 (b integer, bb text)")
  dbSendQuery(db2, "ATTACH 'db1.sqlite' AS db1")
  dbListResults(db2) # only 1 result set

but now it's hard to tell whether or not db1 is attached to db2:

  1. This seems to indicate that it is NOT:

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

  2. This seems to indicate that it is:

       > dbSendQuery(db2, "ATTACH 'db1.sqlite' AS db1")
       Error in sqliteExecStatement(conn, statement, ...) :
         RS-DBI driver: (RS_SQLite_exec: could not execute1: database db1 is already in use)

  3. And this too seems to indicate that it is was attached:

       > dbGetQuery(db2, "DETACH db1")
       NULL
       > dbGetQuery(db2, "DETACH db1")
       Error in sqliteExecStatement(con, statement, bind.data) :
         RS-DBI driver: (RS_SQLite_exec: could not execute1: no such database: db1)

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?

The problem is consistency with other RDBMSs: if the users want to be able
to write portable code, they need to have some guarantee that things behave
consistently between RSQLite, RMySQL, ROracle, etc...
In this regard I would say it's better to keep things as predictable as
possible and maybe to avoid features like the "automatic result set clearing".
Every successful call to dbSendQuery creates a new entry in the result set
table and this entry remains here until explicitly removed by the user,
whatever the nature of the SQL statement was. If SQLite allows only one
result set per connection, then an error should be raised when dbSendQuery
is called and there is already an entry in the result set table.

I can see that the "automatic result set clearing" feature adds some
convenience by discarding result sets that DO NOT correspond to a SELECT
statement (so that they never end up in the result set table) but then
everybody in the DBI family should do the same thing.

As for treating result sets that DO correspond to a SELECT statement
but contain 0 rows the same way as result sets that DO NOT correspond
to a SELECT statement, I don't really like the idea. Because the 2 types
of result sets are fundamentally different: having rows or not in the
former depends on the data found in the DB while getting a result set
of the latter type does not (it only depends on a programmatic decision
i.e. on the nature of the SQL statement that was sent).

Thanks!
H.

Other ideas?

+ seth