CODEWITHSUNDEEP
rdatabaseoracle-databaseshiny
Mal_a
Mal_a

Reputation: 3760

R Shiny Database Connection based on actionButton (eventReactive) ->connection does not exist outside eventReactive

I am having a problem with eventReactive in ShinyApp. I would like the user at the beginning to write user name and password click the actionButton and get a message "sucessfull connection". I managed to do it without any problems, however when i wanna use the con as for example to get list of the tables using dbListTables, it says that: Error: object 'con' has not been found, while using the reactive object connectDB() i get an error: Fehler bei der Auswertung des Argumentes 'conn' bei der Methodenauswahl für Funktion 'dbListTables': Fehler in .getReactiveEnvironment()$currentContext() : Operation not allowed without an active reactive context. (You tried to do something that can only be done from inside a reactive expression or observer.) How can i get the connection working?

My code:

    library(ROracle)
    library(shiny)
    library(DT)


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

        connectDB <- eventReactive(input$connectDB, {

          if(input$drv != "Oracle"){
            stop("Only 'Oracle' implemented currently")
          }else{
            drv <- dbDriver("Oracle")
          }

          con <- dbConnect(drv,"xx/K",username=input$user,password=input$passwd) 
          con
        })

        output$test <- renderText({
          con <- connectDB()
          "connection success"
        })

        tableList <- dbListTables(con,schema="K") #I have tried here to use con and as well reactive object in form of connectDB()
        output$out <- renderPrint(tableList)

        updateSelectizeInput(session, "tabnames", server = TRUE, choices = tableList)

        sqlOutput <- reactive({
          sqlInput <- paste("select * from K.",input$tabnames)
          dbGetQuery(con, sqlInput)
        })

        output$table <- DT::renderDataTable(sqlOutput(), server=TRUE, rownames=FALSE, filter="top", options=list(pageLength=10))

        session$onSessionEnded(function() { dbDisconnect(con) })
      })

    ui_panel <- 
      tabPanel("Test",
               sidebarLayout(
                 sidebarPanel(sidebarPanel(
                   textInput("drv", "Database Driver", value="Oracle"),
                   textInput("user", "User ID"),
                   passwordInput("passwd", "Password"),
                   actionButton("connectDB", "Connect to DB")) 
                 ),
                 mainPanel(
textOutput("test"),
                   selectizeInput("tabnames",label = "server side", choices = NULL),
                   tableOutput("out"),
                   dataTableOutput("table")
                 )
               )
      )


    ui <- shinyUI(navbarPage("Test",ui_panel))

    runApp(list(ui=ui,server=server))

Thanks for any help!

Upvotes: 1

Views: 1782

Answers (1)

Batanichek
Batanichek

Reputation: 7871

Here only part of what you have to do

UI

library(shiny)
shinyUI(navbarPage("Test",
         tabPanel("Test",
                      sidebarLayout(
                                sidebarPanel(
                                  textInput("drv", "Database Driver", value="Oracle"),
                                  textInput("user", "User ID"),
                                  passwordInput("passwd", "Password"),
                                  actionButton("connectDB", "Connect to DB")) ,
                                mainPanel(
                                  textOutput("test"),
                                  uiOutput("tabnames_ui"),
                                  tableOutput("out"),
                                  dataTableOutput("table")
                                ))
                              )
                     ))

Server

library(ROracle)
library(shiny)

shinyServer(function(input, output, session) {
    con=reactiveValues(cc=NULL)

    observeEvent(input$connectDB,{
      if(input$drv != "Oracle"){
        con$cc="Only 'Oracle' implemented currently"
      }else{
        drv <- dbDriver("Oracle")
        con$cc<- dbConnect(drv,"xx/K",username=input$user,password=input$passwd) 
      }
    })
    observe({
      if(!is.null(con$cc)& is(con$cc,"OraConnection")){ # check if connected
        output$test <- renderText({
          "connection success"
        })
        tableList <-reactive({
            dbListTables(con$cc,schema="K")
        }) 
        output$out <- renderPrint(tableList())

        output$tabnames_ui=renderUI({selectInput("tabnames",label = "server side", choices = tableList())})
      }else if (!is.null(con$cc) ){
        output$test <- renderText({
          con$cc
        })
      }
    })


  session$onSessionEnded(function() { 
    observe({
      if(!is.null(con$cc)& is(con$cc,"OraConnection")){# check if connected
    print(paste0("disconnect ",dbDisconnect(con$cc)))}
  }) 
})

  })

Upvotes: 2

Related Questions