How can I get Emacs ess to recognize a query string (within quotes) as code?

Question

Background

I have a function dbquery that simplifies the process of querying a MySQL database from within R.

dbquery <- function(querystring) {
  dvr <- dbDriver("MySQL")
  con <- dbConnect(dvr, group = "databasename")
  q <- dbSendQuery(con, querystring)
  data <- fetch(q, n = -1)
  return(data)
}    

Thus I can send:

dbquery(querystring = "select field_1, field_2, field_3 
                       from table_a join table_b on this = that 
                       join table_c on that = something 
                       where field_4 in (1,2,3);"

However, the variable querystring must be contained within quotes. This makes it so that Emacs ESS will not nicely indent my queries like it would if it were in SQL mode - or even like it does if there are no quotes but just in ESS-R mode.

Question

Is it possible to get ESS to do this? Perhaps by writing the function so that it will accept the query without a quote (and add the quotes within the function), or perhaps adding something to .emacs or ess.el?

Answer 1

I think what you want in MMM Mode. As his name suggests: MultiMajorMode Mode allows to have multiple modes on different regions of the same buffer.

I recommend that you checkout the examples in http://www.emacswiki.org/emacs/HtmlModeDeluxe as they will probably give you an idea how to do it in your case (you might want to add some comment in your code around the sql so that MMM can find the sql code).

You would have to do something like this I guess (untested):

(require 'mmm-mode)
(mmm-add-group
     'sql-in-ess
     '(
             (sql-query
                    :submode sql-mode
                    :face WHATEVERYOUWANT
                    :front "#SQL_QUERY>"
                    :back "#<SQL_QUERY"))
(add-to-list 'mmm-mode-ext-classes-alist '(ess-mode nil sql-in-ess))

However, this might be overkill, unless it happens a lot that you have complex sql queries in the R code.

Answer 2

I don't know of any way to do this. It seems like you're asking, "can I make Emacs be in two modes simultaneously? (i.e. ESS and SQL)" I think the answer is "no" but I hope that someone comes along and shows us a cleaver hack that proves me wrong!

Answer 3

A simple alternative approach would be to use paste, with each line a separate string:

dbquery(querystring = paste("select field_1, field_2, field_3", 
                      "from table_a join table_b on this = that", 
                      "join table_c on that = something", 
                      "where field_4 in (1,2,3);"))

Perhaps a bit clunky, but it works in practice.