CODEWITHSUNDEEP
sqlrevalknitr
Z117
Z117

Reputation: 211

R: Knitr gives error for SQL-chunk

I would like to knit the output of my R-markdown, which includes a couple of SQL-chunks. However, if I start knitting, I get the error:

Line 65     Error in eval(expr, envir, enclos) : object 'pp_dataset' not found Calls: <Anonymous> ... process_group.block -> call_block -> eval_lang -> eval Execution halted

I have no clue what is going on, because if I just run this chunk (which starts at line 64) then it works fine.

The chunk that starts at line 64 looks as follows:

```{sql, connection=con, output.var=pp_dataset, error=TRUE, echo=FALSE, include=TRUE}
SELECT
(...)
order by 1,2
```

I've tried several knit-options like error=TRUE/FALSE, echo=TRUE/FALSE and include=TRUE/FALSE but that doesn't work. Anyone a clue what's wrong?

Upvotes: 4

Views: 3114

Answers (2)

vitallish
vitallish

Reputation: 322

It looks like you need to quote the dataset name in the rchunk options:

```{sql, connection=con, output.var="pp_dataset", error=TRUE, echo=FALSE, 
include=TRUE}
SELECT
(...)
order by 1,2
```

Source: http://rmarkdown.rstudio.com/authoring_knitr_engines.html#sql I answered the question in this post as well. I'm not sure as to the protocol as the answers are identical.

Upvotes: 6

Florian
Florian

Reputation: 25435

When rendering your document, Rmarkdown does not have access to your global environment. So you should make sure that all variables that you want to use are defined within the Rmarkdown document, e.g. in a initial chunk:

```{r setup, include=FALSE, warning=FALSE, message=FALSE}
(...)
```

or you should type

render("yourfile.Rmd")

instead of pressing the knit button. In that case, the document does have access to your global environment variables. In this case I guess the 'con' connection is in your global environment, and not found while rendering. Hope this helps!

EDIT: I was able to reproduce the error with your example code:

I was not able to run your code without first initializing the output variable of the SQL statement. In your top-chunck ( so for example below the line setwd(mydirectory), try:

pp_dataset <- NULL

Hope this also solves the issue for you.

Upvotes: 1

Related Questions