Hi Seb, Babel's sql support did not have any variable handling. I just copied over the variable support from ob-sqlite to ob-sql, so the following should now work (notice: my code block is different from yours)
** sql variables #+results: sql-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=sql-param[0,1] :var column=sql-param[1,1] :var type=sql-param[2,1] :var nullability=sql-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 results in the following code block expansion (C-c C-v v) #+begin_src sql -- 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 valueTable0 ADD valueColumn0 valueType0 @nullability END #+end_src -- Eric Sébastien Vauban <wxhgmqzgw...@spammotel.com> writes: > #+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 _______________________________________________ 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