sqlQuery using a variable constraint not working.

Question

I am trying to pass a query to Access using the RODBC package within R. I can import entire tables using sqlFetch, but I am looking to cherry pick data using a query.

However, I would like to use an R variable to select one of the constraints (Shaft_ID).

To construct the SQL query, I have used both Cat and Paste so I can use quotation marks around the Shaft variable.

My code is as follows:

    Shaft<- "S001"
BL <- sqlQuery(myDBcon, paste("SELECT * FROM BL_FGSL WHERE Shaft_ID ='",Shaft,"'"), error = TRUE, as.is = TRUE)

This creates a data frame, but it does not find any values. If I replace the variable directly with "S001" it works!

Any ideas chaps?

Answer 1

SQL uses the single quote character ', but matching up quotes in SQL injection can be irritating and isn't highly recommended.

I would recommend parameterizing your query, and letting the computer do the work of managing your quotes for you.

library(RODBC)
library(RODBCext)

Shaft<- "S001"

BL <- 
  sqlExecute(myDBcon, 
             query = "SELECT * FROM BL_FGSL WHERE Valid = TRUE AND Shaft_ID = ?", 
             data = list(Shaft_ID = Shaft),
             fetch = TRUE, 
             stringsAsFactors = FALSE,
             error = TRUE)

https://cran.r-project.org/web/packages/RODBCext/vignettes/Parameterized_SQL_queries.html