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

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

: blah

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

#+HEADERS: :var foo="blah"

I get the predictable error:

Error: No such column: foo

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


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?


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

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

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"
  • 1
    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.) Dec 21, 2014 at 3:39
  • Should have mentioned that I tried this as well. It doesn't fail, but has no output.
    – Sohail
    Dec 21, 2014 at 4:08
  • 1
    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. Dec 21, 2014 at 21:30

1 Answer 1

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

| name |          phone |

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

|          phone |

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, 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
    Dec 21, 2014 at 23:19
  • Ok, I found something I can live with for now.
    – Sohail
    Dec 22, 2014 at 1:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.