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 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 From mboxrd@z Thu Jan 1 00:00:00 1970 From: =?utf-8?Q?S=C3=A9bastien_Vauban?= Subject: Re: [Babel] How to pass variables to SQL blocks? Date: Wed, 01 Dec 2010 11:35:31 +0100 Message-ID: <80tyixlr0s.fsf@missioncriticalit.com> References: <80pqtoqfkh.fsf@missioncriticalit.com> <87ipzefcga.fsf@gmail.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 Hi Eric, "Eric Schulte" wrote: > S=C3=A9bastien Vauban writes: > > 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] = :var 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 For your information, this was the intended code snippet: #+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] :tangle yes -- 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 (I thought this feature would be implemented using "real" SQL vars, hence m= y @ prefix. But viewing the code shows the replacement is made outside of the S= QL snippet's knowledge.) Tested. Works as expected... Thanks a lot! Maybe the following should be put on some TODO list, even if not urgent? >> * No warning if block does not exist >> >> Please note that inexistent references are ignored, without further noti= ce. >> 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 Thanks. 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 From mboxrd@z Thu Jan 1 00:00:00 1970 From: "Eric Schulte" Subject: Re: Re: [Babel] How to pass variables to SQL blocks? Date: Mon, 13 Dec 2010 12:53:32 -0700 Message-ID: <87y67th2kj.fsf@gmail.com> References: <80pqtoqfkh.fsf@missioncriticalit.com> <87ipzefcga.fsf@gmail.com> <80tyixlr0s.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=42973 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1PSESt-0004ep-49 for emacs-orgmode@gnu.org; Mon, 13 Dec 2010 14:53:44 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1PSESs-00063h-05 for emacs-orgmode@gnu.org; Mon, 13 Dec 2010 14:53:43 -0500 Received: from mail-pv0-f169.google.com ([74.125.83.169]:45131) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1PSESr-00063M-Ql for emacs-orgmode@gnu.org; Mon, 13 Dec 2010 14:53:41 -0500 Received: by pvc30 with SMTP id 30so1706107pvc.0 for ; Mon, 13 Dec 2010 11:53:40 -0800 (PST) In-Reply-To: <80tyixlr0s.fsf@missioncriticalit.com> (=?utf-8?Q?=22S=C3=A9b?= =?utf-8?Q?astien?= Vauban"'s message of "Wed, 01 Dec 2010 11:35:31 +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 S=C3=A9bastien Vauban writes: > > (I thought this feature would be implemented using "real" SQL vars, hence= my @ > prefix. But viewing the code shows the replacement is made outside of the= SQL > snippet's knowledge.) > I agree that using real SQL variables would be preferable. If you want to submit a patch to this effect I would be happy to review it and apply. > > Tested. Works as expected... Thanks a lot! > > > Maybe the following should be put on some TODO list, even if not urgent? > >>> * No warning if block does not exist >>> >>> Please note that inexistent references are ignored, without further not= ice. >>> 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 > The lack of notice (or error) was originally intentional. The logic being that there may be languages in which <> constructs are part of the language syntax, and in those languages Org-mode should ignore such constructs unless the "something" contained in the <<>>'s is actually the name of a code block. It may make sense to revisit this design decision. Best -- Eric > > Thanks. > > Best regards, > Seb From mboxrd@z Thu Jan 1 00:00:00 1970 From: =?utf-8?Q?S=C3=A9bastien_Vauban?= Subject: Re: [Babel] How to pass variables to SQL blocks? Date: Mon, 13 Dec 2010 22:23:56 +0100 Message-ID: <80fwu1pdsj.fsf@missioncriticalit.com> References: <80pqtoqfkh.fsf@missioncriticalit.com> <87ipzefcga.fsf@gmail.com> <80tyixlr0s.fsf@missioncriticalit.com> <87y67th2kj.fsf@gmail.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 Hi Eric, "Eric Schulte" wrote: > S=C3=A9bastien Vauban writes: >> (I thought this feature would be implemented using "real" SQL vars, henc= e my @ >> prefix. But viewing the code shows the replacement is made outside of th= e SQL >> snippet's knowledge.) > > I agree that using real SQL variables would be preferable. If you want > to submit a patch to this effect I would be happy to review it and apply. I'll give a shot -- not in the really short term, though. Which example sho= uld I look at? shell? >> Maybe the following should be put on some TODO list, even if not urgent? >> >>>> * 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 > > The lack of notice (or error) was originally intentional. The logic > being that there may be languages in which <> constructs are > part of the language syntax, and in those languages Org-mode should > ignore such constructs unless the "something" contained in the <<>>'s is > actually the name of a code block. > > It may make sense to revisit this design decision. IMHO, it should, yes. Because a typo is so easy, and the results can be very different then, from what's expected... OK, there is always a preview mode, but an alert is better (for me). Maybe adding an option for those languages, that would suppress such warnin= gs? Re helper functions for such named code blocks, do you have an idea what I should do to have the IMenu index of code blocks working? See previous thre= ad [[http://mid.gmane.org/808w09foxd.fsf%40missioncriticalit.com][Re: imenu support for babel blocks]]. Thanks for any hint! 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 From mboxrd@z Thu Jan 1 00:00:00 1970 From: "Eric Schulte" Subject: Re: Re: [Babel] How to pass variables to SQL blocks? Date: Mon, 13 Dec 2010 15:06:37 -0700 Message-ID: <87mxo9fhua.fsf@gmail.com> References: <80pqtoqfkh.fsf@missioncriticalit.com> <87ipzefcga.fsf@gmail.com> <80tyixlr0s.fsf@missioncriticalit.com> <87y67th2kj.fsf@gmail.com> <80fwu1pdsj.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=55050 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1PSGXe-0000DX-D4 for emacs-orgmode@gnu.org; Mon, 13 Dec 2010 17:06:47 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1PSGXa-0002iN-QB for emacs-orgmode@gnu.org; Mon, 13 Dec 2010 17:06:46 -0500 Received: from mail-yx0-f169.google.com ([209.85.213.169]:37169) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1PSGXa-0002iG-Md for emacs-orgmode@gnu.org; Mon, 13 Dec 2010 17:06:42 -0500 Received: by yxl31 with SMTP id 31so4287873yxl.0 for ; Mon, 13 Dec 2010 14:06:42 -0800 (PST) In-Reply-To: <80fwu1pdsj.fsf@missioncriticalit.com> (=?utf-8?Q?=22S=C3=A9b?= =?utf-8?Q?astien?= Vauban"'s message of "Mon, 13 Dec 2010 22:23:56 +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 S=C3=A9bastien Vauban writes: > Hi Eric, > > "Eric Schulte" wrote: >> S=C3=A9bastien Vauban writes: >>> (I thought this feature would be implemented using "real" SQL vars, hen= ce my @ >>> prefix. But viewing the code shows the replacement is made outside of t= he SQL >>> snippet's knowledge.) >> >> I agree that using real SQL variables would be preferable. If you want >> to submit a patch to this effect I would be happy to review it and apply. > > I'll give a shot -- not in the really short term, though. Which example s= hould > I look at? shell? > great, and please no rush. shell actually has rather sophisticated variable passing, maybe sqlite and gnuplot would provide examples more similar to sql. > > >>> Maybe the following should be put on some TODO list, even if not urgent? >>> >>>>> * 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 >> >> The lack of notice (or error) was originally intentional. The logic >> being that there may be languages in which <> constructs are >> part of the language syntax, and in those languages Org-mode should >> ignore such constructs unless the "something" contained in the <<>>'s is >> actually the name of a code block. >> >> It may make sense to revisit this design decision. > > IMHO, it should, yes. Because a typo is so easy, and the results can be v= ery > different then, from what's expected... OK, there is always a preview mod= e, > but an alert is better (for me). > > Maybe adding an option for those languages, that would suppress such warn= ings? > That may be the best idea, once again a header argument may be the most flexible solution. > > Re helper functions for such named code blocks, do you have an idea what I > should do to have the IMenu index of code blocks working? See previous th= read > [[http://mid.gmane.org/808w09foxd.fsf%40missioncriticalit.com][Re: imenu > support for babel blocks]]. > I don't have any experience with IMenu support, however to map over all code blocks in a file extracting information I would suggest using the org-babel-map-src-blocks macro. Cheers -- Eric > > Thanks for any hint! > > Best regards, > Seb