3

This is a follow up from this question:

Acessing SQL database using shiny with reactive query

I am trying to build a data frame from data fetched from an SQL database using a shiny app. Currently i am able to query the database and return one set of data. Now I would like to save that data to a data frame and then add more data from subsequent queries. Here is my code:

UI

library(markdown)

shinyUI(fluidPage(
  titlePanel("Generic grapher"),
  sidebarLayout(
    sidebarPanel(

      numericInput("wafer", label = h3("Select wafer ID:"), value = NULL),

      actionButton("do", "An action button")
      ),

      mainPanel(
        verbatimTextOutput("value"), verbatimTextOutput("que"), dataTableOutput(outputId="pos")
      )
    )
  )
)

Server:

library(RMySQL)
library(DBI)
library(sqldf)

con = dbConnect(RMySQL::MySQL(), dbname="xx", username="pete", password="xx", host="xx", port=3306)
query <-  function(...) dbGetQuery(con, ...) 

wq = data.frame()

shinyServer(function(input, output){

  d <- eventReactive(input$do, { input$wafer })

  output$value <- renderPrint({ d() }) 

  a <- reactive({ paste("Select id from wafer where wafer_id=",d(), sep="") })

  output$que <- renderPrint({ a() }) 

  wq <- reactive({  query( a() ) })

  output$pos <- renderDataTable({ wq() })  

  })

Now I am trying to use the information from these two answers to store the data from each search I do in a data frame:

Add values to a reactive table in shiny

What's the difference between Reactive Value and Reactive Expression?

New Server:

library(RMySQL)
library(DBI)
library(sqldf)

con = dbConnect(RMySQL::MySQL(), dbname="xx", username="pete", password="xx", host="xx", port=3306)
query <-  function(...) dbGetQuery(con, ...) 

wq = data.frame()

shinyServer(function(input, output){

  values <- reactiveValues()
  values$df <- data.frame()

  d <- eventReactive(input$do, { input$wafer })

  output$value <- renderPrint({ d() }) 

  a <- reactive({ paste("Select id from wafer where wafer_id=",d(), sep="") })

  output$que <- renderPrint({ a() }) 

  wq <- reactive({  query( a() ) })

  values$df <- reactive({ rbind(values$df, wq() )   }) 

  output$pos <- renderDataTable({ values$df })  

  })

However, when I do this the data table never renders within my app. I dont have an error message. Any ideas where Im going wrong? Any help appreciated!

Community
  • 1
  • 1
Pete900
  • 2,016
  • 1
  • 21
  • 44
  • I have just published an example app that does this: https://github.com/MarkEdmondson1234/ga-dashboard-demo – MarkeD Jul 16 '15 at 09:40
  • check out the server.r for "eventTable" and "eventData", and the MySQL functions in functions.r such as loadData() – MarkeD Jul 16 '15 at 09:44
  • Thanks MarkeD, I will have a go a replicating. That app is very cool! I will learn a lot from it and hopefully implement some of the functions. – Pete900 Jul 16 '15 at 09:47

1 Answers1

2

I think changing

values$df <- reactive({ rbind(values$df, wq() )   })

in your new server.R to

observe({
  values$df <- rbind(isolate(values$df), wq())
})

might fix your problem.

EDIT: Here's a working example using a local connection:

library(markdown)
library(RMySQL)
library(DBI)
library(sqldf)

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars)
query <-  function(...) dbGetQuery(con, ...) 

wq = data.frame()

ui <- shinyUI(fluidPage(
  titlePanel("Generic grapher"),
  sidebarLayout(
    sidebarPanel(

      numericInput("wafer", label = h3("Select number of cylinders:"),
                   value = NULL),

      actionButton("do", "An action button")
    ),

    mainPanel(
      verbatimTextOutput("value"),
      verbatimTextOutput("que"),
      verbatimTextOutput("wq_print"),
      dataTableOutput(outputId="pos")
    )
  )
)
)

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

  values <- reactiveValues()
  values$df <- data.frame()

  d <- eventReactive(input$do, { input$wafer })

  output$value <- renderPrint({ d() }) 

  a <- reactive({ paste("SELECT * FROM mtcars WHERE cyl = ", d(), sep="") })

  output$que <- renderPrint({ a() }) 

  observe({
    if (!is.null(d())) {
      wq <- reactive({  query( a() ) })

      output$wq_print <- renderPrint({ print(str(wq())) })

      values$df <- rbind(isolate(values$df), wq())
    }
  })

  output$pos <- renderDataTable({ values$df })  

})

shinyApp(ui, server)

The relevant changes to your original code are the !is.null(d()) condition for handling the initial NULL value of d(), and using values$df <- rbind(isolate(values$df), wq()) inside an observer. Hope this helps with fixing your code!

Mikko Marttila
  • 10,972
  • 18
  • 31
  • Thanks for you reply. On trying I get the error: observe({ values$df <- rbind(values$df, wq()) }) ERROR: [on_request_read] connection reset by peer – Pete900 Jul 16 '15 at 09:40
  • ah hang on didnt see isolate – Pete900 Jul 16 '15 at 09:41
  • ah still same error with isolate: observe({ values$df <- rbind(isolate(values$df), wq()) }) ERROR: [on_request_read] connection reset by peer – Pete900 Jul 16 '15 at 09:42
  • Is the `dbConnect` call you show in the code the correct one to use? When I try reproducing your example, I get an error connecting to the database followed by `ERROR: [on_request_read] connection reset by peer`. – Mikko Marttila Jul 16 '15 at 10:02
  • er dam, i was supposed to leave out the details :-s ! but yes that is correct...ok need to edit that ASAP ! – Pete900 Jul 16 '15 at 10:07
  • sorry about that. Anyway yes, thats the same error message I get. The connection works for other things and was working before i changed the code. – Pete900 Jul 16 '15 at 10:10
  • yes ive just checked, the dbConnect works without the updated code to observe values$df – Pete900 Jul 16 '15 at 10:12
  • Heh, fair enough! I played around using a local connection instead. I think the problem is that on launch the value of `d()` will be `NULL`. I'll edit a working example in to my answer in a bit. – Mikko Marttila Jul 16 '15 at 10:25
  • @Pete Added a working example, hope you can pick up the relevant changes to your code and see if they help. – Mikko Marttila Jul 16 '15 at 10:41
  • Thank you so much. Also, thanks for being sensitive to my blunder of putting SQL login online!! Brilliant. – Pete900 Jul 16 '15 at 10:59