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

[Eric Schulte]

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 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

results in the following code block expansion (C-c C-v v)
#+begin_src sql
  -- 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 valueTable0
      ADD valueColumn0 valueType0 @nullability
  END
#+end_src

-- Eric

Sébastien Vauban <address@hidden> 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 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