Foxhound is the better* Database Monitor for SQL Anywhere. *better: More thorough, more relevant, more effective. ...more Alerts, more All Clears, more details, more control in your hands.

The Tip With No Name

Here's a Pop Quiz: Why doesn't this loop execute at all, not even once?

   li_idx = 1
   ii_sheet_ct = 3
   SetNull ( lw_sheet )
   do while li_idx <= li_sheet_ct and not IsValid ( lw_sheet )
      if li_idx <= li_sheet_ct then
         if IsValid ( iw_sheet [ li_idx ] ) then
            lw_sheet = iw_sheet [ li_idx ]
         end if
      end if
      li_idx++
   loop

And the answer? PowerScript supports 3-valued logic: true, false and NULL.

The IsValid() function doesn't always return true or false but yields NULL if the argument is NULL. This is also the case with logical operators like and, or and not: if any of the operands are NULL then so is the result.

Here's how the do while expression is evaluated:

   li_idx <= li_sheet_ct and not IsValid ( lw_sheet )
   ------    -----------                   --------
     1    <=      3      and not IsValid (   NULL   )
     --------------              --------------------
           true          and not        NULL
                             ---------------
           true          and      NULL
           ---------------------------
                      NULL

Fans of 3-valued logic say that NULL is good, that NULL represents the "unknown" or "no value" case. They say it's better than using some special value to represent "unknown" and that we should all become good friends with the IsNull() function (which, curiously, is just about the only 2-value logic function PowerBuilder offers).

In the real world, however, NULLs cause endless grief for both programmers and users. Consider the case of an employee table where five employees are in department 100, thirty are in other departments and one employee, the president, has NULL in the department column.

These two SQL statments yield the numbers 5 and 30, giving the impression the company has only 35 employees:

   select count(*) from employee where department = 110
   select count(*) from employee where department <> 110

Banish NULLs!

The solution is simple: Banish NULLs from your applications. Declare all database columns as NOT NULL. Use special values like zero and the empty string "" to represent empty fields. For those columns where your really need to specify "unknown" (very rare in the real world) use a separate column like "Department Known: Y/N" to make it absolutely clear what's going on.

For every rule, of course, there are exceptions. The most severe case is the optional foreign key value: If the column is filled in, it must be a valid primary key value in the other table, but it doesn't need to be filled in. The alternatives are to declare it as a NULLable foreign key or to define a special "(None)" row in the other table. The latter solution is very attractive when defining DropDownDataWindows that allow users to change the column from some known value to "none of the above".

Another difficulty is presented by date and datetime columns: There is no good "zero" or "empty" value for a date. One solution is to use values far in the past or future, like 1901-01-01 for date_hired and 4712-12-31 for termination_date, and tell everyone what they mean: they're simply default values. If you think that's too dangerous then don't make it worse by declaring them NULLable, add columns like "Date Hired Known: Y/N" instead.