From mboxrd@z Thu Jan 1 00:00:00 1970 From: =?utf-8?Q?S=C3=A9bastien_Vauban?= Subject: [Babel] How to pass variables to SQL blocks? Date: Mon, 29 Nov 2010 17:07:10 +0100 Message-ID: <80pqtoqfkh.fsf@missioncriticalit.com> Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Return-path: 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-mXXj517/zsQ@public.gmane.org Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org-mXXj517/zsQ@public.gmane.org To: emacs-orgmode-mXXj517/zsQ@public.gmane.org #+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=3Dparam[0,1] :var @column=3Dparam[1,1] :var @ty= pe=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 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 <> #+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 <> #+end_src Best regards, Seb --=20 S=C3=A9bastien 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