From: "Sébastien Vauban" <wxhgmqzgwmuf-geNee64TY+gS+FvcfC7Uqw@public.gmane.org>
To: emacs-orgmode-mXXj517/zsQ@public.gmane.org
Subject: [Babel] Using Noweb + some problems
Date: Fri, 03 Dec 2010 17:30:45 +0100 [thread overview]
Message-ID: <801v5ybyyy.fsf@missioncriticalit.com> (raw)
#+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
next reply other threads:[~2010-12-03 16:30 UTC|newest]
Thread overview: 2+ messages / expand[flat|nested] mbox.gz Atom feed top
2010-12-03 16:30 Sébastien Vauban [this message]
2010-12-05 15:34 ` [Babel] Using Noweb + some problems Eric Schulte
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=801v5ybyyy.fsf@missioncriticalit.com \
--to=wxhgmqzgwmuf-genee64ty+gs+fvcfc7uqw@public.gmane.org \
--cc=emacs-orgmode-mXXj517/zsQ@public.gmane.org \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
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.