object Object error in R Shiny SQLite App

Question

I am trying to develop an R Shiny app with an SQLite Database. But I am facing this error while getting any data from the database. The error message is:

Loading required package: shiny
Attaching package: ‘dplyr’
The following objects are masked from ‘package:stats’:
    filter, lag
The following objects are masked from ‘package:base’:
    intersect, setdiff, setequal, union**

In-app, it shows:

[object Object]

Can anyone please help me? Do you have any suggestions for developing the SQLite database?

The code is below:

# Libraries
library(stats)
library(shiny)
library(RSQLite)
library(dtplyr)
library(dplyr)
library(DBI)

# Globar Variables 
db <- dbConnect (SQLite(),
                  "/Users/sudiptobosu/200922/20200714PPCP.sqlite")

# Global Function
names_ppcp = dbGetQuery(db, "SELECT ALL PPCP FROM mydata")

#server
shinyServer(function (input, output, session) {
  dplyr::filter
  stats::filter
  
  output$ppcpOut <- reactive({
    input$user1
  })  

  output$propertyOut <- reactive({
    input$user2
  })

  ppcpquery <- reactive({
    input$user1
  })

  queryy <- reactive({
    gsub("<ppc>", ppcpquery(), "SELECT Solubility FROM mydata WHERE PPCP = '<ppc>'")
  })

  output$valueout <- reactive({
    dbGetQuery(db, queryy())
  })

  #propertyquery <- eventReactive ({input$user2})  

  session$onSessionEnded (function() {
    dbDisconnect(db)
  })
})

#ui
ui <- fluidPage ( 
  titlePanel("bo-DEREC CE"),
  sidebarLayout( 
    sidebarPanel(
      selectInput("user1","Select the PPCP",choices = names_ppcp),
      selectInput("user2","Select the property",choices = c("Solubility","Volatility","Adsorbability","Degradability"))
    ),
    mainPanel(
      textOutput("ppcpOut"),
      textOutput("propertyOut"),
      textOutput("valueout")
    )
  )
)

Answer 1

Data frames should not be rendered on user interface with textOutput but tableOutput. Then adjust reactive call to renderTable. And usually input$ objects should be wrapped in reactive. Also, consider properly parameterizing such as with DBI:sqlInterpolate:

server adjustments

...

# INPUTS
ppcpquery <- reactive({
  input$user1
})

propertyquery <- reactive({
  input$user2
})

# OUTPUTS
output$ppcpOut <- renderText({
    ppcpquery()
})  

output$propertyOut <- renderText({
    propertyquery()
})

output$valueOut <- renderTable({
  # PREPARED STATEMENT WITH PLACEHOLDER
  sql <- "SELECT Solubility FROM mydata WHERE PPCP = ?ppc")

  # BIND PARAMETER
  query <- DBI::sqlInterpolate(db, sql, ppc = ppcpquery())

  # RUN QUERY AND RETURN RESULTS
  dbGetQuery(db, query)
})

ui adjustments

...

mainPanel(
  textOutput("ppcpOut"),         # ALIGNS TO renderText
  textOutput("propertyOut"),     # ALIGNS TO renderText
  tableOutput("valueOut")        # ALIGNS TO renderTable
)

---

Now, if dbGetQuery returns a one-row and one-column data frame, then you can use renderText in server and textOutput in ui. But extract value from column:

dbGetQuery(db, query)$Solubility[[1]]