here is the link of data https://drive.google.com/file/d/1CpHF9ocQpT83RHttxIYz5mzfXqShLtKK/view?usp=sharing

I am trying to make a shiny app that pulls some database and displays it. I have the database file in my workspace and this code works perfectly fine in the console. But when I try to run this in a shiny app it does not work.

db<- dbConnect(SQLite(),dbname='pisa_math.db')
      res<-dbGetQuery(db,"select * from dxResponses")
      res

When I use the same code above in a shiny app it fails :

 library(shiny)
library(RSQLite)

    ui <- fluidPage(
      tableOutput("tb4")
    )
    
  server <- function(input, output) { 
    output$tb4 <- renderTable(
        
          db<- dbConnect(SQLite(),dbname='pisa_math.db'),
          res<-dbGetQuery(db,"select * from dxResponses"),
          dbDisconnect(db),
          
          res
        )
    }

I get the error which says:

Warning: Error in : external pointer is not valid
61:
Error : external pointer is not valid

CodePudding user response：

Error likely does not relate to your database but how you call renderTable. As docs indicate, the first argument must be an expression that returns a single R object. By passing comma separated values, you are attempting to use other positional arguments of renderTable.

For multiple line expression use curly braces to encapsulate the expression:

server <- function(input, output) { 
    output$tb4 <- renderTable({
        db <- dbConnect(SQLite(), dbname='pisa_math.db')
        res <- dbGetQuery(db, "select * from dxResponses")
        dbDisconnect(db)
          
        res      
    })
}

Better yet, handle all processing before renderTable:

server <- function(input, output) {
    db <- dbConnect(SQLite(), dbname='pisa_math.db')
    res <- dbGetQuery(db, "select * from dxResponses")
    dbDisconnect(db)
 
    output$tb4 <- renderTable(res)
}