Pass String Variable in R Script to Use It in SQL Statement

Pass R variable to a sql statement

If v is an integer then you don't want to enclose the $v with single quotes - that makes it a string value. Try without the single quotes.

temp <- fn$sqldf("select count(V1) from file_new where V1=$v ")

Pass R variable to RODBC's sqlQuery?

Build the string you intend to pass. So instead of

example <- sqlQuery(myDB,"SELECT * FROM dbo.my_table_fn (x)")

do

example <- sqlQuery(myDB, paste("SELECT * FROM dbo.my_table_fn (", 
                                x, ")", sep=""))

which will fill in the value of x.

Use R variables to an SQL query

Are you looking for this?

sql <- paste0("select * 
           FROM GRID Z
           where Z.LAT ='", Xlat,"' AND Z.LON = '", Xlon,"'")

I assumed that your variables are character. In case the above is running behind a web server, there are options for URL encode and escape to avoid code injections... like this

EDIT: About this:

I would also like to know if instead of = is there something to match the closest or nearest values.

Since you are executing your query via a SQL engine that is more a SQL question than a R one. Like @Vivek says you can do that in sqldf but I guess your data are in a remote database, so it wouldn't help in this case.

All SQL flavours have like, so just use it in your query. Please tell me if I'm misunderstanding your question.

sql <- paste0("select * 
           FROM GRID Z
           where Z.LAT like '", Xlat,"' AND Z.LON like '", Xlon,"'")

Run SQL script from R with variables defined in R

There are several options, but my preferred is "bound parameters".

If, for instance, your 'Query.sql' looks something like

select ...
from MyTable
where CreatedDateTime > ?

The ? is a place-holder for a binding.

Then you can do

con <- dbConnect(...) # from DBI
df = dbGetQuery(con, statement = read_file('Query.sql'), params = list(dt))

With more parameters, add more ?s and more objects to the list, as in

qry <- "select ... where a > ? and b < ?"
newdat <- dbGetQuery(con, qry, params = list(var1, var2))

If you need a SQL IN clause, it gets a little dicey, since it doesn't bind things precisely like we want.

candidate_values <- c(2020, 1997, 1996, 1901)
qry <- paste("select ... where a > ? and b in (", paste(rep("?", length(candidate_values)), collapse=","), ")")
qry
# [1] "select ... where a > ? and b in ( ?,?,?,? )"
df <- dbGetQuery(con, qry, params = c(list(avar), as.list(candidate_values)))

Pass numeric variable to a sql statement in R

Try this one

paste("SELECT long,lat FROM 'Interpolation' where var1>",Q1,sep="")

or

paste("SELECT long,lat FROM Interpolation where var1>",Q1,sep="")

How to pass input variable to SQL statement in R shiny?

The data query needs to be evaluated in a reactive context.

One way would be to move the data query itself into the renderPlot() context e.g.

--server.R--

shinyServer(function(input, output) {

 database <- dbConnect(MySQL(), group= "zugangsdaten", dbname= 'database')

 output$main_plot <- renderPlot({

   table <- dbGetQuery(database, statement = 
              paste("
                SELECT a,b FROM table1
                WHERE id = ",input$segment,"
                AND created_at>='2015-08-01'
              "))

    plot(table$a,table$b)

 })

})

However, it's better to construct a reactive conductor for the data which can be evaluated once when any updates happen and re-used in multiple reactive end-points (see here for details).

This would look something like:

--server.R--

shinyServer(function(input, output) {

 database <- dbConnect(MySQL(), group= "zugangsdaten", dbname= 'database')

 table <- reactive({
            dbGetQuery(database, statement = 
              paste("
                SELECT a,b FROM table1
                WHERE id = ",input$segment,"
                AND created_at>='2015-08-01'
              ")
            )
          })

 output$main_plot <- renderPlot({

    plot(table()$a,table()$b)

 })

}) 

---

Related Topics