[R-sig-DB] RSQLite problems / questions

[Dirk Eddelbuettel]

On 6 July 2006 at 10:04, Dirk Eddelbuettel wrote:
| 
| I am trying to set up some simulations to store data in RSQLite, and I hit a
| few snags I wasn't expecting. Any and all comments and suggestions welcome:
| 
| i)   RSQLite does not seem to like libsqlite 3.3.5:  On a Ubuntu system where
| I locally rebuilt libsqlite3 from Debian unstable source, the current RSQLite
| builds fine -- but dbWriteTable() seemingly never commits its data.frame to
| the database. I could not figure out why and how, nor could I tell RSQLite's
| configure to ignore the system libraries. Only by  "hiding" the sqlite3
| header and libraries in a temp directory was I able to build RSQLite against
| its shipped libsqlite 3.2.( versions.  Has anybody else experienced problems
| with sqlite 3.3.5? 
| 
| ii)  SQLite can use autoincremt'ing columns; one can write to such tables 
| by submitting a NULL in the corresponding field.  I could not figure out how
| to do that with directly with dbWriteTable() -- so I added code to read the
| max value, add one to it and add that as a column to the data.frame submitted
| for writes. This works, but is hardly elegant.  Does anybody have a trick for
| doing this directly?
| 
| iii) After a small (20-some) number of iterations, R dies with the error
| message 'all connections are in use'.  Bash's ulimit tells me I can have 1024
| file discriptors, and as the manual page suspect, I cannot raise that.  That
| said, I never hit this wall before when not working with RSQLite. Is it
| forgetting to close/release file handles somewhere?  Each of my runs write
| two updates, but I have the dbConnect()/dbDisconnect() pair 'inside' the
| update function. Moving the db update from the called function to the calling
| function does not cure it -- so even with a single dbConnect()/dbDisconnect I
| run out of resources.  Any idea why ?

The same problem happens on my Debian testing machine (R 2.3.1, SQLite 3.3.5,
DBI 0.1.10, RSQLite 0.4-1) :

> 
> library(RSQLite)
Loading required package: DBI
> m <- dbDriver("SQLite")
> con <- dbConnect(m, "/tmp/temp.sqlite")
> for (i in 1:500) dbWriteTable(con, "dummytable", data.frame(A=sample(LETTERS, 10), B=rnorm(10)), append=i>1, overwrite=FALSE)
Error in textConnection(readLines(f, n = 2)) : 
	all connections are in use
> i
[1] 46
> 

Same 46 limit of 46 connections I hit earlier.

This seems to be a bug in SQLite or DBI.

Regards, Dirk

-- 
Hell, there are no rules here - we're trying to accomplish something. 
                                                  -- Thomas A. Edison