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.

5 Responses to Handling NULL Values in VFP

  • Anonymous says:

    Hi all, long story short. I grew tired of asking SQL team to supply me views with a default value instead of null, so i wrote this function to cater for nulls….

    *——————————————————–

    FUNCTION Replace_All_Nulls(paraTable)

    *—– Adrian Cupido: 2004-10-29

    *—– As this procedures name says it

    IF Parameters() >= 1

    IF Type(‘paraTable’) = ‘C’

    IF Used(paraTable)

    SELECT (paraTable)

    nFieldCnt = fCount()

    FOR nFieldCounter = 1 To nFieldCnt Step 1

    cField = Type(Field(nFieldCounter))

    cFill = ”

    DO Case

    CASE cField $ ‘C M G’

    cFill = "”"

    CASE cField $ ‘N F I B Y’

    cFill = ’0′

    CASE cField $ ‘D T’

    cFill = ‘Date()’

    CASE cField = ‘L’

    cFill = ‘.F.’

    OtherWise

    Loop

    ENDCASE

    cSql = "Replace All " + Field(nFieldCounter) + ‘ With ‘ + cFill + ‘ For IsNull(‘ + Field(nFieldCounter) + ‘)’

    &cSql

    NEXT nFieldCounter

    ELSE

    *- leave Alone

    ENDI

    ELSE

    *- Leave Alone

    ENDI

    ELSE

    *- Leave Alone

    ENDI

    RETURN

  • Hi

    This is a nice little routine, but the problem is that it assumes you only ever retrieving a single, or at most, a very few records. The overhead to process even a couple of hundred records like this can be quite significant – especially if the table (or more likely, the VIEW) has a lot of columns.

    Generally the better solution is to avoid the issue by using the SQL ISNULL() function in your queries to suppress the NULLs at source.

    So instead of:

    SELECT lastname FROM table

    you use

    SELECT ISNULL( lastname, "" ) AS lastname FROM table

    This way you at least get something sensible directly in the result set and don’t have to resort to a post process.

  • Thanks

    The Spanish version of this article is available at (La versión en Español de este artículo está disponible en:)

    http://www.portalfox.com/modules.php?op=modload&name=Sections&file=index&req=viewarticle&artid=95

    Regards / Saludos,

    Ana

    http://www.amby.net

    http://www.PortalFox.com

  • Jacobus Terhorst says:
    1. This is much faster:

      function ReplaceNulls(cTable)
         *– cTable – alias()
         *– Replaces all of the NULL values in a table with the appropriate default value
         *– Replaces all {^1899/31/12} with {}

         m.cTable = evl(m.cTable, alias())

         local cCommand, a[1], i
         m.cCommand = “”
         
         for i = 1 to afields(a)
             m.cName     = a[m.i, 1]
             m.cType     = a[m.i, 2]
             m.lNullable = a[m.i, 5]

             if m.lNullable
                 do case
                     case m.cType $ “CM”
                         m.cCommand = m.cCommand + textmerge(“,<<m.cName>> with nvl(<<m.cName>>, ”)”)

                     case m.cType $ “NFIBY”
                         m.cCommand = m.cCommand + textmerge(“,<<m.cName>> with nvl(<<m.cName>>, 0)”)
             
                     case m.cType $ “DT”
                         m.cCommand = m.cCommand + textmerge(“,<<m.cName>> with nvl(<<m.cName>>, {})”)
                         
                     case m.cType $ “L”
                         m.cCommand = m.cCommand + textmerge(“,<<m.cName>> with nvl(<<m.cName>>, .f.)”)
                         
                 endcase        
             endif
             
             if m.cType $ “DT”
                 m.cCommand = m.cCommand + textmerge(“,<<m.cName>> with iif(<<m.cName>> = {^1899-12-31}, {}, <<m.cName>>)”)
             endif            
         endfor
         
         if len(m.cCommand) = 0
             return
         endif 

         m.cCommand = textmerge(“replace in <<m.cTable>> all “) + substr(m.cCommand, 2)

         Application.DoCmd(m.cCommand)
         go top in (m.cTable)
      endfunc

    Faster than what? Why on earth would you ever use this? In my opinion this is a very poor solution – apart from anything else the null date you are using is specific and incorrect if the source is SQL Server (it uses 1/1/1900 as its base date!). Second, you are doing multiple REPLACE ALL with complex FOR conditions – how would this run on a result set with 70 or 80 columns and a few thousand rows? The word “fast” does not spring to mind!

    I post this comment because I post all comments, but it is not a good thing to do and I definitely advise against it. – Andy

  • Jacobus Terhorst says:

    It is many  times faster than a scan loop. I uses only one replace all for all columns. You are right it should use 1/1/1900 as zero date. It so happens that the client has date as their “zero” date. It is very fast. 100,000 rows with 6 columns, 5 of then nullable, with 50% of the values nulls only  takes .2 seconds on my machine vs 7 seconds using a scan/foreach column loop.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>