[R-sig-DB] dbListConnections() not working in RSQLite?

[Seth Falcon]

Hi Jonathan,

On 7/5/10 3:12 PM, Jonathan Greenberg wrote:
> This is a fork off my previous discussion -- trying to troubleshoot
> locking and connections has led me to try using dbListConnections().
> Using Seth's example for solving the problem of concurrent read/writes
> to a database, I tossed in a "print(dbListConnections(m))" into the
> main while() loop:
> 
> ***

[snip]

> 
> 	print(dbListConnections(m))

[snip]

> ***
> 
> The response from this statement is:
> list()
> list()
> list()
> list()
> 
> Am I missing something?  I'm noticing this behavior on the MacOS X
> install of RSQLite as well as a Debian amd x64 install.

Until quite recently, this would have done what you are expecting.  In
RSQLite 0.9-0, the memory model that handles SQLite db connections at
the R level was significantly refactored.  You can read some details as
provides in the NEWS file:

   library("RSQLite")
   RShowDoc("NEWS", package="RSQLite")

At the R level, RSQLite db connections now behave like other R objects.
 When you no longer have any variables referring to a connection, they
become available for garbage collection and will be auto-closed if
needed.  Aside from being a better choice for the R interface IMO, this
change also removes a predefined limit on the number of connections in
an R session.

In my view, the dbListConnections function was only needed precisely
because if you didn't close a connection and lost all references to it,
it "leaked" and there would have been no way to close it without a
global registry of connections.

In the new implementation, there is no such registry (hence no
predefined limit on number of connections) and no way to list open
connections.  While it would be possible to add that back in, I feel
pretty strongly that it isn't worth the extra complexity.  If you want a
list of connections, you can maintain that yourself in your code.

It is worth mentioning that the connection count available from
dbGetInfo(SQLite())$num_con does give some insight into number of open
connections, it can over-report, in a sense, if no gc has occured and
there is an orphaned connection.

Hope that helps some.

+ seth