Shiny tests for database transactions

shiny
,
1

Hi,

I am wondering if anyone can share any tips on how best to catch and report any issues with writing to a database based on user input?

For example, I have a simple shiny app that allows a user to fill out a form and save the contents to a database once they click on a save button. This works fine 99% of the time, but occasionally there is an issue with the actual write back to the database. To make sure the write was successful, I am testing to see if the expected contents are actually in the database. I am unclear on the best method to report back an issue to the user.

In this pseudo code, what would the best way to exit out of the function after write 1 was unsuccessful?

observeEvent(input$saveForm, {

Check if write 1 is successful - if unsuccessful, display error message and exit function

Check if write 2 is successful - if unsuccessful, rollback changes from write 1, display error message and exit function

})

I was unable to figure out how to get validate to work in this context.

Thanks a million,

Iain

2

Here's an idea, using poolWithTransaction:

library(shiny)

pool <- dbPool(RSQLite::SQLite(), dbname = ":memory:")
dbWriteTable(pool, "cars", head(cars, 3))

ui <- fluidPage(
  actionButton("go", "Go")
)

server <- function(input, output, session) {
  observeEvent(input$go, {
    ## failed transaction -- note the missing comma
    tryCatch(
      poolWithTransaction(pool, function(conn) {
        dbExecute(conn, "INSERT INTO cars (speed, dist) VALUES (1, 1);")
        dbExecute(conn, "INSERT INTO cars (speed dist) VALUES (2, 2);")
        dbExecute(conn, "INSERT INTO cars (speed, dist) VALUES (3, 3);")
      }),
      error = function(e) {
        showModal(modalDialog(
          title = "Write to DB not successful",
          tags$i("Please try again"), br(), br(),
          tags$b("Error:"), br(),
          tags$code(e$message)
        ))
      }
    )
  })
}

shinyApp(ui, server)

If this is not what you're looking for, or if it doesn't cover all your scenarios, let me know!

4 Likes
3

@barbara, thanks! That is exactly what I had in mind