How to plot data.frame using DATABASE

danimaiams · December 20, 2018, 2:22pm

Hi people!

I trying to create a "dataTable" but i'm having some problem.

I'm catching values from a DB using this:

But when i use the same code in RStudio, it appears that way:

My code:
UI.R

  tabPanel("Dados do Banco",
                         
        h4("Dados de Saida", align = "center"),
        
       DT::dataTableOutput("contents2")
        ),

SERVER.R

output$contents2 = DT::renderDataTable({
    query
  })

DATA.R

query = ("select delta_le 
        from qt_limite_escoamento
        where GRAU = 'N80'")

query = matrix(query, nrow = 1, ncol = 2, byrow = TRUE)

I'm connected with the DATABASE and it's working, but o don't know how to make the data appears correctly in R program.

I need to appear a DATA.FRAME with the right values (206).

Thx

edgararuiz · December 20, 2018, 2:46pm

Hi, can you share the part of your code where R connects to the database please?

andresrcs · December 20, 2018, 4:19pm

You have to fetch results from the database first, assumming that you have an active connection do something like this in your server.R file

output$contents2 = DT::renderDataTable({
    query <- ("select delta_le 
        from qt_limite_escoamento
        where GRAU = 'N80'")
    content <- dbGetQuery(con, query)
    content
})

danimaiams · December 21, 2018, 10:31am

library("ROracle")

Sys.setenv(TZ = "GMT-3")
Sys.setenv(ORA_SDTZ = "GMT-3")

driver <- dbDriver("Oracle")
host <- "hidden"
port <- hidden
sid <- "hidden"

connect.string <- paste(
    "(DESCRIPTION=",
    "(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
    "(CONNECT_DATA=(SID=", sid, ")))",
    sep = ""
)

connection <- dbConnect(
    driver, username = "hidden",
    password = "hidden",
    dbname=connect.string
)

This is the db.R file

danimaiams · December 21, 2018, 10:34am

The connection is ok.

I'll try this tip now. Really Thanks.

danimaiams · December 28, 2018, 1:16pm

Ok. I could make it work, but with no reactive values.

I have this Gadgets for inputs:

                               numericInput(inputId= "diametro", label= "Diametro do Aco:", value= 0),
                               
                               numericInput(inputId= "parede", label= "Parede:", value= 0),
                                                              
                               textInput(inputId= "aqa", label= "AQA:", value= 0),

And I have the following Data.R

query <- sprintf( 
  "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 :1
  and esp_par_tubo :2
  and VAL_LIM_ESCOAMENTO != 0
  
  order by DTH_CRIACAO_REG desc"
)

df <- dbGetQuery(
  connection_reportUser,
  query

How can I make "DIM_EXT_TUBO :1" and "esp_par_tubo :2" receive the values from these Gadgets input?

 numericInput(inputId= "diametro", label= "Diametro do Aco:", value= 0),
                               
 numericInput(inputId= "parede", label= "Parede:", value= 0),

I tried something like this:

...
df <- dbGetQuery(
  connection_reportUser,
  query,
  data.frame(input$diametro, input$parede)

but it returns an erro saying that the "function input was not found".

Thx!

andresrcs · December 28, 2018, 2:50pm

You have, at least, two options, build the sql query inside a reactive expression

library(glue)

output$contents <- renderDataTable({
    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 VAL_LIM_ESCOAMENTO != 0
     order by DTH_CRIACAO_REG desc")
    content <- dbGetQuery(con, query)
    content
})

Or fetch the entire data and do the filtering later inside a reactive expression

library(dplyr)

query <- "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 VAL_LIM_ESCOAMENTO != 0
     order by DTH_CRIACAO_REG desc")
content <- dbGetQuery(con, query)

output$contents <- renderDataTable({
     content %>%
          filter(DIM_EXT_TUBO == as.numeric(input$diametro), esp_par_tubo == as.numeric(input$parede))
})

cderv · December 28, 2018, 9:16pm

How do you connect with the database ? I don't see any code that make the connection. How do you retrieve the data from the database from R ?

danimaiams · January 3, 2019, 12:07pm

I'm using hte following code in db.R file

library("ROracle")

Sys.setenv(TZ = "GMT-3")
Sys.setenv(ORA_SDTZ = "GMT-3")

driver <- dbDriver("Oracle")
host <- "hidden"
port <- hidden
sid <- "hidden"

connect.string <- paste(
  "(DESCRIPTION=",
  "(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
  "(CONNECT_DATA=(SID=", sid, ")))",
  sep = ""
)

connection <- dbConnect(
  driver, username = "DWA_ODS",
  password = "DWA_ODS",
  dbname=connect.string
)

connection_reportUser <- dbConnect(
  driver, username = "report_user",
  password = "hidden",
  dbname=connect.string
)

danimaiams · January 3, 2019, 12:12pm

Do I have to use this code on the server.R or in the data.R?

I tried to manage this in both files but it just didn't work.

My server.R file:

  output$contents2 = DT::renderDataTable({
    tabela_saida
  })

My data.R file:

output$contents <- renderDataTable({
    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 VAL_LIM_ESCOAMENTO != 0
     order by DTH_CRIACAO_REG desc")

df <- dbGetQuery(
  connection_reportUser,
  query
)
df

But it appears the following error:

Error in source("data.R") :
data.R:53:0: unexpected end of input

system · January 24, 2019, 12:12pm

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