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.

The temporary table is created here: https://github.com/exasol/r-exasol/blob/2e3aa43676e0871285c723f0873b8112e45ab034/R/EXADBI-query.R#L68 it looks like this is done to get some metadata about the query result like column names. I don't see an easy way to circumvent this. — sirain, Aug 24 '22 at 06:58

score 2 · Answer 1 · answered Sep 01 '22 at 15:18

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.

score 1 · Answer 2 · answered Aug 24 '22 at 13:33

1

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.

answered Aug 24 '22 at 13:33

Thomas Übensee

11
1

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

2 Answers2