[R-sig-DB] RSQLite problems / questions

[Dirk Eddelbuettel]

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 ?

Thanks in advance for any insights or comments.

Regards, Dirk

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