---
title: "Knit SQL Demo"
---

```{r message=FALSE}
library(DBI)
db <- dbConnect(RSQLite::SQLite(), dbname = ":memory:")
```

```{sql, connection=db}
DROP TABLE IF EXISTS packages
```

```{sql, connection=db}
CREATE TABLE packages (id INTEGER, name TEXT)
```

```{sql, connection=db}
INSERT INTO 
  packages 
VALUES 
  (1, 'readr'), 
  (2, 'readxl'), 
  (3, 'haven')
```

```{sql connection=db}
SELECT * FROM packages
```

```{r}
packageReadR <- "readr"
```

Engine can substitute named placeholders in sql that start with `?`. 

Note that if you don't provide `params` as option values will be evaluated from environment

```{sql, connection=db, output.var="readrPackage"}
SELECT 
  * 
FROM 
  packages
WHERE 
  name = ?packageReadR
```

```{r}
readrPackage
```

To use parameterised queries with native database support provide `params` in the options of the chunk.

```{r setup-parameters}
parameters <- list(package = packageReadR)
```

```{sql, connection=db, output.var="readrPackage", params=parameters}
SELECT 
  * 
FROM
  packages
WHERE 
  name = @package
```

```{r}
readrPackage
``` 

```{sql, connection=db}
DROP TABLE IF EXISTS packages
```

```{sql, connection=db, error = TRUE}
SELECT * FROM packages
```