database query in rmarkdown/quarto document works in R chunk but fails in sql chunk

Question

I work in an rmarkdown / quarto document querying a database (Exasol) with read-only access. This works fine if executed in an R chunk as follows.

```{r} 
conn <- exasol::dbConnect( drv = "exa", exahost = "my_host_url", 
          uid = Sys.getenv("EXASOL_USER"), pwd = Sys.getenv("EXASOL_PWD") )

query <- "SELECT * FROM MY_SCHEMA.MY_TABLE LIMIT 10"
df <- exasol::dbGetQuery(conn, query)
```

Now, I try to run the same query in an sql chunk.

```{sql connection=conn}
SELECT * FROM MY_SCHEMA.MY_TABLE LIMIT 10
```

However, I get the following error message.

Using Schema from statement: "MY_SCHEMA"
Transaction rolled back.
Error in exa.readData(res$connection, query, ...) : 
  Could not receive header
In addition: Warning message:
In .EXAExecStatement(con = conn, stmt = statement, schema = schema,  :
  42500 -6818256 [EXASOL][EXASolution driver]insufficient privileges for creating table (Session: 1741969101333790720)[RODBC] ERROR: Could not SQLExecDirect 'create table "SUS_ANALYTICS".TEMP_791648_CREATED_BY_R as (SELECT * FROM MY_SCHEMA.MY_TABLE LIMIT 10
)'
Error in exa.readData(res$connection, query, ...) : 
  Could not execute SQL: '42000' -6811776' [EXASOL][EXASolution driver]object "MY_SCHEMA"."TEMP_791648_CREATED_BY_R" not found [line 1, column 23] (Session: 1741969101333790720)'
Failed to execute SQL chunk

I have read access only. So, the crucial information appears to be insufficient privileges for creating table. It appears the engine tries to create a temporary table and fails.

Why does this happen as the R chunk works without these problems?
And, more important: Is there a way to make the sql chunk work? :)

PS. Sorry, I do not know how to make this example reproducible.

user_10408166 · Answer

sirain is right. Currently, r-exasol needs to create the temporary table to implement the paging of the DBI interface. With that, you need the write permission to run the sql chunk.

user_536198 · Answer

As already described in the comments and other answers, the problem is caused by Exasol trying to create a table when using DBI::dbSendQuery. There is, however, an easy workaround for this. Just set the sql chunk parameter max.print = -1. This will cause the knitr sql engine to use DBI::dbGetQuery instead if DBI::dbSendQuery. As DBI::dbGetQuery does not try to create a (temporary) table, the problem is evaded.

Example

```{sql connection=conn, max.print=-1}
SELECT * FROM MY_SCHEMA.MY_TABLE LIMIT 10
```

Why?

The following code is from lines 616 ff. of the knitr sql engine

  data = tryCatch({
    if (is_sql_update_query(query)) {
      DBI::dbExecute(conn, query)
      NULL
    } else if (is.null(varname) && max.print > 0) {
      # execute query -- when we are printing with an enforced max.print we
      # use dbFetch so as to only pull down the required number of records
      res = DBI::dbSendQuery(conn, query)
      data = DBI::dbFetch(res, n = max.print)
      DBI::dbClearResult(res)
      data

    } else {
      if (length(params) == 0) {
        DBI::dbGetQuery(conn, query)
      } else {
        # If params option is provided, parameters are not interplolated
        DBI::dbGetQuery(conn, sql, params = params)
      }
    }
    ...

The first else if statement is the relevant part: If you set max.print to a value equal or smaller than 0, it will èxecute DBI::dbGetQuery instead of DBI::dbSendQuery.

database query in rmarkdown/quarto document works in R chunk but fails in sql chunk

2 Answers