CODEWITHSUNDEEP
rshinyshinydashboardshinyapps
Ashish Talgotra
Ashish Talgotra

Reputation: 31

TextInput not giving results in shiny R

I am making a app where the user will enter the id of a company (i.e. PID) and click submit to get the results from SQL server database. I have created the below mentioned code but the code is not picking up the Input value in the SQL query. Please help. The code i have created so far is - 

#install.packages("RODBC")
#install.packages("RODBCext")
library(shiny)
library(RODBC)
library(RODBCext)
library(DT)
library(sqldf)

#UI

ui<-fluidPage(

  #Application Title

  titlePanel(title = "The is Default Database UI Testing Version!!"),
  sidebarLayout(
    sidebarPanel(
      textInput("PID","Enter company's PID",""),
      submitButton("Submit", icon("table"))

    ),

    mainPanel(

      h4("Database information"),
      DT::dataTableOutput({"mydata"})
      #tableOutput("mydata")
    )



  ))

#server

shinyServer <- function(input,output)

{

  #connect to database

con = odbcConnect('DDB')


  #build query
  query <-
"
select [COMPANY].[COMP_ID],[COMPANY].[PID], [COMPANY].[COMP_NAME],[CNTRY_NAME],EVENT.[EVENT_ID],[EVENT],[CONSID_DESC],[EVENT_YR],[EVENT_MON],[EVENT_DAY],[PATH]
               from EVENT inner join [dbo].[COMPANY] on EVENT.[COMP_ID] = [dbo].[COMPANY].[COMP_ID]
               inner join [dbo].[EVENT_TYPE] on EVENT.[EVENT_TYPE_ID] = [dbo].[EVENT_TYPE].[EVENT_TYPE_ID]
               inner join [dbo].[SPECIAL_CONSIDERATION] on EVENT.[CONSID_ID] = [dbo].[SPECIAL_CONSIDERATION].[CONSID_ID]
               inner join [dbo].[COUNTRY] on [dbo].[COMPANY].[CNTRY_CD] = [dbo].[COUNTRY].[CNTRY_CD]
               left join [dbo].[EVENT_DOC] on EVENT.[EVENT_ID] = [dbo].[EVENT_DOC].[EVENT_ID]
               where PID = '?,input$PID'"
(
res <- sqlQuery(con,query))

#ddf<-data.frame('res')

 # df[qry$mydata == input$select_mydata, ]
  output$mydata = DT::renderDataTable({res})
  #output$mydata = renderPrint({res})

}
  shinyApp(ui, shinyServer)

Upvotes: 0

Views: 59

Answers (1)

Pork Chop
Pork Chop

Reputation: 29407

Try this:

library(shiny)
library(RODBC)
library(RODBCext)
library(DT)
library(sqldf)

con <- odbcConnect('DDB')
query <- "select [COMPANY].[COMP_ID],[COMPANY].[PID], [COMPANY].[COMP_NAME],[CNTRY_NAME],EVENT.[EVENT_ID],[EVENT],[CONSID_DESC],[EVENT_YR],[EVENT_MON],[EVENT_DAY],[PATH] from EVENT inner join [dbo].[COMPANY] on EVENT.[COMP_ID] = [dbo].[COMPANY].[COMP_ID] inner join [dbo].[EVENT_TYPE] on EVENT.[EVENT_TYPE_ID] = [dbo].[EVENT_TYPE].[EVENT_TYPE_ID] inner join [dbo].[SPECIAL_CONSIDERATION] on EVENT.[CONSID_ID] = [dbo].[SPECIAL_CONSIDERATION].[CONSID_ID] inner join [dbo].[COUNTRY] on [dbo].[COMPANY].[CNTRY_CD] = [dbo].[COUNTRY].[CNTRY_CD] left join [dbo].[EVENT_DOC] on EVENT.[EVENT_ID] = [dbo].[EVENT_DOC].[EVENT_ID] where PID = MYSHINYPID"

ui <- fluidPage(
  titlePanel(title = "The is Default Database UI Testing Version!!"),
  sidebarLayout(
    sidebarPanel(
      textInput("PID","Enter company's PID",""),
      actionButton("Submit", icon("table"))

    ),
    mainPanel(
      h4("Database information"),
      dataTableOutput('mydata')
    )
  )
)


server <- function(session,input,output){

  sqldata <- eventReactive(input$Submit,{
    req(input$PID)
    query <- gsub("MYSHINYPID",input$PID,query)
    sqlQuery(con,query)
  })

  output$mydata <- DT::renderDataTable({
    sqldata()
  })

}

shinyApp(ui, server)
  1. We going to use the string replace gsub for the query
  2. Then will add req() for the PID so the user has to enter something
  3. The connections ideally should be in the global, so all users can use it only once

Upvotes: 1

Related Questions