Passing query as variable to Rmarkdown sql chunk

Question

I'm trying to use the SQL chunk function available in the preview version of RStudio 1.0 to connect to a SQL Server (using the RSQLServer backend for DBI) and I'm having some difficulty passing variables.

If I connect to the server and then put the query in the chunk it works as expected

```{r, eval = F}
svr <- dbConnect(RSQLServer::SQLServer(), "Server_name", database = 'Database_name')
query <- 'SELECT TOP 10 * FROM Database_name.dbo.table_name'
```
```{sql, connection = svr, eval = F}
SELECT TOP 10 * FROM Database_name.dbo.table_name
```

But if I try to pass the query as a variable it throws an error

```{sql, connection = svr, eval = F}
?query
```

Error: Unable to retrieve JDBC result set for 'SELECT TOP 10 * FROM Database_name.dbo.table_name': Incorrect syntax near 'SELECT TOP 10 * FROM Database_name.dbo.table_name'.
Failed to execute SQL chunk

I think it's related to the way R wraps character vectors in quotes, because I get the same error if I run the following code.

```{sql, connection = svr, eval = F}
'SELECT TOP 10 * FROM Database_name.dbo.table_name'
```

Is there a way I can get around this error?

Currently I can achieve what I want by using inline expressions to print the query, using the pygments for highlighting and running the query in a R chunk with DBI commands, so using code chunks would be a bit nicer.

Answer 1

Looks like Using R variables in queries applies some kind of escaping and can therefore only be used in cases like the example from the documentation (SELECT * FROM trials WHERE subjects >= ?subjects) but not to dynamically set up the whole query.

Instead, the code chunk option can be used to achieve the desired behavior:

_{The example uses the SQLite sample database from sqlitetutorial.net. Unzip it to your working directory before running the code.}

```{r}
library(DBI)
db <- dbConnect(RSQLite::SQLite(), dbname = "chinook.db")
query <- "SELECT * FROM tracks"
```


```{sql, connection=db, code = query}
```

Answer 2

I haven't been able to determine a way to print and execute in the same chunk however with a few extra lines of code it is possible to achieve my desired output.

Printing is solved by CL.'s answer and then I can use EXEC to run the code.

```{sql, code = query}
```

```{sql, connection = svr}
EXEC (?query)
```