How to include a sqlite error into R using RSQLite ?

...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...

#> 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

  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({
      dbGetQuery(db, "SELECT * FROM asiento") })

This topic was automatically closed 54 days after the last reply. New replies are no longer allowed.