How can I format R source code with SQL queries in Sweave without deleting my line breaks?

Question

I use R, MySQL, Sweave and LaTeX to create reports that query the database. My problem is that since the R code is embedded in the .Rnw file, where I don't seem to control the multi-line generation.

Paste the following R code:

library(RMySQL) con <- dbConnect(MySQL(), user='test_user', dbname='sakila', host='localhost', password='password') data <- dbReadTable(con, 'film_list') query <-(' SELECT category, count(FID) AS Number FROM film_list GROUP by category') Cat <- dbGetQuery(con, query) Cat 

Then I get the following output in the PDF generated by Sweave:

 > library(RMySQL) > con <- dbConnect(MySQL(), user = "test_user", dbname = "sakila", + host = "localhost", password = "password") > data <- dbReadTable(con, "film_list") > query <- ("\nSELECT category, count(FID) AS Number \nFROM film_list \nGROUP by category") > Cat <- dbGetQuery(con, query) > Cat 

As a result, the SELECT query is executed from the page.

Is there a way for LaTeX output to represent the SQL query when it was written?

Answer 1

Add the keep.source option to the code snippet option and set it to true

 <<foo,keep.source=TRUE>>= query <- ' SELECT category, count(FID) AS Number FROM film_list GROUP by category' @ 

which is handled by this in latex sources:

 \begin{Schunk} \begin{Sinput} > query <- ' + SELECT category, count(FID) AS Number + FROM film_list + GROUP by category' \end{Sinput} \end{Schunk} 

You can edit the source code according to your needs in the final PDF file if you don't like the standard Sweave package. You can include all the code in your question in a piece, I just used the SQL bit, as that was what you came across.

You do not need ( and ) around the line that you assign query .

Answer 2

A popular trick would be to have two Sweave chunks: The first has echo = TRUE, eval = FALSE and just to display the command, and then the second with echo = FALSE, eval = TRUE, which actually launches the request. More advanced tricks can play with the Sweave driver.

Then you can do other tricks to print the actual query result.