3

I want to use Shiny to let a user build a data frame by accessing an SQL data base. I want it to work in the following way (but open to suggestions):

  • User types in ID (its a number)
  • User presses action button
  • Reactive query is generated
  • Query is sent and data is retrieved, and added to data frame
  • User types in next ID...

Here is my attempt:

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="Test_DB", username="pete", password="xx", host="xx", port=3306)
query <-  function(...) dbGetQuery(con, ...) 

wq = data.frame()
df = 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({ rbind(wq, query( a() )) })

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

When I run, I can see that both the Id and query will print correctly. However I get this error when app firsts runs:

error in evaluating the argument 'statement' in selecting a method for function 'dbGetQuery': Error:

Then when I type in a wafer id, I get:

Error in xi[[j]] : object of type 'closure' is not subsettable

I know from these posts:

R shiny ERROR: object of type 'closure' is not subsettable

Error in <my code> : object of type 'closure' is not subsettable

That i am probabaly trying to subset an R base function without defining the variable name...but i feel stupid, Ive been staring at it for a day and cant see it. Maybe my whole code is a bad way to query SQL by shiny? Any help appreciated.

Ah dam, ok..if i change this:

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

to this:

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

Then I get an output. So im sorry, I guess my question changes to how to then populate a df with each additional click of the action button?

Community
  • 1
  • 1
Pete900
  • 2,016
  • 1
  • 21
  • 44

1 Answers1

0

Store your final object in a list you define as:

wq<- reactiveValues()
....
isolate()

I'm working on something similar with updating a model statement with reactively generated interaction terms. I had to use reactiveValues() and isolate() to get it to work. Joe Cheng has an example Gist.

Here's a link. Maybe it will help you. https://gist.github.com/jcheng5/eaedfed5095d37217fca

Best, NF

nate
  • 1,172
  • 1
  • 11
  • 26
  • 1
    also-you might not be catching the NULL status of objects on startup. This is also shown in the Gist. Helpful tip I wanted to add. Anyways, Good luck! – nate Sep 08 '15 at 21:24
  • Hi. Yes thanks I managed to find this eventually and all seems to work. Ive started to have problems recently though so I will post a separate question. Cheers. Pete – Pete900 Sep 09 '15 at 05:31
  • Cool beans. Keep on keeping on. We all struggle with R together. --NF – nate Sep 11 '15 at 16:35