Log In    |    Knowledge Base    |    4D Home
Tech Tip: Understanding "Map NULL values to blank values"
PRODUCT: 4D Developer | VERSION: 11 | PLATFORM: Mac & Win
Published On: December 19, 2007

With the advent of 4D v11 SQL it has become necessary for the 4D database engine to implement and become compatible with NULL values. The 4D SQL language and 4D database engine both have NULL values implemented, but they are n0t supported in the 4D language.

In the Structure editor using the inspector, each field has the option to map NULL values to blank values. In this case any NULL values stored in 4D database tables are converted when they are accessed with 4D commands. The default conversion values are as follows:

- For Alpha and Text data types: “”
- For Real, Integer and Long Integer data types: 0
- For the Date data type: “00/00/00”
- For the Time data type: “00:00:00”
- For the Boolean data type: False
- For the Picture data type: 0-byte picture
- For the Blob data type: 0-byte blob

The above functionality is standard. But, without the "Map NULL values to blank values" option turned on, accessing these NULL values within the 4D database engine does not automatically map the values to their default 4D language equivalent. For example using the QUERY command to search for a blank value (such as "0" for a longint value) will not find records with NULL values. Turning on the "Map NULL values to blank values" setting forces the all processing within 4D to act the same: using the above default values in the place of NULL values.