Variables in Babel evaluation with SQL blocks

Question

According to the org-mode manual, you can set variables in code blocks as follows:

#+HEADERS: :var foo="blah"
#+BEGIN_SRC elisp
(message foo)
#+END_SRC

#+RESULTS:
: blah

However, when I try to do this with a babel block with SQL, I have no good luck:

#+HEADERS: :var foo="blah"
#+BEGIN_SRC sql
SELECT foo
#+END_SRC

I get the predictable error:

Error: No such column: foo

The output I'd like to see is something like:

blah

In the *SQL* buffer. I've spent a lot of time trying to figure it out but I haven't made any progress yet.

So is what I'm trying to do possible with SQL?

Thanks!

Edit: based on the comments below, the following does work after I explicitly load ob-sqlite:

#+HEADERS: :var foo="blah"
#+BEGIN_SRC sqlite :db ":memory:"
SELECT "$foo";
#+END_SRC

#+RESULTS:
: blah

The problem is that I have a connection in the SQL buffer that I want to reuse rather than hard-code the path to the database via :db since I am executing the same query on multiple databases.

Almost there!

Edit 2: In the end, I ended up doing this:

#+HEADERS: :var foo="blah"
#+HEADERS: :var bar="boop"
#+BEGIN_SRC sqlite :header yes :db (some-lisp-call-here)
SELECT $foo, $bar;
#END_SRC

I can live with this but I went further and factored out the common parameters

# -*- org -*-
#+PROPERTY: header-args:sqlite :header yes :db (some-lisp-call-here) :var foo="blah" :var bar="boop"

Try SELECT $foo instead. (I cannot test this myself right now, but this is what reading the relevant part of ob-sql.el tells me should work.) — Constantine
– Constantine, Commented Dec 21, 2014 at 3:39
Should have mentioned that I tried this as well. It doesn't fail, but has no output. — NNN
– NNN, Commented Dec 21, 2014 at 4:08
If you look at org-babel-execute:sql you can see that the only way of communicating with a database it supports is via org-babel-eval, i.e. by creating a shell command, executing it, and processing the output. As far as I can it cannot pass commands to an *SQL* buffer. — Constantine
– Constantine, Commented Dec 21, 2014 at 21:30

wvxvw · Accepted Answer · 2014-12-21 18:49:46Z

14

#+begin_src sql :engine postgresql :cmdline "-U wvxvw -W secret -d personal"
select * from phonebook;
#+end_src

#+RESULTS:
| name |          phone |
|------+----------------|
| Dad  | XXX-XX-XXXXXXX |
| Mom  | YYY-YY-YYYYYYY |

#+header: :var pn="phone"
#+begin_src sql :engine postgresql :cmdline "-U wvxvw -W secret -d personal"
select $pn from phonebook;
#+end_src

#+RESULTS:
|          phone |
|----------------|
| XXX-XX-XXXXXXX |
| YYY-YY-YYYYYYY |

To confirm what Constantine said. This should work (and it works for me). What can possibly fail? - spelling, wrong user (not the one that owns the database), empty table and so on.

To make sure the block expanded as you'd expect: M-xorg-babel-expand-src-block. Or you could open ob-sql.el and instrument some of the functions that you believe should process the data for debugging, so that you could see the intermediate state.

answered Dec 21, 2014 at 18:49

wvxvw

11.6k3 gold badges33 silver badges58 bronze badges

I updated the question with what I've tried as well as what I'm trying to accomplish.

NNN
– NNN

2014-12-21 21:01:36 +00:00
Commented Dec 21, 2014 at 21:01
2

@nonchalant I don't think that at this time Babel supports :session for sql, so it looks like that if you want to use that, or another process, which already communicates with a database, then you'll have to implement it yourself... or maybe someone else will do it sooner :)

wvxvw
– wvxvw

2014-12-21 23:19:22 +00:00
Commented Dec 21, 2014 at 23:19
Ok, I found something I can live with for now.

NNN
– NNN

2014-12-22 01:16:01 +00:00
Commented Dec 22, 2014 at 1:16

Add a comment |

Stack Exchange Network

Variables in Babel evaluation with SQL blocks

1 Answer 1

Your Answer

Hot Network Questions

Variables in Babel evaluation with SQL blocks

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions