5

I have a large dataset with nearly 2000 variables in r. I then use sqldf to write a few case statements to create new columns on the original dataset. However I get the following error:

 Error in rsqlite_send_query(conn@ptr, statement) : too many SQL variables

I rebooted my laptop today and previously this error never occured.

Any help is appreciated.

greeny
  • 425
  • 1
  • 6
  • 20
  • 1
    See https://www.sqlite.org/limits.html – G. Grothendieck Jan 11 '17 at 22:22
  • Could you clarify on what I should input in r? The link gives info but I can't adjust the maximum columns. I've tried "SQLITE_MAX_COLUMN" with no avail – greeny Jan 12 '17 at 06:45
  • 2
    There is nothing you can do in R. You would need to get the source to RSQLite from CRAN or github, change SQLITE_MAX_COLUMN in the SQLite C code included with RSQLite and then rebuild the package -- which will also rebuild SQLite. – G. Grothendieck Jan 12 '17 at 13:38
  • 1
    Ok so I've got the package source for RSQLite from CRAN. I think I've changed the correct part of the package in the 'sqlite3' folder within 'src'. I then compressed the edited folder as a new .gz file. I then entered this into terminal "R CMD INSTALL path/RSQLite_1.1-3.tar.gz" and reinstalled the package in r. But still no luck. Could you elaborate further? – greeny Jan 14 '17 at 07:48
  • Not an answer to your question, but: whatever you do with `sqldf` you can do in base R or with the `tidyverse` or `data.table`, with less limitations. – Aurèle Jan 20 '17 at 16:14
  • Workarounds would include using a different database. H2 is nearly as easy to install as SQLite since H2 is included in the RH2 R package driver. You do have install Java first but that is trivial to install and you may already have it. – G. Grothendieck Oct 19 '17 at 20:46
  • @Aurèle I don't think OP uses `sqldf` for data transformations, but to store data in an SQLite database on the hard drive. The packages you mention are for data transformations in working memory, but they cannot interact with databases afaik. – altabq Jan 08 '19 at 16:15
  • @altabq Oh, sorry then. – Aurèle Jan 08 '19 at 17:18

1 Answers1

1

I hit the same problem. I just limited the number of columns

# here creating data with alot of columns
a<- mtcars
for( i in 1:1000 ){
b <- mtcars
colnames(b) <- paste( colnames(b), i , sep="_")
a <- cbind( b , a )
}

ncol( a )

# I get the error here
sqldf( "Select SUM( wt) as weights from a ")

#so I just limited the columns
z <- a[ , c( "gear","wt")]
# and than this works
sqldf( "Select SUM( wt ) as weights from z ")
MatthewR
  • 2,660
  • 5
  • 26
  • 37