It's not working. I tried with many syntax.

Look my try:

      and dth_criacao_reg between {as.character(input$dates[1])} and {as.character(input$dates[2])}

With my UI.R:

                   dateRangeInput("dates", 
                                  label = ("Periodo"),
                                  start = Sys.Date() - 90, end = Sys.Date() - 1,
                                  format = "dd/mm/yyyy",
                                  language = "pt-BR",
                                  separator = "ate"
                                  
                   ),

Where is the mistake?

Two comments: 1) check the name of your input and 2) enclose the dates in single quotes. You want the date to have 'YYYY-MM-DD' format; the single quotes are important. Depending on your database backend you might need format DATE 'YYYY-MM-DD'. Postgres does not need the DATE, Teradata requires it (go figure... :thinking:).
You might also have to adjust / convert the format of date format from slash separated to dash separated.

1 Like

I got it, but still not working.

I'm using Oracle database.

Look the data export

In the code it's written:

dateRangeInput("dates", 
                                  label = ("Periodo"),
                                  start = Sys.Date() - 90, end = Sys.Date() - 1,
                                  format = "dd/mm/yyyy",
                                  language = "pt-BR",
                                  separator = "ate"
                   ),

I think the own program can understand and transform the date format by itself.

And other question...
If I want to enter with input called 'GRAU' (the last table collum), I must declare the widget a 'textInput'?

I must type:
'L80 TIPO 1'
'L80_TIPO_1'
L80 TIPO 1
L80_TIPO_1
...
There is a standard shape?

OK, Oracle does require date literals identified by DATE. So try something along the lines:

and dth_criacao_reg between DATE '{as.character(input$dates[1])}' and DATE '{as.character(input$dates[2])}'

I am not that familiar with the glue package, and unsure of how it will handle the single quotes.

you'll get better help if you show the value of query in your post. We're all sort of guessing what's in the query, but you have it right in front of you.

Man... my query is a default value of data. The others parameters have been tested and are correct:

WITHOUT the date, the tableoutput is there:

BUT, when i try to enter with DATE filter, it does not work.

I just tried the last code of the friend @jlacko, but it still not working even with many variation of syntax.

My UI.R code:

dateRangeInput("dates", 
                                  label = ("Periodo"),
                                  start = Sys.Date() - 90, end = Sys.Date() - 1,
                                  format = "dd/mm/yyyy",
                                  language = "pt-BR",
                                  separator = "ate"
                   )
menuItem( text = "Descricao do Material",
                               tabName = "descricao",
                               
                               numericInput(inputId= "diametro", label= "Diametro do Aco:", value= 0),
                               
                               numericInput(inputId= "parede", label= "Parede:", value= 0),
                               
                               textInput(inputId= "grau", label= "Grau:", value= 'x'),
                               
                               textInput(inputId= "aqa", label= "AQA:", value= 'x'),
                               
                               numericInput(inputId= "ciclo", label = "Ciclo:", value = 0),
                               
                               actionButton("plot", "Plotar!")

Server.R

 query <- glue(
      "select 
      cod_ordem_producao as Ordem,
      dim_ext_tubo as Diametro,
      esp_par_tubo as Parede,
      cod_aqa as AQA,
      tmo_ciclo_plan as Ciclo,
      dth_criacao_reg as Data,
      dsc_aco as Grau,
      val_lim_escoamento as LE,
      val_tensao_residual as TR
      from
      QT_QTS.PLA_ORDEM_PRODUCAO
      where DIM_EXT_TUBO = {as.numeric(input$diametro)}
      and esp_par_tubo = {as.numeric(input$parede)}
      and tmo_ciclo_plan = {as.numeric(input$ciclo)}
      and dth_criacao_reg between dates '{as.character(input$dates[1])}' and dates '{as.character(input$dates[2])}'
      and VAL_LIM_ESCOAMENTO != 0
      order by DTH_CRIACAO_REG desc")
    
    df <- dbGetQuery(
      connection_reportUser,
      query
    )

I don't think the query will work with

and dth_criacao_reg between dates '{as.character(input$dates[1])}' and dates '{as.character(input$dates[2])}'

You need date, not dates, to introduce the date literal.

Also, try to show us the value of query string - either via browser() or just plain print() but do get it out :slight_smile:

1 Like

Ok.

Good result (without data input)

Bad result (with data input)

And I wrote "dates" cause it's the ID of my dateRangeInput...
May I have to use DATE for all cases?

@jlacko, It worked.

Many thanks! I owe you one!

I am glad it did!

Date can be tricky; there is a reason why it is a reserved word :wink:

1 Like

I really implore you (all) to use parameterized queries instead of glue() or paste() to form dynamic queries. The latter is a big security hole that can be used to trivially easily perform a sql injection attack.

https://db.rstudio.com/best-practices/run-queries-safely/

4 Likes

would you consider glue_sql() to be a safe option Joe?

While I agree in principle with you recommendation (hard to disagree...) I have a question, more out of curiosity:

Is it necessary to sanitize the results from a dateRangeInput? I mean, it will always return a vector of two dates, no mater what the user inputs?

Or is there a way to somehow trick the dateRangeInput into dropping the proverbial table Students?

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.

Replying my own post in the past:
https://community.rstudio.com/t/using-daterangeinput-with-between-logic-in-sql/22776

I'm using almost the same code, but with other names, like:

dateRangeInput("iData", 
                                  label = ("Periodo"),
                                  start = Sys.Date() - 365, end = Sys.Date() - 1,
                                  format = "dd/mm/yyyy",
                                  language = "pt-BR",
                                  separator = "ate"
                   )

In the GLUE package, doing this:

df2 <- dbGetQuery(
      connection,
      query <- glue(
"select DATA_ENFORNAMENTO_TF2
from DWA_AM_TRACA 
where DATA_ENFORNAMENTO_TF2 between DATE '{as.character(input$iData[1])}' and DATE '{as.character(input$iData[2])}'
")

But, that way it just did not work and I'm copying the example in the link posted.
ex: where AUST.DTH_DESENFORNAMENTO between DATE '{as.character(input$dates[1])}' and DATE '{as.character(input$dates[2])}' <------ this worked

Where is the mistake?

Where are you putting this code? I'm triying to replicate your issue on my postgresql server but works normally for me, the only thing I can think off is that you are putting this code outside a reactive expression.

Could you make a minimal REPRoducible EXample (reprex) about your issue?
Here is a very useful guide about how to make a reprex for a shiny app

library(shiny)
library(ROracle)
library(glue)

shinyApp(
    ui = fluidPage(
dateRangeInput("iData", 
                                  label = ("Periodo"),
                                  start = Sys.Date() - 365, end = Sys.Date() - 1,
                                  format = "dd/mm/yyyy",
                                  language = "pt-BR",
                                  separator = "ate"
                                  
                   )
    ),

    server = function(input, output, session) {
df2 <- dbGetQuery(
      connection,
      query <- glue(
        "select 
        DATA_ENFORNAMENTO_TF2,
        YS,
        UTS
        
        from 
        DWA_AM_TRACA 

        where 
        DATA_ENFORNAMENTO_TF2 between DATE '{as.character(input$iData[1])}' and DATE '{as.character(input$iData[2])}'
                  ")
                )
    }
  )

I tried with other filters in WHERE CONDITION and it worked. But when I insert the DATA CONDITION, it generates an error.

As I said, for getting access to input$ values you need to put your code inside a reactive expression.

This seems to produce valid sql code

library(shiny)
library(glue)

ui <- fluidPage(
    
    dateRangeInput("iData", 
                   label = "Periodo",
                   start = Sys.Date() - 365, end = Sys.Date() - 1,
                   format = "dd/mm/yyyy",
                   language = "pt-BR",
                   separator = "ate"
    ),
    verbatimTextOutput(outputId = "query")
)

server <- function(input, output) {
    
    output$query <- renderText({
    glue("
    select 
        DATA_ENFORNAMENTO_TF2,
        YS,
        UTS
    from 
        DWA_AM_TRACA
    where 
        DATA_ENFORNAMENTO_TF2 between DATE '{as.character(input$iData[1])}' and DATE '{as.character(input$iData[2])}'"
         )
    })
}

shinyApp(ui = ui, server = server)

In your case, you should use renderTable()

output$query <- renderTable({
        df2 <- dbGetQuery(
            con,
            glue("
    select 
        DATA_ENFORNAMENTO_TF2,
        YS,
        UTS
    from 
        DWA_AM_TRACA
    where 
        DATA_ENFORNAMENTO_TF2 between DATE '{as.character(input$iData[1])}' and DATE '{as.character(input$iData[2])}'"
            )
        )
        df2
    })

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.