DF_TEXT fields are used in DataFlex to store text longer then 255 bytes. The number of DF_TEXT fields is limited by the maximum record length. This limit is higher in MS SQL Server then in a DAT file. There are though performance implications that should be considered before using SQL TEXT datatypes (VARCHAR(MAX), NVARCHAR(MAX) or TEXT) .

SQLFlex uses the SQL Server Native Client OLE DB (SQLNCLI-OLEDB) provider as the low-level COM API for data access, for maximum performance. SQLNCLI-OLEDB has some restrictions when using SQL TEXT columns (as well as to it’s binary cousins, VARBINARY(max) and IMAGE).

When you do a FIND, SAVE, or DELETE or call any other DataFlex command that requires data access, SQLFlex turns that into a SQL statement. SQLFlex executes the statements through cursors that are kept available in case the same command is re-executed, for added performance. But if the table accessed has at least one TEXT column, then any existing cursors cannot be reused, due to SQLNCLI-OLEDB limitations. This causes a slight slowdown, as the driver is forced to work around this restriction, by closing and reopening cursors.

There are no problems to have a table with many TEXT columns, but for maximum performance, the usage of TEXT columns should be avoided when possible. This can be done if your DF_TEXT field is smaller than 8,000 bytes, and then map DF_TEXT to SQL data types like CHAR, VARCHAR, or NVARCHAR instead of SQL TEXT or VARCHAR(MAX).

The DataFlex field data type can be defined by creating a FIELD_TYPE token in the INT file or based on the default mappings, shown in the following table:


SQL Server Data Type                             DataFlex Data Type
CHAR, VARCHAR or NVARCHAR <= 255 bytes           DF_ASCII
CHAR, VARCHAR or NVARCHAR > 255 bytes            DF_TEXT
VARCHAR(MAX), NVARCHAR(MAX), TEXT                DF_TEXT
BINARY, VARBINARY, VARBINARY(MAX) or IMAGE       DF_BINARY


Flex2SQL has an option that you can use to specify the SQL native data type that should be used when converting a table. In the Flex2SQL menu, click on Tools -> Preferences and open the TAB Field Mappings. Assign new data types for the DF_TEXT and DF_BINARY fields and migrate you DAT file.

Let us say that you assigned a VARCHAR and a VARBINARY SQL data type to the DF_TEXT and DF_BINARY respectively as showed by the following image:

Default type

When you migrate a DAT file that has a DF_TEXT field, Flex2SQL will create it as a VARCHAR column when the field length is smaller than or equal to 8000 bytes. But if your DF_TEXT field is greater than 8000 bytes, Flex2SQL will create it as a VARCHAR(MAX) column instead.

Any way you choose to map DF_TEXT, the driver will handle it, but if speed is a primary concern, then we suggest trying to use the regular CHAR, VARCHAR, or NVARCHAR column types, whenever possible.

The current driver releases for all of Mertech’s Flex2SQL products is as follows:

There is a cost to insert a new record in a table. Depending on the number of columns and the size of the values inserted the insert can be faster or slower. 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. The driver takes advantage of this and only saves a value into a column when a (any) value was moved to a field. If no value is moved, the driver will let the SQL database 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.

DataFlex applications do not know the 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, if the column is NULL, then the DF_ASCII field will read that as a blank ASCII value, and similarly for other datatypes. The following table shows the complete correlation:

DataFlex Data Type        How a SQL NULL value is mapped to
DF_ASCII                  As a blank ASCII string
DF_BCD                    As a zero (0) value
DF_DATE or DF_DATETIME    As an empty date value
DF_BINARY                 As an empty binary value
DF_TEXT                   As an empty text value

On the other hand, we have to treat the empty DataFlex values and define a rule about how they will be saved into a table column. 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 use the following values:

DataFlex Data            Type How a SQL NULL value is mapped to
DF_ASCII                 ‘ ‘ (one blank space)
DF_BCD                   0 (zero)
DF_DATE or DF_DATETIME   01/01/0001 (or 01/01/1753 for MSSQL)
DF_BINARY                0x0
DF_TEXT                  ‘ ‘ (one blank space)

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

We have added a couple macro commands that you can use to customize 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, the 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

then 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 one blank space would be saved.

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:

SET_FORCE_FIELDS_NULL of DF_BCD to DFTrue
Move 1 to T2.c1
Move 0 to T2.c2
Move 0 to T2.c3
Move “ ” to T2.c4
Move “ ” to T2.c5
Saverecord T2

The driver will save NULL values 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, please look at the your database driver documentation or contact Mertech support.