[R-sig-DB] RSQLite problems / questions

[Prof Brian Ripley]

On Thu, 6 Jul 2006, Dirk Eddelbuettel wrote:

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

The message is that R is hitting its limit (50, including 3 terminal 
connections), so the problem is in DBI.

You can work around it by using showConnections(all=TRUE) and identifying 
those left open and closing them (programmatically within the loop 
eventually).

Brian

-- 
Brian D. Ripley,                  ripley using stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595