[R-sig-DB] concurrent reading/writing in "chunks" with RSQLite (need some help troubleshooting)

[kMan]

I apologize. I sent this initially without correcting the subject line.

Sincerely,
KeithC.

-----Original Message-----
Dear Jonathan,

>I'm trying to cycle through a database, reading a chunk of data from 
>one
table in the DB, processing this data chunk, >and then writing the output to
another table in the database.  I'm having to set the fetch(n=) option
because the >database is too large to read the entire thing into memory at
once.  Here's my sample code:
[snip]

There is no need to open separate connections for reading vs. writing.

Your code was corrupting the connection between R/driver, I think, and I
don't know why. But when I close a connection in R and my OS won't let me
delete the file, there is a different kind of problem. The code below works
on my system. I wish I knew enough to explain wtf happened with your code.
------------------------------------------------------------------------
##
## Prelim/Declarations
##
require(RSQLite)
mysqldb <- "test.sqlite"
m <- dbDriver("SQLite")
tbl.names <- c("TABLEA", "TABLEB")
q1<-paste("SELECT COUNT(*) FROM", tbl.names[1])

##
## Surrogate data (if not allready done) ##
(somedata=data.frame(data1=seq(1:10),data2=seq(1:10)*3))
con<-dbConnect(m, dbname=mysqldb)
dbWriteTable(con,"TABLEA",somedata, overwrite=TRUE)
dbDisconnect(con)

##
## Process TABLEA in parts, write to TABLEB ## con<-dbConnect(m,
dbname=mysqldb) n.lines<-dbGetQuery(con, q1) #NOTE class is data.frame
chunk.size<-3
start.index<-seq(from = 1, to = n.lines[1,1], by = 3)
limit<-c(diff(start.index), n.lines[1,1]-sum(diff(start.index)))
i<-1
for(i in 1:length(limit)){
  if(i<length(limit)) { q2<-paste("SELECT * FROM TABLEA WHERE row_names >=",

      start.index[i], "LIMIT", limit[i])
  } else { q2<-paste("SELECT * FROM TABLEA WHERE row_names =", 
      start.index[i], "LIMIT", limit[i])
  }
  dat<-dbGetQuery(con, q2)
  dbWriteTable(con, tbl.names[2], dat, row.names=FALSE, append=TRUE) }
dbGetQuery(con, "SELECT * FROM TABLEB")
dbDisconnect(con)
------------------------------------------------------------------------

Sincerely,
KeithC.