[R-sig-DB] RSQLite dbWriteTable() fails w/ RS-DBI driver: too many SQL variables

[Andrew Piskorski]

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 using piskorski.com>
http://www.piskorski.com/