#+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 `...@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
* 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
_______________________________________________
Emacs-orgmode mailing list
Please use `Reply All' to send replies to the list.
Emacs-orgmode@gnu.org
http://lists.gnu.org/mailman/listinfo/emacs-orgmode
