Using SQL in R Markdown?

Question

I am learning how to use SQL in R and R Markdown.

I have search all over the internet for a solution and nothing has worked. Here's all the code I have so far:

---
title: "SQLTest"
author: "Cindy S. Cheung"
date: "9/30/2019"
output: html_document
---

```{r setup, include=FALSE}

install.packages("RSQLite", repos = "http://cran.us.r-project.org")

library(RSQLite)
library(DBI)
library(knitr)

db <- dbConnect(RSQLite::SQLite(), ":memory:")

knitr::opts_chunk$set(echo = TRUE)
knitr::opts_chunk$set(connection = "db")

```

```{sql, connection = db, output.var = "Order_Detail"}

dbSendStatement(db, "DROP TABLE IF EXISTS Order_Detail;")

```

This is the error I got.

Error: near "dbSendStatement": syntax error
Failed to execute SQL chunk

I tried various versions of the SQL chunk tag, but nothing has helped. What am I doing wrong?

Thanks.

Answer 1

You are trying to execute R code in a SQL code chunk. Passing the SQL directly should work:

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

Alternatively you could specify as an R code chunk:

```{r}
dbSendStatement(db, "DROP TABLE IF EXISTS Order_Detail")
```