* [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 external index
https://git.savannah.gnu.org/cgit/emacs.git
https://git.savannah.gnu.org/cgit/emacs/org-mode.git
This is an external index of several public inboxes,
see mirroring instructions on how to clone and mirror
all data and code used by this external index.