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

* Re: [Babel] How to pass variables to SQL blocks?
  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
  0 siblings, 1 reply; 6+ messages in thread
From: Eric Schulte @ 2010-12-01  2:34 UTC (permalink / raw)
  To: Sébastien Vauban; +Cc: emacs-orgmode

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

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

* Re: [Babel] How to pass variables to SQL blocks?
  2010-12-01  2:34 ` Eric Schulte
@ 2010-12-01 10:35   ` Sébastien Vauban
  2010-12-13 19:53     ` Eric Schulte
  0 siblings, 1 reply; 6+ messages in thread
From: Sébastien Vauban @ 2010-12-01 10:35 UTC (permalink / raw)
  To: emacs-orgmode-mXXj517/zsQ

Hi Eric,

"Eric Schulte" wrote:
> Sébastien Vauban <wxhgmqzgwmuf-geNee64TY+gS+FvcfC7Uqw@public.gmane.org> 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=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

For your information, this was the intended code snippet:

#+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] :tangle 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

(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.)

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 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

Thanks.

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

* Re: Re: [Babel] How to pass variables to SQL blocks?
  2010-12-01 10:35   ` Sébastien Vauban
@ 2010-12-13 19:53     ` Eric Schulte
  2010-12-13 21:23       ` Sébastien Vauban
  0 siblings, 1 reply; 6+ messages in thread
From: Eric Schulte @ 2010-12-13 19:53 UTC (permalink / raw)
  To: Sébastien Vauban; +Cc: emacs-orgmode

Sébastien Vauban <wxhgmqzgwmuf@spammotel.com> 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 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
>

The lack of notice (or error) was originally intentional.  The logic
being that there may be languages in which <<something>> 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

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

* Re: [Babel] How to pass variables to SQL blocks?
  2010-12-13 19:53     ` Eric Schulte
@ 2010-12-13 21:23       ` Sébastien Vauban
  2010-12-13 22:06         ` Eric Schulte
  0 siblings, 1 reply; 6+ messages in thread
From: Sébastien Vauban @ 2010-12-13 21:23 UTC (permalink / raw)
  To: emacs-orgmode-mXXj517/zsQ

Hi Eric,

"Eric Schulte" wrote:
> Sébastien Vauban <wxhgmqzgwmuf-geNee64TY+gS+FvcfC7Uqw@public.gmane.org> 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.

I'll give a shot -- not in the really short term, though. Which example should
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
>>>> <<add-column-in-table>>
>>>> #+end_src
>
> The lack of notice (or error) was originally intentional.  The logic
> being that there may be languages in which <<something>> 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 warnings?

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 thread
[[http://mid.gmane.org/808w09foxd.fsf%40missioncriticalit.com][Re: imenu
support for babel blocks]].

Thanks for any hint!

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

* Re: Re: [Babel] How to pass variables to SQL blocks?
  2010-12-13 21:23       ` Sébastien Vauban
@ 2010-12-13 22:06         ` Eric Schulte
  0 siblings, 0 replies; 6+ messages in thread
From: Eric Schulte @ 2010-12-13 22:06 UTC (permalink / raw)
  To: Sébastien Vauban; +Cc: emacs-orgmode

Sébastien Vauban <wxhgmqzgwmuf@spammotel.com> writes:

> Hi Eric,
>
> "Eric Schulte" wrote:
>> Sébastien Vauban <wxhgmqzgwmuf@spammotel.com> 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.
>
> I'll give a shot -- not in the really short term, though. Which example should
> 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
>>>>> <<add-column-in-table>>
>>>>> #+end_src
>>
>> The lack of notice (or error) was originally intentional.  The logic
>> being that there may be languages in which <<something>> 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 warnings?
>

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 thread
> [[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

^ 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).