3

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?

Z117
  • 201
  • 3
  • 12

2 Answers2

5

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.

vitallish
  • 312
  • 1
  • 12
  • thanks for this, what is surprising is that for me I didn't need to quote it when running the chunks manually in R studio, but for knitting a full document I do need to quote it – moodymudskipper Jan 20 '20 at 09:44
1

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.

Florian
  • 24,425
  • 4
  • 49
  • 80
  • If I type render("myfile.Rmd") in the console, I get the following error: Error in tools::file_path_as_absolute(input) : file '20170721_Visualisation.Rmd' does not exist In addition: Warning messages: 1: In normalizePath(path.expand(path), winslash, mustWork) : path[1]="20170721 Visualisation.Rmd": The system cannot find the file 2: In normalizePath(path.expand(path), winslash, mustWork) : path[1]="20170721_Visualisation.Rmd": The system cannot find the file – Z117 Jul 24 '17 at 12:00
  • Nope, I found that one. Changed the wd now. So it starts rendering, and now I get the following error (while it still works when running in RStudio): Quitting from lines 65-398 (20170724_Visualisation.Rmd) Error in assign(varname, data, envir = knit_global()) : invalid first argument – Z117 Jul 24 '17 at 12:12
  • Could you maybe reduce your Rmd file to a reproducible example? See [here.](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) Otherwise it is very hard to help. – Florian Jul 24 '17 at 12:22
  • Since the reproducible example is too long, I'll post it as an 'answer' below. However, it is not reproducible as I make a connection with Teradata in my example and use this connection to get data in an SQL-chunk (and this SQL-chunk seems to be the problem). Hope you can help anyway! – Z117 Jul 24 '17 at 14:39
  • I tried predefining my variable in the initial chunk as df <- data.frame() before using it for the SQL chunk, but still getting an error "Error in assign(varname, data, envir = knit_global()) : invalid first argument Calls: ... call_block -> block_exec -> in_dir -> engine -> assign In addition: Warning message: package 'dplyr' was built under R version 3.4.1 Execution halted " – Harry M Aug 03 '17 at 18:52