5

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?

Community
  • 1
  • 1
David LeBauer
  • 31,011
  • 31
  • 115
  • 189

3 Answers3

9

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.

Mortimer
  • 2,966
  • 23
  • 24
  • 1
    If I were you, I'd put `dbSendQuery("` as front and `")` as back tag. Or smth like that... – aL3xa Feb 18 '11 at 01:34
0

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.

David LeBauer
  • 31,011
  • 31
  • 115
  • 189
0

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!

JD Long
  • 59,675
  • 58
  • 202
  • 294
  • @jd-long I guess that would be one answer, but it would also work just to have ess ignore the quotes, or to write the function so that it can take an unquoted string (!?) – David LeBauer Feb 17 '11 at 22:29
  • This IS manageable. I asked a similar question here: http://goo.gl/SoX4q when I was about to do some webdev stuff with Jeff's `RApache` module & `brew` package. `brew` uses ASP(or PHP)-like tags `<%` `%>`, so I wanted to switch between R and HTML syntax. Anyway, I figured out that I don't need `brew`, since whole RApache thing works like a charm with `sys.source`. But MMM is a weapon of choice for such a task... – aL3xa Feb 18 '11 at 01:32
  • ...and, BTW, that kind of "tagged mode switching" is embedded in Sweave mode in ESS. And not only that it's manageable, you're probably using it on daily basis (when writting reports). =) – aL3xa Feb 18 '11 at 01:38
  • 1
    I'm going to delete this answer tomorrow. no sense preserving my dumbassery for eternity. But I'll wear my shame for the night. – JD Long Feb 18 '11 at 02:13