5

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"
  • 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.) – Constantine Dec 21 '14 at 3:39
  • Should have mentioned that I tried this as well. It doesn't fail, but has no output. – cheez Dec 21 '14 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. – Constantine Dec 21 '14 at 21:30
11
#+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. – cheez Dec 21 '14 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 '14 at 23:19
  • Ok, I found something I can live with for now. – cheez Dec 22 '14 at 1:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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