R Shiny with Database

[THIAM HUAT Tan]

Hi All Experts in Shiny and Database issues,

I am able to retrieve my database results out and output to a table, without any problems, with the below code, except that after 16 connections, it is a problem. 

I know that I have to close the connection to solve this problem. However, if I insert dbDisconnect(con) directly after rs <- dbGetQuery(con, sql), I do not get any results output to the table.

Trying below codes does not work too, meaning there is no output to the table:

    rs <- dbSendQuery(con,sql)

    data <- dbFetch(rs)

    dbHasCompleted(rs)

    dbClearResult(rs)

    dbDisconnect(con)

Can anyone suggest a working solution, so that I do not have the maximum 16 connection issue? Or did I place the dbDisconnect(con) wrongly?

library(lubridate)

library(RMySQL)

library(DBI)

# server.R

# --------------------------------------------------------

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

  

  startime <- reactive({

    start_time <-input$starttime

    })

  

  endtime <- reactive({

    end_time <-input$endtime

  })

  

  output$text <- renderText({  

    paste("You have selected:",input$starttime)

  })  

  

  query_output <- reactive({

    con <- dbConnect(RMySQL::MySQL(), host = "www.db4free.net", dbname ="punggol",

                     user = "tanthiamhuat", password = "90795159")

    start_time <- startime()

    end_time <- endtime()

    sql <- paste("select * from PunggolCentral ",

                 "where DateTime > STR_TO_DATE('",start_time,"', '%Y/%m/%d %H:%i:%s')", 

                 "and DateTime < STR_TO_DATE('",end_time,"', '%Y/%m/%d %H:%i:%s')",sep="")

    

    rs <- dbGetQuery(con, sql) 

    

    # rs <- dbSendQuery(con,sql)

    # data <- dbFetch(rs)

    #dbHasCompleted(rs)

    #dbClearResult(rs)

    #dbDisconnect(con)

  })

  

  output$table <- renderDataTable(query_output())

 

})

[Joe Cheng [RStudio]]

First of all, you should change your database password immediately, and in the future, don't post your password in public!

In answer to your question, you can add back all of those comment-out lines; however, the last expression in query_output needs to be the actual data (so, rs, I guess).

Like so:

query_output <- reactive({

  ...

  rs <- dbGetQuery(con, sql)

  dbDisconnect(con)

  rs

})

In this case, you should actually not do that though; instead, right after the dbConnect line, add this line:

on.exit(dbDisconnect(con), add = TRUE)

This will ensure that dbDisconnect will be called whether the reactive succeeds or fails with an error. If you don't do this but just call dbDisconnect() after dbGetQuery(), then if dbGetQuery() throws an error, the connection will not be disconnected.

[Joe Cheng [RStudio]]

Sorry, one more thing--you absolutely must validate that input$starttime and input$endtime are valid date/time strings, do this in the startime and endtime reactives. If they're not valid, either call stop("Invalid date/time") to fail with an error, or req(FALSE) to fail silently. If you don't do this, it will be possible for someone to compromise your database using SQL injection.