R-sig-DB Digest, Vol 68, Issue 7

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.