SQL code in Rnw document with knitr

Question

I used the following sqlcode in the document .Rmd. However, I want to use the same code sqlin the document .Rnw. I would really appreciate if anyone could help me on how to do this. Any help would be greatly appreciated. Thanks

```{r label = setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE, max.print = NA)
```

```{r, echo=FALSE, results='hide'}
library(DBI)
db <- dbConnect(RSQLite::SQLite(), dbname = "survey.db")
dbListTables(db)
```


```{sql, label = Q1, connection=db, tab.cap = "Table Caption"}
SELECT * 
  FROM Person;
```

Prefers to get formatting formatting and printing.

Answer 1

Porting RMarkdown to RNW requires some configuration:

• , : RNW ```{r, echo=FALSE} <<echo=FALSE>>=, RNW @. (. RNW.)
• , RMarkdown , RNW R, engine. , ```{r} <<>>=, ```{sql} <<engine="sql">>=.
• RMarkdown SQL, . knitr Language Engines: SQL rmarkdown.rstudio.com. , 10 . RNW .

SQL RMarkdown , SQL- SQL connection. output.var , .

(. ) SQL , res, output.var R, res , xtable. , , hooks:

_{SQLite sqlitetutorial.net. .}

\documentclass{article}

\begin{document}
\thispagestyle{empty}
<<include=FALSE>>=
library(knitr)
library(DBI)

knit_hooks$set(formatSQL = function(before, options, envir) {
  if (!before && opts_current$get("engine") == "sql") {
    sqlData <- get(x = opts_current$get("output.var"))
    max.print <- min(nrow(sqlData), opts_current$get("max.print"))
    myxtable <- do.call(xtable::xtable, c(list(x = sqlData[1:max.print, ]), opts_current$get("xtable.args")))
    capture.output(myoutput <-do.call(xtable::print.xtable, c(list(x = myxtable, file = "test.txt"), opts_current$get("print.xtable.args"))))
    return(asis_output(paste(
      "\\end{kframe}", 
      myoutput,
      "\\begin{kframe}")))
  }
})

opts_chunk$set(formatSQL = TRUE)
opts_chunk$set(output.var = "formatSQL_result")
opts_chunk$set(max.print = getOption("max.print"))

@

<<echo=FALSE, results="hide">>=
db <- dbConnect(RSQLite::SQLite(), dbname = "chinook.db")
@

<<engine = "sql", connection=db, max.print = 8, xtable.args=list(caption = "My favorite artists?", label="tab:artist"), print.xtable.args=list(comment=FALSE, caption.placement="top")>>=
SELECT *  FROM artists;
@

\end{document}

formatSQL. (Chunk hooks , chunk NULL.) engine="sql" SQL sqlData. xtable max.print .

formatSQL (.. TRUE), SQL formatSQL_result. chunk max.print ( Inf , ).

, xtable, . chunk xtable.args xtable, print.xtable.args print.xtable. , xxtable .

PDF. , R RNW (Windows).