Skip to content

Null Values

SQL represents the fact that some piece of information is missing by a special marker called a null. Columns that contain a null indicate it is not known what the real value is. Informally, you can think of such columns as “containing a null” or “being null.” One could argue that the term “null value” is nonsense because the whole point about nulls is that they are not values. We are aware of this argument, but the term is commonly used within the database community, so we will use it in this discussion.

How nulls are actually represented in the system is implementation dependent. That implementation must (obviously) be such that the system can distinguish between null and non-null values.

Null values complicate internal database logic by creating a three-valued logic as opposed to the normal two-valued (binary) logic. In normal logic operations, the result is either true or false. Using null values will add a third outcome: unknown. This complicates logic considerably.

Null values complicate matters. It may be confusing to the end users if they see unexpected results. More importantly, we have found that allowing null values in index columns has a negative impact on performance. Therefore, we advise avoiding allowing null values in any column that appears in an index. If you do not allow null values in a column, make sure you define an appropriate default for that column. Defaults can be set up per (DataFlex) type.

Whether a column allows null values or not depends on the table definition. This definition can be adjusted by using the DF_FIELD_NULL_ALLOWED attribute. When converting, a configuration file is used that sets up "nullability" per type to convert. These defaults can be adjusted in the driver configuration file.

Support for Null Values

All drivers support null values and nullable columns. The DataFlex SQL Drivers support null values in the data manipulation and data definition logic. You can connect to, convert to, or even adjust the table definition with support for null values and nullable columns.

The DataFlex Pervasive.SQL Driver supports null values when creating records. It does not support null values in data definition logic. The driver cannot create or restructure tables with nullable columns. Null support in the driver is intended to be used when connecting to existing tables with nullable columns. Such tables must be created and maintained (restructured) with external tools, for example, with SQL commands like CREATE TABLE and ALTER TABLE. Tables that are created through the driver (this includes tables that are converted from DataFlex) will not have nullable columns.

Using Null Values in a Program

DataFlex has no equivalent for the SQL concept of null values in a database. The driver adds a column attribute DF_FIELD_IS_NULL of type Boolean that can be used to check if a column is null or to set it to null.

If a row is found that contains a column with the null value, you can still get the value of the column. It will be set to empty or zero (0) depending on the column's type. The only way to know that the column contains the null value is by checking the DF_FIELD_IS_NULL attribute of the column.

If a program wants to save a null value in a record, it needs to set the DF_FIELD_IS_NULL attribute for the column before saving. When records are created, only the columns that have been assigned a value will be assigned an explicit value. All others will get the default value defined for the column in the table definition. If no default is defined, null will be used.

Sort Order of Null Values

When a column contains null values, a problem arises when we sort based on that column. A comparison on a null value will return the unknown result. So there is no way to determine the collating sequence of null values. This has been solved by always collating nulls in a standard way:

  • SQL Server will place null values at the beginning of the sort when sorting in ascending order, and at the end when sorting in descending order.
  • DB2 will place null values at the end of the sort when sorting in ascending order, and at the beginning when sorting in descending order.
  • For ODBC, look at your database documentation to find out how null values collate.
  • Pervasive.SQL will place null values at the end of the sort when sorting in ascending order, and at the beginning when sorting in descending order.

Dates and Null Values

Almost all possible values of the DataFlex database types can be mapped to legal back-end values. The only exception is the zero date value in DataFlex. There is no sensible date value in a SQL back end to map this value to other than null. Pre-2.1 revisions of the SQL Drivers would therefore convert date columns to allow null values with a default value of null. This, however, introduced performance issues when a date column was part of an index segment, making finds on that index slow.

Dummy Zero Date

When the dummy zero date is used, the driver will use the lowest possible date value as the value to map the DataFlex zero date to. This value may be either 1753-01-01 or 0001-01-01 for SQL Server (depending on the data type), and is 0001-01-01 for DB2 and ODBC (by default). The driver will handle this internally. The DataFlex program still uses the zero date value. The driver handles automatic conversion of the dummy zero date value whenever needed.

The dummy zero date logic can be configured by the DF_FILE_USE_DUMMY_ZERO_DATE table attribute. If the attribute is set to true for a table, all date columns that do not allow null values will apply the dummy zero date logic.

If there is a need to access the data from outside the driver, one should handle the dummy zero date value in a special way.

The dummy zero date logic speeds up indexed find operations. In pre-2.1 driver versions, date columns allowed null values. Finding on an index containing segments that allow null values is slow. Since the dummy zero date logic does not create columns that allow null values, finding is as fast as finding using other indexes.

The introduction of the dummy zero date concept changed a number of default settings used at conversion. In pre-2.1 versions, the date columns would be converted to allow null values and have a default value of null. In 2.1 and higher, date columns will be converted not to allow null values and have a default value equal to the dummy zero date value. These defaults can be adjusted in the driver configuration file.

Driver Independent Code for Dummy Zero Dates

The dummy zero date concept is supported in SQL Drivers. The dummy zero date value is not the same for all drivers. When writing embedded SQL code, the programmer will need to filter the dummy zero date values from result sets. In order to do this, the value should be known. For this reason, the driver level attribute DRVR_DUMMY_ZERO_DATE_VALUE was created.

A back-end independent way to handle this would be:

Function CreateZeroDateQuery Integer iDriver Handle hDatabase 
Returns String
String sZeroDateValue
String sQuery
Get_Attribute DF_DATABASE_DUMMY_ZERO_DATE_VALUE of iDriver hDatabase 
To sZeroDateValue
Function_return (“Select * From MyTable Where MyDate = ‘” 
+ sZeroDateValue + “’”)
End_procedure // SelectAllZerodates

For a detailed discussion on the driver and database level attribute, see the Language Reference.

Converting from Nullable Columns to Dummy Zero Date

Data created with a pre-2.1 driver would allow null values for date columns. There may be a need to convert such data to use the dummy zero date logic in order to avoid sort order or performance problems. The conversion can be done in Database Builder or through a DataFlex program.

In Database Builder, the parameter tab page allows you to switch the DF_FILE_USE_DUMMY_ZERO_DATE setting for a table. The field tab page allows you to switch the “nullability” of a column. To convert pre-2.1 data to use dummy zero dates, check the “Use dummy zero date” checkbox on the parameter tab. For every data column in the table, uncheck the “Nullable” column in the field tab.

In Console Mode, a program must be written that converts tables. The essence of the conversion logic would be:

Procedure ConvertTable Integer iTable
Integer iNumFields
Integer iField
Integer iType
Structure_Start iTable
Set_Attribute DF_FILE_USE_DUMMY_ZERO_DATE Of iTable 
To True
Get_Attribute DF_FILE_NUMBER_FIELDS Of iTable To iNumFields
For iField From 1 To iNumFields
Get_Attribute DF_FIELD_TYPE Of iTable iField To iType
If (iType = DF_DATE) 
Set_Attribute DF_FIELD_NULL_ALLOWED Of 
iTable iField To False
Loop
Structure_End iTable
End_Procedure // ConvertTable

See Also

NULL Values and Defaults