[babel] Code which outputs code in another language

From: "Sébastien Vauban" <wxhgmqzgwmuf-geNee64TY+gS+FvcfC7Uqw@public.gmane.org>
To: emacs-orgmode-mXXj517/zsQ@public.gmane.org
Subject: [babel] Code which outputs code in another language
Date: Fri, 24 Dec 2010 11:54:35 +0100	[thread overview]
Message-ID: <80hbe3e904.fsf@missioncriticalit.com> (raw)

#+TITLE:     Code which outputs code in another language
#+DATE:      2010-12-24
#+LANGUAGE:  en_US

#+BABEL:     :engine msosql :eval no

* Input data

#+results: input
| ID  | val1 | val2 |
|-----+------+------|
| ABC |   13 |   34 |
| DEF |   55 |   28 |

* Code

The following code takes a table (or tab separated file) in input, and
generates the SQL =INSERT INTO= commands. This is to import some data directly
into an SQL database.

#+srcname: insert-values-into-table
#+begin_src sh :var table="mytable" :var data=input :results output :exports both :colnames no
echo "$data" | awk -F "\t" -v table=$table -v quote="'" '
    # column names
    NR == 1 {
        for ( i = 1; i <= NF; i++ ) {
            sub(/ *$/, "", $i) # remove trailing spaces
            listOfColumns = listOfColumns $i
            if ( i < NF ) listOfColumns = listOfColumns ", "
        }
    }

    # values
    (2 <= NR) && ($0 !~ /^[\t]+$/) {
        print "INSERT INTO " table " (" listOfColumns ")"
        printf "    VALUES ("
        for ( i = 1; i <= NF; i++ ) {
            sub(/ *$/, "", $i) # remove trailing spaces
            switch ($i) {
                case /^[[:digit:]]+$/: # only numbers
                    printf $i
                    break
                case "NULL": # reserved keyword
                    printf $i
                    break
                default:
                    printf quote $i quote
            }
            if ( i < NF ) printf ", "
        }
        print ")"
    }
'
#+end_src

This can be put to Worg, if wished, as is, or with small adaptations
(depending on the [[*Extra%20questions][Extra questions]]).

* Output data

** What I got

... when using =output= results.

#+results: insert-values-into-table
#+begin_example
INSERT INTO mytable (ID, val1, val2)
    VALUES ('ABC', 13, 34)
INSERT INTO mytable (ID, val1, val2)
    VALUES ('DEF', 55, 28)
#+end_example

If using =pp= or =code=, things get worse (I get tables!?)...

** What I wish

I wish I could say that the results is some code in some language, in order to
obtain the following.

#+results: insert-values-into-table
#+begin_src sql
INSERT INTO mytable (ID, val1, val2)
    VALUES ('ABC', 13, 34)
INSERT INTO mytable (ID, val1, val2)
    VALUES ('DEF', 55, 28)
#+end_src

That way, both editing in Org and exported code would always be made in the
right fontification...

Is that something sensible, that'd be worth being added?

* Extra questions

1. It seems that =:colnames yes= has become the default, as, in the above
   example, I must say =no= to make it work properly?

2. In the same spirit, when using the above code a couple of weeks ago,
   everything was fine for the =hlines= handling: it was passed to the =awk=
   program (and I ignored the line when =NR = 2=). Now, not anymore? Hence, I
   slightly edited my above code, to be compliant with the new way it works.
   Bug or feature? Temporarily or definitive?

Happy christmas!

Best regards,
  Seb

PS- I will be completely out (normally) for one week, starting from tomorrow.

-- 
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