Pass string variable in R script to use it in SQL statement

Question

I tried use a string variable in R script to use through SQL statement for example:

x="PASS"

SQL<- paste("select ID, NAME, STATUS from STUDENT where STATUS =(",x,")",sep="")
Q1 <- dbGetQuery(con, SQL)

The ERROR says:

Error in mysqlExecStatement(conn, statement, ...) :
RS-DBI driver: (could not run statement: Unknown column 'PASS' in 'where clause')

That means STATUS =(",x,")" = PASS and it must 'PASS' with add quote ''

I tried to put the '' but no success as the following.

SQL <- paste("select ID, NAME, STATUS from STUDENT where STATUS ='(",x,")' ",sep="")
Q1 <- dbGetQuery(con, SQL)

I tested it with number and it is work well but when I use string it is not working, because the value must be in quotes ' '.

Answer 1

Use sprintf instead:

x <- "PASS"
sprintf("select ID, NAME, STATUS from STUDENT where STATUS = '%s'", x)

## [1] "select ID, NAME, STATUS from STUDENT where STATUS = 'PASS'"

Answer 2

Try this:

library(gsubfn)
x <- "PASS"

fn$dbGetQuery(con, "select ID, NAME, STATUS from STUDENT where STATUS = '$x' ")

This also works:

s <- fn$identity("select ID, NAME, STATUS from STUDENT where STATUS = '$x' ")
dbGetQuery(con, s)