Re: [babel] Is this supported?

[Dan Davison <davison@stats.ox.ac.uk>]

Sébastien Vauban <wxhgmqzgwmuf@spammotel.com> writes:
<...>
> Is there a way to tangle it with some string replacements being made, such as:
>
> | pTable            | dossier |
> | pColumn           | pfiNew  |
> | pDatatype         | string  |
> | pAcceptnullvalues | NULL    |

>
> I've tried the following, with no success:
>
> #+srcname: add-column-in-table(pTable=dossier,pColumn=pfiNew,pDatatype=string,pAcceptnullvalues=NULL)

Hi Seb,

Those function-arguments / variable assignments won't affect the
*tangled* output. Afaik the only method for making substitutions in the
tangled output is the <<source block name>> block references. So one way
to achieve what you want would be to create a block for each string
replacement (Example [1] below; I don't know if I've got the quoting
right in the sql output). But perhaps Tom/Eric will have a better
answer.

Incidentally, it seems that we do not currently support variables when
*evaluating* an sql block. I.e. the function-arguments that you used:

> #+srcname: add-column-in-table(pTable=dossier,pColumn=pfiNew,pDatatype=string,pAcceptnullvalues=NULL)

will have no effect. The patch below[2] implements that. I'll let Eric
decide whether it's appropriate as I don't know anything about sql. Note
that you would need to quote the strings in your srcname line
(i.e. (pTable="dossier", ...))

Dan


>     #+begin_src sql :tangle dossier.sql
>         -- add column `pfiDossierSentToSecteur' (if column does not exist yet)
>         IF NOT EXISTS (SELECT *
>                        FROM INFORMATION_SCHEMA.COLUMNS
>                        WHERE TABLE_NAME = 'pTable'
>                        AND COLUMN_NAME = 'pColumn)
>         BEGIN
>             ALTER TABLE pTable
>             ADD pColumn pDatatype pAcceptnullvalues
>         END
>         GO
>     #+end_src
>
> Is such a feature supported, or another way to come down to the same result?

Footnotes:

[1] 
----------------------------------------------------------------
#+srcname: pTable
#+begin_src emacs-lisp
"dossier"
#+end_src

#+srcname: pColumn
#+begin_src emacs-lisp
"pfiNew"
#+end_src

#+srcname: pDatatype
#+begin_src emacs-lisp
"string"
#+end_src

#+srcname: pAcceptnullvalues
#+begin_src emacs-lisp
"NULL"
#+end_src

#+begin_src sql :tangle dossier.sql :engine mysql
        -- add column `pfiDossierSentToSecteur' (if column does not exist yet)
        IF NOT EXISTS (SELECT *
                       FROM INFORMATION_SCHEMA.COLUMNS
                       WHERE TABLE_NAME = <<pTable>>
                       AND COLUMN_NAME = <<pColumn>>)
        BEGIN
            ALTER TABLE <<pTable>>
            ADD <<pColumn>> <<pDatatype>> <<pAcceptnullvalues>>
        END
        GO
#+end_src
----------------------------------------------------------------

[2] 
----------------------------------------------------------------
diff --git a/contrib/babel/lisp/langs/org-babel-sql.el b/contrib/babel/lisp/langs/org-babel-sql.el
index 837c5fd..7e37fee 100644
--- a/contrib/babel/lisp/langs/org-babel-sql.el
+++ b/contrib/babel/lisp/langs/org-babel-sql.el
@@ -55,7 +55,8 @@
   "Execute a block of Sql code with org-babel.  This function is
 called by `org-babel-execute-src-block'."
   (message "executing Sql source code block")
-  (let* ((result-params (split-string (or (cdr (assoc :results params)) "")))
+  (let* ((processed-params (org-babel-process-params params))
+        (vars (second processed-params))
          (cmdline (cdr (assoc :cmdline params)))
          (engine (cdr (assoc :engine params)))
          (in-file (make-temp-file "org-babel-sql-in"))
@@ -66,6 +67,9 @@ called by `org-babel-execute-src-block'."
                                     (or cmdline "") in-file out-file))
                    ('nil (error "sql engine not specified"))
                     (t (error "no support for the %s sql engine" engine)))))
+    (mapc (lambda (pair)
+           (setq body (replace-regexp-in-string (format "%s" (car pair)) (cdr pair) body)))
+         vars)
     (with-temp-file in-file (insert body))
     (message command)
     (shell-command command)
----------------------------------------------------------------