Disconnect From MYSQL when user exits

shiny
database
#1

Hi

This is probably a very simple answer that i have not been able to find a clear example
I have a shiny app which connects to a DB runs some operations based on user input
When the user exits the app i would like the connection to close
There is a bit of a workaround where i can open a close the connection to the DB everytime an operation is executed but it seems a bit inefficient

In any shiny app i have created thus far, i have added a line of code into the server section that says session$onSessionEnded(stopApp) which kills the session when the user exits the app
Is there a method i can use which will allow me to somehow add RJDBC::dbDisconnect(con) where con is my connection when the user closes the app

Here is a non working example to show what i am trying to do

DB connection

The code below connects to a mysql db, inserts some records and then exits

library(RMariaDB)
library(dplyr)

# We will try and insert some records based on the IRIS DB
glimpse(iris)

# Connect to the MYSQL DB
con <- dbConnect(RMariaDB::MariaDB(), host = "1.0.0.0", "port" = 9999, dbname = "example",
                     user = "foo", password = "foo1234")

# Inserts some values into the DB
str_update_table <- sprintf("INSERT INTO t_temp VALUES ('%.2f','%.2f','%.2f','%.2f','%s')", 
                            iris$Sepal.Length, iris$Sepal.Width, iris$Petal.Length, 
                            iris$Petal.Width, iris$Species)

# Update the table t_temp based on the SQL above
purrr::walk(str_update_table, function(x) dbSendUpdate(con, x))

RJDBC::dbDisconnect(con)

Thank you for your time

0 Likes

#2

If you are using a single app.R file you can close the connection on stop, by putting this before calling shinyApp(ui = ui, server = server)

onStop(function() {
    dbDisconnect(con)
})

# Run the application 
shinyApp(ui = ui, server = server)

EDIT: You might also have to pass session variable to your server function

server <- function(input, output, session) {
1 Like

#3

perfect, this was exactly what i needed

0 Likes

#4

If your question's been answered (even by you!), would you mind choosing a solution? It helps other people see which questions still need help, or find solutions if they have similar problems. Here’s how to do it:

0 Likes

closed #5

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

0 Likes