Testing For Null (empty or blank) Values

The purpose of this page is to explain how to test for null, empty, and/or blank values across the various Data Types that are supported by Statelake.

Data values in Statelake can be:

fields in Source, Destination, or custom datasets. Please also see Field Class .
special variables, such as Super Globals. Please also refer to Variables.
user-declared variables. Please refer to Variables for more information.

Each data value will be of a particular Data Type, and it is important that the relationship between values and types is understood so that Statelake can be configured correctly.

Simple Data Types

The majority of the Data Types, such as String, Memo, Integer, floating-point, fixed-point, DateTime, and Boolean, will always have a default non-null value. And String could only be empty or blank.

Integer, Fixed-point, Or Floating-point

An Integer, fixed-point, or floating-point data type variable has a default value of zero (0), and cannot therefore ever be considered to be null, empty or blank.

Please also refer to Integer (Integer, Cardinal, Int64) or Fixed-point (Currency), and Floating-point (Double, Extended) for more information.

Boolean

A Boolean variable has a default value of False, and can only ever be True or False so cannot be null, empty or blank.

Boolean is available for reference.

DateTime

A DateTime data type has a default value of the day that is considered to be day zero (0) - 30 December 1899. As such it will never be null, empty or blank.

Please also see DateTime (TDateTime) for details.

String Data Type (Memo)

A String data type variable (or Memo) is empty when it contains no character, i.e. it has a length of zero (0) characters. This will occur when the variable has been declared but not yet accessed, so will be default have a String length of 0.

It will be blank when it contains one or more space characters, and nothing else. A blank String can be forced to be an empty String by using the Trim function which removes spaces.

A String can be proven to be empty using the Length function, which returns the number of characters in the given string. Length will return 0 if the String is empty, as shown in the code example that follows using the String variable called mystring.

  //String variable declared but not yet used 
  LogInfo('LENGTH DECLARED string = '+IntToStr(Length(mystring)));      //Result is 0 (EMPTY)
  
  //Set string with 3 spaces
  mystring := '   '                                
  LogInfo('LENGTH 3 SPACES = '+IntToStr(Length(mystring)));            //Result is 3 (not EMPTY)
  
  //Trim the spaces from the string
  mystring := Trim(mystring);
  LogInfo('LENGTH TRIM SPACES = '+IntToStr(Length(mystring)));         //Result is 0 (EMPTY)
  
  //Fill the string with text
  mystring := 'Sooty and Sweep';      
  LogInfo('LENGTH 15 WORDS = '+IntToStr(Length(mystring)));            //Result is 15 (not EMPTY)

But a String variable can never be null.

A Memo is simply a very large String so the rules that apply to a String apply to a Memo.

Please also refer to String (String, Widestring).

Variant Data Type

Statelake makes a lot of use of variables of the Variant data type.

But a Variant is like a chameleon - the data type will change as soon as a value is assigned to it. Initially, each Variant variable will be of the Variant data type, which is essentially a data sub-type.

Within a script, a Variant that has been declared but is yet to have a value assigned to it, will return as empty.

As soon as the first value is assigned to the Variant variable, the data type will be derived from the value that is assigned. So if a String is assigned to a Variant, then its data type will be String, and if an Integer is assigned to the Variant, then the data type will be Integer.

There are a couple of ways of testing whether a Variant variable contains a null value or a null sub-type. The VarIsNull function or VarType function can be used. All of the following examples will confirm that the variable called thevariable contains null - VarIsNull will return a True, and VarType will return a 1.

  //Forcing a variable to be NULL to return a 1
  thevariable := null;            
  LogInfo('VarType set as NULL = '+IntToStr(VarType(thevariable)));                 //Result is 1
 
  //Testing TRUE for a NULL variable 
  LogInfo('VarIsNull set as NULL = '+BooleanAsString(VarIsNull(thevariable)));      //Result is TRUE
  
  //Setting the variable to an integer
  thevariable := 7;                                                                 //Variable is not NULL
  LogInfo('VarType set as INTEGER = '+IntToStr(VarType(thevariable)));              //Result is 3
  
  //Testing FALSE for a NULL variable 
  LogInfo('VarIsNull set as INTEGER = '+BooleanAsString(VarIsNull(thevariable)));   //Result is FALSE
  

And when testing for an empty Variant type variable that has been declared but not yet accessed, the VarIsEmpty function or VarType function can be used. Both the following examples will confirm that the variable called thevariable is empty - VarIsEmpty will return a True, and VarType will return a 0.

//Variable declared but not yet accessed will return 0 when EMPTY

LogInfo('VarType UNSET = '+IntToStr(VarType(thecounter)));

//Variable declared but not yet accessed will return TRUE when EMPTY

LogInfo('VarIsEmpty UNSET = '+BooleanAsString(VarIsEmpty(thecounter)));

Please note that the function used in this example called BooleanAsString is a user-defined custom function declared in the top of the executing script. For details of this function please see Functions And Procedures.

Any empty or unfilled field in a database are stored with null as a default value, so a Variant variable that reads an empty field pulled from a database by a dataview will return a value of null.

A field in a dataset is a more sophisticated type of variable - an "object" which includes some built-in type-conversion routines. .Value is used to get or set the value of the field as a Variant.

The Value property (.Value) of a dataset field object returns the data value of that field as a Variant data type.
- It can be of Variant type null if the underlying database field value is null i.e. no value has been written to that field as yet so it has defaulted to null.
- It will be of Variant type String if the underlying database column is a String type (e.g. varchar or nvarchar) and a string value (including an empty string) has been assigned to that column in that row in the database i.e. spaces or text have been written to that particular record field in the database.
- It will be of Variant type Integer if the underlying database column is an Integer type (e.g. int) and a value has been assigned to that column in that row in the database i.e. a whole number has been written to that particular record field in the database.
The data value contained within the field object can be forced to a particular data type by using the .AsString, .AsInteger, and .AsFloat properties of the field object. So instead of using .Value, reference the field using either .AsString, AsInteger, or .AsFloat to have null values converted into a blank String , zero (0), or 0.000 respectively.

But be mindful that a null value is different from a blank String.

The following example illustrates this using ANY_DATAVIEW to refer to a dataview in your configuration, and Any_Field to refer to a field within that same dataview.

procedure ScriptEvent (var Value : variant);
var  
  myVariant : variant;
  myString: string;
  myInteger: integer;
begin
  myVariant := ANY_DATAVIEW['Any_Field'].Value;         // .Value returns a Variant
  if VarIsNull(ANY_DATAVIEW['Any_Field'].Value) then    //If the field is NULL
    LogInfo('Any_Field has a Null value');
  myString := ANY_DATAVIEW['Any_Field'].AsString;       // .AsString forces conversion to a string type
  if Trim(ANY_DATAVIEW['Any_Field'].AsString) = '' then // Note: using .AsString and the Trim function
    LogInfo('Any_Field is either Null, an empty string, or a string of one or more spaces');
  if ANY_DATAVIEW['Any_Field'].AsInteger = 0 then       //.AsInteger will fail if value is not Null or numeric
    LogInfo('Any_Field is either Null, or zero'); 
end;

The following code uses VarType, VarIsNull, and VarIsEmpty to test the contents of a database field.

  //Testing an "empty" database field to return a value of 1 if NULL
  thevariable := DR_TRANS['CUSTORDERNO'].Value;                                //This field is NULL 
  LogInfo('VarType DB NULL = '+IntToStr(VarType(thevariable)));                //Result is 1
  LogInfo('VarIsNull DB NULL = '+BooleanAsString(VarIsNull(thevariable)));     //Result is TRUE
  LogInfo('VarIsEmpty DB NULL = '+BooleanAsString(VarIsEmpty(thevariable)));   //Result is FALSE
  
  //Testing a "non-empty" database field 
  thevariable := DR_TRANS['ADDRESS1'].Value;                                      //Field is not NULL
  LogInfo('VarType DB not NULL = '+IntToStr(VarType(thevariable)));               //Result is 256
  LogInfo('VarIsNull DB not NULL = '+BooleanAsString(VarIsNull(thevariable)));    //Result is FALSE
  LogInfo('VarIsEmpty DB not NULL = '+BooleanAsString(VarIsEmpty(thevariable)));  //Result is FALSE
  LogInfo('');

The results from the above code show that the empty database field returns a 1 from VarType indicating it is null, so it also returns True from VarIsNull and False from VarIsEmpty - it cannot obviously be null and also be empty.

The code also shows that the non-empty database field contains a String - as indicated by the result of 256 from VarType. It is also therefore, neither null or empty so both these values return False.

For full details about these Statelake functions please refer to Variant Functions.