How to use the Database Update Framework

Be sure to first follow these instructions: First Time Usage and Workspace Setup.

Steps for making database updates:

Drag a cDbUpdateHandler object from the Studio's Database Update Framework Class Palette group, and drop it to the source code right after the cApplication object. Be sure to be in "source code mode" or the Studio's designer will complain that the object cannot be dropped on a non-data aware object.
By default the DbVersion.DatabaseVersion database table/field will be used to keep the current database version. This value will be updated by the cDbUpdateVersion class as soon as a successful cDbUpdateVersion update has finished. However, the table name can be changed to use one of your own, but is more work to do so.
Place cDbUpdateVersion objects within the cDbUpdateHandler container object, as siblings. Add one object for each database revision. You can drag cDbUpdateVersion objects from the Database Update Framework class palette and drop them within the cDbUpdateHandler container object.
In each cDbUpdateVersion object set the database update version property pnVersionNumber to a consecutive number. Then use the OnUpdate event to put code to update the database, or enter a "Use" statement with a package filename that contains an OnUpdate procedure. The OnUpdate event is automatically fired when the object is created. The code can consist of structural database changes as well as code to change/update data, whatever is needed.
Compile and run your program.

The cDbUpdateFunctionLibrary has been imported to the cDbUpdateHandler class and thus all of those functions can be used in both the cDbUpdateHandler and the cDbUpdateVersion objects without any need to create an object of the cDbUpdateFunctionLibrary class.

Functions are available for converting tables to SQL, create new SQL tables, connect to existing SQL tables, add/edit columns/fields and at the same time initialize the new column with a value, add/change indexes and much much more. These functions are "re-entrant" in their design, meaning that if it has been executed before, it will not be run again. So in short; functions first checks if it is OK to execute before attempting to make any changes.

Note

If you are using DataFlex 19 or later and have placed a cConnection object inside the cApplication object, those "Managed Connection" settings will automatically be picked up by the framework. However, since there are additional properties that can be set by the SQLConnection tool it might still be a good idea to use it. An alternative is to set those properties in the cDbUpdateHandler object.

Sample Code 1

To convert the whole Order Entry Sample Application - including data - to an IBM DB2 database you would add the following code.

If you are using Microsoft SQL Server see Sample Code 2 below.

Object oApplication is a cApplication

End_Object

// These are needed to keep the compiler happy;

Declare_Datafile Customer

Declare_Datafile Invt

Declare_Datafile OrderDtl

Declare_Datafile OrderHea

Declare_Datafile OrdSys

Declare_Datafile SalesP

Declare_Datafile Users

Declare_Datafile Vendor

Object oDbUpdateHandler is an cDbUpdateHandler
Set Data_File_Field to File_Field DbVersion.DatabaseVersion

Object oUpdateVersion1 is a cDbUpdateVersion
Set pnVersionNumber to 1.0
Set pbUseConnectionID to True

Set pbCopyData to False

Set pbRecnum to False

Set pbToANSI to False

Set psDriverID to DB2_DRV_ID // (The default driver is DATAFLEX_ID)

// DB2 specific settings;

// Note: These are only necessary to set if not specified if not set with the SQLConnections program.

Set psBaseTableSpace to "USERSPACE1"

Set psIndexTableSpace to "USERSPACE1"

Set psLongTableSpace to "USERSPACE1"

Set psSchema to "TestSchema"

Procedure OnUpdate

Boolean bOK

Get ApiTableConvertToSQL Customer.File_Number to bOK

Get ApiTableConvertToSQL Invt.File_Number to bOK

Get ApiTableConvertToSQL OrderHea.File_Number to bOK

Get ApiTableConvertToSQL OrderDtl.File_Number to bOK

Get ApiTableConvertToSQL OrdSys.File_Number to bOK

Get ApiTableConvertToSQL SalesP.File_Number to bOK

Get ApiTableConvertToSQL Users.File_Number to bOK

Get ApiTableConvertToSQL Vendor.File_Number to bOK

End_Procedure

End_Object

End_Object

Comments: The above code would the first time it is run;

Check that nobody else is using the database
Inform the user that the database needs to be updated (unless the pbSilentMode = True, which can be good if this is e.g. a webapp.)
Lock the database so nobody else can start the program until it has finished the update(s).
Close all open tables
Convert all the sample tables to MS-SQL and populate each SQL table with data. See the ApiTableConvertToSQL_Ex function for parameters and usage.
Update the DbVersion.DatabaseVersion with the "1.0" value to notify the framework that this piece of code has been run successfully.
Show a log if any errors occurred during update.
Unlock database and continue running the program, now with MS-SQL data.
Re-open all tables that was open before the update(s) started (including setting Master/Alias table properties).
Continues to run the program.

The assumption above is that a SQL database already exists and the database was specified with the SQLConnections tool - or if DataFlex 19 or later; a cConnection has been setup in the cApplication object . If the database doesn't exist yet you first need to create it with a "database management software" that is native to the database used. However, if you are using Microsoft SQL you can create the database with the framework. See "Sample Code 2".

Sample Code 2

// These are needed to keep the compiler happy;

Declare_Datafile Customer

Declare_Datafile Invt

Declare_Datafile OrderDtl

Declare_Datafile OrderHea

Declare_Datafile OrdSys

Declare_Datafile SalesP

Declare_Datafile Users

Declare_Datafile Vendor

Object oDbUpdateHandler is an cDbUpdateHandler
Set Data_File_Field to File_Field DbVersion.DatabaseVersion

// Optionally you may create an MS-SQL database, in case it doesn't exist yet:

// This option is only available for MS-SQL.

Procedure OnPreUpdate

Boolean bOK

Get SqlDatabaseCreate MSSQLDRV_ID "OrderEntry" True True to bOK

If (bOK = False) Begin

Send Info_Box "Nope, that didn't work. Program will now exit."

Send Exit_Application

End_Procedure

End_Procedure

Object oDbUpdateVersion1 is a cDbUpdateVersion

Set pnVersionNumber to 1.0

// Add your code to the OnUpdate event

Procedure OnUpdate

Boolean bOK

Get ApiTableConvertToSQL Customer.File_Number to bOK

Get ApiTableConvertToSQL Invt.File_Number to bOK

Get ApiTableConvertToSQL OrderHea.File_Number to bOK

Get ApiTableConvertToSQL OrderDtl.File_Number to bOK

Get ApiTableConvertToSQL OrdSys.File_Number to bOK

Get ApiTableConvertToSQL SalesP.File_Number to bOK

Get ApiTableConvertToSQL Users.File_Number to bOK

Get ApiTableConvertToSQL Vendor.File_Number to bOK

End_Procedure
End_Object

Object oDbUpdateVersion1.1 is a cDbUpdateVersion

Set pnVersionNumber to 1.1

Procedure OnUpdate

Boolean bOK

Get SQLColumnRename Customer.File_Number "Purchases" "YearlyPurchases" to bOK

// The "1" parameter will initialize all current records with a 1 for the new field.

Get SQLColumnAdd Customer.File_Number "IsPrinted" SQL_CHAR 4 0 True "1" to bOK

End_Procedure
End_Object

// ...or put your update code (Procedure OnUpdate) in a separate package:
Object oDbUpdateVersion1.3 is a cDbUpdateVersion
Set pnVersionNumber to 1.3

Use MyDbUpdateVersion1.3.pkg
:
End_Object

End_Object

Sample Code 3

Object oDbUpdateHandler is an cDbUpdateHandler
Set Data_File_Field to File_Field DbVersion.DatabaseVersion

Object oDbUpdateVersion1 is a cDbUpdateVersion

Set pnVersionNumber to 1.0

Procedure OnUpdate

Send ApiTableConvertAllAddException MyHistoryTable.File_Number

// This will convert all tables in your Filelist.cfg to SQL, except for those

// tables added with the ApiTableConvertAllAddException message:

Send ApiTableConvertALLToSQL

End_Procedure
End_Object

End_Object

Next Topic

cDbUpdateHandler