How to change table captions for knitr sql chunks?

Question

Here is a knitr chunk that connects to a database and a sql chunk that runs a query on the database:

```{r dbConnect}
con <- RMySQL::dbConnect(RMySQL::MySQL(), dbname = "test")
```

```{sql rowsintable, connection=con}
select count(*) from tablename;
```

I use this document to generate a PDF file. In the pdf document, the table returned by the sql chunk has a caption: "Table 1: 1 records". How can I change this caption?

This question is related, but doesn't use SQL chunks, and it sets the caption inside an R function, not in the knitr chunk options.

Answer 1

You can use the chunk option tab.cap, e.g.

```{r dbConnect}
con <- RMySQL::dbConnect(RMySQL::MySQL(), dbname = "test")
```

```{sql rowsintable, connection=con, tab.cap="Here is the caption."}
select count(*) from tablename;
```