Eric Brown <brown@fastmail.fm> writes:

> Dear List:
>
> It is possible to embed SQL code as a string to be evaluated in R. I am
> interested in formatting the SQL code in its own source code block, with
> its own syntax highlighting and editing mode (C-c ').

This sounds very interesting, and I would be interested in doing thia as
well. Up to now, I was writing the sql statements in R, which is a pain
(paste, paste0, ...). But editing them in org and passing them to an R
source block as a variable sounds interestin, but I don't think this
will work. What might work is using noweb expansion, but I never used
it.

Check out the worg [1] "Simple Literate Programming Example (Noweb
syntax)"

From the page:

,----
| In the Org-mode file:
| 
| #+name: hello-world-prefix
| #+begin_src sh :exports none
|   echo "/-----------------------------------------------------------\\"
| #+end_src
| HTML export of code: In the Org-mode file
| 
| #+name: hello-world-postfix #+begin_src sh :exports none echo
| "\-----------------------------------------------------------/"
| #+end_src
| 
| HTML export of code: The third code block does have a tangle header
| argument indicating the name of the file to which the tangled source
| code will be written. It also has Noweb style references to the two
| previous code blocks. These references will be expanded during
| tangling to include them in the output file as well.
| 
| In the Org-mode file:
| 
| #+name: hello-world
| #+begin_src sh :tangle hello :exports none :noweb yes
|   <<hello-world-prefix>>
|   echo "|                       hello world                         |"
|   <<hello-world-postfix>>
| #+end_src
|
| HTML export of code: Calling org-babel-tangle will result in the following
| shell source code being written to the hello.sh file:
| 
| #!/usr/bin/env sh
| 
| # [[file:~/org/temp/index.org::*Noweb%20test][hello-world]]
| 
| echo "/-----------------------------------------------------------\\"
| echo "|                       hello world                         |"
| echo "\-----------------------------------------------------------/"
| # hello-world ends here
|
| In addition, the following syntax can be used to insert the results of
| evaluating a code block, in this case one named example-block.
| 
| # <<example-block()>>
`----

I guess this should work - and I will try it out - learned something.

Thanks for the question,

Rainer

>
> The first time I run the code, I am prompted for R starting directory,
> but I get an error:
>
> ---
> load ESSR: + + + Error: unexpected string constant in:
> source('~/.emacs.d/elpa/ess-20140913.1153/etc/ESSR/R/.load.R',
> local=TRUE) #define load.ESSR
> load.ESSR('"
> ---
>
> and the console locks.  I can C-g to get out of it, and then
> re-evaluate, and the code prints what I expect -- the text of the SQL
> command.
>
> Is this the right way to go about this?  Have I discovered a bug, or
> perhaps accidentally a wrong way to get the right answer?
>
> Is this an ESS problem, and not an orgmode problem, per se?  My ESS
> normally starts up fine, so I thought I would ask on this list first.
>
> A minimal example (first failing, second evaluation giving expected
> output) follows.
>
> Best regards,
>
> Eric
>
> Debian GNU/Linux (jessie)
> Emacs 24.3.93
> Org current from org repo
> ESS from MELPA (ca. 14.09)
> R 3.1.1 compiled from source
>
>
>
> ----- SESSION -----
> #+TITLE: Test SQL Code
> #+AUTHOR: Eric Brown                                              
> #+EMAIL: brown@fastmail.fm
> #+PROPERTY: session *R*                                           
> #+PROPERTY: cache no
>
> #+name: sqlsource
> #+begin_src sql :engine postgresql :eval yes :noweb-ref sqlsrc :exports code :results none
>   select 
>     * 
>   from 
>     t 
>   limit 
>     10
> #+end_src
>
> #+name: rsource
> #+begin_src R :noweb yes :results output :exports both
>   input <- '
>   <<sqlsrc>>
>   '
>   cat(input)
>   # dbGetQuery(connectionHandle, input)
> #+end_src
>
> #+RESULTS: rsource
> : 
> : select 
> :   * 
> : from 
> :   t 
> : limit 
> :   10
> ---------------------
>
>
>
>


Footnotes: 
[1]  http://orgmode.org/worg/org-contrib/babel/intro.html

-- 
Rainer M. Krug
email: Rainer<at>krugs<dot>de
PGP: 0x0F52F982