Deleting row in table in sqlite DB from R

shiny
, , ,
1

I am building a shiny application which will allow CRUD operations by a user on a table which exists in an sqlite3 database. I am using the input$table_rows_selected() function in DT to get the index of the rows selected by the user. I am then trying to delete the rows (using an action button deleteRows ) from the database which have a matching timestamp (the epoch time stored as the primary key). The following code runs without any error but does not delete the selected rows.

observeEvent(input$deleteRows, {

  if(!is.null(input$responsesTable_rows_selected)){
    s=input$responsesTable_rows_selected

    conn <- poolCheckout(pool)

    lapply(length(s), function(i){
      timestamp = rvsTL$data[s[i],8]

      query <- glue::glue_sql("DELETE FROM TonnageListChartering
                          WHERE TonnageListChartering.timestamp = {timestamp}
                          ", .con = conn)

      dbExecute(conn, sqlInterpolate(ANSI(), query))
    })

    poolReturn(conn)
    # Show a modal when the button is pressed
    shinyalert("Success!", "The selected rows have been deleted. Refresh
             the table by pressing F5", type = "success")
  }

})

pool is a handler at the global level for connecting to the database.

pool <- pool::dbPool(drv = RSQLite::SQLite(),
                     dbname="data/compfleet.db")

Why does this not work? And if it did, is there any way of refreshing the datatable output without having to reload the application?

1 Like