CODEWITHSUNDEEP
r
ASH
ASH

Reputation: 20302

Use Shiny App to Query a SQL Server DB

I guess I really don't understand this process like I thought I did. I found the link below, and it seems pretty helpful, but after making only slight modifications, I can't get it to do what I want.

R Shiny SQL Server Query

I just want to launch a webpage and have a user enter a parameter, to be passed into a query. 

library(RODBCext)
library(shiny)

ui <- shinyUI(

  pageWithSidebar(

    headerPanel("Hide Side Bar example"),
    sidebarPanel(
      textInput("CATEGORY", "Enter CATEGORY below"),
      submitButton(text="Submit")
    ),
    mainPanel(
      tabsetPanel(
            tabPanel("Data", tableOutput("tbTable"))
      )

    )
  )
)

browser()

server <- function(input, output, session)      
  myData <- reactive({
    req(input$Id)

    #connect to database 
    dbhandle = odbcDriverConnect('driver={SQL Server};server=my_server;database=data_WH;trusted_connection=true')

    browser()

    #build query
    #query = "SELECT * FROM [my_db].[dbo].[my_table] where [CATEGORY] = '1070'"
query = "SELECT * FROM [my_db].[dbo].[my_table] where [CATEGORY] = ?"

    browser()

    #store results
    res <- sqlExecute(channel = dbhandle, 
                      query = query,
                      data = list(input$Id),
                      fetch = TRUE,
                      stringsAsFactors = FALSE) 

    #close the connection
    odbcClose(dbhandle)
    #return results
    res
  })

  output$tbTable <- 
    renderTable(
      myData()
)

shinyApp(ui = ui, server = server)

The SQL string is fine. I entered browser, to try to debug the script, as described in the link below.

https://shiny.rstudio.com/articles/debugging.html

browser()

It didn't pause the code; it didn't help me debug the code. It didn't do anything at all.

Any thoughts anyone?

Upvotes: 2

Views: 10561

Answers (1)

Benjamin
Benjamin

Reputation: 17369

It looks like your server function is incorrectly defined.

A function defined of the form

fn <- function(arg1, arg2)
 expression1

 expression2

Is actually going to be evaluated as 

fn <- function(arg1, arg2){
  expression1
}

expression2

In your server function, because you didn't place your braces, the only expression in the function definition is creating the myData reactive. Your output$tbTable element is completely separate from your function, never gets called in the app, and so your reactive is never processed. You should try this:

library(RODBCext)
library(shiny)

ui <- shinyUI(

  pageWithSidebar(

    headerPanel("Hide Side Bar example"),
    sidebarPanel(
      textInput("CATEGORY", "Enter CATEGORY below"),
      submitButton(text="Submit")
    ),
    mainPanel(
      tabsetPanel(
        tabPanel("Data", tableOutput("tbTable"))
      )

    )
  )
)

server <- function(input, output, session)    
{ # NOTE THE BRACE HERE
  myData <- reactive({
    req(input$CATEGORY)

    #connect to database 
    dbhandle = odbcDriverConnect('driver={SQL Server};server=my_server;database=data_WH;trusted_connection=true')

    #build query
    #query = "SELECT * FROM [my_db].[dbo].[my_table] where [CATEGORY] = '1070'"
    query = "SELECT * FROM [my_db].[dbo].[my_table] where [CATEGORY] = ?"

    #store results
    res <- sqlExecute(channel = dbhandle, 
                      query = query,
                      data = list(input$CATEGORY),
                      fetch = TRUE,
                      stringsAsFactors = FALSE) 

    #close the connection
    odbcClose(dbhandle)
    #return results
    res
  })

  output$tbTable <- 
    renderTable(
      myData()
    )

} # AND NOTE THE CLOSING BRACE HERE

shinyApp(ui = ui, server = server)

Upvotes: 2

Related Questions