From mboxrd@z Thu Jan 1 00:00:00 1970 From: "Eric Schulte" Subject: Re: [Babel] How to pass variables to SQL blocks? Date: Tue, 30 Nov 2010 19:34:29 -0700 Message-ID: <87ipzefcga.fsf@gmail.com> References: <80pqtoqfkh.fsf@missioncriticalit.com> Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Return-path: Received: from [140.186.70.92] (port=35896 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1PNd6j-0002LA-Va for emacs-orgmode@gnu.org; Tue, 30 Nov 2010 22:11:55 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1PNcWj-0003U0-D5 for emacs-orgmode@gnu.org; Tue, 30 Nov 2010 21:34:38 -0500 Received: from mail-gx0-f169.google.com ([209.85.161.169]:38315) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1PNcWj-0003Rw-8m for emacs-orgmode@gnu.org; Tue, 30 Nov 2010 21:34:37 -0500 Received: by gxk5 with SMTP id 5so3515960gxk.0 for ; Tue, 30 Nov 2010 18:34:36 -0800 (PST) In-Reply-To: <80pqtoqfkh.fsf@missioncriticalit.com> (=?utf-8?Q?=22S=C3=A9b?= =?utf-8?Q?astien?= Vauban"'s message of "Mon, 29 Nov 2010 17:07:10 +0100") List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: =?utf-8?Q?S=C3=A9bastien?= Vauban Cc: emacs-orgmode@gnu.org 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=3Dsql-param[0,1] :var column=3Dsql-param[1,1] :v= ar type=3Dsql-param[2,1] :var nullability=3Dsql-param[3,1] -- add column `@column' (if column does not exist yet) IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =3D '@table' AND COLUMN_NAME =3D '@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 =3D '@table' AND COLUMN_NAME =3D '@column') BEGIN ALTER TABLE valueTable0 ADD valueColumn0 valueType0 @nullability END #+end_src -- Eric S=C3=A9bastien Vauban 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 fo= r 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=3Dparam[0,1] :var @column=3Dparam[1,1] :var @= type=3Dparam[2,1] :var @nullability=3Dparam[3,1] > -- add column `@column' (if column does not exist yet) > IF NOT EXISTS (SELECT * > FROM INFORMATION_SCHEMA.COLUMNS > WHERE TABLE_NAME =3D '@table' > AND COLUMN_NAME =3D '@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 `<>' (if column does not exist yet) > IF NOT EXISTS (SELECT * > FROM INFORMATION_SCHEMA.COLUMNS > WHERE TABLE_NAME =3D '<>' > AND COLUMN_NAME =3D '<>') > BEGIN > ALTER TABLE <
> > ADD <> <> <> > 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 yo= u 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 > <> > #+end_src > > * No warning if block does not exist > > Please note that inexistent references are ignored, without further notic= e. > That's the case for such a block (where I forgot the number suffix): > > #+srcname: contains-inexistent-ref > #+begin_src sql :noweb yes > <> > #+end_src > > Best regards, > Seb