VDF-GUIdance logo



  Visual DataFlex Logo
  

Shared knowledge leads to accumulated knowledge

        Printer Friendly Page


dbUpdateUtil

by Allan Greis Eriksen

Summary

This is a tool to restructure and update datafiles on a remote site. You can use this tool as part of your update scripts. You can also use this tool to report all differences between databases even through con. kits.

Complete with all source code files.
Version 2 - for VDF5, VDF6 and VDF7 (not maintained anymore)
Version 4.0 - for VDF8, VDF9.x, VDF10 and Vdf11.x
NB: Version 4.0 and up can now work with Pervasive and MS SQL Connectivity Kit.
Version 5.1 - for VDF12.x
Size: 998 KB Download
Date Created: 29/10/2000
Date Updated: 09/02/2008
Author: Allan Greis Eriksen
Company: NordTeam Gruppen ApS


What it is

This utility is able to update any kind of databases, where the update is based upon a set of description-files (*.NTA, *.NTB, *.NTC).
The update-utility itself supports structural changes (updates) with preservation of the existing data as well as a change of the data in a record on a specific database (initializing).
Thanks to my boss I'm now able to give it away for free, the source code that is!
The update-utility is able to initialize the databases to make sure that certain information is present after the database is updated, including the CODETYPE and CODEMAST databases.
An example of this would be to make sure that certain choices in combo-boxes are present, or maybe you want to change a supervisor password, update system files... you name it!
The initializing process can be forced to use indexes to make sure that no duplicate records are created.

Technical description

The update-utility is using FIELDNAMES rather than field numbers, so it is possible to insert fields anywhere inside the database.
The update-utility is able to understand "Known-Issues" files (NTK file). In these files you can define which fieldnames are identical.
By keeping these files you can update previous versions of databases without needing to know what version the end user has.
If the information in the "Known-Issues" files get obsolete you don't have to erase that information, thereby insuring
you can update any previous versions.
There are limitations for initalizing fieldtypes:
- TEXT-fields can not include Linefeed-characters such as a RETURN/ENTER-character. The field can be considered a ASCII-type where data can be of one long text-line.
- BINARY-fields can not be initialized.
- OVERLAP-fields can not be initalized by themselves, only by the fields that the OVERLAP-field is build upon.
The textfile is an example of how to make a "Known Issues" file. What it contains is instructions to the update utility what to do when the update utility finds a fieldname in the old database that is not represented in the new database.
As for now you will have to make this file on your own (i.e. with notepad).
Give the name for the file the same name as the physical name of the dataflex-file.
In the example the dataflex file could be VTXT.DAT and the "Known-Issues" file would then be "VTXT.NTK".
The extension .NTK tells the update utility that this is a "Known Issues" file.
When the update utility meets a fieldname in the old datafile that it cannot find in the new datafile it will search the information in the "Known Issues" file for the specific datafile.
The update utility understands 1 instruction (as for now)
"IDENTICAL".
IDENTICAL OldFieldName NewFieldName
This tells the update utility that the fieldname in the old datafile has
been renamed to NewFieldName. This instruction causes the
update utility to handle the fieldnames as the same field and any changes made to the field are updated without loss of data.
Examples in VTXT.NTK:

"Identical, Num, Vtxtnum"
The field with the name "Num" in the VTXT-file has been renamed to "Vtxtnum"
in the updated VTXT-file.

"IDENTICAL , @PASSWORD , PASSWORD"
The field with the name "@PASSWORD" in the VTXT-file has been renamed to
"PASSWORD" in the updated VTXT-file.
Please note that the update utility is case insensitive regarding the instructions and fieldnames, and also insensitive regarding how many spaces before or after each instruction and fieldname. One thing though, each instruction has to start on a new line!

The update-utility is returning a status-file that tells you whether or not the update was successful or not. That can be used in
conjunction with i.e. WISE to make certain safety issues like replacing the partly updated databases with the backup version made
before the update-utility was started.
The update-utility consists of 2 applications, a version that is to be operated manually and a full-automatic version.
The manual version is for the developer where you can test an update and make notes on for example fieldname changes.
This is also the version to use when you need to make description (*.NTA, *,NTB, *.NTC) files and/or the initialize-files.
The other version (for the end user/customer) only consists of progress bars.
It will start the update when invoked and close by itself when it has finished the update.
For the full automatic part to work I am using WISE and a version number-file.
That is useful for me when some of my customers have to upload their information from a laptop to the main database on a server. By examining the version numbers in these databases right at the beginning of the dataflexapplication I can force the user to update
their application if any of the version numbers is different from the hardcoded version number inside the application.
The autoupdate utility works with registry keys to tell where to find the complete database, the filelist and the descriptionfiles, the initialization files and "known issues" files.
You will have to define 3 string values on the clients machine under the following registry key:
For version 2.2:
HKEY_LOCAL_MACHINE\Software\Data Access Corporation\Visual DataFlex\6\Defaults

For version 4.0+
HKEY_LOCAL_MACHINE\SOFTWARE\Data Access Worldwide\Visual DataFlex\11.1\Defaults

Key HKEY_LOCAL_MACHINE\SOFTWARE\Data Access Corporation\Visual DataFlex\6\Defaults
Name \Update database
Value String, Directories for the databasefiles

Key HKEY_LOCAL_MACHINE\SOFTWARE\Data Access Corporation\Visual DataFlex\6\Defaults
Name \Update description
Value String, Indicate path to descriptionfiles

Key HKEY_LOCAL_MACHINE\SOFTWARE\Data Access Corporation\Visual DataFlex\6\Defaults
Name \Update filelist
Value String, Indicate filelist-name including path

As for the Update database you can specify multiple datapaths i.e. "H:\Data1;H:\Data2" or "\\Server\Data1;\\Server\Data2" and so on.
The Update Filelist could then be "H:\Data1\Filelist.cfg"
The description files (and initialize files, known issues) should be placed in a separate map and that could then be "H:\Descriptions". In WISE these files are placed in a temporary map and after the update utility has finished the map is erased.

The program manupd.src

You can compile this program with the compiler from VDF5/6 and 7 without the need to create a workspace first. Just select [defaults] as workspace and choose the filename.
(as for VDF8+ it is now IDE aware and can be registered with normal .ws file).
Hit the compile button after that.
When you start the program you can start with generating description and initialize files.
We are going to show you an easy example where we will add an extra record into the codemast file for the shipping type. Assume that you have a customer using the order-entry example and that they want to have the option that customers need to pay before anything is shipped. We are going to call this a prepayment. So, we will need to add a line with the option "PREPAY"
First select the datadescription view.
Selecting the <start> button will write out all the necessary description fields.

When you select the <Initializing> button, the update utility shows a list of datafiles in a selectionlist from where you can select a datafile to initialize. Select the CODEMAST file.
If the datafile already has an initialize file, it will be marked with (init) before the filename in the list.
A new window will popup and this one you can specify new records that should be present after the datafile has been updated.
All the fieldnames are listed in the view and each row represents a field in the datafile.
In order to create a new record use the standard DataFlex-keys (F5, F2, F7, F8 and so on) or use the buttons at the right side of the
window.
All data is handled in internal memory and is not written to an initialization file until you select the <Generate> button.
When you are done select the index that should be used during initializing the datafile.
When choosing an index file, you make sure that the initialisation does overwrite existing data and therefore not causing duplicate record errors. This is also why you can change for example a supervisor password (requiring that the password field is not part of the index *S*).
What we have done here is just entered our data, clicked on the <save> button to store the new record in memory and after that selected index 1.
Then you can click on the <Generate> button so that the initialization file is actually written to disk .
The destination of the file is the path specified in the Description form and it will be named the same as the physical name of the datafile with the extension "NTI" (i.e. "CODEMAST.NTI").
The update utility will automatically initialize a file if an initialization file for the updated file is present in the description-path.
The reason I write "dummy" to CODETYPE.NTA and CODEMAST.NTA is the fact that the update utility only updates those files that have a .NTA file in the description map.
Now the CODETYPE and CODEMAST files are never (or should never be) updated, but we would still like to have the chance to initialize these files. The presence of the NTA-file is the handle access to access these.
Note for if you try to run this example, be aware that by default the flexerrs file is not physically available in the data folder while it is listed in the filelist. You have to copy this file into the data folder before running the example.
I use the manual update utility to prepare for the automatic update utility.
When I'm done creating the initialization and description files I test to see if the test database (a customer database or deploy-version) work.
Then I examine the test file ("TESTUPD.TXT") to see If there are any changes that need to be solved using the "Known Issues" files.
Then I make the "Known Issues" file(s) in the description-map.
After that I try it again to see if the test resolves the issues I've written in the Known Issues files.
Now would be a good time to update the database without the test-option, and then run a test to se if the test file does not report any changes at all.
Finally the description map is packed inside a WISE script with all its safety operations (making a backup, checking to see whether or not the update went allright), along with the automatic update utility new vdf-programs and so on...
Click here to download the wisescript examples (28kb)

Support


Please direct your support questions to the peer-to-peer newsgroup which is available at:
news://news.mijnwebapp.nl/VDF-GUIdance
This will also help out other people which might have the same questions as you.

Download


dbUpdateUtil.zip ~ 225Kb, released March 9, 2005

In the zipfile, you will find both version 2.0 and version 3+.

Version 2.0 - for VDF5, VDF6 and VDF7 (not maintained anymore)
Version 3+ - for VDF8.x and up

Subversion



http://svn.vdf-guidance.com/dbUpdateUtil/trunk/

Additional download


Requires the following packages to compile:
- WINAPI Filehandling (by Wil van Antverpen)
- Binary file access and other filehandling in WinAPI (by "meself")

Database difference report


Here is an example of how a testreport could look like when testing af databaseupdate.
First I made a copy of the database in the Order11.1 example workspace. Then I changed some in the customer database file.
Here is a manual update preformed with the test attribute ON. It will go through the update and report any changes in the database that would make an update but with out actually updating anything. This is a good tools to se if there are any changes in the database and if so what the changes are. You can also do that on MS-SQL and Pervasive databases.
This report shows that if the update is run (without the test attribute) it will changes the Customer database file in the following way:
- Field Email_address is changed from 30 to 50 characters.
- Field Credit_limit's main index is set to the new index 4
- A new index is added with Credit_limit in descending order and customer_number as second segment.