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
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.