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

[Herve Pages]

Hi Seth,

Just FYI, I tried with RMySQL and here too dbGetQuery() seems to use the
temp connection trick. So, not surprisingly, it leads to the same kind of
problems:

  > library(RMySQL)
  > db <- dbConnect("MySQL", ...)
  > dbGetQuery(db, "USE test") # selecting the 'test' database
  > dbSendQuery(db, "SELECT * FROM go_term")
  <MySQLResult:(24051,0,3)>
  > dbGetQuery(db, "SELECT * FROM go_term")
  Error in mysqlExecStatement(conn, statement, ...) :
    RS-DBI driver: (could not run statement: No Database Selected)

A workaround for this particular error would have been to specify the database
at connection time with the 'db' arg:

  > test_db <- dbConnect("MySQL", ..., db="test")

Then any temp connection created by dbGetQuery() will use the same db by default.

But again, I can see many different ways to get hit by the temp connection "feature",
especially when one needs to work with several databases and wants to switch between
them with the USE command.

Seth Falcon wrote:
[...]
> I'm becomming quite convinced that the temp conneciton "feature" is
> far too expensive in terms of maintenance (the cache_size issue) and
> user confusion (the ATTACH type of issues).  I would not be surprised if
> the code originated before SQLite even supported ATTACH.
> 
> I will be testing a patch that removes the temp connection and gives a
> clear error message about how to close open connections.

Good. Let me know if you need more testing.
Thanks a lot!

H.