[R-sig-DB] RSQLite problems / questions

[Prof Brian Ripley]

On Fri, 7 Jul 2006, Prof Brian Ripley wrote:

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

Actually, in package RMySQL, which has in mysqlImportFile()

   if(missing(header) || missing(row.names)){
     f <- file(fn, open="r")
     if(skip>0)
       readLines(f, n=skip)
     flds <- count.fields(textConnection(readLines(f, n=2)), sep)
     close(f)
     nf <- length(unique(flds))
   }

and that never closes the connection it created.  This needs to be 
something like

     con <- textConnection(readLines(f, n=2))
     flds <- count.fields(con, sep)
     close(con)

or perhaps better, use an on.exit action to close the connection.

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