1

I'd like to save the changes of rhandsontable via shiny in the sqlite database but it doesn't work.

I create and save the data base:

library(collapse)
library(DBI)
library(shiny)
library(RSQLite)
library(rhandsontable)

# Create sqlite base and connect ----------------------------------------------

db <- dbConnect(RSQLite::SQLite(), "test.sqlite")

task <- c("evaluation", 
          "coordination", 
          "seminars",
          "collaboration")

d <- qTBL(task) |> 
  fmutate(Minutes = as.integer(NA))
numberofrows <- nrow(d)

dbWriteTable(db, "monitoring", d, overwrite = T)
dbDisconnect(db)

Here's the shiny app:

ui = fluidPage(
  rHandsontableOutput("hotable1", width = "100%"),
  actionButton("action", label = "Write to DB"),
  hr()
)

server <- shinyServer(function(input, output, session) {
  
  previous <- reactive({d})
  
  MyChanges <- reactive({
    if(is.null(input$hotable1)){return(previous())}
    else if(!identical(previous(),input$hotable1)){
      mytable <- as.data.frame(hot_to_r(input$hotable1))
      mytable <- mytable[1:numberofrows,]
      mytable[,2][is.na(mytable[,2])] <- NA
      mytable[numberofrows+1,2] <- sum(mytable[,2],na.rm = T)
      mytable[numberofrows+1,1] <- "Total"
      mytable
    }
  })
  output$hotable1 <- renderRHandsontable({rhandsontable(MyChanges())})
  
  data <- eventReactive(input$action, {
    db <- dbConnect(RSQLite::SQLite(), "test.sqlite")
    dbWriteTable(db, "monitoring", input$hotable1, append = TRUE, overwrite = FALSE)
    data <- dbReadTable(db, "monitoring")
    dbDisconnect(db)
    return(data)
  })
})


shinyApp(ui = ui, server = server)

When I look at what's been saved, there's only NA and the "Total" row has not been saved:

db <- dbConnect(RSQLite::SQLite(), "test.sqlite")
tbl(db, "monitoring")
kokolet
  • 11
  • 1
  • You are reading the entirety of the database file into the reactive object `data` but you never write updated output back to the file. Normally you would have to send some sort of update query to the file when you want to change it. See https://stackoverflow.com/questions/20546468/how-to-pass-data-frame-for-update-with-r-dbi. You could observe change to `MyChanges` and write them back out to file. The connection to the file itself is terminated when you run dbDisconnect – MrFlick Aug 14 '23 at 15:06

0 Answers0