SQL exercises in {learnr} return only errors

[jdlong]

hey y'all, after seeing Mine's presentation at rstudio::conf about the {learner} package I decided to try and whip up some test content for teaching SQL. I can't figure out how to get an exercise block to work with SQL. Here's what I tried:

In the preamble I set up a connection called db like so:

  db <- DBI::dbConnect(
    RPostgreSQL::PostgreSQL(),
    dbname   = "mydb",
    port     = 5439,
    host     = "mydb.yada.yada",
    user     = "userid",
    password = "topsecretpassword"
  )

then I test to make sure it is working by doing this:

```{sql sql_chunk1, connection=db}
select * from mytable;
```'

Ignore the trailing ', I can't recall how to make the fences show right, I'll edit this later.

Anyhow, that block works and returns the expected records from mytable when I start my tutorial. So that's cool. Right after that I try to set up an SQL exercise chunk like so:

```{sql sql_chunk, connection=db, exercise=TRUE}

```'

and that gives me an exercise code box, but no matter what I put in there, I get an "unexpected symbol" or just "unexpected" error:

Am I missing something trivial? I'm flummoxed!

[technocrat]

Without trying to reproduce the setup, I'll offer the obvious, which is that the code box is parsing the commands as regular R statements

> select * from table;
Error: unexpected symbol in "select * from table"

which suggests, in turn, that the box chunk is not recognizing that it has been directed to run an sql cursor on the db connection. That is a species of the more general question of how to instruct the box that it is running in a special environment.

That get the mills grinding?

[grrrck]

Yes, you're missing a very minor detail and it's completely not your fault at all. The issue is that the connection chunk option must be a character. More specifically, it must be the name of the connection object, which is created in the setup chunk, as a character string.

In your case the following two-character change should fix this for you.

```{sql sql_chunk1, connection="db"}
select * from mytable;
```

Here's another example using an in-memory duckdb database provided by a package.

---
title: "SQL"
output: learnr::tutorial
runtime: shiny_prerendered
---

## Using SQL

https://forum.posit.co/t/sql-exercises-in-learnr-return-only-errors/96328

```{r setup}
library(learnr)

library(starwarsdb)
con <- starwars_connect()
```

```{sql sql-ex, connection = "con", exercise = TRUE}
select * from people;
```

I'm really glad to hear that you're building SQL tutorials using learnr. We are still working on this feature and I'll be happy to help if you run into any issues like this one.

[grrrck]

This might actually be the problem you're currently seeing (my other answer will solve the problem you'll hit once you solve this one). SQL support for exercises is in the development version of learnr only. If you installed learnr from CRAN, you'll need to reinstall from GitHub:

remotes::install_github("rstudio/learnr")

[technocrat]

Unquoted arguments considered harmful, I guess. At least it's not an environment setup exercise like reticulate. Thx

[jdlong]

believe it or not, the quotes don't matter (at least on my machine at this moment). I can run the code either way once I upgraded to the GitHub version of learnr

Thanks for your fast and excellent help!

[jdlong]

I'll be skinning the reticulate environment cat next

[cderv]

@jdlong

the quotes don't matter

The connection= can be used with the object directly in R Markdown, it should be necessary to use the object name with quotes. Examples for the SQL engine in : 2.7 Other language engines | R Markdown: The Definitive Guide

@grrrck is it expected to different for learnr tutorials than other Rmd formats ?

[grrrck]

The connection= can be used with the object directly in R Markdown, it should be necessary to use the object name with quotes.

learnr is a little special in this regard, since it's a Sshiny pre-rendered output. The issue I discovered is real and reproducible under certain circumstances. In my case, the connection object included pointers which don't serialize well, but for other database connections this seems not to be a problem. I opened an issue in learnr for further tracking and research: Best practices for specifying the database connection in SQL exercises · Issue #495 · rstudio/learnr · GitHub

[cderv]

Thanks that is good to know!