Server code unable to display out on my shiny app

shiny
, ,
1

Good day everyone. so I am encountering huge challenge on Shiny app. I am trying to build a process control model for a bank that determines the completeness or otherwise when data are spooled. It does this by comparing the table features from source to that of destination and tells you that your spool is 'complete', 'incomplete' or 'duplicated'. source is from oracle db and destination is sql db, I therefore connected R to oracle and sql db, and wrote an R script to compare the both tables.
Finally I am building a shiny app to display this comparison, I have written the U.I code which works perfectly well but the server code returns absolutely nothing when ran. Please I need a solution. Here is the code. In this code, I have captured 3 things I want tto see, Spc model, graph for row completeness, and graph for missing fields. Thank you in anticcipation



library(shiny)
library(shinythemes)
library(RODBC)


# Define UI
ui <- shinyUI(fluidPage(
  headerPanel(title="STATISTICAL PROCESS CONTROL"),
  sidebarLayout(
    sidebarPanel(
      tags$h3("input:"),
      textInput("txt1", "oracle script:", "select * from xxxxxxx"),
      textInput("txt2", "sql script:", "SELECT * FROM xxxxxx"),
      
    ), # sidebarPanel
    
    submitButton("submit")
  ),
  
  mainPanel(
    tabsetPanel(type = "tab",
                tabPanel("spc",tableOutput("spc")),
                tabPanel("plot_rowcount",plotOutput("plot_rowcount")),
                tabPanel("plot_cell",plotOutput("plot_cell"))
                
                
    ) # mainPanel
    
  ), # Navbar 1, tabPanel
  
  
) # navbarPage
) # fluidPage




# Define server function  
shinyServer(function(input, output) {
  
  SPC= reactive({req(input$txt1,input$txt2)
    
    #SQL_DB_CONNECTION
    con=odbcConnect("dsn",uid="sa", pwd="Analytics10$")
    on.exit(odbcClose(con), add=TRUE) 
    sql_db= sqlQuery(con,paste(input$txt1, ";"))
    
    #ORACLE_DB_CONNECTION
    con_2 <- odbcConnect("oracle_dsn",uid="DATAANALYTICS", pwd="password10$")
    on.exit(odbcClose(con_2), add = TRUE)
    odbcQuery(con_2,paste(input$txt2, ";"))
    oracle_db = sqlGetResults(con_2, as.is=FALSE, errors=FALSE, max=1000000, buffsize=1,nullstring=NA, na.strings="NA", believeNRows=TRUE, dec=getOption("dec"))
    
    #ROW_COUNT_STATISTICS
    source_count= nrow(oracle_db)
    Destination_count=nrow(sql_db)
    spool_status = if (source_count>Destination_count) {print("incomplete")} else if (source_count<Destination_count) {print("duplicate error")} else {print("complete")}
    ROW_COUNT_STATISTICS = data.frame(source_count,Destination_count,spool_status) 
    
    #CELL_STATISTICS
    missing_fields= length(which(is.na(sql_db)))
    No_cell_fields=nrow(sql_db)*ncol(sql_db)
    complete_fields= No_cell_fields - missing_fields
    percentage_cell_completeness= complete_fields/No_cell_fields *100
    CELL_STATISTICS= data.frame(missing_fields,complete_fields,percentage_cell_completeness)
    
    SPC= list(ROW_COUNT_STATISTICS=ROW_COUNT_STATISTICS,CELL_STATISTICS=CELL_STATISTICS)
    
    #PLOTTING A GRAPH
    options(scipen=999)
    library(ggplot2)
    
    #ROW COUNT PLOT
    ROW_COUNT= c(source_count,Destination_count)
    Table=c("Source","Destination")
    ROW_COUNT_STAT_ggplot=data.frame(ROW_COUNT,Table)
    plot_1 = ggplot(ROW_COUNT_STAT_ggplot,aes(x= Table,y=ROW_COUNT)) + geom_point(aes(col=spool_status), size=5) + ggtitle("ROW PLOT") 
    p= if (source_count>Destination_count) {source_count} else {(Destination_count)}
    measurement=coord_cartesian(ylim=c(0,p))
    plot_rowcount=plot_1+measurement
    
    #CELL_PLOT
    fields= c("Missing_fields","Complete_fields")
    Cell_count=c(missing_fields,complete_fields)
    CELL_PLOT_ggplot=data.frame(fields,Cell_count)
    plot_2= ggplot(CELL_PLOT_ggplot,aes(x= fields,y=Cell_count))+ geom_count(aes(fill=percentage_cell_completeness)) + ggtitle("CELL PLOT")
    measurement_2=coord_cartesian(ylim=c(0,No_cell_fields))
    plot_cell= plot_2 +measurement_2
  })
  
  output$spc <-renderTable{SPC}
  
  
}  

#Create Shiny object
shinyApp(ui = ui, server = server)
2

obviously theres no hope of us running this code, as we don't have your database.
but what do you want SPC object to contain(return) ?
plot_cell ?

 plot_cell= plot_2 +measurement_2
  return(plot_cell)
  })

also if it is reactive than you would access it with function brackets,
and render functions are regular functions that need regular params (whether or not you use curly ones also)

  
  output$spc <-renderTable({
                             SPC()
                           })
3

Okay, my inputs are the sql and oracle scripts, and I want it to return SPC, plot_rowcount and plot_cell. Thanks for your immediate help

4

if you want the SPC reactive to 'be' 3 things, you need to put those 3 things into a list that SPC() can return.
But you wont be able to directly 'render that. rather you'll have to pick the renderable item from in that list

SPC <- reactive({
etc. etc.

return (list("spc"=spc,"plot_cell"=plot_cell,"plot_rowcount"=plot_rowcount))
)} #end SPC() 

# later use 
output$spc <-renderTable({
                            SPC()$spc # possibly ?
                          })
5

wow, my choice of not making SPC a list where the plots become an element is because when I display spc, it wont show the graphs, it will just show the graph details, except the graphs are seperated.

secondly, is it possible not to make spc reactive and still get my desired result?, as the result of spc is dependent on the sql and oracle script inputted.

finally, I did the correction you highlighted on your first response and it gave me the following error. What would be your advice on my best approach. Thank you

first argument is not an open RODBC channel
Error: no function to return from, jumping to top level
Error: no function to return from, jumping to top level
Error in output$SPC = renderTable({ : object 'output' not found
Error in output$plot_rowcount = renderPlot({ : object 'output' not found
Error: unexpected '}' in "}"

6

SPC or spc ?
its confusing. better names where the roles of the object are clearly defined will help you write better code.
you can display plots that are in a list. it just means you have to choose them.
do you want an example of that ?

I find this confusing, because typically reactivity is desired when inputs are updated. what is your problem with the 'reactivity' ?

well , lets not debug something that was a misundertanding, you dont just want to return plot_cell after all but rather 3 things.

I think it would be better if you could hardcode some database response to simulate the db without needing a db, and simulate input data, without needing input data so that others could run your code, its a million times easier to debug the problematic parts

7

Im sorry if I confused you with my second question as i am new to R and Shiny. Thank you for clearing me on the meaning of reactivity.
I followed your initial format and got minimal error.

##New server code
return (list(P=SPC,plot_cell=plot_cell,plot_rowcount=plot_rowcount))
  )} 
  
  
  outPut$P <-renderTable({
    SPC()$P})
  outPut$plot_cell <-renderPlot({
    SPC()$Plot_cell})
  output$plot_rowcount= renderPlot({
    SPC()$Plot_rowcount})
  
}


# Create Shiny object
shinyApp(ui = ui, server = server)


##New main panel code


mainPanel(
    tabsetPanel(type = "tab",
                tabPanel("SPC MODEL",tableOutput("P")),
                tabPanel("plot_rowcount",plotOutput("plot_rowcount")),
                tabPanel("plot_cell",plotOutput("plot_cell"))


##Error
   

)} 
Error: unexpected ')' in:
"    return (list(P=SPC,plot_cell=plot_cell,plot_rowcount=plot_rowcount))
  )"
>   
>   
>   outPut$P <-renderTable({
+     SPC()$P})
Error in outPut$P <- renderTable({ : object 'outPut' not found
>   outPut$plot_cell <-renderPlot({
+     SPC()$Plot_cell})
Error in outPut$plot_cell <- renderPlot({ : object 'outPut' not found
>   output$plot_rowcount= renderPlot({
+     SPC()$Plot_rowcount})
Error in output$plot_rowcount = renderPlot({ : object 'output' not found
>   
> }
Error: unexpected '}' in "}"
> 
> 
> # Create Shiny object
> shinyApp(ui = ui, server = server)

Listening on http://127.0.0.1:7215
8

R is type sensitive, aBC is different from Abc and abc and ABC.
here you have plot_cell being returned in a list but you try to read Plot_cell from a list , etc.
also outPut has been capitalised, shiny's output list is all lowercase output.

9

Thank you for your corrections, after adjusting codes as advised, error still persist
Please see below. Thanks

server code

# Define server function  
server <- function(input, output) {
  
  SPC= reactive({req(input$txt1,input$txt2)
    #SQL_DB_CONNECTION
    con=odbcConnect("dsn",uid="sa", pwd="Analytics10$")
    on.exit(odbcClose(con)) 
    sql_db=sqlQuery(con,"input$txt1")
    
    #ORACLE_DB_CONNECTION
    con_2 <- odbcConnect("oracle_dsn",uid="DATAANALYTICS", pwd="password10$")
    on.exit(odbcClose(con_2))
    odbcQuery(con_2,"input$txt2")
    oracle_db = sqlGetResults(con_2, as.is=FALSE, errors=FALSE, max=1000000, buffsize=1,nullstring=NA, na.strings="NA", believeNRows=TRUE, dec=getOption("dec"))
    
    #ROW_COUNT_STATISTICS
    source_count= nrow(oracle_db)
    Destination_count=nrow(sql_db)
    spool_status = if (source_count>Destination_count) {print("incomplete")} else if (source_count<Destination_count) {print("duplicate error")} else {print("complete")}
    ROW_COUNT_STATISTICS = data.frame(source_count,Destination_count,spool_status) 
    
    #CELL_STATISTICS
    missing_fields= length(which(is.na(sql_db)))
    No_cell_fields=nrow(sql_db)*ncol(sql_db)
    complete_fields= No_cell_fields - missing_fields
    percentage_cell_completeness= complete_fields/No_cell_fields *100
    CELL_STATISTICS= data.frame(missing_fields,complete_fields,percentage_cell_completeness)
    
    SPC= list(ROW_COUNT_STATISTICS=ROW_COUNT_STATISTICS,CELL_STATISTICS=CELL_STATISTICS)
    
    
    #PLOTTING A GRAPH
    options(scipen=999)
    library(ggplot2)
    
    #ROW COUNT PLOT
    ROW_COUNT= c(source_count,Destination_count)
    Table=c("Source","Destination")
    ROW_COUNT_STAT_ggplot=data.frame(ROW_COUNT,Table)
    plot_1 = ggplot(ROW_COUNT_STAT_ggplot,aes(x= Table,y=ROW_COUNT)) + geom_point(aes(col=spool_status), size=5) + ggtitle("ROW PLOT") 
    p= if (source_count>Destination_count) {source_count} else {(Destination_count)}
    measurement=coord_cartesian(ylim=c(0,p))
    plot_rowcount=plot_1+measurement
    
    
    
    #CELL_PLOT
    fields= c("Missing_fields","Complete_fields")
    Cell_count=c(missing_fields,complete_fields)
    CELL_PLOT_ggplot=data.frame(fields,Cell_count)
    plot_2= ggplot(CELL_PLOT_ggplot,aes(x= fields,y=Cell_count))+ geom_count(aes(fill=percentage_cell_completeness)) + ggtitle("CELL PLOT")
    measurement_2=coord_cartesian(ylim=c(0,No_cell_fields))
    plot_cell= plot_2 +measurement_2
    
    return (list(P=SPC,plot_cell=plot_cell,plot_rowcount=plot_rowcount))
  )} 
  
  
  output$P <-renderTable({
    SPC()$P})
  output$plot_cell <-renderPlot({
    SPC()$plot_cell})
  output$plot_rowcount= renderPlot({
    SPC()$plot_rowcount})
  
}


# Create Shiny object
shinyApp(ui = ui, server = server)

#UI CODE

# Define UI
ui <- shinyUI(fluidPage(
  headerPanel(title="STATISTICAL PROCESS CONTROL"),
  sidebarLayout(
    sidebarPanel(
      tags$h3("Input:"),
      textInput("txt1", "oracle script:", ""),
      textInput("txt2", "sql script:", ""),
      
    ), # sidebarPanel
    
    submitButton("SUBMIT")
  ),
  
  mainPanel(
    tabsetPanel(type = "tab",
                tabPanel("SPC MODEL",tableOutput("P")),
                tabPanel("plot_rowcount",plotoutput("plot_rowcount")),
                tabPanel("plot_cell",plotoutput("plot_cell"))
                
                
    ) # mainPanel
    
  ), # Navbar 1, tabPanel
  tabPanel("Navbar 2", "This panel is intentionally left blank"),
  tabPanel("Navbar 3", "This panel is intentionally left blank")
  
) # navbarPage
) # fluidPage

##ERROR


>   output$P <-renderTable({
+     SPC()$P})
Error in output$P <- renderTable({ : object 'output' not found
>   output$plot_cell <-renderPlot({
+     SPC()$plot_cell})
Error in output$plot_cell <- renderPlot({ : object 'output' not found
>   output$plot_rowcount= renderPlot({
+     SPC()$plot_rowcount})
Error in output$plot_rowcount = renderPlot({ : object 'output' not found
>   
> }
Error: unexpected '}' in "}"
> 
> 
> # Create Shiny object
> shinyApp(ui = ui, server = server)

Listening on http://127.0.0.1:3967
10

you have capitalisation typos here for plotOutput (you lowercased the O)

the bracket order is incorrect , should be })

11

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