0
I've consulted many Stack articles and Rstudio pages, and can't determine why my SHINY app, which references an ODBC database, works locally but not when it's placed on my SHINY server (free version).
I've tried using the pool package, but it does not help. For example, when I run the pool example it functions fine from RStudio, but not when I try to access it via the web. In contrast, if I make an app that uses the preloaded R datasets (such as mtcars or iris), my apps work on the web.
My ODBC database is in SQL, and holds data from a WordPress site (GravityForms plugin).
What are the essential steps to create a SHINY app, which references databases , to work on the web?
My app uses a ui server and global file; each is shown below.
ui
library(dplyr)
library(ggplot2);library(stringr);library(shiny);library(DT)
# Define UI for application that draws a histogram
ui <- shinyUI(fluidPage(
# Application title
titlePanel("Online Events Reporter"),
fluidRow(
column(3,
h4("Select Your Event:"),
selectInput("eventSelect","Event Name",eventchoice) ) ),
fluidRow(column(8,
plotOutput("evalPlot"))),
hr(),
dataTableOutput("table")
) )
server
library(dplyr);library(DBI);library(DT)
library(ggplot2);library(stringr);library(shiny);library(zoo)
# Define server logic required to draw a histogram
server <- function(input, output,session) {
# event data contains only responses from one of the eval quetsions
eventdata <-reactive({
subset(temp, temp$title2==input$eventSelect) })
comments2 <-reactive({
subset(comments, comments$title2==input$eventSelect) })
output$evalPlot <- renderPlot({
# generate bins based on input$bins from ui.R
ggplot(data=eventdata(),
mapping=aes(x=eventdata()$title2, fill=factor(eventdata()$Rating) ),
position = "fill") +
geom_bar()+theme_minimal()+ggtitle("Summary of Evaluation Responses")
})
output$table = renderDataTable(comments2(),
options=list(
pageLength=8)
)
}
global
library(DBI);library(dplyr);library(zoo)
con <- dbConnect(odbc::odbc(), "tablename", timeout = 10)
gfdata<-dbGetQuery(con,'SELECT * FROM `tablename`.`namehere` ')
onlineSesh<-gfdata[gfdata$form_id==11,] # all works fine
# create options for ui drop-down menu
eventchoice<-onlineSesh %>%
dplyr::filter(meta_key=="2") %>%
select(meta_value) %>% unique() %>% unlist(use.names = F)
# sloppy munging in here but it works...
t<-onlineSesh
x<-onlineSesh[onlineSesh$meta_key=='3', c( "meta_value","entry_id")] # dates
colnames(x)[1]<-"sessiondate"
t<-merge(t, x,by="entry_id")
colnames(t)[1]<-"WorkshopID"
eitems<-c("Strongly Agree", "Agree","Slightly Agree","Slightly Disagree","Disagree","Strongly Disagree")
# when meta_key==2, title
t$title<-ifelse(t$meta_key=='2',t$meta_value,NA)
t$title2<-na.locf(t$title,na.rm = FALSE) # we need the event title on all relevant records!!!
t$Rating<-factor(t$meta_value, levels=c("Strongly Agree", "Agree","Slightly Agree",
"Slightly Disagree","Disagree","Strongly Disagree"),ordered = T)
# keep records that contain answers;
temp<-base::subset(t, meta_value %in% eitems) # this file utilized by SHINY
comments<-subset(t, t$meta_key=="19") # another file utilized by SHINY
comments<-comments %>% select(title2,meta_value,sessiondate)