dbHasCompleted() yields an Error: Invalid result set.

[Daniel-Zhou-93]

I'm trying to process the entries of one table and insert the results into another table. The processing works and the rows are inserted, but the pipeline stops after the first loop with "Error: Invalid result set".

library("RSQLite")

# create a small database with one table with columns a, b, c
dbConn <- dbConnect(RSQLite::SQLite(), "~/mytest.db")

dbExecute(dbConn, 'CREATE TABLE IF NOT EXISTS mytable(a VARCHAR(255), b INTEGER, c DOUBLE);')

dbExecute(dbConn, 'CREATE TABLE IF NOT EXISTS second_table(a VARCHAR(255), d DOUBLE);')

# add records in there pasted together for b and c.
#rs <- dbSendStatement(dbConn, 'INSERT INTO mytable VALUES(:id, :val_list);')
for (i in seq(1,10)) {
  rs <- dbSendQuery(dbConn, paste('INSERT INTO mytable VALUES(:id, ', paste(unlist(c(sample(1:10, 1), rnorm(1))), collapse = ','), ');'))
  dbBind(rs, params = list(id = paste("id", i,sep='')))#, val_list = paste(unlist(c(3, 3.2)), collapse = ',')))

  dbClearResult(rs)
}

# erroneous code follows
rs <- dbSendQuery(dbConn, paste('SELECT * from mytable;'))
while(!dbHasCompleted(rs)) {
  each.row <- dbFetch(rs, n = 5)
  for (i in seq(,nrow(each.row))) {
    rsin <- dbSendStatement(dbConn, 'INSERT INTO second_table VALUES(:id, :d);')
    dbBind(rsin, params = list(id = each.row$a[i], d = each.row$b[i] + each.row$c[i]))
    dbClearResult(rsin)
  }
  
  print(each.row)
}
dbClearResult(rs)

dbDisconnect(dbConn)

The traceback() traces back to:

3: result_has_completed(res@ptr)
2: dbHasCompleted(rs)
1: dbHasCompleted(rs)

dbHasCompleted() works fine on its own, but when I add the INSERT statement, I end up with this error. However, I need to process the rows from mytable and insert them into second_table. If this isn't the way to do it, how would I go about doing this?

Thank you for your help in advance.

[krlmlr]

Thanks. Are you trying to copy data within the same database, or from one database into another? For the former, can you try INSERT INTO ... SELECT ... FROM ... as a single SQL statement? The dplyr::rows_append() function might also be useful.

[Daniel-Zhou-93]

Thanks. Are you trying to copy data within the same database, or from one database into another?

I need to pull data a, b, c from mytable and insert a, f(b,c) into second_table, where f is a function that takes b and c as arguments. (Here, f(b, c) = b + c for simplicity, but my actual use case involves a Haversine distance function, among other things.)

[krlmlr]

Got you. So it's difficult to compute f(b, c) on the database.

That's a use case I haven't encountered so far. The restriction to a single result set is somewhat artificial, we might want to lift that. Would it be feasible to read the whole data as a data frame and to write the resulting data as a whole too?