2

Is there a way to set a global database connection in a Rnotebook so you don't have to set the database connection for each SQL chunk?

Currently I have to set it as follows for each SQL chunk:

```{sql connection = my_connection}
select * from my_table
```

I would like to just be able to do the following: 

```{sql}
select * from my_table
```
ansek
  • 443
  • 3
  • 17

2 Answers2

6

From Rmarkdown docs:

Setting a Default Connection

If you have many SQL chunks, it may be helpful to set a default for the connection chunk option in the setup chunk, so that it is not necessary to specify the connection on each individual chunk. You can do this as follows:

```{r setup}
library(DBI)
db <- dbConnect(RSQLite::SQLite(), dbname = "sql.sqlite")
knitr::opts_chunk$set(connection = "db")
```

So, in your example, set a hook with

```{r setup}
# insert your my_connection declaration here
knitr::opts_chunk$set(connection = "my_connection")
```

and put that chunk before all other chunks.

Blaza
  • 253
  • 1
  • 10
  • I tried that and it didn't seem to work in Rnotebooks although they are just rmarkdown documents. Have tried it in an Rnotebook? – ansek Aug 19 '17 at 18:10
  • Yes I did and it worked ok. Can you give a bit more code we can see? Are you sure you're putting the "my_connection" in quotes and not sending the object? – Blaza Aug 19 '17 at 18:19
  • Yes, I did put the connection in quotes. It is weird. Here is more of a sample. – ansek Aug 19 '17 at 18:34
  • Yes, I did put the connection in quotes. It is weird. Here is more of a sample. ` ```{r connections} my_conn = dbConnect(odbc(), .connection_string = "Driver={SQL Server};Server=MY_SERVER;Database=MY_DB;Trusted_connection=true;") knitr::opts_chunk$set(connection="my_conn") ``` ` ```{sql} select top 10 * from information_schema.columns ``` This returns: Error in (function (sql, outputFile, options) : The 'connection' option (DBI connection) is required for sql chunks. Failed to execute SQL chunk – ansek Aug 19 '17 at 18:42
  • Sorry. The formatting didn't come out as I expected. – ansek Aug 19 '17 at 18:44
  • I don't know what might be the problem. Can you check if [this basic notebook](https://gist.github.com/Blaza/1b8089ebf701f37facedf00b87df23ca) works? It runs without a problem on my RStudio (I use RSQLite package to get a sample database) – Blaza Aug 19 '17 at 18:58
  • Will check it when I get back from feeding the kids. – ansek Aug 19 '17 at 19:20
  • I think I have a better understanding of what is going on. I use my Rnotebooks as an IDE instead of using the source screen. So I will run each chunk of a Rnotebook independently instead of all at once as part of a knit. It looks like the default connection will work when you knit but not when you run chunk by chunk. Does that make sense to you? – ansek Aug 19 '17 at 23:55
  • 2
    Yes it does. However, when I renamed the chunk where we set up the connection from "connections" to "setup", it worked for me even chunk by chunk, as I had "Run Setup Chunk Automatically" checked in RStudio (under Run menu). I updated the [gist](https://gist.github.com/Blaza/1b8089ebf701f37facedf00b87df23ca) above to reflect that. Perhaps this solves it. – Blaza Aug 20 '17 at 08:34
  • 2
    That worked!!!! You have to name the r chuck "setup" and it works!! Thank you!!!!!! – ansek Aug 20 '17 at 13:33
  • This works. However, when you add a sql chunk through the **Insert** option in Rstudio it looks like this: \`\`\`{sql connection=}\`\`\` so you have to either remove the connection part or add the connection name. I've put in a feature request at the github repo. – Jas Apr 10 '18 at 22:47
0

this works. But when I insert a SQL chunk, the default is:

```{sql connection=}

```

instead of :

```{sql}

```

which means I need to add connection name or delete " connection=". How do I let it insert either:

```{sql connection="con"}

```

or:

```{sql}

```
Reddy Lee
  • 1
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 18 '23 at 11:10