Friday, September 12, 2008

Producing dynamic data-entry forms from DB2 tables for Delphi

Original Source click here

Level: Intermediate

Bob Swart (drbob@chello.nl), Author, Trainer, Consultant, and Webmaster, Bob Swart Training and Consultancy

05 Aug 2004

This article examines IBM DB2 Universal Database metadata in order to dynamically build views and forms, including how to dynamically generate a new CLX form and stream the form as a Delphi .pas and .xfm file, ready to be added to Kylix and Delphi CLX projects .
Show developerWorks content related to my search: DelphiShow developerWorks content related to my search: Delphi


Introduction

In my last article, I examined IBM® DB2® Universal Database™ (UDB) metadata in order to dynamically build views and forms. I used Borland® Kylix™ 3 on Linux and the dbExpress data access drivers to analyze the IBM DB2 UDB database tables, fields (names and types) to allow the user to select a specific table, toggle which fields should be shown, and dynamically view the output in both a datagrid and individual data-aware controls.

This time, we'll extend this approach by allowing the user to specify more complex queries (such as JOINs). As an additional step, we'll produce a stand-alone form and save this in .pas and .xfm source format, so the resulting form can be added to a Borland Delphi™ or Kylix project. This is similar to the original BDE-based (and Windows only) database-form wizard, but this time producing a cross-platform CLX form.



Back to top


Migrating from Linux

Last time, I ended with a Kylix 3 project, which runs on Linux. This time, I want to start with migrating this project to Windows, and extending it there (to illustrate the fact that the project and the resulting generated CLX forms are cross-platform, and can be used in both Kylix on Linux and Delphi on Windows).

Migrating the project from Kylix to Delphi only involves one change: the TSQLConnection properties for the LibraryName and VendorLib are set to libsqldb2.so and libdb2.so on Linux, but must be set to dbexpdb2.dll and db2cli.dll on Windows. For instructions on how to create a cross-platform project, see The Big Switch: Moving from Windows to Linux with Kylix 3.



Back to top


Adding SQL capabilities

Once you can reopen the project in Delphi 7, you can make some enhancements to the Meta Data tab of the page control. Specifically, you want to allow the user to enter an SQL statement instead of only being able to select a tablename from the list of available tables. Figure 1 shows the new user interface (showing the fields for the EMP_PHOTO table), with a memo field that you can use to enter a SQL statement at the lower left corner.


Figure 1. New dynamic forms for DB2.
Figure 1. New dynamic forms for DB2

Just above the TMemo control, I've placed the text SELECT * FROM, as a little hint that the user only has to specify the tablenames and, optionally, a where clause here. The code to construct the SQL statement and retrieve the metadata in order to get the fields names and place them in the TCheckListBox can be seen below:

procedure TDBWizForm.QCheckBoxClick(Sender: TObject);
var
i: Integer;
begin
if QCheckBox.Checked then
begin
ClientDataSet1.Active := False;
SQLDataSet1.CommandType := ctQuery;
SQLDataSet1.CommandText := 'select * from ' + QMemo.Text;
SQLDataSet1.FieldDefs.Update; // get meta information
CheckListBox1.Clear;
for i:=0 to Pred(SQLDataSet1.FieldDefs.Count) do
CheckListBox1.Items.Add(SQLDataSet1.FieldDefs[i].Name)
end
end;

As a practical example, I've entered a SQL statement to join the EMPLOYEE and EMP_PHOTO tables, based on the EMPNO fields. Since the SELECT * FROM is already given, I only have to enter the EMP_PHOTO, EMPLOYEE WHERE EMP_PHOTO.EMPNO = EMPLOYEE.EMPNO in the query memo field, and then check the Select fields from query checkbox, which will execute the code above to produce the list of fields in the TCheckListBox.


Figure 2. Select fields from query.
Figure 2. Select fields from query.

Note that the EMPNO field appears twice: once from the EMP_PHOTO table (named EMPNO) and once from the EMPLOYEE table (then automatically named EMPNO_1).

In the above example, I've selected the PICTURE, FIRSTNME, LASTNAME, PHONENO, HIREDATE, and SEX field from the query with the where clause as specified in the lower left corner.

In order to produce the actual data for the DBGrid and other data-aware controls, you need to add some code to either take the TableName (from the TListBox) or the where clause (from the TMemo control). The decision is based on the check of the checkbox, and is coded as follows:

  SQLDataSet1.CommandText := 'SELECT ';
comma := False;
for i:=0 to Pred(CheckListBox1.Items.Count) do
begin
if CheckListBox1.Checked[i] then
begin
if not comma then comma := True
else
SQLDataSet1.CommandText := SQLDataSet1.CommandText + ', ';
SQLDataSet1.CommandText := SQLDataSet1.CommandText + CheckListBox1.Items[i]
end
end;
if QCheckBox.Checked then
SQLDataSet1.CommandText :=
SQLDataSet1.CommandText + ' FROM ' + QMemo.Text
else
if ListBox1.ItemIndex >= 0 then
SQLDataSet1.CommandText :=
SQLDataSet1.CommandText + ' FROM ' + ListBox1.Items[ListBox1.ItemIndex];

This makes sure that you can use either the TListBox with tablenames, or the TMemo with the SQL where clause, to select the fields and produce the data for views.



Back to top


Adding more controls

One of the fields that you've selected in Figure 2 is the PICTURE field. Last time, I made a distinction between Memo fields that are represented by a TDBMemo, while all other fields are represented by a TDBEdit. For an image field, which can be ftGraphic or - in this example - an ftBlob, you can use a TDBImage control. Note that the case for the ftBlob is not a 100 percent certainty: most BLOB fields contain just binary data, not always images. But for our example, the ftBlob PICTURE field contains images (in different formats such as bmp or gif).

The additional code snippet that has to be added to detect the field type and respond to it by dynamically creating a TDBImage is shown below:

    if (ClientDataSet1.FieldDefs[i].DataType = ftGraphic) or
(ClientDataSet1.FieldDefs[i].DataType = ftBlob) then
begin
with TDBImage.Create(Self) do
begin
Parent := TabSheet3;
Left := 126;
Top := Y - 4;
Y := Y + 204;
Height := 200;
Width := 200;
DataSource := DataSource1;
DataField := ClientDataSet1.FieldDefs[i].Name;
end
end

The result of this code in the example where you selected the PICTURE, FIRSTNME, LASTNAME, PHONENO, HIREDATE, and SEX fields can be seen in Figure 3.


Figure 3. Dynamic data controls preview.
Figure 3. Dynamic data controls preview.

Note that there's an additional button here that will be used to recreate the controls from this tabsheet on a new CLX form. One that will be streamed out - as both a Delphi .pas source file and an .xfm form file; ready to be added to a Kylix or Delphi CLX project.



Back to top


Designing the new form skeleton

While it's nice to see the data in the tabsheet (as in Figure 3), it would be more powerful to see the controls in a new form - separate from the original form where you specified the tablename or query and selected the field names to use. You would have to recreate the TLabel, TDBEdit, TDBMemo, and TDBImage controls, which means you can start with an almost empty skeleton form that is ready to create a new dbExpress connection to the DB2 UDB database, enter the SQL statement, and place the data-aware controls to show the data.

Using Delphi 7, I've designed a new CLX form with a TSQLConnection, TSQLDataSet, TDataSetProvider, TClientDataSet, TdataSource, and finally a TDBNavigator control; see Figure 4 for the layout. They are all hooked up as you've done several times before, but without any data-aware controls at this point.


Figure 4. New form template at design time.
Figure 4. New form template at design time.

Now it's time to implement the OnClick event handler for the Generate Code button that was first shown in Figure 3.



Back to top


Generating the new form contents

The Generate Code button will create an instance of the new CLX skeleton form, and dynamically add the TLabel, TDBEdit, TDBMemo, and TDBImage controls (using the same property values as in the original form). The code to create the new CLX form and clone the controls to be placed on the new CLX form is shown below:

procedure TDBWizForm.btnWizardClick(Sender: TObject);
var
ThisForm: TDBWizForm;
NewForm: TNewForm;
LabelNr,EditNr,MemoNr,ImageNr,i: Integer;
begin
ThisForm := Self;
NewForm := TNewForm.Create(Self);
try
NewForm.Caption := 'Dr.Bob''s Database Form Wizard';
NewForm.SQLConnection1.Params.Clear;
NewForm.SQLConnection1.Params.Assign(ThisForm.SQLConnection1.Params);
NewForm.SQLDataSet1.CommandText := ThisForm.SQLDataSet1.CommandText;

LabelNr := 0;
EditNr := 0;
MemoNr := 0;
ImageNr := 0;
for i:=0 to Pred(ThisForm.ComponentCount) do
begin
if (ThisForm.Components[i] is TLabel) then
begin
with TLabel.Create(NewForm) do
begin
Inc(LabelNr);
Name := Format('Label%d',[LabelNr]);
Parent := NewForm;
Left := (ThisForm.Components[i] as TLabel).Left;
Top := (ThisForm.Components[i] as TLabel).Top;
Width := (ThisForm.Components[i] as TLabel).Width;
Alignment := taRightJustify;
Caption := (ThisForm.Components[i] as TLabel).Caption
end
end
else
if (Components[i] is TDBEdit) then
begin
with TDBEdit.Create(NewForm) do
begin
Inc(EditNr);
Name := Format('Edit%d',[EditNr]);
Parent := NewForm;
Left := (ThisForm.Components[i] as TDBEdit).Left;
Top := (ThisForm.Components[i] as TDBEdit).Top;
Width := 200;
DataSource := NewForm.DataSource1;
DataField := (ThisForm.Components[i] as TDBEdit).DataField
end
end
else
if (Components[i] is TDBMemo) then
begin
with TDBMemo.Create(NewForm) do
begin
Inc(MemoNr);
Name := Format('Memo%d',[MemoNr]);
Parent := NewForm;
Left := (ThisForm.Components[i] as TDBMemo).Left;
Top := (ThisForm.Components[i] as TDBMemo).Top;
Height := 200;
Width := 200;
DataSource := NewForm.DataSource1;
DataField := (ThisForm.Components[i] as TDBMemo).DataField
end
end
else
if (Components[i] is TDBImage) then
begin
with TDBImage.Create(NewForm) do
begin
Inc(ImageNr);
Name := Format('Image%d',[ImageNr]);
Parent := NewForm;
Left := (ThisForm.Components[i] as TDBImage).Left;
Top := (ThisForm.Components[i] as TDBImage).Top;
Height := 200;
Width := 200;
DataSource := NewForm.DataSource1;
DataField := (ThisForm.Components[i] as TDBImage).DataField
end
end
end;

try
NewForm.ClientDataSet1.Active := True;
except
end;
NewForm.ShowModal; // See Figure 5.
NewForm.ClientDataSet1.Active := False;
NewForm.SQLConnection1.Connected := False;
WriteComponentResFile(UnitName+'.xfm', NewForm);
finally
NewForm.Free
end
end;

At the end of this event handler, the NewForm is shown using the call to ShowModal, which results in the screen shown in figure 5. This will present the new form with data, since the TClientDataSet is activated just before the form is shown (and deactivated when the form is closed again).


Figure 5. New form at runtime.
Figure 5. New form at run time.

After the form is shown, you can create a Delphi .xfm file that contains the streaming information for the form, all components on it, and their property values. The call to WriteComponentResFile will create a binary resource file for the form. This can be used by a Kylix or Delphi CLX project, but also requires a corresponding .pas file with source code declarations for each component on the form.



Back to top


Generating the associated source code

The final step - also integrated in the "Generate Code" OnClick event handler - is the creation of the corresponding .pas file with the component declarations. This is a three-phase process. First, you write the first part of the .pas file, followed by the dynamic addition of the individual TLabel, TDBEdit, TDBMemo, and TDBImage controls, and finally you add the last part of the .pas file.

procedure TDBWizForm.btnWizardClick(Sender: TObject);
var
f: System.Text;
LabelNr,EditNr,MemoNr,ImageNr,i: Integer;
begin
System.Assign(f,UnitName+'.pas');
Rewrite(f);
writeln(f,'unit ',UnitName,';');
writeln(f,'interface');
writeln(f,'uses');
writeln(f,' SysUtils, Types, Classes, Variants, QTypes, QGraphics, QControls, QForms,');
writeln(f,' QDialogs, QStdCtrls, DBXpress, QCheckLst, DB, SqlExpr, QComCtrls, FMTBcd,');
writeln(f,' DBClient, Provider, QGrids, QDBGrids, QDBCtrls, QExtCtrls, QMask;');
writeln(f);
writeln(f,'type');
writeln(f,' TNewForm = class(TForm)');
writeln(f,' SQLConnection1: TSQLConnection;');
writeln(f,' SQLDataSet1: TSQLDataSet;');
writeln(f,' DataSetProvider1: TDataSetProvider;');
writeln(f,' ClientDataSet1: TClientDataSet;');
writeln(f,' DataSource1: TDataSource;');
writeln(f,' DBNavigator1: TDBNavigator;');

LabelNr := 0;
EditNr := 0;
MemoNr := 0;
ImageNr := 0;
for i:=0 to Pred(ThisForm.ComponentCount) do
begin
if (ThisForm.Components[i] is TLabel) then
begin
Inc(LabelNr);
Name := Format('Label%d',[LabelNr]);
writeln(f,' ',Name,': TLabel;');
end
else
if (Components[i] is TDBEdit) then
begin
Inc(EditNr);
Name := Format('Edit%d',[EditNr]);
writeln(f,' ',Name,': TDBEdit;');
end
else
if (Components[i] is TDBMemo) then
begin
Inc(MemoNr);
Name := Format('Memo%d',[MemoNr]);
writeln(f,' ',Name,': TDBMemo;');
end
else
if (Components[i] is TDBImage) then
begin
Inc(ImageNr);
Name := Format('Image%d',[ImageNr]);
writeln(f,' ',Name,': TDBImage;');
end
end;

writeln(f,' procedure FormCreate(Sender: TObject);');
writeln(f,' private');
writeln(f,' { Private declarations }');
writeln(f,' public');
writeln(f,' { Public declarations }');
writeln(f,' end;');
writeln(f);
writeln(f,'var');
writeln(f,' NewForm1: TNewForm;');
writeln(f);
writeln(f,'implementation');
writeln(f);
writeln(f,'{$R *.xfm}');
writeln(f);
writeln(f,'procedure TNewForm.FormCreate(Sender: TObject);');
writeln(f,'begin');
writeln(f,' try');
writeln(f,' ClientDataSet1.Active := True');
writeln(f,' except');
writeln(f,' end');
writeln(f,'end;');
writeln(f);
writeln(f,'end.');
System.Close(f);
end;

The complete source code integrated both event handler in one, and is available for download.



Back to top


Using the result

The result is a .pas and corresponding .xfm file that can be used by Kylix on Linux as well as Delphi CLX projects on Windows. As an example, the resulting form for the demo given so far, as shown by Delphi 7 at design time is shown in Figure 6. This form is the same as the original skeleton form, with the addition of the TLabel, TDBImage, and TDBEdit controls, and the fact that the Height has been changed to display all controls.


Figure 6. Generated new form at design time.
Figure 6. Generated new form at design time.

As I mentioned last time, the generated forms do not contain a button to call the TClientDataSet's ApplyUpdates method (in order to send updates back to the database) or the UndoLastChange and other Undo methods. That's left as an exercise for the reader. As a little hint, I would suggest adding these buttons to the skeleton form in NewForm, so the changes that you make will be used in all newly generated forms. In that aspect, the NewForm can be seen as base class for all dynamically created database forms.



Back to top


Summary

In both this, and the previous, article you've examined IBM DB2 Universal Database metadata in order to dynamically build views and forms. You have used Borland Kylix 3 on Linux and Delphi 7 on Windows with dbExpress to analyze the DB2 UDB database tables, fields (names and types) to allow the user to select a specific table or specify the join/where-clause of an SQL statement, toggle which fields should be shown, and dynamically view the output in both a datagrid and individual data-aware controls.

The last step involved dynamically generating a new CLX form and streaming this form as a Delphi .pas and .xfm file, ready to be added to Kylix and Delphi CLX projects (resulting in our very own custom database form wizard for DB2 UDB database tables).




Back to top



0 Comments: