SQLite and Shiny/flexdashboard: Cannot embed a reactiveValues() object in a SQL query

Question

I am trying to filter a sqlite3 database and print/plot the resulting data. E.g. the nycflights13::weather data. But in a flexdashboard environment it complains of not being able to embed a reactive object in an SQL query. Does that mean that I can't use SQLite with flexdashboard?

---
title: "Untitled"
output: 
  flexdashboard::flex_dashboard:
    orientation: columns
    vertical_layout: fill
runtime: shiny
---

```{r setup, include=FALSE}
library(flexdashboard)
library(DBI)
library(dplyr)
library(tidyr)
library(pool)

con <- dbConnect(RSQLite::SQLite(), dbdir=":memory:")
DBI::dbWriteTable(con, "weather", nycflights13::weather)
bigdf<-tbl(con, "weather")
```

Column {.sidebar}
----------------------------
```{r}

selectInput("year", label = "year :",
            choices = c(2012,2013,2010))
```


Column {data-width=650}
-----------------------------------------------------------------------

### Chart A

```{r}
a<-reactive({
  a<-
   bigdf%>% 
    filter(origin=="EWR" & year == as.integer(input$year)) %>%
    head(25)%>%
    collect()
})
renderTable(a())
```

Column {data-width=350}
-----------------------------------------------------------------------

### Chart B

```{r}
reactive({
  plot(a()$year,a()$wind_speed)
})
```

Answer 1

I tweaked the code a bit to use the !! fix as suggested as well as make the b plot a reactive object that can then be rendered with a call to renderPlot.

---

title: "Untitled"
output: 
  flexdashboard::flex_dashboard:
    orientation: columns
    vertical_layout: fill
runtime: shiny
---

```{r setup, include=FALSE}
library(flexdashboard)
library(DBI)
library(dplyr)
library(tidyr)
library(pool)

con <- dbConnect(RSQLite::SQLite(), dbdir=":memory:")
DBI::dbWriteTable(con, "weather", nycflights13::weather)
bigdf<-tbl(con, "weather")
```

Column {.sidebar}
----------------------------
```{r}
selectInput("year", label = "year :",choices = c(2013,2012,2010),selected = 2013)
```


Column {data-width=650}
-----------------------------------------------------------------------

### Chart A

```{r}
a <- reactive({
    a <-
      bigdf %>% 
      filter(origin=="EWR" & year == as.integer(!!input$year)) %>%
      head(25) %>%
      collect()
    a  # make sure to return something
})
renderTable(a())
```

Column {data-width=350}
-----------------------------------------------------------------------

### Chart B

```{r}
b <- reactive({  # make the plot a reactive object
  plot(a()$year, a()$wind_speed)
})
renderPlot(b()) # then render it
```

Result shown here.