Use the DB2 table to generate a dynamic data input form for Delphi

This article examines the metadata of IBM DB2 Universal Database to dynamically build views and forms, including how to dynamically generate a new clx form and convert the form stream to Delphi. PAS and. XFM files and add them to the kylix and Delphi clx projects.

Introduction
In the previous article, I checked the metadata of IBM DB2 Universal Database (UDB) to dynamically build views and forms. I used Borland kylix 3 and dbexpress Data Access drivers on Linux to analyze the IBM DB2 UDB database tables and fields (names and types) and allow users to select specific tables, switch back and forth the fields to be displayed, and dynamically view the output in the data grid and a single data sensing control.

This time, we will extend this method to allow users to specify more complex queries (such as join ). As an additional step, we will generate a separate form and save it in. PAS and. XFM source format. Therefore, we can add the result form to the Borland Delphi or kylix project. This is similar to the original database-Form Wizard Based on BDE (and only used for Windows), but this time generates a cross-platform clx form.

Migrate from Linux
Last time, I completed a kylix 3 project running on Linux. This time, I first need to migrate the project to Windows and expand it there (it indicates that the project and the resulting clx form are cross-platform, it can be used for kylix on Linux or Delphi on Windows ).

Migrating this project from kylix to Delphi involves only one change: The tsqlconnection attribute of libraryname and vendorlib is set to libsqldb2.so and libdb2.so on Linux, but on windows, this attribute must be set to dbexpdb2.dll and db2cli. DLL. For instructions on how to create a cross-platform project, see big transformation: migrate from windows to Linux using kylix 3.

Add SQL function
Once you can re-open the project in Delphi 7, you can enhance the meta data tab of page control. Specifically, you must allow users to enter SQL statements, rather than selecting the table name from the list of available tables. Figure 1 shows the new user interface (fields in the emp_photo table are displayed). You can enter an SQL statement using the memo field in the lower left corner.

Figure 1. New Dynamic forms for DB2

Just above the tmemo control, I placed the textSelect * from, as a small prompt: you only need to specify the table name and the following optionalWhere clause. You can use code to construct SQL statements, retrieve metadata (metadata) to obtain field names, and place these field names in tchecklistbox. The Code is as follows: 

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 an actual example, I entered an SQL statement to connect the table "employee" and "emp_photo". This operation is based on the empno field. BecauseSelect * from, so I only need to inputEmp_photo, employee where emp_photo.empno = employee. empno, and then enableSelect fields from query check box, which will execute the above Code to generate a list of fields in tchecklistbox.

Figure 2. Select a field from the query

Note that the empno field appears twice: one is in the emp_photo table (called empno), and the other is in the employee table (then automatically called empno_1 ).

In the instance aboveWhere clause. I select the picture, firstnme, lastname, phoneno, hiredate, and sex fields from the query.

To provide real data for DBGrid and other data-aware controls, you need to add some code to get the table name (from tlistbox) orWhere clause (from the tmemo control ). This decision is based on the check box activation, and its encoding is 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 ensures that you can use tlistbox with the table name or tmemo with the SQL WHERE clause to select fields and generate data for the view.

Add more Widgets
The picture field is selected in Figure 2. Last time, I made a distinction between the memo fields represented by tdbmemo, although all other fields are represented by tdbedit. For image fields that can be ftgraphic or ftblob (in this instance), you can use the tdbimage control. Note that ftblob is not exactly the case: Most BLOB fields only contain binary data, not images. However, in this example, the ftblob picture field contains an image (in different formats, such as BMP or GIF ).

Some additional code must be added to check the field type and respond to this by dynamically creating tdbimage. These additional code snippets are as follows:

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;

In the example where you have selected the picture, firstnme, lastname, phoneno, hiredate, and sex fields, result 3 of the Code is shown.

Figure 3. Dynamic Data Control Preview

Note that there is an additional button here, which will be used to re-create these controls from the tabsheet on the new clx form. This form will be streamed into the Delphi. Pas source file and. XFM Form file, and you are ready to add them to the kylix or Delphi clx project.

Design a new form framework
Although viewing data in tabsheet has excellent results (in 3), in the new form (different from the table name or query you specified earlier, and select the form of the field name to be used) to view the control will have more powerful functions. You must re-create the tlabel, tdbedit, tdbmemo, and tdbimage controls, which means that you can start from an almost empty frame form and prepare to create a new dbexpress connection to the DB2 UDB database, enter an SQL statement and place the data-aware control to display the data.

Through Delphi 7, I designed a new clx form, which contains the tsqlconnection, tsqldataset, tdatasetprovider, tclientdataset, tdatasource, and tdbnavigator controls. Its layout is shown in Figure 4. As you have done many times before, they are all associated, but there is no data-aware control yet.

Figure 4. New form template during design

Now we should implement the onclick event handler for the generate code button that appears for the first time in figure 3.

Generate new Form Content
The generate code button will create an instance of the new clx framework form, and dynamically add tlabel, tdbedit, tdbmemo, and tdbimage controls (using the same attribute value as the original form ). Use the following code to create a new clx form and clone the control that will be placed on the new clx form:

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 the event handler, newform is displayed by calling showmodal, as shown in result 5. Because tclientdataset is activated before the form is displayed (activation is canceled when the form is closed), the new form with data is displayed.

Figure 5. New Form at runtime

After the form is displayed, you can create a Delphi. XFM file that contains streaming information of the form, all its components, and their attributes. Call writecomponentresfile to create a Binary source file for the form. This file can be used for kylix or Delphi clx projects, but also requires the corresponding. Pas file, which contains the source code declaration of each component on the form.

Generate relevant source code
The last step (also integrated in the "generate code" onclick event handler) is to create the corresponding. Pas file containing the component declaration. This is a process composed of three phases. First, you need to write the first part of the. Pas file, dynamically add a single tlabel, tdbedit, tdbmemo, and tdbimage control, and finally 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;

Complete source code integration is included in the event handler and can be obtained through download.

Result
The result is a. Pas file and the corresponding. XFM file, which can be used in Linux kylix and Windows Delphi clx projects. For example, a result form for the given demo is provided, as shown in figure 6. This figure shows the result form displayed by Delphi 7 during design. The form is the same as the initial frame form, but the tlabel, tdbimage, and tdbedit controls are added. In fact, the height has been modified to display all the controls.

Figure 6. New Form generated during design

As I mentioned last time, the generated form does not contain buttons that call the applyupdates method of tclientdataset (used to send updates back to the database), undolastchange, and other undo methods. This will be done as an exercise for the reader. As a small tip, we recommend that you add these buttons to the frame form in newform. Therefore, your modifications will be used in all newly generated forms. In this way, newform can be considered as a base class for all dynamically created database forms.

Conclusion
In this and previous articles, the IBM DB2 Universal Database metadata is checked to dynamically build views and forms. You have used Borland kylix 3 on Linux with dbexpress and Delphi 7 on Windows to analyze DB2 UDB data tables and fields (names and types ), you can select a specific table or the join/WHERE clause of a specified SQL statement, switch back and forth the fields to be displayed, and dynamically view the output in the data grid and a single data sensing control.

The last step includes dynamically generating a new clx form and streaming the form into Delphi. PAS and. XFM files, and are ready to add them to kylix and Delphi clx projects (generate our own database Form Wizard for DB2 UDB database tables ).