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, Dec 21 '14 at 03:39
Should have mentioned that I tried this as well. It doesn't fail, but has no output. — Sohail, Dec 21 '14 at 04:08
If you look at [`org-babel-execute:sql`](http://orgmode.org/cgit.cgi/org-mode.git/tree/lisp/ob-sql.el?id=release_8.2.10#n90) 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, Dec 21 '14 at 21:30

score 14 · Accepted Answer · answered Dec 21 '14 at 18:49

#+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.

I updated the question with what I've tried as well as what I'm trying to accomplish. — Sohail, Dec 21 '14 at 21:01
@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, Dec 21 '14 at 23:19

Variables in Babel evaluation with SQL blocks

1 Answers1