dbExecute with placeholders not working reliably with RSQLite

Question

I have a very simple table where I want to update certain rows with dbExecute() however the placeholder argument doesn't always work.

rows = dbExecute(con, "UPDATE rndid SET assigned = 'N', date = ?
                       WHERE id IN (?)", 
                 params = list(NA, toString(tempIDs$id))
                )

The above code works fine if there's only one row in tempIDs, but if there's more than one it doesn't update any rows nor does it throw an error.

If I just construct the string with paste() instead then it works regardless of the number of rows:

rows = dbExecute(con, paste("UPDATE rndid SET assigned = 'N', date = NULL 
                             WHERE id IN (",toString(tempIDs$id),")"
                           )
                 )

Anyone with an idea why the placeholder method fails or at least how I can try to debug it? I'd like to see what statement is actually executed by dbExecute.

This is the table def:

CREATE TABLE rndid (
  id INTEGER UNIQUE NOT NULL,
  assigned TEXT DEFAULT 'N' NOT NULL,
  date TEXT DEFAULT NULL
);

Answer 1

The reason is IDs from params are getting interpreted as a single string, see below example:

Set up dummy mtcars database:

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars[1:5, 1:4])
dbReadTable(con, "mtcars") 
#    mpg cyl disp  hp
# 1 21.0   6  160 110
# 2 21.0   6  160 110
# 3 22.8   4  108  93
# 4 21.4   6  258 110
# 5 18.7   8  360 175

# rows to match on cyl values
x <- c(4,6)

Using sqlInterpolate without SQL it is becoming '4, 6' instead of 4, 6:

sqlInterpolate(ANSI(),
               "UPDATE mtcars SET mpg = 0, disp = ?x1 WHERE cyl IN (?x2)",
               x1 = NA, x2 = toString(x))
# <SQL> UPDATE mtcars SET mpg = 0, disp = NULL WHERE cyl IN ('4, 6')

We need to use SQL to avoid:

dbExecute(con,
          sqlInterpolate(ANSI(),
                         "UPDATE mtcars SET mpg = 0, disp = ?x1 WHERE cyl IN (?x2)",
                         x1 = NA, x2 = SQL(toString(x))))
# [4]

dbReadTable(con, "mtcars") 
#    mpg cyl disp  hp
# 1  0.0   6   NA 110
# 2  0.0   6   NA 110
# 3  0.0   4   NA  93
# 4  0.0   6   NA 110
# 5 18.7   8  360 175

#disconnect
dbDisconnect(con)