emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
* [Babel] Using Noweb + some problems
@ 2010-12-03 16:30 Sébastien Vauban
  2010-12-05 15:34 ` Eric Schulte
  0 siblings, 1 reply; 2+ messages in thread
From: Sébastien Vauban @ 2010-12-03 16:30 UTC (permalink / raw)
  To: emacs-orgmode-mXXj517/zsQ

#+TITLE:     Make use of NoWeb with string replacement
#+DATE:      2010-12-03
#+LANGUAGE:  en_US

* Abstract

Difficult to sum up. Though, questions turn around the reuse of code for
tangle purpose (read: Noweb "calls") whose text must be replaced inside.

Two real problems are also identified -- see [[*Important%20remarks][Important remarks]].

* Somewhere in my local LOB

(I'm beginning to "play" with one such file. Though, I've never succeeded
copying a new snippet to it, using =C-c C-v i=, even when it's written it's
successfully added)

** Add a column into a table

#+srcname: add-column-in-table(table, column, type, nullability)
#+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 $table
    ADD $column $type $nullability
END
#+end_src

** Convert date to French format

#+srcname: convert-date-to-French-format
#+begin_src sql :var column :engine msosql
CONVERT(varchar(10), $column, 103) AS $column
#+end_src

<<THERE>>

=103= is the code for the "French" format =dd/mm/yyyy=.

* Somewhere in my work file

** Add the following columns

Create 3 new columns:

#+results: sql-param
| table       | column  | type    | nullability |
|-------------+---------+---------+-------------|
| prestations | prsNbr1 | tinyint | NULL        |
| prestations | prsNbr2 | tinyint | NULL        |
| prestations | prsNbr3 | tinyint | NULL        |

*** Code snippet (to be tangled later on)

I want to apply the values onto the following chunk of code:

#+srcname: add-column-in-table(table, column, type, nullability)
#+begin_src sql :results output
-- 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

*** Expanded code block

Write out the result of the expansions:

#+call: add-column-in-table(table=sql-param[2,0], column=sql-param[2,1], type=sql-param[2,2], nullability=sql-param[2,3])

#+call: add-column-in-table(table=sql-param[3,0], column=sql-param[3,1], type=sql-param[3,2], nullability=sql-param[3,3])

#+call: add-column-in-table(table=sql-param[4,0], column=sql-param[4,1], type=sql-param[4,2], nullability=sql-param[4,3])

Pressing =C-c C-v C-e= on the above =#+call= lines shows me the following
stack trace:

#+begin_src emacs-lisp
Debugger entered--Lisp error: (wrong-type-argument stringp nil)
  intern(nil)
  (let ((--cl-var-- ...)) (cond (... ...) (... ...) (... ...) (t ...)))
  (case (intern engine) ((quote msosql) (format "osql %s -s \"	\" -i %s -o %s" ... ... ...)) ((quote mysql) (format "mysql %s -e \"source %s\" > %s" ... ... ...)) ((quote postgresql) (format "psql -A -P footer=off -F \"	\"  -f %s -o %s %s" ... ... ...)) (t (error "no support for the %s sql engine" engine)))
  (let* ((result-params ...) (cmdline ...) (engine ...) (in-file ...) (out-file ...) (command ...)) (with-temp-file in-file (insert ...)) (message command) (shell-command command) (search-forward "#+end_src") (forward-char 1) (insert "\n#+results:\n#+begin_example\n") (insert "#+end_example\n") (beginning-of-line) (previous-line) (insert-file-contents out-file))
  org-babel-execute:sql("-- add column `$column' (if column does not exist yet)\nIF NOT EXISTS (SELECT *\n               FROM INFORMATION_SCHEMA.COLUMNS\n               WHERE TABLE_NAME = '$table'\n               AND COLUMN_NAME = '$column')\nBEGIN\n    ALTER TABLE $table\n    ADD $column $type $nullability\nEND\n" ((:var type . "tinyint") (:var table . "prestations") (:var column . "prsNbr1") (:var nullability . "NULL") (:colname-names) (:rowname-names) (:result-params "silent" "replace") (:result-type . value) (:comments . "") (:shebang . "") (:cache . "no") (:noweb . "no") (:tangle . "no") (:exports . "code") (:results . "silent") (:hlines . "no") (:session . "none") (:result-type . value) (:result-params "replace") (:rowname-names) (:colname-names)))
  funcall(org-babel-execute:sql "-- add column `$column' (if column does not exist yet)\nIF NOT EXISTS (SELECT *\n               FROM INFORMATION_SCHEMA.COLUMNS\n               WHERE TABLE_NAME = '$table'\n               AND COLUMN_NAME = '$column')\nBEGIN\n    ALTER TABLE $table\n    ADD $column $type $nullability\nEND\n" ((:var type . "tinyint") (:var table . "prestations") (:var column . "prsNbr1") (:var nullability . "NULL") (:colname-names) (:rowname-names) (:result-params "silent" "replace") (:result-type . value) (:comments . "") (:shebang . "") (:cache . "no") (:noweb . "no") (:tangle . "no") (:exports . "code") (:results . "silent") (:hlines . "no") (:session . "none") (:result-type . value) (:result-params "replace") (:rowname-names) (:colname-names)))
#+end_src

** Use date conversion code

I would like to export, as results, the following snippet where the Noweb
references are replaced by their correct SQL equivalent.

I've tried using =code= or =output= as results, but that does not get me where
I want to go.

#+srcname: extract-data
#+begin_src sql :engine mysql :noweb yes :results code
SELECT TOP 5 tableID,
       etpNumber,
       <<convert-date-to-French-format(column="frmDate")>>,
       <<convert-date-to-French-format(column="signDate")>>
FROM table
#+end_src

Good result would be:

#+begin_src sql
SELECT TOP 5 tableID,
       etpNumber,
       CONVERT(varchar(10), frmDate, 103) AS frmDate,
       CONVERT(varchar(10), signDate, 103) AS signDate
FROM table
#+end_src

But, maybe, I'm expecting something that's not possible (yet)?  Or I'm simply
mixing concepts in my mind (tangling, evaluating)?

* Important remarks

Note here:

- When *evaluating* the above block, it calls the command-line engine, and
  puts error results back somewhere above in this file: it goes to [[THERE]] (in
  section [[*Convert%20date%20to%20French%20format][Convert date to French format]])!

- When executing, I've seen a *missing newline* just before =#+end_example=:
  it wasn't beginning on its own line.

* Questions

- When we're using snippets from different files, which BABEL file
  instructions are considered:
  + the one the snippet belongs to, or
  + the one where the snipped is used?

- Would it be possible to add the speed commands (like =e=, =v=, etc.) onto
  the =#+call= lines?

- Can I get somehow what I expect?  Is that possible as of today?

- Is the above the right way to do things?

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] 2+ messages in thread

* Re: [Babel] Using Noweb + some problems
  2010-12-03 16:30 [Babel] Using Noweb + some problems Sébastien Vauban
@ 2010-12-05 15:34 ` Eric Schulte
  0 siblings, 0 replies; 2+ messages in thread
From: Eric Schulte @ 2010-12-05 15:34 UTC (permalink / raw)
  To: Sébastien Vauban; +Cc: emacs-orgmode

Hi Seb,

I find this email difficult to parse.  Could you take some extra time in
composition of these emails to distill the problem/question into a small
clear statement of a couple of sentences with a single <10 line example.

There is simply too much content below and without investing a great
deal of time I don't know which parts are germane to your question.

Thanks -- Eric

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

> #+TITLE:     Make use of NoWeb with string replacement
> #+DATE:      2010-12-03
> #+LANGUAGE:  en_US
>
> * Abstract
>
> Difficult to sum up. Though, questions turn around the reuse of code for
> tangle purpose (read: Noweb "calls") whose text must be replaced inside.
>
> Two real problems are also identified -- see
> [[*Important%20remarks][Important remarks]].
>
> * Somewhere in my local LOB
>
> (I'm beginning to "play" with one such file. Though, I've never succeeded
> copying a new snippet to it, using =C-c C-v i=, even when it's written it's
> successfully added)
>
> ** Add a column into a table
>
> #+srcname: add-column-in-table(table, column, type, nullability)
> #+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 $table
>     ADD $column $type $nullability
> END
> #+end_src
>
> ** Convert date to French format
>
> #+srcname: convert-date-to-French-format
> #+begin_src sql :var column :engine msosql
> CONVERT(varchar(10), $column, 103) AS $column
> #+end_src
>
> <<THERE>>
>
> =103= is the code for the "French" format =dd/mm/yyyy=.
>
> * Somewhere in my work file
>
> ** Add the following columns
>
> Create 3 new columns:
>
> #+results: sql-param
> | table       | column  | type    | nullability |
> |-------------+---------+---------+-------------|
> | prestations | prsNbr1 | tinyint | NULL        |
> | prestations | prsNbr2 | tinyint | NULL        |
> | prestations | prsNbr3 | tinyint | NULL        |
>
> *** Code snippet (to be tangled later on)
>
> I want to apply the values onto the following chunk of code:
>
> #+srcname: add-column-in-table(table, column, type, nullability)
> #+begin_src sql :results output
> -- 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
>
> *** Expanded code block
>
> Write out the result of the expansions:
>
> #+call: add-column-in-table(table=sql-param[2,0],
> column=sql-param[2,1], type=sql-param[2,2],
> nullability=sql-param[2,3])
>
> #+call: add-column-in-table(table=sql-param[3,0],
> column=sql-param[3,1], type=sql-param[3,2],
> nullability=sql-param[3,3])
>
> #+call: add-column-in-table(table=sql-param[4,0],
> column=sql-param[4,1], type=sql-param[4,2],
> nullability=sql-param[4,3])
>
> Pressing =C-c C-v C-e= on the above =#+call= lines shows me the following
> stack trace:
>
> #+begin_src emacs-lisp
> Debugger entered--Lisp error: (wrong-type-argument stringp nil)
>   intern(nil)
>   (let ((--cl-var-- ...)) (cond (... ...) (... ...) (... ...) (t ...)))
>   (case (intern engine) ((quote msosql) (format "osql %s -s \"	\" -i %s -o %s" ... ... ...)) ((quote mysql) (format "mysql %s -e \"source %s\" > %s" ... ... ...)) ((quote postgresql) (format "psql -A -P footer=off -F \"	\"  -f %s -o %s %s" ... ... ...)) (t (error "no support for the %s sql engine" engine)))
>   (let* ((result-params ...) (cmdline ...) (engine ...) (in-file ...) (out-file ...) (command ...)) (with-temp-file in-file (insert ...)) (message command) (shell-command command) (search-forward "#+end_src") (forward-char 1) (insert "\n#+results:\n#+begin_example\n") (insert "#+end_example\n") (beginning-of-line) (previous-line) (insert-file-contents out-file))
>   org-babel-execute:sql("-- add column `$column' (if column does not exist yet)\nIF NOT EXISTS (SELECT *\n               FROM INFORMATION_SCHEMA.COLUMNS\n               WHERE TABLE_NAME = '$table'\n               AND COLUMN_NAME = '$column')\nBEGIN\n    ALTER TABLE $table\n    ADD $column $type $nullability\nEND\n" ((:var type . "tinyint") (:var table . "prestations") (:var column . "prsNbr1") (:var nullability . "NULL") (:colname-names) (:rowname-names) (:result-params "silent" "replace") (:result-type . value) (:comments . "") (:shebang . "") (:cache . "no") (:noweb . "no") (:tangle . "no") (:exports . "code") (:results . "silent") (:hlines . "no") (:session . "none") (:result-type . value) (:result-params "replace") (:rowname-names) (:colname-names)))
>   funcall(org-babel-execute:sql "-- add column `$column' (if column does not exist yet)\nIF NOT EXISTS (SELECT *\n               FROM INFORMATION_SCHEMA.COLUMNS\n               WHERE TABLE_NAME = '$table'\n               AND COLUMN_NAME = '$column')\nBEGIN\n    ALTER TABLE $table\n    ADD $column $type $nullability\nEND\n" ((:var type . "tinyint") (:var table . "prestations") (:var column . "prsNbr1") (:var nullability . "NULL") (:colname-names) (:rowname-names) (:result-params "silent" "replace") (:result-type . value) (:comments . "") (:shebang . "") (:cache . "no") (:noweb . "no") (:tangle . "no") (:exports . "code") (:results . "silent") (:hlines . "no") (:session . "none") (:result-type . value) (:result-params "replace") (:rowname-names) (:colname-names)))
> #+end_src
>
> ** Use date conversion code
>
> I would like to export, as results, the following snippet where the Noweb
> references are replaced by their correct SQL equivalent.
>
> I've tried using =code= or =output= as results, but that does not get me where
> I want to go.
>
> #+srcname: extract-data
> #+begin_src sql :engine mysql :noweb yes :results code
> SELECT TOP 5 tableID,
>        etpNumber,
>        <<convert-date-to-French-format(column="frmDate")>>,
>        <<convert-date-to-French-format(column="signDate")>>
> FROM table
> #+end_src
>
> Good result would be:
>
> #+begin_src sql
> SELECT TOP 5 tableID,
>        etpNumber,
>        CONVERT(varchar(10), frmDate, 103) AS frmDate,
>        CONVERT(varchar(10), signDate, 103) AS signDate
> FROM table
> #+end_src
>
> But, maybe, I'm expecting something that's not possible (yet)?  Or I'm simply
> mixing concepts in my mind (tangling, evaluating)?
>
> * Important remarks
>
> Note here:
>
> - When *evaluating* the above block, it calls the command-line engine, and
>   puts error results back somewhere above in this file: it goes to [[THERE]] (in
>   section [[*Convert%20date%20to%20French%20format][Convert date to
> French format]])!
>
> - When executing, I've seen a *missing newline* just before =#+end_example=:
>   it wasn't beginning on its own line.
>
> * Questions
>
> - When we're using snippets from different files, which BABEL file
>   instructions are considered:
>   + the one the snippet belongs to, or
>   + the one where the snipped is used?
>
> - Would it be possible to add the speed commands (like =e=, =v=, etc.) onto
>   the =#+call= lines?
>
> - Can I get somehow what I expect?  Is that possible as of today?
>
> - Is the above the right way to do things?
>
> Best regards,
>   Seb

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

end of thread, other threads:[~2010-12-05 15:34 UTC | newest]

Thread overview: 2+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2010-12-03 16:30 [Babel] Using Noweb + some problems Sébastien Vauban
2010-12-05 15:34 ` 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).