Error Embedding SQL Source from code block into R Source of Another (noweb)

Error Embedding SQL Source from code block into R Source of Another (noweb)

[Eric Brown]

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 ').

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

Re: Error Embedding SQL Source from code block into R Source of Another (noweb)

[Rainer M Krug]

[-- Attachment #1: Type: text/plain, Size: 4392 bytes --]

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

[-- Attachment #2: Type: application/pgp-signature, Size: 494 bytes --]

Re: Error Embedding SQL Source from code block into R Source of Another (noweb)

[Grant Rettke]

On Tue, Sep 30, 2014 at 3:05 AM, Rainer M Krug <Rainer@krugs.de> wrote:
> Eric Brown <brown@fastmail.fm> writes:

Like Rainier mentioned you might want to use [noweb] to tangle both
for output tangling and execution. My system is set up to tangle for
both eg:

This
## ✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂
#+name: query
#+begin_src sql
select
    *
from
    table
where
    x > 10
#+end_src

#+begin_src R :tangle output.R
input <- '
    «query»
'
#+end_src
## ✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂
produces this
## ✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂
input <- '
    select
        *
    from
        table
    where
        x > 10
'
## ✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂
and

this
## ✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂
#+name: msg
#+begin_src sql
Hello, world.
#+end_src

#+begin_src R :eval yes
print("«msg»")
#+end_src

#+NAME:
#+begin_example
[1] "Hello, world."
#+end_example
## ✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂

produces this

## ✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂
╭────
│ Hello, world.
╰────

╭────
│ print("«msg»")
╰────

╭────
│ [1] "Hello, world."
╰────
## ✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂

http://orgmode.org/manual/noweb.html#noweb

Re: Error Embedding SQL Source from code block into R Source of Another (noweb)

[Eric Brown]

Thanks Rainer and Grant for your suggestions.  A couple of things:

1) My current code does what I want, the second time. Its advantage is
that everything is kept inside of one .org file.  Also, I am wondering
if it is an R/ESS issue.

2) Grant, I tried your code, but there seem to be some missing headers.
Do you have a complete minimal example that exhibits the behavior that
you have demonstrated?

Re: Error Embedding SQL Source from code block into R Source of Another (noweb)

[Charles Berry]

Eric Brown <brown <at> 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 ').
> 
> 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.


[rest deleted]

Hmmm. Maybe the bug that was fixed by commit

 0fd29a5ee7d14c3695b22998196373b9a3637413

about two weeks back? Make sure ob-R.el is up to date and compiled (or
that ob-R.elc is deleted).


Anyway, your code works as expected for me - first time.

---

FWIW, I prefer to use :var headers to import strings, but it takes some
setup:


--8<---------------cut here---------------start------------->8---

#+BEGIN_SRC emacs-lisp
  (defun grab-src (name)
    (save-excursion
      (org-babel-goto-named-src-block name)
      (nth 1 (org-babel-get-src-block-info 'light))))
#+END_SRC

#+BEGIN_SRC R :var y=(grab-src "sqlsource") :results output :exports both
  y
#+END_SRC

#+RESULTS:
: [1] "select \n  * \nfrom \n  t \nlimit \n  10"

--8<---------------cut here---------------end--------------->8---



HTH,

Chuck

Re: Error Embedding SQL Source from code block into R Source of Another (noweb)

[Eric Brown]

Charles Berry <ccberry@ucsd.edu> writes:

> Hmmm. Maybe the bug that was fixed by commit
>
>  0fd29a5ee7d14c3695b22998196373b9a3637413
>
> about two weeks back? Make sure ob-R.el is up to date and compiled (or
> that ob-R.elc is deleted).
>
>
> Anyway, your code works as expected for me - first time.
>
> ---
>
> FWIW, I prefer to use :var headers to import strings, but it takes some
> setup:
>
>
>
> #+BEGIN_SRC emacs-lisp
>   (defun grab-src (name)
>     (save-excursion
>       (org-babel-goto-named-src-block name)
>       (nth 1 (org-babel-get-src-block-info 'light))))
> #+END_SRC
>
> #+BEGIN_SRC R :var y=(grab-src "sqlsource") :results output :exports both
>   y
> #+END_SRC
>
> #+RESULTS:
> : [1] "select \n  * \nfrom \n  t \nlimit \n  10"
>

In fact M-x R before evaluation did fix things.  I will try to get the
org source going, which contains the fix.  Otherwise, I might be content
to wait for the fix to make its way into org ELPA.  (I'm on .emacs
complexity overload)

Great idea about the grab-src function! Worked like a charm.

Thanks again,
Eric

Re: Error Embedding SQL Source from code block into R Source of Another (noweb)

[Grant Rettke]

On Tue, Sep 30, 2014 at 9:32 AM, Eric Brown <brown@fastmail.fm> wrote:
> 2) Grant, I tried your code, but there seem to be some missing headers.
> Do you have a complete minimal example that exhibits the behavior that
> you have demonstrated?

Sorry for omitting that key point:

✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂
#+name: msg
#+begin_src sql
Hello, world.
#+end_src

#+begin_src R :eval yes
print("«msg»")
#+end_src

#+NAME:
[1] "Hello, world."
#+end_example

#+begin_src emacs-lisp
(print emacs-version)
(print org-version)
(print org-babel-default-header-args)
(print org-babel-default-inline-header-args)
#+end_src

#+NAME:
#+begin_example

"24.3.1"

"8.2.7c"

((:eval . "always") (:noweb . "no-export") (:exports . "both")
(:results . "output") (:comments . "no") (:session . "none") (:cache .
"no") (:hlines . "no") (:tangle . "no"))

((:results . "value replace") (:eval . "always") (:session . "none")
(:exports . "results"))
#+end_example
✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂

Re: Error Embedding SQL Source from code block into R Source of Another (noweb)

[Nick Dokos]

Eric Brown <brown@fastmail.fm> writes:

> 2) Grant, I tried your code, but there seem to be some missing headers.
> Do you have a complete minimal example that exhibits the behavior that
> you have demonstrated?

Add :noweb yes. Also Grant seems to have gone non-standard and replaced
the <<noweb>> markup with guillemots. Using the default markers,
it should read:

--8<---------------cut here---------------start------------->8---
#+name: query
#+begin_src sql
select
    *
from
    table
where
    x > 10
#+end_src

#+begin_src R :noweb yes :tangle output.R
input <- '
    <<query>>
'
#+end_src
## ✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂
produces this
## ✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂✂
input <- '
    select
        *
    from
        table
    where
        x > 10
'
--8<---------------cut here---------------end--------------->8---

Nick

Re: Error Embedding SQL Source from code block into R Source of Another (noweb)

[Grant Rettke]

On Tue, Sep 30, 2014 at 2:15 PM, Nick Dokos <ndokos@gmail.com> wrote:
> Eric Brown <brown@fastmail.fm> writes:
>
>> 2) Grant, I tried your code, but there seem to be some missing headers.
>> Do you have a complete minimal example that exhibits the behavior that
>> you have demonstrated?
>
> Add :noweb yes. Also Grant seems to have gone non-standard and replaced
> the <<noweb>> markup with guillemots. Using the default markers,
> it should read:

Yikes what a week sorry about that here is what you would need I shall
be defining a function to provide all such details in the future and
thanks Nick for addressing:

╭────
│ (setq org-babel-noweb-wrap-start "«")
│ (setq org-babel-noweb-wrap-end "»")
╰────

Re: Error Embedding SQL Source from code block into R Source of Another (noweb)

[Nick Dokos]

Grant Rettke <gcr@wisdomandwonder.com> writes:

> On Tue, Sep 30, 2014 at 2:15 PM, Nick Dokos <ndokos@gmail.com> wrote:
>> Eric Brown <brown@fastmail.fm> writes:
>>
>>> 2) Grant, I tried your code, but there seem to be some missing headers.
>>> Do you have a complete minimal example that exhibits the behavior that
>>> you have demonstrated?
>>
>> Add :noweb yes. Also Grant seems to have gone non-standard and replaced
>> the <<noweb>> markup with guillemots. Using the default markers,

That should be "guillemets".

>> it should read:
>
> Yikes what a week sorry about that here is what you would need I shall
> be defining a function to provide all such details in the future and
> thanks Nick for addressing:
>
> ╭────
> │ (setq org-babel-noweb-wrap-start "«")
> │ (setq org-babel-noweb-wrap-end "»")
> ╰────
>
>

And an OT question:

Since on my keyboard I can type << much more easily than I can type «
I was wondering: does your keyboard provide an easy way to type a
guillemet? Is that why you changed it? Or is it purely aesthetics?

-- 
Nick

Re: Error Embedding SQL Source from code block into R Source of Another (noweb)

[Grant Rettke]

> On Tue, Sep 30, 2014 at 9:23 PM, Nick Dokos <ndokos@gmail.com> wrote:
>> Grant Rettke <gcr@wisdomandwonder.com> writes:
>> Yikes what a week sorry about that here is what you would need I shall
>> be defining a function to provide all such details in the future and
>> thanks Nick for addressing:
>>
>> ╭────
>> │ (setq org-babel-noweb-wrap-start "«")
>> │ (setq org-babel-noweb-wrap-end "»")
>> ╰────
>>
>>
>
> And an OT question:
>
> Since on my keyboard I can type << much more easily than I can type «

Totally agreed, I'm using a US QWERTY keyboard.

> I was wondering: does your keyboard provide an easy way to type a
> guillemet?

No but I use the [key-chord] package to do so like this:

╭────
│ (key-chord-define-global "<<" (lambda () (interactive) (insert "«")))
│ (key-chord-define-global ">>" (lambda () (interactive) (insert "»")))
╰────

It is the same amount of typing but you get the desired character.

> Is that why you changed it? Or is it purely aesthetics?

Aesthetic personal preferences. After the fact, this preference has
proven to be valuable for non-org-users, though.

When I use `org' at work and show literate documents to non-users, the
guillemet makes it crystal clear that the text is somehow special, and
not just "something enclosed by two less-than and greater-than
symbols". Not totally sure why people react this way, but they do.


[key-chord]
https://github.com/emacsmirror/key-chord/blob/master/key-chord.el

Re: Error Embedding SQL Source from code block into R Source of Another (noweb)

[Nick Dokos]

Grant Rettke <gcr@wisdomandwonder.com> writes:

>> On Tue, Sep 30, 2014 at 9:23 PM, Nick Dokos <ndokos@gmail.com> wrote:
>>> Grant Rettke <gcr@wisdomandwonder.com> writes:
>>> Yikes what a week sorry about that here is what you would need I shall
>>> be defining a function to provide all such details in the future and
>>> thanks Nick for addressing:
>>>
>>> ╭────
>>> │ (setq org-babel-noweb-wrap-start "«")
>>> │ (setq org-babel-noweb-wrap-end "»")
>>> ╰────
>>>
>>>
>>
>> And an OT question:
>>
>> Since on my keyboard I can type << much more easily than I can type «
>
> Totally agreed, I'm using a US QWERTY keyboard.
>
>> I was wondering: does your keyboard provide an easy way to type a
>> guillemet?
>
> No but I use the [key-chord] package to do so like this:
>
> ╭────
> │ (key-chord-define-global "<<" (lambda () (interactive) (insert "«")))
> │ (key-chord-define-global ">>" (lambda () (interactive) (insert "»")))
> ╰────
>
> It is the same amount of typing but you get the desired character.
>
>> Is that why you changed it? Or is it purely aesthetics?
>
> Aesthetic personal preferences. After the fact, this preference has
> proven to be valuable for non-org-users, though.
>
> When I use `org' at work and show literate documents to non-users, the
> guillemet makes it crystal clear that the text is somehow special, and
> not just "something enclosed by two less-than and greater-than
> symbols". Not totally sure why people react this way, but they do.
>
>
> [key-chord]
> https://github.com/emacsmirror/key-chord/blob/master/key-chord.el

Ok - thanks!
--
Nick