Force Fields to NULL or Not NULL

Mertech’s ISAM drivers were developed always focusing on getting the best performance. We have been introducing many and different optimizations in all drivers, always looking for the best performance as possible. We will talk now a little bit more about one of the optimizations done to the drivers with regards inserting a new record.

You know that there is a cost to insert a new record into a table. Depending on the number of columns and the size of the values inserted as well, the insert can be faster or slower. I mean, if you have a table with 100 columns, it will be faster to insert a new row, setting values for only 10 columns, than trying to insert values into all 100 columns. With this concept in mind, we managed the driver inserts to take advantage of that. Driver effectively saves a value into a column when a (any) value was moved to a field. If no value is moved, then driver will leave the SQL database to deal with the value that should be assigned to the column. If the column has a default value, then the default value is saved otherwise a NULL will be used instead.

DataFlex applications do not know the concept of a NULL value that is applied to SQL Databases. In this case 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, if this VARCHAR column has a NULL value, then the DF_ASCII field read that as a blank ASCII value. The similar thing happens to the other DataFlex column data types. The following table shows the complete correlation:

In the other hand, we have to treat the empty DataFlex data values and define a rule about how they will be saved into a table column as well. In this case, there are 2 different behaviors that are applied to indexed and no-indexed columns:

• Indexed columns: since they are NOT NULL columns we always have to save a value into that. If the Indexed column does not have a default value defined to be used, then the driver will be using the following values:

• No-indexed columns: a NULL value will be saved if no value is moved to a field. This means that if a value is moved to a field, even if it is a zero or a blank space, the field gets that value.

Besides of adding this optimization, we added couple macro commands that you can use to handle this behavior.

The first one is SET_FORCE_FIELDS_NOT_NULL. You can use it to tell the driver that during an insert no column (independent of the data type) will have a NOT NULL value in it. This means that even if you don’t move a value to a column, no matter the column data type, driver is going to make sure that this column (and the others as well) will have a value in it that is not a NULL.

For example, let us say we have a table named T1 with the following numeric columns (c1, c2, c3) where c1 is an indexed column when you do the following:

SET_FORCE_FIELDS_NOT_NULL to DFTrue

Move 1 to T1.c1

Move 0 to T1.c2

Saverecord T1

The driver will make sure that c3 will have a Not NULL value in it, and in this case as this is a numeric column, a zero (0) value will be saved in it. If c3 was a string column, then a one blank space would be saved in it instead.

The second macro command is named SET_FORCE_FIELDS_NULL and you can use it to tell the driver that columns from a given data type must have a NULL value saved in it even if a blank, empty or a zero value was moved to it. Note that this command is supported by ORAFlex and MySQLFlex only.

Let us use now a table named T2 as an example to show how this command works out. Table T2 has c1, c2 and c3 as numeric columns and c4 plus c5 as string columns. Also c1 is the only indexed column in table T2. If you do the following:

<strong>SET_FORCE_FIELDS_NULL</strong> <strong>of </strong>DF_BCD <strong>to </strong><span style="font-family: mceinline;">DFTrue</span>

Move  1   to T2.c1

Move  0   to T2.c2

Move  0   to T2.c3

Move " " to T2.c4

Move " " to T2.c5

<strong>Saverecord </strong>T2

Then, driver will save a NULL value into c2 and c3, because they are numeric columns but c4 and c5 will have a blank space saved in instead.

For more details about this topic, please take a look at Flex2SQL driver documentation.