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

[Gabor Grothendieck]

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 using 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 using piskorski.com>
> http://www.piskorski.com/
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>