NULL Handling

There is a cost to insert a new record in a table. Depending on the number of columns and the size of the values inserted the insert can be faster or slower. If you have a table with 100 columns, it will be faster to insert a new row, setting values for only 10 columns, than trying to insert values into all 100 columns. The driver takes advantage of this and only saves a value into a column when a (any) value was moved to a field. If no value is moved, the driver will let the SQL database deal with the value that should be assigned to the column. If the column has a default value, then the default value is saved otherwise, a NULL will be used.

DataFlex applications do not know the concept of an SQL NULL value. A NULL value is always mapped to an empty value in a DataFlex application. For example, if you have a DF_ASCII field that is assigned to a VARCHAR table column, if the column is NULL, then the DF_ASCII field will read that as a blank ASCII value, and similarly for other datatypes. The following table shows the complete correlation:
 

DataFlex Data Type        How a SQL NULL value is mapped to
 DF_ASCII                  As a blank ASCII string
 DF_BCD                    As a zero (0) value
 DF_DATE or DF_DATETIME    As an empty date value
 DF_BINARY                 As an empty binary value
 DF_TEXT                   As an empty text value

 
On the other hand, we have to treat the empty DataFlex values and define a rule about how they will be saved into a table column. In this case, there are 2 different behaviors that are applied to indexed and no-indexed columns:

• Indexed columns: since they are NOT NULL columns we always have to save a value into that. If the Indexed column does not have a default value defined to be used, then the driver will use the following values:
  
  

DataFlex Data            Type How a SQL NULL value is mapped to
 DF_ASCII                 ‘ ‘ (one blank space)
 DF_BCD                   0 (zero)
 DF_DATE or DF_DATETIME   01/01/0001 (or 01/01/1753 for MSSQL)
 DF_BINARY                0x0
 DF_TEXT                  ‘ ‘ (one blank space)

• No-indexed columns: a NULL value will be saved if no value is moved to a field. This means that if a value is moved to a field, even if it is a zero or a blank space, the field gets that value.

We have added a couple macro commands that you can use to customize this behavior.

The first one is SET_FORCE_FIELDS_NOT_NULL. You can use it to tell the driver that during an insert no column (independent of the data type) will have a NOT NULL value in it. This means that even if you don’t move a value to a column, no matter the column data type, the driver is going to make sure that this column (and the others as well) will have a value in it that is not a NULL.

For example, let us say we have a table named T1 with the following numeric columns (c1, c2, c3) where c1 is an indexed column when you do the following:
 

SET_FORCE_FIELDS_NOT_NULL to DFTrue
 Move 1 to T1.c1
 Move 0 to T1.c2
 Saverecord T1

then driver will make sure that c3 will have a Not NULL value in it, and in this case as this is a numeric column, a zero (0) value will be saved in it. If c3 was a string column, then one blank space would be saved.

The second macro command is named SET_FORCE_FIELDS_NULL and you can use it to tell the driver that columns from a given data type must have a NULL value saved in it even if a blank, empty or a zero value was moved to it. Note that this command is supported by ORAFlex and MySQLFlex only.

Let us use now a table named T2 as an example to show how this command works out. Table T2 has c1, c2 and c3 as numeric columns and c4 plus c5 as string columns. Also c1 is the only indexed column in table T2. If you do the following:
 

SET_FORCE_FIELDS_NULL of DF_BCD to DFTrue
 Move 1 to T2.c1
 Move 0 to T2.c2
 Move 0 to T2.c3
 Move “ ” to T2.c4
 Move “ ” to T2.c5
 Saverecord T2

The driver will save NULL values into c2 and c3, because they are numeric columns but c4 and c5 will have a blank space saved in instead.

For more details about this, please look at the your database driver documentation or contact Mertech support.