Windows batch scripting and learning to love German footballers

Windows scripting – the computing equivalent of the German football team.
I suppose I should qualify that statement, if only to avoid irate comments from any German readers.
The German National side has always been admired rather than loved.

Yes, they have been more successful than most, and have produced more than their share of great players (Beckenbauer,
Rummenigge, Matthaus…add you’re own favourites here). Yet they are rarely cheered by the neutral.
This lack of popularity probably has quite a bit to do with the fact that, in major finals, Germany seem to be condemned to be cast as the bad guys against the forces of footballing light (the Total Football of Cruyff’s Netherlands in 1974), or the plucky underdog (Czechoslovakia in 1976, Denmark in 1992).

The footballing triumph regarded by Germans above all others is Das Wunder von Berne.
The 1954 World Cup Final was meant to be a coronation of one of the great teams in history. The Hungarians of Ferenc Puskas came into the final on the back of an unbeaten run going back 4 years, including an 8-3 thrashing of West Germany in the group stages of the tournament.

After eight minutes of the final, all was going to plan with Hungary already 2-0 up. What followed was one of the great comebacks – and great upsets – in the history of the game. Rahn completed the miracle with his second of the match, and the winner, with six minutes left.
Sepp Herberger, Fritz Walter and Helmut Rahn are the German equivalent of Alf Ramsey, Bobby Moore and Geoff Hurst. Outside of Germany, they remain largely unknown.
Incidentally, apologies for that 1966 reference, but unlike Germans (Italians, Spaniards…) we English have only that single triumph, or the odd glorious failure to look back on.

So, back to Windows batch scripting. It’s widely used, but next to the richness and variety of it’s siblings in the unix world, it appears hideously limited. However, there are times where it is simply unavoidable.

What follows are some basic examples of

• Accepting user input
• Using variables
• a simple for loop ( because there is no other kind )
• branching
• interaction with an Oracle database

At this point, I have to say that if you are on a Unix/Linux system, or have access to Cygwin, there are far better ways of working with your database.

For any remaining poor unfortunates…

The Batch Script

@echo off
rem
rem Test batch script to show some examples of branching and user
rem input
rem

rem 
rem Prompt for a player name and save the input to the player variable
rem
SET /P player=" Enter your favourite German player or C to cancel : "
rem
rem Do a string insensitive match on player and branch to the appropriate label
rem
IF /I %player%==C GOTO seeya
IF /I %player%==WALTER goto summertime
rem
rem If both the above tests return false, then output this message an quit
rem
echo I'm only a simple batch script. I don't know him. 
goto end

:seeya
echo Operation cancelled
goto end

:summertime
echo Fritz Walter weather. The typical British summer 
goto end

:end

When we run this we get :

c:\bat>simple.bat
Enter your favourite German player or C to cancel : c
Operation cancelled

c:\bat>simple.bat
Enter your favourite German player or C to cancel : Rahn
I'm only a simple batch script. I don't know him.

c:\bat>simple.bat
Enter your favourite German player or C to cancel : Walter
Fritz Walter weather. The typical British summer

c:\bat>

Incidentally, Germans refer to wet weather as “Fritz Walter weather”.
There is an urban myth that eskimos have innumerable words for snow. In English however, it’s fair to say that we have quite a few for rain. Spitting, spotting, shower, deluge, drizzle…and that’s just this summer.

The FOR LOOP

This next example doesn’t quite pre-date 1954, although it feels like it should. Yes there are some
places where Forms6i is still in use…

@echo off
rem
rem Batch script to convert Oracle Developer 6i text objects in the given
rem directory into their runtime executable formats.
rem
SET /P source_dir=" Enter Location of Forms/Reports/Menus to convert : "
SET /P conn_str=" Enter the database connect string to use for the conversion : "
chdir /D %source_dir%
echo Converting Menus
for %%i in (*.mmt) do  Ifcmp60 module_type=MENU module=%%~nxi parse=y userid=%conn_str% batch=yes
for %%i in (*.mmb) do  Ifcmp60 module_type=MENU module=%%~nxi userid=%conn_str% batch=yes
echo Converting Forms
for %%i in (*.fmt) do  Ifcmp60 module=%%~nxi parse=y userid=%conn_str% batch=yes
for %%i in (*.fmb) do  Ifcmp60 module=%%~nxi userid=%conn_str% batch=yes
echo Converting Reports
for %%i in (*.rex) do  rwcon60 userid=%conn_str% stype=rexfile dtype=rdffile source=%%i overwrite=yes logfile=reps.log batch=yes
for %%i in (*.rdf) do  rwcon60 userid=%conn_str% stype=rdffile dtype=repfile source=%%i overwrite=yes logfile=reps.log batch=yes
echo Conversion completed.

The for %%i in (*.fmb) can be translated as While there is a file with the extension .fmb in the directory do this action (in this case, generate the executable .fmx).

Multiple actions

Performing multiple actions based on a condition in a batch script can be problematic. The most common solution appears to be to use
the CALL keyword to simply call another batch script….

@echo off
rem
rem Test batch script to show some examples of branching and user
rem input
rem

rem 
rem Prompt for a player name and save the input to the player variable
rem
SET /P player=" Enter your favourite German player or C to cancel : "
rem
rem Do a string insensitive match on player and branch to the appropriate label
rem
IF /I %player%==C GOTO seeya
IF /I %player%==WALTER goto summertime
rem
rem If both the above tests return false, then output this message an quit
rem
echo I'm only a simple batch script. I don't know him...hang on, I'll ask someone... 
CALL famous.bat %player%
goto end

:seeya
echo Operation cancelled
goto end

:summertime
echo Fritz Walter weather. The typical British summer 
goto end

:end

The batch script we’re calling is ….

@echo off
IF /I %1% == RAHN goto know_him
goto stumped

:know_him
echo Ah, The Boss...or The Cannon from Essen
goto end

:stumped
echo sorry, I'm stumped too
goto end

:end

When we run this we get :

c:\bat>simple2.bat
Enter your favourite German player or C to cancel : Littbarski
I'm only a simple batch script. I don't know him...hang on, I'll ask someone...

sorry, I'm stumped too

c:\bat>simple2.bat
Enter your favourite German player or C to cancel : Rahn
I'm only a simple batch script. I don't know him...hang on, I'll ask someone...

Ah, The Boss...or The Cannon from Essen

c:\bat>

I had to squeeze in a reference to Pierre Littbarski somewhere in all this as he was my favourite German player
when I was growing up.

Simple database stuff

It’s at this point that my aversion to messing around with Windows batch scripts really comes into focus.
Yes, you can call a SQL script easily enough. However, getting any values out seems to require lots
of writing to and reading from temporary files and all sorts of other programming gymnastics.
I’m sure that someone out there has a simpler way of doing ( in which case, please share).
In the meantime, here’s another simple example – this time, of invoking a script using SQL*Plus.

@echo off
SET /P conn_str="Enter uid/pwd@db : "
sqlplus -s %conn_str% @get_time.sql

The SQL script itself looks like this :

set heading off
SELECT TO_CHAR(SYSDATE, 'HH24:MI') 
FROM dual;
quit

Of course, it is possible to pass positional parameters to the SQL script itself.
I think that’s quite enough for now. I have something to refer back to next time I find myself in Windows batch script limbo.
Deb has just wandered in and hinted strongly that it won’t actually rain much tomorrow, which would be a perfect time
to go and mow the jungle that is our back garden.
Fritz Walter – where are you when I need you ?