- Note
-
This is one of a few posts on new features from a recent four-day knitr backlog sprint; see that post for the full details of the sprint.
knitr’s sql engine lets you write a SQL query in a code chunk, run it
against a DBI connection, and drop the result into your document. It has been a
bit bare-bones for a long time. This cycle a group of contributors pushed it a
long way forward. If you write reports against a database, several of these are
going to feel like they should have existed all along.
Show every statement, not just the first
A sql chunk with several statements separated by semicolons used to be
submitted all at once, and depending on the DBI backend, results after the first
statement could silently vanish. The new sql.interlaced option splits the
chunk into individual statements, runs them in order, and emits the source and
result of each as an alternating sequence of blocks—the way an R chunk echoes
each expression next to its output:
```{sql, connection=con, sql.interlaced=TRUE}
SELECT count(*) FROM flights;
SELECT count(*) FROM airlines;
```
The splitting is done by a small pure-R helper that knows to ignore semicolons
inside string literals, quoted identifiers, and comments, so no external SQL
parser is needed. If a statement fails, execution stops there (respecting the
error option) (#2093).
Bring your own result function
By default the engine calls DBI to execute the query and collect the result. But
sometimes you don’t want to collect eagerly—you want a lazy handle you can
keep processing with dplyr. The new sql.result.fun option lets you
substitute your own function(conn, query) for the built-in execution:
```{sql, connection=con, output.var="tbl",
sql.result.fun=function(conn, query) dplyr::tbl(conn, dbplyr::sql(query))}
SELECT * FROM flights
```
Combined with output.var, the lazy object is assigned to a variable without
collecting it, while the chunk still gets the sql engine’s syntax
highlighting. You get to keep writing real SQL and hand the result off to
dplyr downstream (#1778).
Statements that change data
Not every SQL statement returns rows. INSERT, UPDATE, DELETE and friends
return a count of affected rows, and the engine now knows the difference. It
recognizes more keywords (ALTER, GRANT, MERGE, TRUNCATE, CALL, …) as
non-SELECT statements and routes them to DBI::dbExecute() instead of
dbGetQuery(), which avoids spurious warnings from some drivers. When the
automatic detection guesses wrong (think SELECT ... INTO or
UPDATE ... RETURNING), override it with sql.is_statement
(#1896).
The affected-row count is now available too. Capture it with output.var, and
optionally report it with a template via sql.statement.msg, where {n} is the
count:
```{sql, connection=con, sql.statement.msg="Rows affected: {n}"}
DELETE FROM flights WHERE dep_delay IS NULL
```
By default no such message is shown, so existing documents are unaffected (#2050, #2051).
Passing extra arguments to DBI
Finally, sql.args is a named list forwarded to the underlying DBI functions,
so you can set any backend-specific argument without the engine needing to know
about it:
```{sql, connection=con, sql.args=list(immediate=TRUE)}
CREATE TEMP TABLE foo AS SELECT 1 AS x
```
These are passed only when supplied, so DBI’s own defaults otherwise stand (#2128).
There’s more I haven’t shown (dollar-quoted strings no longer trip the interpolation code, for instance)—see the changelog. Everything here is in the development version; the backlog summary has the install command.
