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!