I'm trying to use dynamic insert statements with my database but it fails on character columns. See code below.
library(dplyr)
library(DBI)
library(pool)
library(RSQLite)
df1 <- data.frame(stringsAsFactors = F, id = 1:4, value = letters[1:4])
df2 <- data.frame(stringsAsFactors = F, id = 1:4, value = 100:103)
con <- dbPool(SQLite(), dbname = "test") %>% poolCheckout()
dbWriteTable(con, "with_text", df1, overwrite = T)
dbWriteTable(con, "no_text", df2, overwrite = T)
db1 <- dbReadTable(con, "with_text")
db2 <- dbReadTable(con, "no_text")
new1 <- db1[1,]
new2 <- db2[1,]
query1 <- sprintf(
"INSERT INTO %s (%s) VALUES (%s);",
"with_text",
paste(names(new1), collapse = ", "),
paste(new1, collapse = ", ")
)
query2 <- sprintf(
"INSERT INTO %s (%s) VALUES (%s);",
"no_text",
paste(names(new2), collapse = ", "),
paste(new2, collapse = ", ")
)
db_query1 <- dbSendStatement(con, query1)#fails
dbClearResult(db_query1)
dbReadTable(con, "with_text")
db_query2 <- dbSendStatement(con, query2)
dbClearResult(db_query2)
dbReadTable(con, "no_text")
The #fails line produces this error:
Error in rsqlite_send_query(conn@ptr, statement) : no such column: a
The value of query1 is:
[1] "INSERT INTO with_text (id, value) VALUES (1, a);"
I realize the issue is the lack of single quotes (') around the text value but there has to be a workaround for that. Any help is appreciated. I tried adding column types but couldn't get it to work.
I know that this is a simplified example so I hope that this solution works for your real use case.
query1 = sprintf("INSERT INTO %s (%s) VALUES (%s,\'%s\');",
"with_text",
paste(names(new1),
collapse = ","),
new1[1,1],
new1[1,2])
I'm editing to add more of an explanation. If you know your second value is going to be text then you can add single quotes into your sprintf to surround that value but you will need to call these values separately instead of calling the entire row and pasting them together. I added \'%s\' escaped single quotes around your second value and then called the values separately.