emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
* [Babel] How to pass variables to SQL blocks?
@ 2010-11-29 16:07 Sébastien Vauban
  2010-12-01  2:34 ` Eric Schulte
  0 siblings, 1 reply; 6+ messages in thread
From: Sébastien Vauban @ 2010-11-29 16:07 UTC (permalink / raw)
  To: emacs-orgmode-mXXj517/zsQ

#+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-mXXj517/zsQ@public.gmane.org
http://lists.gnu.org/mailman/listinfo/emacs-orgmode

^ permalink raw reply	[flat|nested] 6+ messages in thread

end of thread, other threads:[~2010-12-13 22:06 UTC | newest]

Thread overview: 6+ messages (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
2010-11-29 16:07 [Babel] How to pass variables to SQL blocks? Sébastien Vauban
2010-12-01  2:34 ` Eric Schulte
2010-12-01 10:35   ` Sébastien Vauban
2010-12-13 19:53     ` Eric Schulte
2010-12-13 21:23       ` Sébastien Vauban
2010-12-13 22:06         ` Eric Schulte

Code repositories for project(s) associated with this public inbox

	https://git.savannah.gnu.org/cgit/emacs/org-mode.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).