RSQLite dbWriteTable() fails w/ RS-DBI driver: too many SQL variables
SQLITE_MAX_VARIABLE_NUMBER in the sqlite source (in RSQLite) is currently set to 999.
On Tue, Oct 20, 2009 at 3:16 AM, Andrew Piskorski <atp at piskorski.com> wrote:
The simple RSQLite dbWriteTable() example below fails with the cryptic
RS-DBI driver error. ?Interestingly, if I reduce the size of my matrix
from 1000 to 998 columns, then it works.
Could anyone here please suggest what's going wrong, and how I should
go about trying to fix it? ?How do I see the actual SQL that this
RS-DBI thing is generating? ?Also, why doesn't this give me a stack
trace like stop() or any other R error? ?(Yes, I have "options(error =
recover)" set.)
library("RSQLite")
dbdr <- dbDriver("SQLite")
ff <- "/home/andy/t/tmp.sqlite"
mm <- matrix(1:1000^2 ,nrow=1000 ,ncol=1000 ,dimnames=list(paste("r",1:1000,sep="") ,paste("c",1:1000,sep="")))
# Here up to 998 columns work, any higher fails:
db <- dbConnect(dbdr ,dbname=ff)
dbWriteTable(db ,"my_table" ,as.data.frame(mm ,stringsAsFactors=F) ,row.names=T ,overwrite=T ,append=F)
dbDisconnect(db)
dbWriteTable(db ,"my_table" ,as.data.frame(mm[,1:999] ,stringsAsFactors=F) ,row.names=T ,overwrite=T ,append=F)
Warning in value[[3L]](cond) : ?RS-DBI driver: (error in statement: too many SQL variables) [1] FALSE
dbWriteTable(db ,"my_table" ,as.data.frame(mm[,1:998] ,stringsAsFactors=F) ,row.names=T ,overwrite=T ,append=F)
[1] TRUE This is with: - R 2.9.2 (Patched), 2009-09-24, svn.rev 49961, x86_64-unknown-linux-gnu - RSQLite_0.7-3.tar.gz (2009-10-04) ?http://cran.r-project.org/web/packages/RSQLite/ - DBI_0.2-4.tar.gz (2007-10-17, still the latest on CRAN) - Linux, Ubuntu 8.04.3 LTS Thanks! -- Andrew Piskorski <atp at piskorski.com> http://www.piskorski.com/
_______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB at stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-db