From: "Eric Schulte" <schulte.eric@gmail.com>
To: "Sébastien Vauban" <wxhgmqzgwmuf@spammotel.com>
Cc: emacs-orgmode@gnu.org
Subject: Re: [Babel] How to pass variables to SQL blocks?
Date: Tue, 30 Nov 2010 19:34:29 -0700 [thread overview]
Message-ID: <87ipzefcga.fsf@gmail.com> (raw)
In-Reply-To: <80pqtoqfkh.fsf@missioncriticalit.com> ("Sébastien Vauban"'s message of "Mon, 29 Nov 2010 17:07:10 +0100")
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 <wxhgmqzgwmuf@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
next prev parent reply other threads:[~2010-12-01 3:11 UTC|newest]
Thread overview: 6+ messages / expand[flat|nested] mbox.gz Atom feed top
2010-11-29 16:07 [Babel] How to pass variables to SQL blocks? Sébastien Vauban
2010-12-01 2:34 ` Eric Schulte [this message]
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
List information: https://www.orgmode.org/
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=87ipzefcga.fsf@gmail.com \
--to=schulte.eric@gmail.com \
--cc=emacs-orgmode@gnu.org \
--cc=wxhgmqzgwmuf@spammotel.com \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
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).