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:

Flex2SQL for MySQL
(ver 10.0.3.29 updated 3/23/10)
 

 Flex2SQL for PostgreSQL 8

(ver 10.0.2.33 updated 3/23/10) 

 

Flex2SQL for Oracle (ver 10.0.3.40 updated 3/23/10)

Flex2SQL for SQL Server 2008 and 2005 (ver 10.0.4.28 updated 3/23/10)

BTR2SQL for Oracle (ver 4.2.1.6 updated 3/12/10)
 

BTR2SQL for MS SQL Server 2008 and 2005 (ver 4.2.1.6 updated 3/12/10) 

Flex2SQL for DB2 (ver 8.1.0.17 updated 2/21/06)  

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.

A new addition to Visual DataFlex 14.1  studio is the ability to do data modeling without leaving the studio. The erstwhile DBBuilder still works but is being designated as a tool to maintain the embedded DataFlex database. 

The VDF 14.1 studio was designed to work with minimal changes in the various drivers available in the market from Mertech and DAW. In order to get the information the studio needs  from the driver, a driver specific XML file was introduced which contains attributes supported by the underlying driver ( look in your VDF 14.1\bin directory for these files).

Mertech’s V9 and v10 drivers both work with VDF 14.1 runtime.  However, since most of the changes in VDF 14.1 are in the studio, meaning the new features are geared towards the developer, we recommend that you use v10 drivers for working with VDF 14.1 studio.  

If you do use v9 drivers with VDF 14.1 studio, you will encounter a couple of issues:  1)  you will encounter a crash in the studio when the Mertech login dialog  pops up when you open the table. This is due to some resource contention at the runtime level. This issue was fixed in v10 working in conjunction with Data Access  2) Some new attributes were added to v10 which are present in the driver XML file shipped with VDF 14.1. These attributes are absent in v9 drivers and you will get errors about missing attributes. 

So our recommendation is that if you are using VDF 14.1 studio for designing your apps, use v10 drivers. However, you can continue to deploy with both v9 and v10 drivers with VDF 14.1 runtimes.  The new changes in v10 though will give you better performance and new features.

Refreshing Table Structure
A new feature of VDF 14.1 studio is the ability to refresh the  table definition to get the latest table structure from the backend. A side effect of caching table structures locally in .TD or CCH files is that those structures can out of synch if the tables were modified with non-DF tools on the backend .

To solve this issue, Mertech decided to completely get rid of .TD files so that on every open, we get a fresh structure information from the server and improved the code to open the files just as fast the first time and much faster if the files are closed and opened again. We do this by caching the structures in memory. 

VDF 14.1 studio refreshes the table structure by first  deleting the driver structure cache files (.TD or .CCH in the case of DAW CKs) and then opening and closing the files and generating the .FD file on the fly. This forces drivers using the cache  files to re-read the table structure information from the server.  We feel a better approach is to do this through the API and let the driver handle it (but that is a different story). 

In the case of v10 drivers, if the change is made to the backend and you refresh the table structure, the change will not be read back into the studio because we do not have the .TD files anymore and opening and closing will not release the in cache memory. If the studio had done this through an API call, let the driver handle it, then this would not have been a problem because the driver knows how to refresh its structure information (we have added a new command for that).  Because v10 always reads the data from the backend the first time a file is opened, you will always get the most up to date table definition. However, if someone makes the change to the table while you had the file open, then you will have to get out of the studio and then reopen it.

This is a temporary issue, has limited scope so we will be fixing this  in the next inline revision of v10 drivers.