Pass a string variable in an R script to use in a SQL statement

Question

I tried using a string variable in an R script to use with an SQL statement, for example:

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

ERROR says:

Error in mysqlExecStatement (conn, statement, ...):
RS-DBI driver: (failed to start the statement: Unknown column "PASS" in the "where" section)

This means STATUS = (", x,") "= PASS and it should" PASS "with the quote added ''

I tried to put '' , but did not succeed, as shown below.

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

I tested it with a number, and it works well, but when I use a string, it does not work, because the value must be in quotation marks. ' '

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 the following:

 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) 

Answer 3

EDIT for Windows

Try

 x = "PASS" SQL<- paste0("select ID, NAME, STATUS from STUDENT where STATUS = ", shQuote(x, 'sh')) Q1 <- dbGetQuery(con, SQL) 

In general, shQuote is useful for built things, such as:

 paste0("SELECT * FROM urtable where urvar IN(", paste0(shQuote(LETTERS, 'sh'), collapse = ','), ")") [1] "SELECT * FROM urtable where urvar IN('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')" # 

If you have simple character strings. More complex character strings may require different approaches. For example, in PoSTgreSQL, you can use Dollar-Quoted String Constants to remove characters.

You will not indicate which SQL option you are using or bundled the R package. Some R packages may have helper functions, such as postgresqlEscapeStrings in RPostgreSQL or dbEscapeStrings in RMySQL .