[Orgmode] [Babel] How to pass variables to SQL blocks?

[Sébastien Vauban]

#+TITLE:     How to pass variables to SQL code blocks?
#+DATE:      2010-11-29
#+LANGUAGE:  en

* Abstract

I'm trying to abstract similar blocks of SQL code, making use of variables
passed to blocks. Though, I can't get any var passed to the SQL block --
maybe my mistake!

While doing this exercise:

- I tried an alternative way using Noweb syntax -- but this only works for one
  instantiation of the parameters --, and

- I found that no warning are emitted if referring to inexistent blocks.

* Using vars

Using this table of parameters,

#+results: param
| table       | valueTable0       |
| column      | valueColumn0      |
| type        | valueType0        |
| nullability | valueNullability0 |

I want to apply the values onto the following chunk of code:

#+srcname: add-column-in-table-0
#+begin_src sql :var @table=param[0,1] :var @column=param[1,1] :var 
@type=param[2,1] :var @nullability=param[3,1]
-- add column address@hidden' (if column does not exist yet)
IF NOT EXISTS (SELECT *
               FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = '@table'
               AND COLUMN_NAME = '@column')
BEGIN
    ALTER TABLE @table
    ADD @column @type @nullability
END
#+end_src

* Using Noweb

Using Noweb, I can pass values to some variables, doing this:

#+srcname: table
#+begin_src sql
valueTable1
#+end_src

#+srcname: column
#+begin_src sql
valueColumn1
#+end_src

#+srcname: type
#+begin_src sql
valueType1
#+end_src

#+srcname: nullability
#+begin_src sql
valueNullability1
#+end_src

It works:

#+srcname: add-column-in-table-1
#+begin_src sql :noweb yes
-- add column `<<column>>' (if column does not exist yet)
IF NOT EXISTS (SELECT *
               FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = '<<table>>'
               AND COLUMN_NAME = '<<column>>')
BEGIN
    ALTER TABLE <<table>>
    ADD <<column>> <<type>> <<nullability>>
END
#+end_src

but *only once*, as I can't overwrite the value of the 4 variables...

#+srcname: table
#+begin_src sql
valueTable2
#+end_src

#+srcname: column
#+begin_src sql
valueColumn2
#+end_src

#+srcname: type
#+begin_src sql
valueType2
#+end_src

#+srcname: nullability
#+begin_src sql
valueNullability2
#+end_src

The above chunks of code (with *new values*) were silently ignored, as you can
see when exporting this code, which should add another column in another
table:

#+srcname: add-column-in-table-2
#+begin_src sql :noweb yes
<<add-column-in-table-1>>
#+end_src

* No warning if block does not exist

Please note that inexistent references are ignored, without further notice.
That's the case for such a block (where I forgot the number suffix):

#+srcname: contains-inexistent-ref
#+begin_src sql :noweb yes
<<add-column-in-table>>
#+end_src

Best regards,
  Seb

-- 
Sébastien Vauban