I am trying this query to fetch my value from a MS SQL database on basis of two conditions but still I am getting exception in the syntax part.
Can anybody tell me what is the correct way to write a parameterized query in R?
Following is the query I used:
query<- paste0("SELECT [value] FROM [RiskDashboard].[dbo].[tbl_Simulation]
where Row_Id=", row_id[c(1),] ," AND Script_Id=", script_id[c(1),] ,)
T_data<-sqlQuery(ch,query)
print(T_data)
Parameterizing data is very important - especially from a security perspective. The examples you have are string concatenations and are subject to SQL injections.
The RODBCext package does have support for parameterization.
First - standard SQL parameterization syntax:
SELECT ColA, ColB FROM MyTable where FirstName = ? and LastName = ?
Each ? mark indicates in order the values that will appear in a vector. This syntax is true for ODBC regardless of platform. Others have extended to support position. eg. OLEDB supports @P1, @P2 etc.
While maybe not important for your R queries - in a multi-user system parameterized queries execute faster because the query plan is stored by the database-server (true of both Oracle and SQL Server).
To semi-plagiarize from the documentation:
library(RODBCext)
connHandle <- odbcConnect("myDatabase")
data <- sqlExecute(connHandle, "SELECT * FROM myTable WHERE column = ?", 'myValue', fetch = TRUE)
odbcClose(connHandle)
Documentation is here: https://cran.r-project.org/web/packages/RODBCext/vignettes/Parameterized_SQL_queries.html
More discussion here: Parameterized queries with RODBC
i got the correct way as follows:
query<- paste0("SELECT [Row_Id],[Script_id],[value] FROM
[RiskDashboard].[dbo]. [tbl_Simulation] where Row_Id='",row_id[c(i),],"'
AND Script_Id='",script_id[c(i),],"'")
