REXX, the REstructured eXtended eXectuor

In many ways, Unix and Linux provide a very friendly development environment for programmers.   There's a large suite of system utilities that accomplish all sorts of things from sorting to cutting to joining.  And all these utilities can be glued together with pipes, either ad-hoc on the command line or as part of shell script. While I've done a fair amount of shell programming, I'm not a big fan of either ksh or bash as scripting languages.  The shell scripting languages are quite archaic, and I'd really prefer something more modern.

Many years ago, I learned to program in the EXEC-2 language, a scripting language used by IBM's VM/CMS operating system.  EXEC-2 and the Unix shell languages share many of the same virtues and vices, and if you learned one you could easily understand the other.  I liked the quick edit-test development cycle, the ability to directly interface with the operating system, the ability to interface directly with the system editor, the ability to create new system commands, and the clumsy but powerful control structures.  You could do a lot of clever things in EXEC-2 or the shell; but it always felt a bit clumsy.

And then IBM introduced the rest of the world to REXX, included as the System Product Interpreter in VM/SP Release 3. Wow!  Everything I loved about EXEC-2 or shell in a modern language that resembled PL/I or the data step of the SAS system.

REXX was the brainchild of IBMer Mike Colishaw.  Colishaw's aims for REXX were to create a language that was easy to learn and easy to use, a language that was powerful enough for serious program development, a language that favored the REXX programmer over the REXX implementer.  And he succeeded.  REXX is an easy to learn, intrepreted language with nearly all the control structures found in PL/I.  And, unlike the old EXEC-2 or ksh/bash scripting languages, there's a really strong correlation between syntax and semantics.

So, let's talk about REXX.   We'll install Rexx on a Linux Ubuntu system, and we'll demonstrate a little Rexx code, and we'll conclude with some pointers to more information.

Starting off, I thought I'd check the Ubuntu Software Center.  I couldn't find a version of REXX in the Ubuntu Software Center.  But, googling "rexx site:packages.ubuntu.com" turned up at link at http://packages.ubuntu.com/lucid/regina-rexx.  Clicking the link gives us:




Perfect!  We have a build for Ubuntu 12.04.  We could download and install it, but let's let apt-get do the work for us.  After su'ing to root, just run apt-get install regina-rexx:



Answer Y to continue, and in a few minutes, the Regina Rexx interpreter is installed on your system.  And, voila, you're ready to run the traditional Hello World test:



Yup, the traditional Hello, World works.  Even better,  Rexx takes 22/7 and computes pi.   Neither EXEC-2 nor the shell will do that so easily.

What does a short Rexx program look like?  Our first example demonstrates an easy trick to make a Rexx program execute like any bash or ksh shell program in your $PATH. The program also demonstrates the Rexx parse instruction.

#!/usr/bin/rexx
/* This is a Rexx comment. The #! is used solely to make our 
   program execute like any ksh or bash script.  
*/
parse arg first_name '('common_name')' last_name . 

say 'First name: ' first_name
say 'Common name:' common_name
say 'Last name:  ' last_name

exit 0

Shell programmers will recognize the very unRexx-like first line. For ksh or bash programs, the first line is a comment (#) followed by a bang (!) that points to the interpreter. This is usually /bin/bash or /bin/ksh. Luckily, we can put the pathname to our Rexx interpreter in the #! path, and it works the same.  If you're not sure where the Rexx interpreter is installed, use the whereis command.  Just remember to chmod +x the Rexx script, and then execute it.  Here's a sample:


Two other noteworthy points in this program:  the parse instruction and the exit command.

The parse instruction will save you more time than almost any other feature of REXX.  Parse is extremely powerful, and it splits strings into variables better than almost any other programming language I can think of.

The exit instruction returns success codes to the operating system.  In Unix or Linux, you can check these code with the $? variable in your shell. If you execute a program from a REXX program, you can check the success code with the predefined rc variable.

One more short program.  From Robert Sedgewick's book Algorithms, we'll translate the greatest common denominator program from Pascal to REXX.  Here's the REXX code:

/* Example from R. Sedgewick's _Algorithms_ */
/* Find the greatest common divsor using    */
/* Euclid's algorithm.                      */
trace r
arg x y

 say gcd(x,y)

exit 0

/* Internal function */
gcd:procedure 
arg u, v

if v = 0 then 
   return u
else
   return gcd(v, u//v)
 

and here's the output, run on a Windows PC:

c:\> example.rexx 12 8
     5 *-* arg x y
       >>>   "12"
       >>>   "8"
     7 *-* say "The greatest common denominator of" x "and" y "is" gcd(x,y)
       >V>   "12"
       >V>   "8"
    12 *-* gcd:
       >V>   "12"
       >V>   "8"
       *-*  procedure
    13 *-*  arg u, v
       >>>    "12"
       >>>    "8"
    15 *-*  if v = 0 then
       >V>    "8"
    19 *-*  return gcd(v, u//v)
    12 *-*  gcd:
       >V>    "8"
       >V>    "12"
       >V>    "8"
       *-*   procedure
    13 *-*   arg u, v
       >>>     "8"
       >>>     "4"
    15 *-*   if v = 0 then
       >V>     "4"
    19 *-*   return gcd(v, u//v)
    12 *-*   gcd:
       >V>     "4"
       >V>     "8"
       >V>     "4"
       *-*    procedure
    13 *-*    arg u, v
       >>>      "4"
       >>>      "0"
    15 *-*    if v = 0 then
       >V>      "0"
    16 *-*    return u
       >V>      "4"
The greatest common denominator of 12 and 8 is 4
     9 *-* exit 0

From the top, a few new instructions.  First, the trace instruction:  trace r instructs REXX to display the variable assignments as it executes the code.  The arg instruction assigns the command line arguments to variables.  The say instruction writes to the console.  The procedure instruction starts a new procedure or function.  GCD is a function because it has a return statement. Notice that gcd is recursive, and that REXX traces all the recursion for us.

So far, we've talked about Classic Rexx, the ANSI standard REXX language as originally conceived by Cowlishaw.  There are two other versions that are worth exploring.  NetREXX is an alternative to Java, and runs on any system with a JVM.  ooREXX is the open source version of IBM's object-oriented REXX interpreter.  REXX is implemented on many different systems, too.  In addition to Linux, REXX was the system interpreter for OS/2 and VM/CMS, and REXX is also available for Windows, Amiga, and the other IBM mainframe operating systems.

Frederick Brooks, in No Silver Bullet —Essence and Accident in Software Engineering , argues that there is no silver bullet that will magically solve all the problems inherit in software development.  I think he's right:  in the search for the silver bullet, we've spent the past 40 years reinventing the wheel.  So,  I won't claim that REXX is the silver bullet, but Mike Cowlishaw has certainly created the shiniest bullet we're likely to see for a very long time.

Resources

The Rexx Language Association promotes the use of Rexx, sponsors an annual symposium, and the website includes a forum.  You'll find information about NetRexx and OORexx here, too.

Mark Hessling's home page, www.rexx.org, includes all the Rexx projects that he has developed or is maintaining, including Regina Rexx.  In addition to Regina Rexx that I installed, you'll also find goodies like Rexx libraries to connect to databases, develop network programs, and write PDF files.  If you were a fan of the VM/CMS Xedit editor, THE (The Hessling Editor) is Mark Hessling's implementation of the classic CMS editor.

The classic book on Rexx,  The Rexx Language: A Practical Approach to Programming, by Rexx creator Mike Cowlishaw, is a short and very readable introduction to the Rexx language.

The IBM Infocenters for z/VM  and z/OS include a Rexx Reference and a Rexx User's Guide.  Just go to the Infocenter of your choice, and search for "rexx reference" or "rexx users guide".

The Design of the REXX Language by M. F. Cowlishaw.  This academic article appeared in The IBM Systems Journal Vol. 23 No. 4, 1984.

The Man Behind Rexx: z/Journal Visits IBM Fellow Mike Cowlishaw in this 2004 interview.

Alice's Adventures in SQL-NULL-Land

Programming in a relational database environment is usually a very predictable affair.   Everyone understands the concept of rows and columns in tables.  SQL has only four operations to learn (five if you count MERGE).  And thanks to products like Excel, business users know the concept, too, and we all have a common language of tables, rows, and columns.

But, for programmers, there is one thing that remains tricky, and it's a little thing if it was a thing at all, and if we're not careful, we're soon tumbling down Alice's rabbit hole.  That one little thing is the NULL.  And the NULL is at its trickiest when we use it in logic.

Here's a sample Oracle PL/SQL anonymous block.  Before you cut and paste this snippet of code into SQLPLUS or another SQL tool of your choice, answer this question: what will it print?

set serveroutput on 
declare

   the_letter varchar2(1) := null; 

begin

   if the_letter not in ( 'A', 'B', 'C')   then 
        dbms_output.put_line('if true');
   else 
       dbms_output.put_line('else false');
   end if; 

end;

This bit of code cuts to the point right away. Execute it, and the output is

else false

There's no mistake here, "else false" is the code-correct answer. Sure, the value in the_letter may not be an A or a B or a C, and perhaps you expect it to print "if true".  This result may not be what you want or expect, but this code always prints "else false". And in SQL-NUL-Land, that is the correct answer.  Here's another code snippet:

set serveroutput on 
declare

   alice boolean;

begin 

    alice := 'RABBIT' = NULL;
  
   if ( alice ) then 
      dbms_output.put_line('The rabbit is null');
   end if;

   if NOT (alice) then 
      dbms_output.put_line('The rabbit is not null');
   end if;

end;

Execute that, and what do you see? It's like listening to John Cage's 4'33": Nothing. And in that nothing is everything.

We are taught that binary variables have two states: TRUE or FALSE.  And that's true, or not false, except when handling NULLs.  Our binary variable ALICE actually has three states: TRUE, FALSE, or NULL.  As programmers, we need to properly code for the three possible states of a binary variable.

There are a several ways of handling this, here are few suggestions:

• Use the VALUE, COALESCE, or NVL function  to assign a default value to any variable that might be null.
• Leave an extra ELSE on all IF/THEN/ELSE constructs.
• Explicitly test for NULL values. Notice that we explicitly test for NULL using the keyword "is" and not the equal "=" sign.
• Properly initialize all variables when we declare them.

Here's a code snippet showing all three suggestions.

declare
   alice boolean;
begin
   alice := 'RABBIT' = NULL;
 
   if NOT( coalesce(alice,false) ) then
      dbms_output.put_line('The rabbit is not null');
   end if;
       
   if (alice) then 
      dbms_output.put_line('The rabbit is not null');
   elsif NOT(alice) then
      dbms_output.put_line('The rabbit is not null');
   else -- Catch the third state
      dbms_output.put_line('Alice is undefined, neither true nor false');
   end if;

   if alice is null then -- Explicitly test for null
      dbms_output.put_line('Alice is undefined, neither true nor false');
   end if;
      

end;

Yes, the examples are a bit contrived, and of course, you wouldn't really code something like

alice := 'RABBIT' = NULL;

But null values can occur in odd ways. Perhaps a function returning a value behaves badly and returns a null? Or a SQL select doesn't satisfy the where clause? Consider this snippet, based on a real problem:

declare

   the_name varchar2(32);

   is_bedrock_citizen boolean;

begin

   -- Function returning who is using this routine
   the_user := get_citizen_name();  

   -- See if they live in Bedrock
   is_bedrock_citizen := the_user in ('FRED','WILMA','BARNEY','BETTY'); 

   if NOT is_bedrock_citizen then
       raise_application_error(-20001,
                               'You are not a Bedrock citizen.');
   else
       dbms_output.put_line('You are a Bedrock citizen.');
       -- More code below...
    
   end if;

end;

When the get_citizen_name() function works properly and returns someone's name, the program works as its designers intended. The name is compared to the list of Bedrock citizens and assigns a TRUE or FALSE value to the is_bedrock_citizen variable. Foks who live outside of Bedrock get the raise_application_error message and the program stops;  Wilma, Fred, Barney, and Betty get the "You are a citizen" message and the program continues.

But what happens when get_citizen_name() misbehaves and returns a NULL value? Now we have a logical comparison using a NULL value, and the result is neither TRUE nor FALSE, the logical value is NULL. The logical test of NOT is_bedrock_citizen fails and the else block executes. So, whether this user was a citizen or not, if get_citizen_name() misbehaves, then everyone is a citizen!

Good luck, and don't go down this rabbit hole!  Initialize your variables, and be prepared to handle NULLs.

Who are you? The Sequel

In an earlier post, I described some of the information available to us when we deliver a web page from an Oracle database and web server. Before we deliver a page, we can query the remote address of the party asking for the page. For example,

select owa_util.get_cgi_env('REMOTE_ADDR') remote_addr
from dual; 

REMOTE_ADDR
----------------- 
24.91.24.118

1 row selected.
 

But what does that number really mean? Where is it coming from? In this post, we'll take a closer look at the IP address, and we'll get a little more information about the address.

The address by itself may not mean very much to us.  Most of us in the U.S use IPV4 addressing, which looks something like 192.168.1.10  -- 4 numbers separated by 3 periods.  If you're familiar with IP addresses, you'll recognize that an address like 192.168 is a private address, one that is within your firewall.  But there's more information to be gleaned from these addresses than just inside the firewall or outside the firewall.  If we could see the name that the address belongs to, then we'd have a better idea of who owns the address.

Behind every name is an IP address.   If we start a command window or terminal window and ping Google, we see that ping returns the  www.google.com's IP address:

tom@linux-dv8000:~$ ping www.google.com 
PING www.google.com (208.117.233.54) 56(84) bytes of data. 

We want to do the opposite, a reverse name lookup.  This is the same problem you face when you get those mysterious 877- phone calls and you wonder who is calling you. Fortunately, our computer system's name resolver can resolve names into IP address, and the name resolver can do the reverse - return a name from an IP address.

The Oracle database includes a package that interfaces with the computer's name resolver:  utl_inaddr. The utl_inaddr package includes two functions.  GET_HOST_NAME takes an IP address and returns a domain name.  GET_HOST_ADDRESS takes a host name and returns an IP address.  Here are a couple of examples.  First, we call GET_HOST_ADDRESS to resolve a host name into an IP address; then we do the reverse, we call GET_HOST_NAME to find a host name given an IP address:

select utl_inaddr.get_host_address('www.msln.net') from dual;

UTL_INADDR.GET_HOST_ADDRESS('WWW.MSLN.NET')
---------------------------------------------------
169.244.19.130

1 row selected.

select utl_inaddr.get_host_name('169.244.19.130') from dual;

UTL_INADDR.GET_HOST_NAME('169.244.19.130')
---------------------------------------------------
scrane.msln.net

1 row selected.

So, 169.244.19.130 belongs to msln.net.   Point our browser at  http://msln.net, and we get the home page of the Maine School and Library Network.  Point our browser at 169.244.19.130, and we get 404 Not Found.  Knowing the host name tells us much more than just knowing the IP address.

There's just one caveat when using these functions:  depending on your DBAs and your installation, you may not have access to use the network functions.   If you get an error like

ORA-24247: network access denied by access control list (ACL) 

then it's time to visit the DBAs ard request that they add your schema or your user account to the network ACL.

Finally, here's an Oracle PL/SQL function that accepts either a host name or an IP address and returns either an IP address or a hostname.  The function starts by using a regular expression to identify the arguement as either a name or an address, and then the function calls the utl_inaddr to do the translation.  Notice at the end we have an exception block, to make sure we always return something to the caller. 

function resolve_host( host varchar2) return varchar2 is

  v_return    varchar2(256);

begin

  if regexp_like(host,'[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+') then

    -- It's an IP address
    select utl_inaddr.get_host_name(host)
    into v_return
    from dual;

  else

    -- It's a hostname
    select utl_inaddr.get_host_address(host)
    into v_return
    from dual;

  end if;

  return v_return;

exception
    when others then
      return host;

end resolve_host;

One Hundred Years Ago

One hundred years ago, the Berlin Philharmonic conducted by Arthur Nikisch and engineers from Deutsche Grammophone recorded the first movement of Beethoven's Fifth Symphony.  2013 is the 100th anniversary of this historic recording, and DG is celebrating this partnership by issuing a 50 CD collection of recordings made by DG and the orchestra over the last 100 years.

In addition to this first recording by Nikish, the set includes performances with Sir Simon Rattle, Claudio Abbado, Leonard Bernstein, Wilhelm Furtwängler,  Gustavo Dudamel, Karl Böhm, and Herbert Von Karajan conducting the orchestra.

Read more about the DG's Centenary Edition where you can listen to the first movement of  Nikisch's historic recording with the Berlin Philharmonic.  For six minutes, take a step back in time with Arthur Nikisch and the Berlin Philharmonic as they play the first movement of Beethoven's Fifth Symphony.  Just look for the red play button in the audio widget, just after the first paragraph.  Push play and listen... 

Translate and Replace and Replace and Replace and ...

One task many programmers face is examining a string of characters and replacing specific characters with different ones.  Every COBOL programmer is familiar with the INSPECT verb, and if they've really been programming COBOL for a while, they're familiar with the now-obsolete EXAMINE verb, too. We're not going to step that far back; instead we'll look at a couple ways of handling this problem in Oracle's PL/SQL programming language.

Oracle's PL/SQL function library includes several functions that replace single characters or multiple characters with a new characters or string of characters:

• translate translate 
• replace  replace 
• regexp_replace regexp_replace 

Regexp_replace and its siblings regexp_like and regexp_substr are worthy of their own post, so we'll focus on the translate and replace functions.

Translate is the simplest function. Translate performs single characters substitution.  There are three arguments:  the input string we want to examine, a string of characters that we want to replace, and a new string of characters that we want to use as replacements.  Here's a simple example:

select 'abcdefgh' input,
       translate('abcdefgh','bdfh','BDFH') output
from dual;

INPUT       OUTPUT
--------    --------
abcdefgh    aBcDeFgH

We see that every occurrence of b, d, f, or h was replaced by the upper case equivalent.  Every character in the second argument is replaced by its equivalent character in the third argument.

This is a really easy way to do one-to-one character translations.  Sometimes, we want to replace a single character by a string of characters, or perhaps replace a string of characters by a single character.  The replace function handles this nicely.   Here's an example; anyone needing to translate multi-byte characters to a single-byte character is familiar with this problem:

select '10 ≥ 5' input,
       replace('10 ≥ 5','≥ ','>=') output
from dual;

INPUT       OUTPUT
--------    --------
10  ≥  5     10  >= 5

This is pretty handy.  We translated the single character greater than or equal to symbol into a two-character string that we can represent in a 7-bit ASCII codeset.

The replace function can convert strings into single characters, too.  Here's another example:

select '(c)2013' input, 
       replace('(c)2013','(c)','©') output 
from dual;

INPUT       OUTPUT
--------    --------
(c)2013     ©2013 

This is pretty handy, too.  We can take a string encoded in 7-bit ASCII and replace character strings like (c) and >= with their single-character equivalents in a multi-byte character set.  

 There's just one thing missing:  Translate can change many different characters for us in one call, but it does not handle character string replacements.  Replace can change characters strings, but only one string at a time.  If we want to change both the © and the  ≥, we need to do something like this:

select '© ≥' input,
       replace(replace('© ≥','©','(c)),'≥','>=') output
from dual;

INPUT    OUTPUT
-----    ------
© ≥      (C) >=

This works fine for a couple of translations, but for more than a few translations, this is pretty cumbersome. Why not let the computer do it for us? We'll define a new function, called MREPLACE, that will handle multiple replace strings. The first argument will be the string we want to examine. The following arguments are pairs of strings: first the old string, then the new string. Here's an example of MREPLACE in use:

select '© ≥' input,
       mreplace('© ≥',
                '©','(c)',
                '≥','>=') output
from dual;

INPUT OUTPUT                                                                     
----- ------
© ≥  (c) >= 

Perfect! Now, with just one function call, we have an easy way to replace multi-character or single-character strings with multi-character or single-character strings in one function call. Here's the source:

create or replace
function mreplace (
    p_string in varchar2, 
    p_from1 in varchar2 default null, p_to1 in varchar2 default null,
    p_from2 in varchar2 default null, p_to2 in varchar2 default null,
    p_from3 in varchar2 default null, p_to3 in varchar2 default null,
    p_from4 in varchar2 default null, p_to4 in varchar2 default null,
    p_from5 in varchar2 default null, p_to5 in varchar2 default null,
    p_from6 in varchar2 default null, p_to6 in varchar2 default null,
    p_from7 in varchar2 default null, p_to7 in varchar2 default null,
    p_from8 in varchar2 default null, p_to8 in varchar2 default null 
                  ) return varchar2 as

begin 

  if ( p_from1 is null ) then return p_string;
  else
     return mreplace(replace(p_string,p_from1, p_to1),
                     p_from2 , p_to2 ,
                     p_from3 , p_to3 ,
                     p_from4 , p_to4 ,
                     p_from5 , p_to5 ,
                     p_from6 , p_to6 ,
                     p_from7 , p_to7 ,
                     p_from8 , p_to8 );
  end if;           

end mreplace;

How does it work? MREPLACE uses recursion, repeatedly calling itself. MREPLACE keeps calling itself until the p_from1 argument is null. The test to stop the recursion is important if you don't want your DBAs and sysadmins darkening your office door! Also, notice that MREPLACE accepts one string to examine and eight from/to pairs. When we invoke it recursively, we call it with one string to examine that includes our string and the first from/to pair:

          replace(p_string, p_from1, p_to1)

but only seven from/to pairs:

                     p_from2 , p_to2 ,
                     p_from3 , p_to3 ,
                     p_from4 , p_to4 ,
                     p_from5 , p_to5 ,
                     p_from6 , p_to6 ,
                     p_from7 , p_to7 ,
                     p_from8 , p_to8 )

So, each time MREPLACE calls itself, it shifts all the arguments to the left. The eigth from/to pair are not specified and defaults to null. When p_from1 is null, then all the arguments are used, all the substitutions are completed, and MREPLACE returns the answer.

For the purposes of illustration, MREPLACE accepts up to 8 from and to pairs, but in practice we would set it to accept more, maybe up to 32 from/to pairs, or 64 from/to pairs or even more if required.  Remember, MREPLACE stops the recursion when it finds the first null p_from1 argument, so you should define it to accept more from/to substitution pairs than you expect to use.

Happy computing!

How to start your car

Last weekend, I volunteered to drive classic cars across the auction block at the Owls Head Transportation Museum's 36 Annual Auto Auction.  Seeing so many classic cars in one venue is a real treat.  Some of these old cars, like the Jaguar E-type displayed at the Museum of Modern Art, are great works of art.  And others, well, not so much...

And the Museum is a real treasure, too.  Almost every old car in the collection drives, and almost every old plane   flies.  During weekend events, the museum's  volunteers will drive the Ford Model Ts and take visitors for rides, and the museum's pilots will fly aircraft.  Watching World War I era aircraft fly around the Museum while modern aircraft take off and land at nearby Knox County airport is a lesson in aviation history.

So, how do you start your car?   This is where the fun begins.  Most of us grew up starting cars with three-position ignition switches:   Off, Run, Start.  Lately, a few upscale manufacturers are installing a separate Start button, often in conjunction with a keyless entry system.   It's an old twist to a new problem:  Austin-Healey roadsters from the early 50's through the early 60's used the same arrangement:  There's a two-position ignition switch and a separate starting button labelled "S".

Newer (say, post 1950) cars use a lightweight switch that operates a heavy-duty relay.   The large current required to start a engine mandates the use of a heavy-duty switch between the battery and the starter motor. So, cars with small Start button on the dash, or cars that start using the ignition switch, all have relays.

On older cars of the 20s, 30s, and 40s, there is no relay, just a heavy-duty switch, usually located on the floor.  The switch springs are so strong that it requires foot pressure to operate them comfortably.  Starting these cars usually requires a little tap-dance to find the button, but once you've found it, you're all set.

So, sitting in a 1952 Buick Special, I was puzzled because after looking for the usual suspects, I couldn't find the starter switch.

"Push the accelerator to the floor" someone wiser than me said.

"Huh?"

"Push the accelerator to the floor.  That will start it."

So I did, and the engine turned over and fired up.  Buick engineers craftily linked the starter switch to the accelerator pedal.  I don't know why; perhaps to make it easier to restart if you stalled?

The really old stuff, pre-1920, have hand-cranks.  Hand-cranking is handy when the battery is weak, you need more exercise, or you just want to impress people.  It's also a great way to injury yourself if an engine backfires:  your arm and shoulder strength vs. a twenty-horsepower engine.  Do the math:  you're no match for a cantankerous engine.

 Finally, you drive off.  More fun now, especially with large American sedans of the 50s and 60s.  It's really amazing how much you have to turn the steering wheel before the car actually begins to change direction!  And the power brakes that are so sensitive you could stop the car with your little finger on the pedal.  But, most of these cars drive pretty much like a modern car.  You will see three-speed manual transmissions, or reverse hidden in odd places, or push-button automatics, or manual transmissions with shifters on the steering column.  If the car is English, then the driver sits in the right-hand seat.  But, at least the clutch is on the left, the brake pedal in the middle, and the accelerator on the right.

Except for the Ford Model T:  transmission pedal is on the left, reverse pedal is in the middle, and the brake pedal is on the right.  The accelerator (better called the throttle control) is the right paddle on the steering column.  Ford started building Model Ts around 1908 when there was no standard layout, and Ford built 15 million of them, so the T really makes a standard.  In fact, 15 million cars makes the Model T the second-most popular car of all time.  Only VW made more cars of one model:  20 million Beetles make the Bug the most popular car ever.

Happy motoring!

Oracle Apex Tabular Forms

The Tabular Forms feature of Oracle's Apex web development tool allows users to update multiple rows in a table using only declarative programming.  All of the Javascript and PL/SQL is done behind the scenes, making tabular forms a relatively easy to use and powerful tool.

 After logging into your Apex environment (Oracle offers a free demo site), and navigating to your page, it's simple to create a tabular form:  Create Region -> Form -> Tabular Form . Then,   the tabular form wizard will guide you  through the process of selecting the table and columns, choosing a primary key, choosing columns to update, and adding buttons and branching. 

Run the page, and you find that the tabular form lets you scroll thru every row in the table.  Pretty cool, but maybe you only want a subset of the rows?   So your page has an ID field, and you modify the tabular form with the WHERE clause

select rowid,            /* Apex generates the select */
       table_key, 
       table_col1, 
       table_col2, 
       table_colx  
 where table_key = :id   /* You add the where clause */

Run the page again, and nothing shows up.  No rows, no data, nothing.  If the ID field of your page is  blank, that's the correct response.  Nothing in the table satisfies the where clause, so nothing is displayed.  If you enter an ID value that's not in the table, again, nothing is displayed.  If you enter an ID value that is in the table, only the matching rows are displayed.   Perfect... almost.

There is an Add Row button to add new rows, and using the Add Row button is the way the tabular form is designed to work.  But in some cases, we would like an empty row of cells to display if there isn't any data.  The end-user may find it odd that nothing is displayed on the screen, and the blank row gives them a place to start.

Depending on what version of Apex you're using, you may be able to modify the tabular query by using a UNION to select a row of null values from dual.  But this doesn't work with every release of Apex, so we'll take a different approach.

If we examine the Add Row button, we find that it fires a bit of Javascript:

javascript:apex.widget.tabular.addRow(); 

So, we will execute the JavaScript addRow function when the page loads by using a dynamic action.  Here's how to do it: create a new dynamic action and name it Add_1_Row.  Click Next.  The Event should be Page Load,   don't choose a Condition, and click Next.  The Action should be Execute JavaScript Code,   the code should be javascript:apex.widget.tabular.addRow();  , and click Next.  Then click Create Dynamic Action.

Now, your end-user will always have a blank row to enter data.   And if they need another blank row, the Add Row button is ready for them to use.  Best of all, you don't need to worry about how to process this row of blanks because the Apex multi-row processes auto-magically handle all the inserting and updating for you. Now it's perfect!

For more information about how APEX handles commits in tabular forms, see my post discussing commits in APEX.

D'où êtes-vous?

D'où êtes-vous?

I was stumped.  We were finishing breakfast at La Brioche Lyonnais in the Latin Quarter of Montreal, QC.  I managed, thanks to a  friendly and very patient waitress, to order my breakfast in French.  When she asked what I wanted on my sandwich, and what salad dressing I wanted, I was able to answer her questions. I explained to my wife how to ask for some more water. I was actually conversing passably in French.

But now I was stumped.  After 4 years of French in high school and college, I should have been able to answer her question. "Nous habitons a Nouveau Hampshire" would have been a great response. But it's been a long time since I studied French, and processing language at the speed of normal conversion is much trickier that reading it or slowly replying to questions. 

This is one of those experiences that made me think about this blog's topic: things we've learned but lost over time.  

The experience also reminded me that we loose knowledge over generations.  My French great-grandmother would easily have answered the question that stumped me.

Another example: friends who are taking up permaculture are re-learning skills that their grandparents and great-grandparents took for granted:  how to raise chickens, how to keep bees, how to grow vegetables and fend off the deer,  how to run the tractor, how to build fences and farm buildings.

And another: a local auto repair shop that specializes in vintage cars was working on a  1966 Ford Mustang with dealer tags.  The young mechanics at the Ford dealership, all trained to work on modern computer-controlled cars, couldn't figure out why the Mustang wouldn't start.  The problem: bad points, a six-dollar tune-up part and the first thing most old-school mechanics would check. 

Ending on an upbeat note:  if you visit Montreal, I  recommend  La Brioche Lyonnaise.  The smoked salmon was great, and the crêpes were everything you'd want a crêpe to be. Bon appétit!
  
btw... on this side of the border, Cafe Papillon in Rehobeth Beach, Delaware is a good bet.

Get Regular (Expressions)

There's been a lot of debate about Unix since it was first introduced by Bell Labs.  Computer geeks engaged in their own Ford vs. Chevy debate: Unix vs. VMS, multiple Unix minicomputers vs.an IBM mainframe, System V vs. BSD, and so on.  For every proponent of Unix's case-sensitive commands and file system, there were an equal number of opponents who just found it an annoying nuisance. Ah, the Holy Wars of Computerdom.  It was fun stuff.

But whether your roots are in Unix, or VMS, or MVS, or any of the PC/Mac operating systems, I think most of us have found regular expressions to be one of the most useful things to come out of the Unix/Bell Labs world.

We all use simple pattern matching of one form or another.  The MS-DOS DIR command uses an asterisk (*) to match any number of characters and a question mark (?) to match a single character.  The SQL LIKE operator is another example.  The percent sign (%) matches any number of characters, and the underscore (_) matched a single character.  That's really great stuff, and it's good enough 75% of the time.

But there are some problems that simple pattern matching won't solve.  Suppose I want to select rows of data from a table that had valid email_addresses.  My query might look like this:

select email_address
from email_address_table
where email_address is not null;

Well, that query gets all rows that have data in email_address column.  But are the data any good?  Who knows? Let's try the LIKE operator:

select email_address
from email_address_table
where email_address like '%@%';

That's a bit better.  At least we'll see data that looks like username@hostname.  But a regular expression can do even better.

select email_address
from email_address_table
where regexp_like( email_address, 
           '^[a-zA-Z0-9\._-]+@[a-zA-Z0-9\.]+\.[a-zA-Z]+$');

OK, so the example with LIKE is simpler.  But, after we pull all the data using LIKE, we need to do some post-processing ( in C, C++, Java, PL/SQL) to get the data we really wanted.  This regular expression accepts that data that

1. Has a username before the "@" symbol.  The username can be any combination of letters, numbers, periods, underscore, or dash.
2. Has an @ symbol separating the username from the hostname.
3. Has a hostname consisting of letters, numbers, and periods, and ends with a top-level domain consisting of just letters.

This is a really  big improvement.  Now we have a tool that works for 99% of the data.

The magic is in the regular expression metacharacters.   Metacharacters are part of the regular expression grammar; this list is just a start to describe the example:

1. ^ -- matches the beginning of the string
2. .  -- period matches any character
3. [] -- describes a set of characters to match.  In our example, the set of characters includes the letters from a to z and A to Z, period, underscore, and dash
4. + -- count operator, specifies that the string must have one or more of the bracketed characters.
5. \ -- escape character. If we want to match on a metacharacter, we must escape it.  The period is a metacharacter, and if we want period to match a period and nothing else, we escape it.
6. $ -- matches the end of the string.

 And anything that's not a metacharacter matches itself.  For example, the "@" symbol in our expression is not a metacharacter, so it only matches itself.

Let's look at some sample email addresses.  One of these is a good email address, the others are not:

Pebbles 
Wilma_Flintstone@Bedrock
Fred_Flinstone@SlateConstruction.com

Now, lets see how the three queries do.

The first query, not null, accepts all the data. 'Pebbles' clearly does not have an email address, and Wilma's address is not complete.

The second query, using LIKE, does not accept Pebbles.  Wilma's address is still a problem.

The third query, using a regular expression, accepts only Fred's address at SlateConstruction.com.

Here's a test page at Email and Phone Test for phone numbers and email addresses.  The test page splits the phone number into an area code and exchange, and the page splits the email address into an username and hostname. How the page does that is another day's topic.

This is a topic that's really worth a deep dive.  Mastering Regular Expressions is really deep dive into the topic.  Another tip:  try using a regular expression whenever you get a chance.  It's a bit like playing the piano:  the more you try regular expressions, the better you'll understand them.

Unfortunately, there are several versions and many implementations of regular expressions, and some implementations are better than others.  The GNU/Linux egrep command  and the PERL language have excellent regular expression implementations. Both are good, but they differ, so take time to read the documentation that comes with your favorite editor or text processor.