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:
*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"; #+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.
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"