Programmatically building SQL Query R/Shiny/RODBC

Question

I'm building a SQL Query statement using inputDateRange() in R/Shiny. My issue is in handling various strings to include the dates into the WHERE condition of the SQL:

Here is my code:

t.query <- paste0("Select [sensor_name], [temperature] from [dbo].
[temperature_sensor] where network_id = '24162' and date > "
, sQuote(format(input$my.dateRange[1], format="%d-%m-%Y"))  
, " and date < "
, sQuote(format(input$my.dateRange[2], format="%d-%m-%Y"))
)

Now the statement closes with a single quote and I receive the error below:

42000 102 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near '‘'. [RODBC] ERROR: Could not SQLExecDirect 'Select [sensor_name], [temperature] from [dbo].[temperature_sensor] where network_id = '24162' and date > ‘18-09-2017’ and date < ‘22-09-2017’'

I need to close the string with " as I started it in "select ...., I tried to explicitly add """ or dQuote("") to concatenate " but I'm still encountering an error.

Any advice is highly appreciated?

Benjamin · Accepted Answer · 2017-09-22 16:36:55Z

I'd recommend using RODBCext, which will allow you to parameterize your query as

library(RODBCext)
channel <- odbcConnect(...) # make your connection object here
Data <- 
  sqlExecute(channel = channel,
             query = "Select [sensor_name], [temperature] 
                      from [dbo].[temperature_sensor] 
                      where network_id = ? and date between ? and ?",
             data = list('24162',
                         format(input$my.dateRange[1], 
                                format = "%Y-%m-%d"),
                         format(input$my.dateRange[2],
                                format = "%Y-%m-%d")),
             fetch = TRUE,
             stringsAsFactors = FALSE)

This approach has a lot of advantages, including removing the frustration of matching quotes (which you shouldn't do because of the next reason), and protecting your data against SQL injection.

Collectives™ on Stack Overflow

Programmatically building SQL Query R/Shiny/RODBC

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related