How the automatic update logic works

Warning! It is important to understand that the framework may not always be able to know how to update when using a package file generated by the DUFCodeGenerator utility. It doesn't really matter if it is an embedded DataFlex or SQL table, the same logic applies.

When a table has been changed the following logical order is performed internally by the framework;

1) New fields are inserted

2) Fields with the same name are moved to their new position

3) Fields are renamed

Example

Consider the following example scenario. The "New Table" is in development and the "Current Table" is at customer site;

What can be deduced fromt he above? (We will assume that no fields has been deleted)

•The first three fields are unchanged.

•Three new fields have been added. However, they two of them have not been added to the end of the table, instead two have been inserted and one added at the end.

•Two fields seems to have changed in length (ValueOld and ValueNew)

•Two fields seems to have been renamed, but we cannot be sure.

NOTE: It is important to remember that - not only when dealing with the embedded database - new fields should always be added at the end of a table. This has been an SQL general guideline for decades.

In the above example, and if no pre-preparation of the table is made, DUF would make a mess of the table.

So what could be done to prepare this table before using the auto-generated packed from the DUFCodeGenerator program?

The truth of the above is that the two new fields; "ReferencePoint", "Capture" have been inserted and a new field "BroTableRef" has been added to the end of the table. The two fields "ValueOld" and "ValueNew" have been renamed and have a new length.

If we would rename "ValueOld" to "FilterOld" and "ValueNew" to "FilterNew" we would be in a much better situation.

Other preparations that might be needed is for example if the filelist.cfg contains an empty file slot. That will generate an error and the DUF conversion will stop. That can be fixed by using the ApiTableRemove function.

If a table has been moved to another place (slot) in the filelist, the ApiTableMoveFileListEntry function can be used to prepare before using the auto-generated package.

Example Solution

Procedure OnUpdate

Object oDbUpdateHandler is a cDbUpdateHandler

    Set piDbVersionFileNumber  to 200

    Set piDbVersionFieldNumber to 1

    Set pbAutoCreateDbVersionTable to True  // Automatically creates the DbVersion table when the program is started.

    Object oDbUpdateVersion0_9 is a cDbUpdateVersion

        Set pnVersionNumber to 0.9

        // To prepare the database before the auto-generated package is used.

        Procedure OnUpdate

            Boolean bOK

            Handle hTable

            Move 178 to hTable

            Get ApiTableRemove hTable to bOK // Filelist slot that does not have a file on disk.

            // Move table "Location" from filelist slot 6 to 10 etc.

            Get ApiTableMoveFileListEntry "Location" "Location" "Locations"    6 10 "DATAFLEX" to bOK

            Get ApiTableMoveFileListEntry "LocId" "LocId" "Location - Id"      3  6 "DATAFLEX" to bOK            

            Get ApiTableMoveFileListEntry "LocSp" "LocSp" "Location - Sp"      4  7 "DATAFLEX" to bOK

            Get ApiTableMoveFileListEntry "LocFi" "LocFi" "Location - Filters" 5  8 "DATAFLEX" to bOK

            Move 18 to hTable

            // This table have new fields inserted and added to, as well as new field names and fields that

            // have been moved. We need to rename the fields to not confuse DUF, before we use the auto-generated

            // package below.

            Get ApiColumnRename hTable "ValueOld"  "FilterOld" to bOK

            Get ApiColumnRename hTable "ValueNew" "FilterNew" to bOK

        End_Procedure  

    End_Object

    Use DUF_MultipleTables1_0.pkg // Auto-generatore package.

End_Object

Note: There are also other table and column functions available that might be applicable for pre-processing of a table before an auto-generated package is used. See the help under API Make Database Changes.