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?

cat is only used for the side effect of writing to STDOUT (or a file); it returns NULL rather than the printed text -- is.null(cat(1:5, "\n")). — nrussell
– nrussell, Commented Aug 24, 2016 at 13:09
The reason you return nothing is you query is looking for ' S001 ' (with spaces on both sides) because you used paste() without specifying the separator: Either use paste(<SQL string>, sep="") or paste0(<SQL String>) — Parfait
– Parfait, Commented Aug 25, 2016 at 2:22

Benjamin · Accepted Answer · 2016-08-24 13:03:45Z

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

answered Aug 24, 2016 at 13:03

Benjamin

17.3k8 gold badges53 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

BillyJo_rambler Over a year ago

Thanks for that Benjamin, and letting me know that RODBCext exists! I tried the code, but I am getting the following errors:- Error in sqlExecute(myDBcon, query = "SELECT * FROM BL_FGSL WHERE Valid = TRUE AND Shaft_ID = ?", : HY104 98 [Microsoft][ODBC Microsoft Access Driver]Invalid precision value [RODBCext] Error: SQLBindParameter failed In addition: Warning message: HY104 98 [Microsoft][ODBC Microsoft Access Driver]Invalid precision value

BillyJo_rambler Over a year ago

Is this an issue with matching data types?

Benjamin Over a year ago

It seems like it. What is the data type for Shaft_ID?

Benjamin Over a year ago

this might be relevant... social.msdn.microsoft.com/Forums/en-US/…

BillyJo_rambler Over a year ago

Shaft_ID has a Short Text data type.

|

Collectives™ on Stack Overflow

sqlQuery using a variable constraint not working.

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related