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.