Handling NULL Values in VFP

Handling NULL Values in VFP

I was reminded the other day of some of the issues that can arise in code when an unexpected null value is encountered. This led me back to re-visit my understanding of NULL value handling in VFP and that is what this blog is about. Let us begin by considering the various possible states in which a VFP value (whether it is a field in table, or a variable) can exist?  In Visual FoxPro there are actually three conditions;

·         the value can be equal to something (.T.)

·         the value can be not equal to something (.F.)

·         the value can be in a state where its value cannot be determined (NULL). 

The first two are not a problem and we all deal with these scenarios daily in code. It is the third that can cause us problems unless we specifically plan for it. I often find it helpful, when thinking about NULL values to think of the NULL as meaning “I don’t know”. This helps to make sense of the behavior of nulls when they are encountered.

The first thing to remember about nulls is that the answer to any comparison involving a NULL value is always NULL. Consider the following code:

luVal = NULL

? luVal > 0

? luVal = "A CHARACTER STRING"

? luVal <= DATE()

? luVal = .T.

The value returned in every case is actually NULL . In other words when VFP is asked how a NULL ( = I don’t know) relates to some other value, the answer is always NULL ( = I don’t know ). Now you may be thinking at this point that this is a silly example, because you can always check the type of a variable (or field) using the TYPE(), or VARTYPE() functions.

Unfortunately TYPE() is definitely not recommended for checking for nulls because, although it will return a type of “L” for a variable that has been initialized as NULL like this:

luVal = NULL

? TYPE("luVal")  && Returns ‘L’

it will return the original data type if the variable originally contained some non-null value and has since acquired a NULL, like this:

lnTotal = 100

luVal = 10

? TYPE( ‘luVal’ )            && Type = “N”

luVal = IIF( luVal > 20, luVal, NULL )

? TYPE( ‘luVal’ )            && Value = NULL but Type = “N”

lnTotal = lnTotal + luVal       && lnTotal = NULL - not even 100!!!

Why is this so bad?  Consider what would happen if you were to be using code which accumulated a values inside a loop (e.g. a SCAN) and one record contained a NULL  Not only would your total so far be lost, but you would not even get the value back because as soon as you reached the null record the value in the accumulator would be set to NULL and all subsequent additions would simply end up as NULL  You would not get an error, but you certainly would not get the result you desired.

VARTYPE() is definitely better behaved in this respect. By default it returns a type of “X” if the tested value contains NULL, although, by passing the second parameter as .T. you can get the original data type (mimics the behavior of TYPE() in other words).

In practice the best way to handle nulls is to test for them explicitly using either the ISNULL() function (that returns .T. if the tested value is null) or to wrap expressions that may contain nulls inside an NVL() so the any null is replaced with a more easily handled value. (In fact it was the omission of an NVL() that caused me grief this week and occasioned this blog).

NVL() is simply a function that substitutes a specified value when a NULL is encountered, like this:

luVal = NULL

? NVL( luVal, 0 )    && Returns 0, not NULL!

Fortunately you CAN use functions like SUM() and the CALCULATE functions, or an SQL SUM() even when NULL values are present.  Visual FoxPro is intelligent enough to simply ignore such values - and indeed this is one of positive benefits of NULL support when calculating averages and counting records - NULLS are ignored and do not affect the results.

Having said all that, what was my problem? I forgot that when you are concatenating trimmed character strings, one of which contains a NULL, the result is NULL. The actual scenario that bit me was caused by a call to a SQL Server stored procedure that returned a value in a cursor. That value was supposed to be a space-padded character string containing up to six characters. This was to be trimmed, and used to generate a file name by concatenating it with the logged-in user id. Something like this:

lcNextSeq = ALLTRIM( cur_result.cNextSeq )  && This field was NULL

lcFileName = lcUserID + lcNextSeq

The problem arose when the stored procedure returned NULL because a previous insert operation had failed and I had not wrapped the result in an NVL(). This did not generate an error, but the result was NULL. Now this particular one is, I think, a bug (though it has been in the product since VFP 6.0 at least).  The following snippet shows why:

lcVal = ALLTRIM(NULL)

? lcVal              && .NULL.

luVal = NULL

? luVal              && .NULL.

? "Test" + lcVal  && .NULL.

? "Test" + luVal  && OPERATOR/OPERAND MISMATCH!!!!

After all, if a mismatch arises when a character string is concatenated with one containing a NULL, then simply applying an ALLTRIM() function should not allow the operation to proceed without error.

The moral of the story is, of course, to always plan for NULLs!

Incidentally you may be thinking that since you only use VFP data and you don’t allow nulls in your tables anyway, that this sort of thing is not going to happen. You will be right, so long as you don’t ever use an outer join in an SQL Query. Remember that in such a query a NULL value is returned in the result set if there is no matching record – irrespective of whether your tables allow nulls or not.

Published Sunday, December 04, 2005 7:00 AM by andykr