NULL Handling¶

SQL allows records to be inserted into a table without a value in a field if the field is optional. This means that when a record is saved the state of optional fields can be NULL. NULL is not a value, it is used as a placeholder for missing or inapplicable information.

DataFlex Applications¶

DataFlex applications have no 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, and the column is NULL, DataFlex reads the DF_ASCII field as a blank ASCII string. The following table shows how SQL NULL values are mapped in a DataFlex application:

DataFlex Data Type	SQL NULL value is mapped to
DF_ASCII	A blank ASCII string “”
DF_BCD	A zero value (0)
DF_DATE or DF_DATETIME	See Datetime for values.
DF_BINARY	An empty binary value “”
DF_TEXT	An empty text value 0x0

Datetime¶

The default format for datetime fields depends on the configured date format (for example, military, US, or European) for the computer on which the application runs. The difference between these formats is the order of the fields. For example, yyyy-mm-dd vs. dd-mm-yyyy.

The default datetime value, shown as (yyyy-mm-dd) is:

0000-00-00 00:00:00 for MySQL Windows drivers v12.0 and above

0001-01-01 00:00:00 for earlier versions of the MySQL drivers

0001-01-01 00:00:00 for PostgreSQL drivers

0001-01-01 00:00:00 for Oracle drivers

1753-01-01 00:00:00 for MS SQL for DATETIME

1900-01-01 00:00:00 for MS SQL for SMALLDATETIME

0001-01-01 00:00:00 for MS SQL for DATETIME2 and others

Note

The DF_LOW value for the date data type is 0000-00-00 for MySQL Windows drivers v12.0 and above. The DF_LOW value for older drivers defaults to 0001-01-01. Applications using an old driver and performing a FIND GT/LT/GE/LE against an Index with a Date segment, will not find records with values of 0000-00-00.

Migration Options¶

Mertech always recommends migrating fields as NOT NULL since there is no way in DataFlex to differentiate between NULL and a blank value. Sometimes it may be necessary to use NULL for a certain column. If this is required, make sure that column is never used as an index segment. Using NULL for index segments may give unexpected results and can be detrimental to performance.

The Flex2SQL Classic Migration Utility allows you to choose whether you want a non-index field to be NULLable when creating a new table or restructuring an existing table (Maintenance > Create Table or Restructure Table > Fields tab). If NOT NULL (the default setting) is selected, you can choose an appropriate default value for the field.

The Flex2SQL Classic Migration Utility also allows you to set field handling preferences during migration. General preferences and user-defined default values for NOT NULL field types can be set in the Convert Database > Migration Options tab. Table-specific settings are made in the Convert Database > Fields tab. System defaults for NOT NULL fields are listed in the table below:

DataFlex Data Type	System default for NOT NULL columns
DF_ASCII	‘ ‘(one blank space)
DF_BCD	0 (zero)
DF_DATE or DF_DATETIME	See Datetime for values.
DF_BINARY	0x0
DF_TEXT	‘ ‘(one blank space)

The reason a blank space is used as a default value for DF_ASCII and DF_TEXT columns is because some database servers treat an empty string in different ways under different circumstances. By using a single space character instead of an empty string all database servers will respond similarly.

Note

Since indexed columns are always migrated as NOT NULL, a user-defined (if available) or system default value is assigned by the driver if none was previously assigned.

Commands related to Nulls¶

Mertech provides two commands that you can use to customize field handling behavior. The first one is SET_FIELDS_NOT_NULL. Passing this command DFTRUE tells the driver that during an insert no columns of a given data type should be NULL. So even if a blank is moved to a column, the driver makes sure that the column has a value in it. The second command is SET_FIELDS_NULL. You can use this command to tell the driver that columns of a given data type must be assigned NULL even if a value was moved to the column.

The following table shows the effective NULL value handling by the Mertech driver based on the values of FIELDS_NULL and FIELDS_NOT_NULL.

	`SET_FIELDS_NOT_NULL to FALSE`	`SET_FIELDS_NOT_NULL to TRUE`
`SET_FIELDS_NULL to FALSE`	(Default values) If a value is never moved to a field, the field is NULL. If a value is moved to a field (even if it is 0 or the default date), the field retains that value.	If a value is never moved to a field, the field is assigned 0 or the default value for that field. If a value is moved to a field (even if it is 0 or the default date), the field retains that value.
`SET_FIELDS_NULL to TRUE`	The field is considered NULL regardless of if a 0 or empty string (“”) is moved to the field or if no value is assigned.	(Conflicting values) The SET_FIELDS_NULL value is ignored because the NOT_NULL value takes precedence.

Note

The syntax for these commands allows you to specify a different NULL handling setting for each of the data types supported by this operation.

An example makes this easier to understand:

// Table named T1 has the following numeric columns (c1, c2, c3)
SET_FIELDS_NOT_NULL DF_BCD to True
Move 1 to T1.c1
Move 0 to T1.c2
Saverecord T1

The driver makes sure that c3 has a zero (0) value stored in it, instead of being NULL. If c3 was a string column (and SET_FIELDS_NOT_NULL DF_ASCII to True), then one blank space would be saved.