SQL query in Shiny

[dsamhat]

I am trying to create a shiny app that will query a sql table based on the text input of a user.  I think I am close but continue to receive an error stating "$ operator is invalid for atomic vectors".  Any help would be appreciated.  

UI Code

library(shiny)

shinyUI(pageWithSidebar(

  

  # Application title

  headerPanel("Stock"),

  

  sidebarPanel(

    textInput("symbol", "Symbol", ""),

    

    submitButton(text="Submit")

  ),

   

 mainPanel(

    plotOutput("ratePlot"))

))

Server Code

library(shiny)

library(RODBC)

dbhandle <- odbcDriverConnect('driver={SQL Server Native Client 10.0};server=ppi-sql9;database=VendorData;trusted_connection=yes')

locate <- sqlQuery(dbhandle,"SELECT [TransDate]

                   ,[UnderlyingSymbol]

                   ,[Rate]

                   FROM [VendorData].[dbo].[tblBorrow]

                   where UnderlyingSymbol=toupper(input$symbol)")

shinyServer(function(input, output) {

  output$ratePlot <- renderPlot({

    data <- locate[locate$UnderlyingSymbol == toupper(input$symbol),]

    if (nrow(data) == 0)

      NULL

    else

      plot(data$Rate)

  })

  

})

[Mike C]

well, im assuming the issue is in the query portion and not the output portion (but you may want to check this if you haven't already), so my first guess would be to make reactives from the inputs, and use those in the queries instead, and see if that is a sufficient 'fix',
ie
sym=reactive({
input$symbol

})

locate <- sqlQuery(dbhandle,"SELECT [TransDate]

                   ,[UnderlyingSymbol]

                   ,[Rate]

                   FROM [VendorData].[dbo].[tblBorrow]

                   where UnderlyingSymbol=toupper(sym())")

personally I would consider throwing in an action button in the ui and an isolate in the sym() so it wouldn't do a query each time a letter was typed.  It would also help with debugging, where putting an
if(input$myactionbutton<1)
return()
browser()
would show if the error occurred in the query or in the output.  Hope this helps.

[dsamhat]

Thanks for the response Mike.  I used your suggestions and I believe I am further along now but I am having problems now with the plotting function.  I keep receiving " Error in locate[, "Rate"] : incorrect number of dimensions.  My updated server code is below.  Thanks again for the help. 

library(shiny)

library(RODBC)

dbhandle <- odbcDriverConnect('driver={SQL Server Native Client 10.0};server=ppi-sql9;database=VendorData;trusted_connection=yes')

sym=reactive({

  input$symbol

})

#sym <- input$symbol

locate <- sqlQuery(dbhandle,"SELECT [TransDate]

                   ,[UnderlyingSymbol]

                   ,[Rate]

                   FROM [VendorData].[dbo].[tblBorrow]

                   where UnderlyingSymbol=toupper(sym())")

shinyServer(function(input, output) {

  output$ratePlot <- renderPlot({

   # data <- read.table(locate)

    #data <- locate

    if (is.null(data))

      NULL

    else 

      x <- as.numeric(locate[,"Rate"])

      plot(x, xlim = c(1,100), ylim =c(1,100))

    

  })

  

})