2

I was having an issue getting the following error with RSQLite and was having trouble diagnosing the problem:

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

The database showed the correct, fixed number (24) of columns and should have had ~190 rows. Because the many rows at once won't fit in RAM, each entry (row) is iteratively appended.

Unfortunately it kept failing on entry 99. However, when I tried to only enter rows 95 to 105 into a database, it works.

    # Doesn't work

    samplesToAdd <- samplesToAdd[1:100, ]

    newDatabase2 <- DBI::dbConnect(RSQLite::SQLite(),
                           "C:/Users/chase/Documents/GitHub/IDBac_App/inst/app/SpectraLibrary/z1.sqlite")
    IDBacApp::addNewLibrary(samplesToAdd = samplesToAdd,
                    newDatabase = newDatabase2,
                    selectedIDBacDataFolder = selectedIDBacDataFolder)


    Warning: Error in result_create: too many SQL variables

    # Works  

    samplesToAdd <- samplesToAdd[95:105, ]

    newDatabase2 <- DBI::dbConnect(RSQLite::SQLite(),
                           "C:/Users/chase/Documents/GitHub/IDBac_App/inst/app/SpectraLibrary/z1.sqlite")
    IDBacApp::addNewLibrary(samplesToAdd = samplesToAdd,
                    newDatabase = newDatabase2,
                    selectedIDBacDataFolder = selectedIDBacDataFolder)

SQLiteDB

So, why was this failing for "too many variables" when there were only 24?

chasemc
  • 849
  • 6
  • 12

1 Answers1

1

Stupidly, there was a for loop that was assigning globally. This was having the effect of re-adding multiple columns each iteration. The SQLite just doesn't append the extra columns, so doesn't fail until the insert is too large.

However, the puzzling nature of the problem can be seen in the simplified example below. 

library(RSQLite)
library(magrittr)
library(dplyr)

a <- mtcars[1, ]
b <- cbind(mtcars[1, ], mtcars[2, ])


> as_tibble(a)
    # A tibble: 1 x 11
       mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
    * <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
    1    21     6   160   110   3.9  2.62  16.5     0     1     4     4
> as_tibble(b)

Error: Columns `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb` must have unique names

# But "tibbble" wasn't used:
> b
          mpg cyl disp  hp drat   wt  qsec vs am gear carb mpg cyl disp  hp drat    wt  qsec vs am gear carb
Mazda RX4  21   6  160 110  3.9 2.62 16.46  0  1    4    4  21   6  160 110  3.9 2.875 17.02  0  1    4    4




con <- DBI::dbConnect(RSQLite::SQLite(), 
file.path(getwd(),"StackoverflowExample", "exw.sqlite"))

DBI::dbWriteTable(conn = con,
              name = "IDBacDatabase", # SQLite table to insert into
              a, # Insert single row into DB
              append = TRUE, # Append to existing table
              overwrite = FALSE) # Do not overwrite


DBI::dbWriteTable(conn = con,
              name = "IDBacDatabase", # SQLite table to insert into
              b, # Insert single row into DB
              append = TRUE, # Append to existing table
              overwrite = FALSE) # Do not overwrite



 db <- dplyr::tbl(con, "IDBacDatabase")

 db

 # Source:   table<IDBacDatabase> [?? x 11]
 # Database: sqlite 3.22.0           [C:\Users\chase\Documents\StackoverflowExample\ex.sqlite]
     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
 1    21     6   160   110   3.9  2.62  16.5     0     1     4     4
 2    21     6   160   110   3.9  2.62  16.5     0     1     4     4

Edit To make failing insert:

    b <- mtcars[1, ]
    for(i in 1:1000){
        b <- cbind(b, mtcars[2, ])
    }
chasemc
  • 849
  • 6
  • 12