From mboxrd@z Thu Jan  1 00:00:00 1970
From: =?utf-8?Q?S=C3=A9bastien_Vauban?= <wxhgmqzgwmuf-geNee64TY+gS+FvcfC7Uqw@public.gmane.org>
Subject: [babel] Code which outputs code in another language
Date: Fri, 24 Dec 2010 11:54:35 +0100
Message-ID: <80hbe3e904.fsf@missioncriticalit.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Return-path: <emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org-mXXj517/zsQ@public.gmane.org>
List-Id: "General discussions about Org-mode." <emacs-orgmode.gnu.org>
List-Unsubscribe: <http://lists.gnu.org/mailman/listinfo/emacs-orgmode>,
	<mailto:emacs-orgmode-request-mXXj517/zsQ@public.gmane.org?subject=unsubscribe>
List-Archive: <http://lists.gnu.org/archive/html/emacs-orgmode>
List-Post: <mailto:emacs-orgmode-mXXj517/zsQ@public.gmane.org>
List-Help: <mailto:emacs-orgmode-request-mXXj517/zsQ@public.gmane.org?subject=help>
List-Subscribe: <http://lists.gnu.org/mailman/listinfo/emacs-orgmode>,
	<mailto:emacs-orgmode-request-mXXj517/zsQ@public.gmane.org?subject=subscribe>
Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org-mXXj517/zsQ@public.gmane.org
Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org-mXXj517/zsQ@public.gmane.org
To: emacs-orgmode-mXXj517/zsQ@public.gmane.org

#+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 =3DINSERT INTO=3D commands. This is to import some data d=
irectly
into an SQL database.

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

    # values
    (2 <=3D NR) && ($0 !~ /^[\t]+$/) {
        print "INSERT INTO " table " (" listOfColumns ")"
        printf "    VALUES ("
        for ( i =3D 1; i <=3D 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 =3Doutput=3D 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 =3Dpp=3D or =3Dcode=3D, 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 =3D:colnames yes=3D has become the default, as, in the abo=
ve
   example, I must say =3Dno=3D 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 =3Dhlines=3D handling: it was passed to the =
=3Dawk=3D
   program (and I ignored the line when =3DNR =3D 2=3D). 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 tomorro=
w.

--=20
S=C3=A9bastien 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