#+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@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode