1

I have an R notebook where I am reading in data from a database using an sql chunk and then assigning it to a data frame. I would then like to use it in R chunks within the R notebook so am using the output.vars option in the sql chunk.

When I run all and then preview it works perfectly but when I knit it I get the error: "Error in eval(expr, envir, enclos) : object 'x' not found".

The following is some simple code that will reproduce this error:

---
title: "R Notebook"
output:
  html_notebook: default
  html_document: default
---

```{r setup}
library(DBI)
library(RSQLite)
db = dbConnect(SQLite(), dbname = "C:/R/chinook.db")
```

```{sql connection = db, output.vars = 'x'}
SELECT * FROM artists
```

```{r}
x[1:10,]
```

I am using:

  • R version 3.4.0 (2017-04-21)
  • Platform: i386-w64-mingw32/i386 (32-bit)
  • Running under: Windows 7 x64 (build 7601) Service Pack 1
  • RStudio 1.0.143
  • DBI_0.6-1
  • knitr_1.15.1

The example uses the SQLite sample database from sqlitetutorial.net http://www.sqlitetutorial.net/download/sqlite-sample-database/?wpdmdl=94.

I have also tried different types of databases without any success.

Colin
  • 13
  • 4

2 Answers2

1

I am not sure your syntax chunk in the middle would work. Why don't you just do like:

```{r}
library(DBI)
library(RSQLite)
db <- dbConnect(SQLite(), dbname = "C:/R/chinook.db")
x <- dbGetQuery(db, 'SELECT * FROM artists')

```

```{r}
x[1:10,]
```
amatsuo_net
  • 2,409
  • 11
  • 20
  • 2
    Just in case you find this interesting, they added a SQL engine in [knitr v. 1.14](http://rmarkdown.rstudio.com/authoring_knitr_engines.html) so the syntax should work. I think the OP just misspelled one argument. – Sraffa May 06 '17 at 16:32
  • Thanks. Good to know. – amatsuo_net May 06 '17 at 17:27
1

I think you have to change output.vars to output.var

```{sql connection = db, output.var = 'x'}
SELECT * FROM artists
```

It works for me like that.

Sraffa
  • 1,658
  • 12
  • 27
  • Thanks Sraffa - stupid me. What made it difficult to spot was that the individual chunk still passes the data frame to R when running the individual chunk when using 'output.vars' – Colin May 06 '17 at 21:43