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

Question

I am using R, MySQL, Sweave and LaTeX to generate reports that query a database. My problem is that as the R code is embedded in a .Rnw file, where I do not seem to have control on multiline formating.

I embed 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

I then 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

With the result that the SELECT query runs off the page.

Is there a way to have the LaTeX output present the SQL query as it was written?

Answer 1

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

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

Which is processed to this in the latex sources:

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

You are free to adjust the source code to suit your needs in the final PDF if the standard Sweave wrapping is not to your liking. You can include all the code in your Question in the chunk, I just used the SQL bit as this was the thing you were having trouble with.

You don't need the ( and ) round the string you assign to query.

Answer 2

A popular trick would be to have two Sweave chunks: The first has echo=TRUE,eval=FALSE and is merely for showing the command, and then a second with echo=FALSE,eval=TRUE which actually runs the query. More advanced tricks could play with the Sweave driver.

You can then do other tricks to pretty-print the actual query result.