O$: Null Fields

[Bastiaan Olij]

Hi Christine,

As some of the other replies already mentioned, NULL values are a fact
of life in SQL and we're just spoiled to some extent in Omnis. NULL
values have a very very good purpose and can actually be very beneficial
to the integrity of your database. We Omnis developers are unblessed by
the limits of the Omnis datafile resulting in that we are blissfully
ignorant to the positive sides of NULL, as these hardly exist in a
datafile.

What I've often found is that columns are set to allow NULL by default
without people thinking about it, and off course the insert as empty/0
is a handy feature to have in that situation.

Imho you as a developer should make a choice to when to allow NULL and
when not. Make sure that columns that make sense to have a NULL in them
(date field, foreign keys, etc) are the only columns that allow NULL.
Going through all your schema classes and setting $nonull to true on any
column where NULL makes no sense will give you a hard time for 2 or
three days and saves you hours and hours of work for years to come.

In your datafile -> SQL conversion process you could replace any
unwanted NULL to '' or 0 making sure your data is clean. In Omnis you
simply override your $insert and $update like this:

-------
Method CleanNulls
--
Set reference lvClassRef to $schemas.[$cinst.$sqlclassname]
For lvColNo from 1 to $cinst.$colcount step 1
  Calculate lvColName as $cinst.$cols.[lvColNo].$name
  Set reference lvColRef to lvClassRef.$objs.[lvColName]
  ;  Note I use len, not isnull, because len also captures empty dates
and strings.
  If lvColRef.$nonull&len($cinst.C[lvColNo])=0     ;; Do not allow NULL
but it is empty?
    Switch lvColRef.$coltype
      Case kDate
        Breakpoint {Empty/NULL date not allowed!!}
      Case kNumber
        Calculate $cinst.C[lvColNo] as 0     ;; NULL -> 0
      Default
        Calculate $cinst.C[lvColNo] as ''     ;; Empty/NULL -> ''
    End Switch
  End If
End For

Method $insert
--
do method CleanNulls

Quit method $default.$insert()
-------

This way you ensure you have consistent data and you do not have to
worry about NULLs. You could make CleanNulls a public method of your
table class and also call it after you define a row variable or did a
$add on a list. This will give minimum overhead in starting clear data
entry.

I do want to point out yet again as I've done in several other posts
(directing this to everyone on the list), all though Omnis allows us to
be careless with defining columns allowing NULL, the consequences are
often overlooked. You may well find that properly using and reacting to
NULL values will suddenly solve those illusive bugs that your customers
have reported to you but that you never where able to reproduce.

A simple example:
----
if MyColumn = ''
  ok message You have to enter a value!!!
  quit method
end if
;; do some very important code that will fail if MyColumns is empty
----
Will fail horridly if MyColumn is NULL

----
if MyColumn = '' | isnull(MyColumn)
----
May logically seem the answer to this, but trust me, it will fail just
as horridly.

Greetz,

Bas

Christine Penner wrote:
> Hi Everyone,
>
> We are in the process of converting our software to SQL. In the old
> DML code we never had to deal with nulls much. Now I need some advice
> how to deal with them in some specific problems that have come up. We
> decided in most cases we don't want the overhead of always clearing
> nulls after finding data.
>
> When we have one field we are looking at there is no problem. When we
> have more than one it can cause problems. Here is an example. Any
> suggestions would be appreciated.
>
> If
> (pOldRow.FF_HIRED<>pNewRow.FF_HIRED)|(pOldRow.FF_PREVIOUS_MONTHS<>pNewRow.FF_PREVIOUS_MONTHS)
>
> In this case we compare old and new values to see if anything has
> changed. Typically in this situation everything will be null except
> pNewRow.FF_HIRED. It still fails though because of the nulls. We do
> this type of thing all over the program. It would be good to not have
> to write a pile of code each time checking for nulls.
>
> Christine
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
>


-- 
Kindest Regards,

Bastiaan Olij
e-mail/MSN: bastiaan at basenlily.nl
web: http://www.basenlily.nl
Skype: Mux213
http://www.linkedin.com/in/bastiaanolij