Development Team Blog

When a column contains a NULL value, its content is undefined. if you are using Visual DataFlex commands/methods, the Connectivity Kit has ways (e.g. DF_FIELD_IS_NULL) to deal with return values and take care of NULL values for you.

Now, if you are using Embedded SQL (ESQL), you have direct access to what is stored in the SQL backend and will need to take care of NULL values yourself, otherwise you might see gibberish when the value returned is NULL -- and NULLs can be anything! In that case, your queries will need to treat the return values taking into account NULL values as well.

How to Handle NULL Values
When using ESQL, you may choose to handle NULL values either in the query you are submitting or in your program.

1. Handling NULL values in the query you are submitting
In this case, the query itself is ready for any kind of value it retrieves, including NULL values.

If you need help rewriting your query, you can find various examples and articles online with suggestions on what to do. A couple of interesting ones I found that would be worth reading are:

• Handling NULL values in SQL Server 2005

https://www.techrepublic.com/article...l-server-2005/

• Strategies for approaching NULL values with SQL Server

http://www.akadia.com/services/dealing_with_null_values.html


2. Handling NULL values in your program
You may choose to submit a simple query and deal with the values afterwards in Visual DataFlex. You could do that by using a routine something like:

Code:

Get SQLColumnValue of hoStatement 9 to sVal 
// ignore error 54
Send Ignore_Error of Error_Object_Id 54
Move False to Err 
// move number(svalue)
Move (Number(sVal)) to nConvertedVal
If (Err) Begin   // check Err
   Send Info_Box ("Value failed to convert to number:" * sval) "Handle it"
   Move 0.00 to ArbkRecords[iCount].nARB_ARBTID
End
Else Begin
   Move sVal to ArbkRecords[iCount].nARB_ARBTID 
End

But, Wait, There is More!
You need to be careful about the solution you create. In the above example, the Err flag will NOT be set when using SQLColumnValue; it is when you do a “Move to a variable” that it will be set if the column contains “invalid” values. But that may not be accurate, though.

Since NULL may contain anything, it may contain values that appear to be OK, when in reality they shouldn’t mean anything when read into a variable. For example, it could contain 5,000 when NULL and 5,000 will not trigger an error when moved into a numeric variable… In DataFlex you would end up with a 5,000 value instead of 0 for a column that was NULL.

Treat the NULLs at the Source to Avoid Misinterpreting Values
So, if you are using ESQL and accessing SQL data directly, the best solution is to control NULL values at the ESQL level and modify your query. You could, for example, modify your query to use the function IsNull for the columns that are NULLable and you need to interpret NULL as 0 – e.g. isnull([MY_NUMERIC_ID],0).

You can also create a hybrid solution, like the function below (shared by Nils G. Svedmyr from RDC Tools International - nils.svedmyr@rdctools.com) that uses the DF_FIELD_NULL_ALLOWED attribute on queries that include an * (i.e. retrieve all columns). In this example, the function automatically inserts the appropriate IsNull for nullable columns:

Code:

    // Takes a file_number and a string with an ESQL selection string as params.
    // Returns a selection string for ESQL with inserted IsNull for each NULLable column,
    // thus resulting in queries that will return proper values and NO NULL values!
    //
    //  Example: The passed parameters: Arbk.File_Number and "SELECT * FROM ARBK ORDER BY RECNUM"
    //    will return a string with the select statements for each field/column that exists for the
    //    table - BUT for each column that can contain a NULL it will insert a "ISNULL" and a 0 or "",
    //    depending on the field/column type. This will result in that fetched NULL values will be
    //    changed into either a "" (empty string) or a "0" (zero), depending on field type.
    Function FormatESQLAllColumnsNULLHandling Integer iFile String sQuery Returns String
        Integer iFields iCol iPos
        Integer iFieldType iPoints
        Boolean bNull
        String sRetval sFieldName
        
        Move (Uppercase(sQuery)) to sQuery                         
        // First check that there is a "*" in the selection string that denotes
        // that all columns should be selected - otherwise the function has no meaning.
        Move (Pos("*", sQuery)) to iPos     
        // If we found no "*" - just return the string as it was.
        If (iPos <=0) Function_Return sQuery
        Get_Attribute DF_FILE_NUMBER_FIELDS of iFile to iFields
        For iCol from 1 to iFields
            Get_Attribute DF_FIELD_TYPE of iFile iCol to iFieldType
            If (iFields <> DF_OVERLAP) Begin // Skip overlap fields.
                Get_Attribute DF_FIELD_NULL_ALLOWED of iFile iCol to bNull
                Get_Attribute DF_FIELD_NAME         of iFile iCol to sFieldName
                If (bNull = False) Begin
                    Move (Append(sRetval, (",[" + sFieldName + "]"))) to sRetval
                End                          
                
                // Can this column contain NULL values?
                If (bNull = True) Begin
                    If (iFieldType = DF_BCD or iFieldType = DF_DATE) Begin
                        Move (Append(sRetval, (",ISNULL([" + sFieldName + "],0)"))) to sRetval
                    End
                    
                    Else If (iFieldType = DF_ASCII or iFieldType = DF_TEXT) Begin
                        Move (Append(sRetval, (",ISNULL([" + sFieldName + "],'')"))) to sRetval
                    End
                    
                End 
//                Showln "FieldName = " sFieldName " FieldType = " iFieldType " Null = " bNull
            End
        Loop
        // Remove the very first "," in the query:
        Move (Replace(",", sRetval, "")) to sRetval
               
        Move (Replace("*", sQuery, sRetval)) to sRetval       
        Function_Return sRetval
    End_Function

No matter which type of solution you choose or how you implement it, NULL values need to be handled appropriately to avoid "bad" values to be computed or displayed when running your application.