From mboxrd@z Thu Jan 1 00:00:00 1970 From: "Eric Schulte" Subject: Re: [Babel] Using Noweb + some problems Date: Sun, 05 Dec 2010 08:34:46 -0700 Message-ID: <87ipz8z10p.fsf@gmail.com> References: <801v5ybyyy.fsf@missioncriticalit.com> Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Return-path: Received: from [140.186.70.92] (port=39458 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1PPGc2-0000Cp-U7 for emacs-orgmode@gnu.org; Sun, 05 Dec 2010 10:34:58 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1PPGc0-0000Lq-95 for emacs-orgmode@gnu.org; Sun, 05 Dec 2010 10:34:54 -0500 Received: from mail-gy0-f169.google.com ([209.85.160.169]:35332) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1PPGc0-0000Lf-01 for emacs-orgmode@gnu.org; Sun, 05 Dec 2010 10:34:52 -0500 Received: by gyg10 with SMTP id 10so20414475gyg.0 for ; Sun, 05 Dec 2010 07:34:51 -0800 (PST) In-Reply-To: <801v5ybyyy.fsf@missioncriticalit.com> (=?utf-8?Q?=22S=C3=A9b?= =?utf-8?Q?astien?= Vauban"'s message of "Fri, 03 Dec 2010 17:30:45 +0100") List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: =?utf-8?Q?S=C3=A9bastien?= Vauban Cc: emacs-orgmode@gnu.org 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=C3=A9bastien Vauban 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 =3DC-c C-v i=3D, even when it's writte= n 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 =3D '$table' > AND COLUMN_NAME =3D '$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 > > <> > > =3D103=3D is the code for the "French" format =3Ddd/mm/yyyy=3D. > > * 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 =3D '$table' > AND COLUMN_NAME =3D '$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=3Dsql-param[2,0], > column=3Dsql-param[2,1], type=3Dsql-param[2,2], > nullability=3Dsql-param[2,3]) > > #+call: add-column-in-table(table=3Dsql-param[3,0], > column=3Dsql-param[3,1], type=3Dsql-param[3,2], > nullability=3Dsql-param[3,3]) > > #+call: add-column-in-table(table=3Dsql-param[4,0], > column=3Dsql-param[4,1], type=3Dsql-param[4,2], > nullability=3Dsql-param[4,3]) > > Pressing =3DC-c C-v C-e=3D on the above =3D#+call=3D lines shows me the f= ollowing > 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=3Doff -F \" \= " -f %s -o %s %s" ... ... ...)) (t (error "no support for the %s sql engin= e" engine))) > (let* ((result-params ...) (cmdline ...) (engine ...) (in-file ...) (ou= t-file ...) (command ...)) (with-temp-file in-file (insert ...)) (message c= ommand) (shell-command command) (search-forward "#+end_src") (forward-char = 1) (insert "\n#+results:\n#+begin_example\n") (insert "#+end_example\n") (b= eginning-of-line) (previous-line) (insert-file-contents out-file)) > org-babel-execute:sql("-- add column `$column' (if column does not exis= t yet)\nIF NOT EXISTS (SELECT *\n FROM INFORMATION_SCHEMA.COL= UMNS\n WHERE TABLE_NAME =3D '$table'\n AND COLU= MN_NAME =3D '$column')\nBEGIN\n ALTER TABLE $table\n ADD $column $typ= e $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_SC= HEMA.COLUMNS\n WHERE TABLE_NAME =3D '$table'\n = AND COLUMN_NAME =3D '$column')\nBEGIN\n ALTER TABLE $table\n ADD $col= umn $type $nullability\nEND\n" ((:var type . "tinyint") (:var table . "pres= tations") (:var column . "prsNbr1") (:var nullability . "NULL") (:colname-n= ames) (:rowname-names) (:result-params "silent" "replace") (:result-type . = value) (:comments . "") (:shebang . "") (:cache . "no") (:noweb . "no") (:t= angle . "no") (:exports . "code") (:results . "silent") (:hlines . "no") (:= session . "none") (:result-type . value) (:result-params "replace") (:rowna= me-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 =3Dcode=3D or =3Doutput=3D 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, > <>, > <> > 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 si= mply > 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 [[THER= E]] (in > section [[*Convert%20date%20to%20French%20format][Convert date to > French format]])! > > - When executing, I've seen a *missing newline* just before =3D#+end_exam= ple=3D: > 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 =3De=3D, =3Dv=3D, = etc.) onto > the =3D#+call=3D 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