How to use the Database Update Framework
Be sure to first follow these instructions: First Time Usage and Workspace Setup.
Note for DUF 2.0
If your database already has been converted to SQL, please use the program "DUF Code Generator" to auto-generate DUF code from your development database. Read more about it in the help under "Tools" - "DUF Code Generator"
Steps for making database updates:
1.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.
2.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.
3.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.
4.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.
5.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".
// 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
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