Handling Nulls with GeneXus

The goal of this document is to explain how to work with -or avoid- null values in GeneXus-generated applications.

Attribute Values

An attribute can have one of the following values:

a. One value that depends on its data type.
b. Empty: This is a special value, i.e. zero for numeric data, empty string for character data, etc.
c. Null (only if it allows nulls): Well, in fact this is not a value. This means that the "value" for this attribute should be considered as "not specified", "not available", "not assigned" or unknown. So, Null is very different from empty.

It is important to make difference between these two special values, to avoid error o retrieve undesired results.
 

How to manipulate null values (Not Empty) in GeneXus ?

a. Set the attribute to accept nulls.
First at all, to work with nulls, the attribute must be able to take null values. This is set at the Transaction level for each secondary attributes. See Nulls Property in Attributes for more details.

b. Using the .SetNull() method.
If the attribute allows nulls in the table, using this method can force the attribute to take the null value.

c. Don't reference an attribute in a New or in a Business Component/Transaction's structure.
To achieve this, either the object's or the model's "Initialize Not Referenced Attributes" property must be set to "No". In this way, any attribute that accepts nulls and isn't referenced in a Procedure's New group or in a BC/Transaction structure will take the null value.

d. Assign the Nullvalue function
If the "Generate Null for Nullvalues" property is set to "Yes", the result of assigning this function to an attribute that accepts nulls will be null.

Note: It is not possible to assign a null value to a variable, so BC variables can't use the nullvalue function to set null their properties. The only way to explicitly set the null value in Business Components is through the SetNull() method. Also can be null if apply one the above mentioned cases.

e. Using the attribute's "Empty as null" property.
If the attribute's property "Empty as null" is "Yes", and the attribute value is empty (no value is specified in the Transaction form, or the empty one is assigned), null value will be taken.
See Empty As Null property for more details.

f. Null values are not copied when is assigned to another attribute.
For example, if you have the following assignment in a NEW group: X = Y (X = &Y), and Y (&Y) is null, then, X will take the empty value. The correct way to preserve de nullability information is as follows:

If Y.IsNull()
  X.SetNull()
Else
  X = Y
Endif

How to Avoid Nulls

a. Set the attributes to not accept nulls.

b. Use the .SetEmpty() method.

c. Otherwise, you can take these measures for protection purposes:

• Initialize not referenced attributes = Yes
• Generate null for nullvalues = No

Parameters and null/empty values

When passing parameters between objects there is no concept of null. Always the empty value will be received, even in the following situations :

• In a transaction there is a object.call(attribute) where attribute is on the form and the control has Empty As Null = Yes. Empty value is receives by object.
• object.call(nullvalue(attribute)) and Generate null for nullvalue = Yes. Empty value is receives by object.
• object.call(attribute.setnull()). In this case a specification error is displayed.

Empty Value for each DBMS / Data Type

This table shows the corresponding empty value for each data type and DBMS.

Compatibility

If you are converting a Knowledge Base from a version previous to GeneXus 9.0, please check this document information.

Comments & Collaboration

NULL values in a database: Should it be allowed?
A very interesting article about problems when using NULLs in our Data Base.