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)
The error is in your
renderPlot
function.Note that the last command in your
renderTable
function isdbGetQuery(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:dbGetQuery(conn, query)
but without storing the fetched results locally.ceatdb <- tbl(conn, "Ceat_table")
but without loading the results fully into R usingcollect()
.I recommend you only use one of these approaches.
Option 1: save the results of
dbGetQuery
:Option 2: load remote table into memory with
collect()
: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 ofnames(my_table)
andcolnames(my_table)
. Local tables will give the same results for both these commands, but remote tables will not.