Inserting strings with '?' (question mark) results in error.

[bhaskarvk]

Reproducible code

library(DBI)
dbdir <- file.path( tempdir() , "sendupdate" )
con <- dbConnect(MonetDBLite::MonetDBLite(), dbdir)

# create table
dbSendUpdate(con, "CREATE TABLE foo(a INT,b VARCHAR(100), c VARCHAR(100))")
# insert value, bind parameters to placeholders in statement
dbSendUpdate(con, "INSERT INTO foo VALUES(?,?,?)", 1, "foo", "bar") # WORKS
dbSendUpdate(con, "INSERT INTO foo VALUES(?,?,?)", 2, "foo?", "bar") # ERROR
dbSendUpdate(con, "INSERT INTO foo VALUES(3, \"foo?\", \"bar\")") #ERROR

print(dbGetQuery(con, "SELECT * FROM foo"))

dbSendUpdate(con, "DROP TABLE foo")
dbDisconnect(con,shutdown=TRUE)
unlink(dbdir, recursive = TRUE)

Errors on the two statements that fail

dbSendUpdate(con, "INSERT INTO foo VALUES(?,?,?)", 2, "foo?", "bar")

Error in .local(conn, statement, ...) : 
  Unable to execute statement 'INSERT INTO foo VALUES(2,'foo'bar'',FALSE)'.
Server says 'syntax error, unexpected IDENT, expecting ')' or ',' in: "insert into foo values(2,'foo'bar"
' [#42000].

dbSendUpdate(con, "INSERT INTO foo VALUES(3, \"foo?\", \"bar\")")
Error in .local(conn, statement, ...) : 
  Unable to execute statement 'INSERT INTO foo VALUES(3, "fooFALSE", "bar")'.
Server says 'ParseException:SQLparser:SELECT: identifier 'fooFALSE' unknown'.

Looks like having a '?' in a string causes it to be treated as a parameter. So how do we insert data that actually has '?' in it ?

[bhaskarvk]

Looks like a problem with .bindParameters function @ https://github.com/hannesmuehleisen/MonetDBLite/blob/9f2e060558a0bfa119087aeb476c5441f66cc248/R/dbi.R#L611

MonetDBLite:::.bindParameters("INSERT INTO foo VALUES(?,?,?)", list(1, "foo?", "bar"))

gives

[1] "INSERT INTO foo VALUES(1,'foo'bar'',?)"

[hannes]

good catch! Will fix by using new DBI code that should do it.

[bhaskarvk]

BTW just found out that in monetDB double quotes are used for parameters and single quotes for strings. It's not documented clearly anywhere but I found it mentioned in mailing list discussions.

So

dbSendUpdate(con, "INSERT INTO foo VALUES(3, \"foo?\", \"bar\")") #FAILS

# BUT

dbSendUpdate(con, "INSERT INTO foo VALUES(3, 'foo?', 'bar')") # WORKS CORRECTLY

# So I tried putting single quotes around the ?, but that fails too due to the handling of .bindParameters

dbSendUpdate(con, "INSERT INTO foo VALUES(?,'?','?')", 2, "foo?", "bar") # ERROR

[hannes]

you are not supposed to quote placeholders in general, but otherwise, yes. 'strings' and "identifiers" in MonetDB

[bhaskarvk]

Agreed! I was just experimenting.

[hannes]

should be fixed in https://github.com/hannesmuehleisen/MonetDBLite/commit/a2c2f11707be9dc875e6e18a238f2ec470fb339a