Friday, September 12, 2008

IBM DB2 databases and Enterprise Core Objects (Delphi 2005)

Original Source click here

Level: Intermediate

Bob Swart (, Developer, Bob Swart Training & Consultancy

19 Jan 2006

This article shows how to use Borland Delphi 2005 and the Enterprise Core Objects (ECO) to import an IBM DB2® Universal Database™ (DB2 UDB) database and create a UML object model for it, which is the basis for a .NET® WinForms application. The object model (called EcoSpace) uses the DB2 UDB database as persistence layer, and can be used to connect to user controls (like DataGrids), offering drag-and-drop, autoforms (for individual items), save, undo, redo, and so on, all on top of the DB2 UDB database tables.
Show developerWorks content related to my search: DelphiShow developerWorks content related to my search: Delphi

Using ADO.NET / BDP and SAMPLE database

In this article, we will use the Borland Data Provider for ADO.NET as persistence layer for the Enterprise Core Objects II Object Model. We'll start to use the DB2 UDB SAMPLE database, but the main work will be done in an empty DB2 UDB database.

Creating the ECO 2 application

Start Delphi 2005 Architect and select File -> New-> Other, which brings up the Object Repository with the list of new items to start a new Delphi 2005 application. From the Delphi for .NET Projects, double-click on the ECO WinForms Application icon, as shown in Figure 1.

Figure 1. Delphi 2005 Object Repository
Delphi 2005 Object Repository

In the New Application dialog, you only have to enter the name of the new application, for which I've used ECO2DB2. Optionally, you can change the location as well, as shown in Figure 2.

Figure 2. New ECO application wizard
New ECO Application Wizard

After you click on the OK button, a new Delphi 2005 ECO 2 project will be created, with many files whose purpose may not be immediately clear, but I'll explain most of them in this article.

Back to top

Wrapping the DB2 SAMPLE database in ECO

One of the enhancements in ECO II that comes with Delphi 2005 compared to its predecessor is the ability to wrap an existing database in an ECO model. There are still some limitations, as you'll see, but you can take an existing database and have most or all of its tables imported as UML classes.

As an experiment, try to import the DB2 UDB SAMPLE database tables. First, in the Project Manager, double-click on the ECO2DB2EcoSpace.pas unit, and go to design mode. This is the unit that controls the persistency of the EcoSpace, i.e. where the Object Model will be stored. We can use a PersistenceMapperXml component to store the EcoSpace in an XML file, or a PersistenceMapperBdp component to store the EcoSpace inside a database connected through a Borland Data Provider connection driver. The later offers the ability to use an DB2 database, so place a PersistenceMapperBdp component on the ECO2DB2EcoSpace designer, and at the bottom of the Object Inspector click on the DB2Setup verb to initialize the PersistenceMapperBdp component for a DB2 database.

Now open up the Data Explorer (a tab in the Project Manager), and open the DB2 Connection list. Make sure it's configured to connect to the DB2 UDB SAMPLE database (specify MASTER as database, and provide a correct username and password to connect to the database). Drag this DB2 Connection node to the EcoSpace designer, resulting in a BdpConnection component being placed right next to the PersistenceMapperBdp component in the non-visual components area of the WinForms Designer.

This should also automatically connect the Connection property of the PersistenceMapperBdp component (pointing it to the BdpConnection component), as well as the PersistenceMapper property of the TECO2DB2EcoSpace Form itself.

Now, click on the right-most button in the toolbar at the bottom of the designer, which says Wrap Existing Database with Eco in its tooltip. First, you'll get a dialog to ask you about naming conventions, followed by a dialog that informs you the import / wrapping was completed. In the message window of Delphi 2005, you can read some warnings at this time, which include the following:

Reverseengineering DB2 databases is only partially supported,
relations will not be detected.
Analyzing database SAMPLE...
Generating object model and object/relational mapping information...
Generating ECO Delphi source code file SAMPLE.pas...
Generating ECO XML file SAMPLE.xml...

Apparently, wrapping DB2 databases is only partially supported with Delphi 2005 at this time, and relations will not be detected (although this has been improved with Delphi 2006). There is another limitation as well, which we'll see in a moment.

When the wrapping is done, a second messagebox is shown with instructions to add the mapping information to the application, as shown in Figure 3:

Figure 3. Wrap existing database with ECO
Wrap existing database with ECO

Before we act on the instructions of the dialog, let's first examine the result of the wrapping of the DB2 UDB database tables. Two new files were generated and added to the project: SAMPLEMapping.xml and SAMPLECoreClasses (where SAMPLE is the name of the DB2 UDB database that we used for the wrapping in ECO). The EcoSpace file contains the UML model that was imported, based on the (useful) tables found inside the SAMPLE database, and the XML file contains the necessary information for the mapping of this database to the EcoSpace.

In order to make sure the application uses the mapping, we must drop a FileMappingProvider component onto the ECO space designer. In the FileMappingProvider component's FileName property, we need to specify the file SAMPLEMapping.xml (a relative path to the filename is best, since you also need to deploy the SAMPLEMapping.xml file with the application).
Select the PersistenceMapperBdp component, and point the RunTimeMappingProvider property to the FileMappingProvider component. This makes sure that the ECO2DB2EcoSpace unit knows to use the file mapping to map the EcoSpace to the DB2 UDB SAMPLE database.

Now go to the Project Manager and click on the Model View tab. This will show the model nodes, including the default CoreClasses unit with the empty UML Model that we got when we created the new ECO 2 application. But it also contains the SAMPLECoreClasses node which was generated when the DB2 UDB SAMPLE database was imported. If you open the SAMPLECoreClasses node, you'll notice nodes for Empphoto and Empresume that map to the EMP_PHOTO and EMP_RESUME tables. Unfortunately, you won't see any other tablenames resurfacing here, as can be seen in Figure 4.

Figure 4. Model View
Model View

The reason why these other tables are missing is probably because they do not have a primary key (index field) associated with them: only the EMP_PHOTO and EMP_RESUME tables use a primary key field.

Anyway, to look inside the SAMPLECoreClasses unit, just double-click on the SAMPLECoreClasses node in the Model View, which will display the UML diagram, as seen in Figure 5.

Figure 5. SAMPLE CoreClasses Diagram
SAMPLE CoreClasses Diagram

This proves that only two tables were converted: EMP_PHOTO and EMP_RESUME. The reason for this is that the other tables in the DB2 UDB SAMPLE database have no primary key, and hence no index information to associate a unique ECO key with.

It's not really useful to continue with the Empphoto and Empresume classes without the associating Employee class, so close the diagram for the SAMPLEEcoSpace. At least you've seen - in general - what it takes to make the mapping in the future. Using Delphi 2006, the ability to wrap existing databases has been greatly improved, but this will be covered in more detail again some other time.

Go to the ECO2DB2EcoSpace unit and remove the FileMappingProvider component again, as well as the BdpConnection component pointing to our DB2 UDB SAMPLE database. We better start fresh with an empty DB2 UDB database.

Back to top

Creating an empty DB2 ECO Database

Normally, you would need an empty database as container to store the EcoSpace. Delphi 2005 comes with a CD-ROM that contains the DB2 UDB Developer edition, which I assume you have installed. You can use the Control Center (found in the DB2 General Administrator Tools), and in the Control Center use the treeview to browse your system, DB2 instances and databases. Create a new database using the wizard, which will give you a 7-step wizard where you only have to specify the name of the new empty database (something like ECO2).

The SQL DDL which is used to create the database is as follows:


Figure 6. Create Database Wizard
Create Database Wizard

When you've created the new database, you may want to run the Configuration Advisor to tune your database (this is not needed at this time).

After the empty DB2 UDB ECO2 database has been created, you need to go to the Data Explorer and add a new DB2 Connection to the ECO2 database.

Then, return to the ECO2DB2EcoSpace unit, and click on the new DB2 Connection node in the Data Explorer, and drag it to the ECO2DB2EcoSpace designer. This will again setup all required links, but this time pointing to the empty ECO2 database.

Back to top

Building our own UML Model

Before we can continue with the ECO2DB2EcoSpace unit, we must now first design the UML Model that we want to use. Return to the Model View and double-click on the default CoreClasses node. This will show the diagram designer (currently still empty) where we can build our own UML Model.

There are two tables in the DB2 UDB SAMPLE database that are interesting enough to rebuild as UML classes: The EMPLOYEE table, with a EMPNO field (that should have been a key field) as well as a WORKDEPT field that can be used to select a record from the DEPARTMENT table. The DEPARTMENT table itself is also interesting, holding a MGRNO field that points back to the EMPLOYEE table, as well as a ADMRDEPT field that points back to the EMPLOYEE table itself. Let's see if we can model just these two tables plus their (often implicit) relations as UML classes and associations.

As a little reminder (or cheat sheet), below are the two tables with their list of fields:

Figure 7. Original EMPLOYEE Table
Original EMPLOYEE Table

Figure 8. Original Department Table
Original Department Table

Designing Employee

Right-click on the designer area of the CoreClasses UML designer and use the pop-up menu to add a new ECO Class, call it Employee.

Where the SAMPLE database used the EMPNO field as unique value to determine the employee, which was used by the DEPARTMENT table as well, we can skip this field, since ECO itself will add unique index fields to identify class instances. Right-click on the Employee class and use the pop-up menu to add attributes. Add the FirstName, and LastName attributes of type string, and the MidInit attribute of type WideChar.

The WORKDEPT field was only used as linking field to the DEPARTMENT table, and has no meaningful value by itself. We can model this using an association later, so skip this field. Add the PhoneNo attribute of type String, HireDate attribute of type DateTime, Job attribute of type String, EdLevel attribute of type Integer, Sex attribute of type WideChar, Birthdate attribute of type DateTime, and the Salary, Bonus, and Comm attributes of type Double.

The beauty of designing classes is that apart from attributes, we can also add operations (or methods) to the Employee class. Like a function to dynamically calculate the age (based on the birthdate and the current date), or a function to return the full name (based on FirstName, MidInit and LastName of course). To design the later, right-click on the Employee class again, and this time add an operation called FullName with a result of type String. Note that Delphi will automatically adds () to the FullName, to indicate that it's a method. The Employee class is now designed as shown in Figure 9.

Figure 9. Employee Class
Employee Class

To implement the FullName() operation, right-click on the FullName() operation in the class and select Go to Definition, which will bring you to the source line in the CoreClasses.pas unit for the FullName() function definition. Hold down the Ctrl-key and click on the FullName definition to jump to the implementation. Here, you can write the following simple code (note that the internal attribute fields have an underscore prefix):

Listing 1. Implementing the FullName() Operation

function Employee.FullName(): String;
Result := _FirstName + #32 + _MidInit + #32 + _LastName;

I leave it as exercise for the reader to check if _MidInit is empty before placing it in the middle of the FullName result.

Designing Department

Now, return to the UML diagram in order to design the Department class. Right-click on the designer area and add a new ECO Class called Department. You can skip the DEPTNO field (just like the EMPNO field), so start with the DeptName field of type String. The MGRNO field is actually a link back to the EMPLOYEE table, and we'll deal with that in a minute when adding associated, so skip that field as well. Same with the ADMRDEPT field, which is actually a link from the EMPLOYEE table back to itself. The only field you need to add is the LOCATION field of type String.

Adding Association Relations

With the Employee and Department classes being designed, we must now design the associations between them (which were implicitly defined using the EMPNO and WORKDEPT fields of the EMPLOYEE table, and the DEPTNO, MGRNO, and ADMRDEPT fields of the DEPARTMENT table). The WORKDEPT field of the EMPLOYEE table obviously pointed to the DEPARTMENT table, using the DEPTNO field to link to. This can be replaced by an association from Employee to Department. In the Tool Palette, select the Association element, then click on the Employee class, and drag to the Department class. This will draw an association between Employee and Department. Use the Object Inspector to set the name of the new association to Employment.

An association has two ends: one at the Employee side and one at the Department side, both set to 0..1 multiplicity. Based on the fact that the WORKDEPT field in the EMPLOYEE table pointed to the DEPTNO field in the DEPARTMENT table, I think it's safe to assume that an Employee can link to only one Department (or none, since WORKDEPT is a nullable field), so that multiplicity can remain at 0..1. For the Department, it means that zero or more Employees can be linked to it. So at the Employee side, the multiplicity must change from 0..1 to 0..* instead.

Now we must add the link back from the Department class to the Employee class, implementing the MGRNO field. This time the multiplicity must be set so that a Department is managed by 0 or 1 Employees (MGRNO is nullable), and an Employee can in fact manage zero or more Departments.

Only one relationship left: replacing the ADMRDEPT field, which is between the Department class and itself (one Department is managed by another, which in turn can manage multiple Departments). This is done by an association between Department and itself. The final UML Model can be seen in Figure 10.

Figure 10. Employee-Department UML Model
Employee-Department UML Model

Although we no longer have the EMPNO and WORKDEPT fields (in the EMPLOYEE table) nor the DEPTNO, MGRNO, and ADMRDEPT fields (in the DEPARTMENT table), the new UML Model can store the same information through the explicit association relations.

Back to top

Making the Model Persistent

Time to make the new UML Model - aka the EcoSpace - persistent. Return to the ECO2DB2EcoSpace unit where the PersistenceMapperBdp and BdpConnection component are waiting to be connected to the UML Model in the EcoSpace. We must now generate the required tables for the EcoSpace and add them to the empty DB2 UDB ECO2 database. This can be done by clicking on the left-most button at the bottom of the EcoSpace designer, which says Generate Schema. After you click on the button, the application is recompiled, and a dialog is shown to display which tables can (optionally) be deleted, which must be dropped and recreated, and which new tables will be created in the database, as shown in Figure 11.

Figure 11. Generate Schema
Generate Schema

After you click on the OK button, the seven new tables are added to the DB2 UDB ECO2 database. Note that apart from the EMPLOYEE and DEPARTMENT tables, we get five ECO specific tables that are needed for the EcoSpace management itself.

There is only one step left: building the user interface on top of the EcoSpace.

Back to top

Building the User Interface

The user interface is build on the WinForm, which can be found in the Winform.pas unit. When you click on the Design tab for the WinForm unit, you see five components already present in the non-visual component area of the designer: rhRoot, EcoGlobalActions, EcoAutoForms, EcoListActions, and EcoDragDrop. We'll use all of them (sometimes implicitly), so don't remove them!

We need to add another component, namely an ExpressionHandler, which can be found in the Enterprise Core Objects category of the Tool Palette. Set the RootHandle property of the ExpressionHandler to point to the rhRoot component, and call the ExpressionHandler ehEmployee, since this one will be used to work with the Employee class instances. Each ExpressionHandler needs an OCL Expression to produce data. Double-click on the Expression property to display the OCL Expression Editor, and use this dialog to produce the expression Employee.allInstances, as shown in Figure 12.

Figure 12. OCL Expression Editor
OCL Expression Editor

Click on OK to close the OCL Expression Editor. Now we need something to display the result of the OCL Expression. Returning .allInstances means that we get a list back, so we need a control that can display a list of items, such as the DataGrid. Place a DataGrid control on the WinForm, and point its DataSource property to ehEmployee to display all instances of the Employees class.

We now need to place some buttons to create new Employees, and to save the entire EcoSpace (in the Db2 UDB ECO2 database). Place three Button components below the DataGrid, and set their Text property to New, Delete, and Save respectively. Instead of writing code (which we did in 2004 when we used Enterprise Core Objects in Delphi 8 for .NET), we can use the fact that the EcoGlobalActions component makes it possible to connect regular Button controls to Eco (Global) Actions. These actions can be found in the ECO | GUI category in the Object Inspector, which contains the EcoActions property. If you open the drop-down combobox for this property in the Object Inspector, you see the following possible values: UpdateDatabase, Undo, Redo, SetCheckPoint, ToggleActive, CreateSchema, and EvolveSchema. The UpdateDatabase action is the one we need to select for the Save Button. This will update the EcoSpace inside the DB2 UDB ECO2 database, as required.

For the New and Delete Button components, we need to use other ECO Actions, namely ECO List Actions, which are introduced by the EcoListActions component on the form. In the ECO | ListAction category of the Object Inspector, you'll see the BindingContext property (which needs to point to the GUI controls like a DataGrid), the EcoListAction property (which can be Add, Delete, and several Move actions, Unlink, Reload, ShowAutoForm, MoveUp and MoveDown), and the RootHandle property. For the Add and Delete Button, we must point the BindingContext to the DataGrid component that shows the Employee instances, the EcoListAction to Add and Delete respectively, and the RootHandle to ehEmployee; the ExpressionHandle that returns all instances of the Employee class.

The ShowAutoForm action shows a special data entry form for one class instance. But apart from the EcoListActions, we can also control this by setting the EcoAutoForm property of the DataGrid component to True. This will make sure that you get an ECO AutoForm as soon as you double-click on a record in the DataGrid. The EcoAutoForms component in the non-visual area of the WinForm designer made sure we can set this property, by the way. The complete WinForm with all ECO components at design-time can be seen in Figure 13.

Figure 13. ECO Application at design-time
ECO Application at design-time

Feel free to add another ExpressionHandle and DataGrid connecting to all instances of the Department class. Next time, I'll show how to add the relations between the two classes to finish the example.

Time to save your work and compile the application. Once you run it, you can click on the New Button to create new Employee instances and edit their values in the DataGrid. Apart from editing in the DataGrid, you can also double-click on the Employee in the DataGrid to get an AutoForm that can be used as an easier way to enter the Employee attribute values, see Figure 14 for an example of the Employee AutoForm.

Figure 14. Employee AutoForm
Employee AutoForm

Don't forget to click on the Save Button to save the contents of the EcoSpace in the DB2 UDB ECO2 database!

Back to top


In this article, we've used Borland Delphi 2005 and the Enterprise Core Objects (ECO) to import an DB2 UDB database and create an UML object model for it. WE also created an empty DB2 UDB database, and a new UML Object Model. The object model (called EcoSpace) used the DB2 database as persistence layer, and was used to connect to user controls (like DataGrids), offering autoforms (for individual items), new, delete, save, etc. all on top of the DB2 UDB database tables.

Back to top

Next time

Next time we'll continue with the exploration of Enterprise Core Objects and Model Driven Development using the DB2 UDB SAMPLE database, this time producing ASP.NET Web Forms applications. Apart from the move to the web, I will also demonstrate how to add the association relations between the Employee and Department classes next time, so we'll end up with a more useful application.

Back to top