...Hi all,
I am trying to create a shiny form in order to add rows in a table. I am using RSQLite package as sqlite database interface. The table is stored in a sqlite database and has been created with a primary key.
As you know, the primary key condition controls that data with the same key is unique. So, if I try to add an existing value into the column primary key, sqlite answers, as expected, with an error –“Warning: Error in: UNIQUE constraint failed:” - , but my shiny App crashes, because I have not found the way to include this error into R . I have to initialize the Shiny App.
I know I could write a R software control of the key, but I would be losing the database functionalities that sqlite provides.
Does anyone know any tip about this issue?
Thanks in advance.
This is a reprex. If you try to add twice the column named ‘id’, the error appears...
library(shiny);library(DBI);library(RSQLite);library(data.table)
#> Warning: package 'shiny' was built under R version 3.5.3
#> Warning: package 'DBI' was built under R version 3.5.2
#> Warning: package 'RSQLite' was built under R version 3.5.3
#> Warning: package 'data.table' was built under R version 3.5.3
shinyApp(
ui = fluidPage(
DT::dataTableOutput("asiento", width = 300),tags$hr(),
textInput("id", "Id", "AA"),
textInput("concepto", "Concepto","xx"),
actionButton("submit", "Submit")),
server = function(input, output, session) {
db <-dbConnect(SQLite(), ":memory:")
dbSendQuery(conn = db,"PRAGMA foreign_keys=ON")
dbSendQuery(conn = db,
"CREATE TABLE asiento(
id CHAR(2) NOT NULL,
concepto CHAR(50),
PRIMARY KEY(id))")
fields <- c('id','concepto')
formData <- reactive({
sapply(fields, function(x) input[[x]]) })
observeEvent(input$submit, {
data <- data.table(t(formData()))
nuevo_data <- dbSendQuery(conn = db,
"INSERT INTO asiento
VALUES (:id,:concepto)"
)
dbBind(nuevo_data,data) })
output$asiento <- DT::renderDataTable({
input$submit
dbGetQuery(db, "SELECT * FROM asiento") })
}
)