CODEWITHSUNDEEP
sql-serverrshiny
asp
asp

Reputation: 31

Execute Microsoft SQL query on R Shiny

I am writing an R-Shiny app. Can some one tell me how to execute a Microsoft SQL query in R Shiny ?

This is what I have done so far:

data <- reactive({
    conn <- reactive ({ databaseOpen(serverName="[serverName]", databaseName=[dbName])})
    qr <- reactive ({ SELECT * from myTable })
    res <- reactive ({databaseQuery(conn = conn,query = qr)})
    close(conn)
    View(res)
})

Any help is appreciated !

Upvotes: 2

Views: 3927

Answers (2)

Fern19
Fern19

Reputation: 11

I was able to call a query by creating a function outside of the server and ui functions (in other words, in a global.r). Then the server function could call that query function using one of the inputs in the function.
Here is my code:

queryfunction <- function(zipper){

  odbcChannel <- odbcConnect("myconnection")

  querydoc <- paste0("
                SELECT distinct *
                     FROM mydb
                     where substring(nppes_provider_zip,1,2) = '43'
                     and [provider_type] = 'General Practice'
                     ")

   pricetable <- sqlQuery(odbcChannel, querydoc)

   close(odbcChannel)

   pricetable[which(substring(pricetable$nppes_provider_zip,1,5)==zipper),]
}

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


  output$mytable1 <-   renderDataTable(data.table(queryfunction(input$zip)))
})

Upvotes: 1

asp
asp

Reputation: 31

I figured it out. It can be done as:

server.r

serverfun<-function(input, output){

# Storing values in myData variable
myData <- reactive({

    # Opening database connection
    conn <- databaseOpen(serverName = "myServer",databaseName = "myDB")

    # Sample query which uses some input
    qr <- paste( "SELECT name FROM Genes g WHERE Id = ",input$myId," ORDER BY name")

    # Storing results
    res <- databaseQuery(conn = conn,query = qr) 

    # closing database
    databaseClose(conn)

    # Returning results
    res
})

output$tbTable <- renderTable({
    # Checking if myData is not null
    if(is.null(myData())){return ()}
    # return myData
    myData()

})

ui.r

library("shiny")
shinyUI(

  pageWithSidebar(

    headerPanel("Hide Side Bar example"),
    sidebarPanel(
      textInput("Id", "Enter ID below","1234")
    ),
    mainPanel(
      tabsetPanel(
         tabPanel("Data", tableOutput("tbTable"))
      )
    )
  )
)

Upvotes: 0

Related Questions