Not being able to get column values from sql in R shiny for particular date

Question

I am newbie in R
I am trying to plot control chart in Shiny using sql database I created.
In below code I am able to get the sql data as per date.
but I am not being able to access the column values for which I have to plot the graph.
Following is the 1st few lines of database:

id   product_name    product_config  detected_width  created  
1    Belt            width           69.84           2020-04-19  
2    Belt            width           71.12           2020-04-19  

In the plot tab, I am getting the following error:
Error: 'data' must be of a vector type, was 'NULL'
So, the column values are not getting selected I guess. Can anyone please help with this.

library(pool)
library(dplyr)
library(shiny)
library(DBI)
library(plotly)
library(qcc)

ui <- fluidPage(
  fluidRow(
    column(4,
           h3("Selecting Data"),
           dateInput("date", "Enter a date:", value = Sys.Date())
    ),
    column(8,
           h3("Plot"),
           tabsetPanel(
             tabPanel("Table", tableOutput("tbl")), 
             tabPanel("Control Chart",plotOutput("plot"))
    )
  )
 )
)

server <- function(input, output, session){
  output$tbl <- renderTable({
    conn <- dbConnect(
      drv = RMySQL::MySQL(),
      dbname = "testdatabase",
      host = "localhost",
      username = "root",
      password = "root"
    )
    on.exit(dbDisconnect(conn), add = TRUE)
    sql <- "SELECT * FROM Ceat_table WHERE created = ?date1;"
    query <- sqlInterpolate(conn, sql, date1 = input$date)
    dbGetQuery(conn, query)
  })
  output$plot <- renderPlot({
    conn <- dbConnect(
      drv = RMySQL::MySQL(),
      dbname = "testdatabase",
      host = "localhost",
      username = "root",
      password = "root"
    )
    on.exit(dbDisconnect(conn), add = TRUE)
    sql <- "SELECT * FROM Ceat_table WHERE created = ?date1;"
    query <- sqlInterpolate(conn, sql, date1 = input$date)
    dbGetQuery(conn, query)
    ceatdb <- tbl(conn, "Ceat_table")
    a<-qcc(ceatdb$detected_width,type = "xbar.one")
    plot(a)
  })
}


shinyApp(ui = ui, server = server)

Answer 1

The error is in your renderPlot function.

Note that the last command in your renderTable function is dbGetQuery(conn, query). This fetches the data from the database, and because it is the last command the results from this command are what are passed to the UI.

In contrast for the renderPlot function you access the same table two different ways:

1. Directly as dbGetQuery(conn, query) but without storing the fetched results locally.
2. As a remote table with ceatdb <- tbl(conn, "Ceat_table") but without loading the results fully into R using collect().

I recommend you only use one of these approaches.

Option 1: save the results of dbGetQuery:

conn <- dbConnect( your_connection_details_here )
on.exit(dbDisconnect(conn), add = TRUE)
sql <- "SELECT * FROM Ceat_table WHERE created = ?date1;"
query <- sqlInterpolate(conn, sql, date1 = input$date)

ceatdb = dbGetQuery(conn, query) # key change

a<-qcc(ceatdb$detected_width,type = "xbar.one")
plot(a)

Option 2: load remote table into memory with collect():

conn <- dbConnect( your_connection_details_here )
on.exit(dbDisconnect(conn), add = TRUE)

ceatdb <- tbl(conn, "Ceat_table") %>% collect() # key change

a<-qcc(ceatdb$detected_width,type = "xbar.one")
plot(a)

Note that for remote tables, the $ notation can not be used to access columns in the same way as for local tables. One way to demonstrate this is to compare the output of names(my_table) and colnames(my_table). Local tables will give the same results for both these commands, but remote tables will not.