3

Suppose you're trying to do a typical insert-or-update loop with RSQLite. I would expect the following to work:

library(DBI)
testdb <- dbConnect(RSQLite::SQLite(), "test.sqlite")
dbExecute(testdb, "CREATE TABLE spray_count (spray TEXT, count INTEGER)")
urs <- dbSendStatement(testdb, "UPDATE spray_count SET count = count + :count WHERE spray = :spray")
irs <- dbSendStatement(testdb, "INSERT INTO spray_count VALUES (:spray, :count)")
for (i in 1:nrow(InsectSprays)) {
  print(paste("update", i))
  dbBind(urs, InsectSprays[i,])
  if (!dbGetRowsAffected(urs)) {
    print(paste("insert", i))
    dbBind(irs, InsectSprays[i,])
  }
}

But it does not:

[1] "update 1"
Error in rsqlite_bind_rows(res@ptr, params) : 
  external pointer is not valid
In addition: Warning message:
Closing open result set, pending rows

Basically it seems that you can have only one prepared statement at a time, and creating a second one somehow invalidates the first. Am I missing something or is this a limitation of DBI and/or RSQLite? I have DBI v0.6-1 and RSQLite v1.1-2.

Tavin
  • 390
  • 2
  • 13
  • This is deliberate, future versions may lift this restriction. Could you please try opening a second connection for the `INSERT` statement? – krlmlr May 29 '17 at 15:26
  • Thanks, that does the trick as a workaround. – Tavin May 30 '17 at 13:36
  • @krlmlr can you say anything more about why it's deliberate or when the restriction might be lifted? There are some situations where the workaround does not suffice... – Tavin May 31 '17 at 23:45
  • 1
    It simplifies the definition of the interface and the tests we have to implement to check compliance of a DBI backend. Which situations do you have in mind? – krlmlr Jun 01 '17 at 16:12

0 Answers0