Using placeholders in RMySQL queries

Question

I am trying to use placeholders with my RMySQL queries. For example, I have this table in MySQL,

id      name    birth           color   foods                   cats
1       Sybil   1970-04-13      black   lutefisk,fadge,pizza    3
2       Nancy   1969-09-30      white   burrito,curry,eggroll   4
3       Ralph   1973-11-02      red     eggroll,pizza           4

I would like to insert a record into this table, and from reading the documentation for dbBind I thought I might be able to do so with the code below,

library(RMySQL)

con <- dbConnect(MySQL(), user = "cbuser", password = "cbpass", dbname = 
                 "cookbook", host = "localhost")

sql <- "INSERT INTO profile (name,birth,color,foods,cats)
        VALUES(?,?,?,?,?)"

values <- list("De'Mont", "1973-01-12", NULL, "eggroll", 4)

rs <- dbSendStatement(con, sql)

dbBind(rs, values)

rows <- dbGetRowsAffected(rs)

dbClearResult(rs)

But when I run this I get the following error,

Error in .local(conn, statement, ...) : 
  could not run statement: You have an error in your SQL syntax; check the 
manual that corresponds to your MySQL server version for the right syntax to 
use near '?,?,?,?,?)' at line 2

I can insert the record successfully if I avoid the placeholders, such as below,

sql <- "INSERT INTO profile (name,birth,color,foods,cats)
        VALUES('De\\'Mont','1973-01-12',NULL,'eggroll',4)"

rs <- dbSendStatement(con, sql)

But my understanding is that using placeholders can be advantageous (for example, automatic handling of escaping of characters) so I would like to learn how this works.

try escaping the question marks in your SQL statement like `\\?` — 93i7hdjb, Mar 14 '18 at 14:19
i tried it but got the same error messsage (except that `?` was replaced by `\?`) — jochapjo, Mar 14 '18 at 14:44
In your successful attempt, you are escaping `'` like `'De\\'Mont'`, shouldn't it be the same with the `?` ? — 93i7hdjb, Mar 14 '18 at 14:47

Using placeholders in RMySQL queries

0 Answers0