Producing dynamic data-entry forms from DB2 tables on Linux

Age : 34 Joined : 08 Mar 2008 Posts : 1264

Introduction

In this article, I'll examine IBM® DB2® Universal Database™ (UDB) metadata in order to dynamically build views and forms. Specifically, I will use Borland® Kylix 3™ on Linux and the Borland dbExpress data access drivers to analyze the 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 is a pure runtime approach; no further coding is necessary. In a follow-up article, I'll use Borland Delphi™ to take this approach one step further by dynamically generating forms and adding them to the project at design-time (our very own database form wizard).

Starting from scratch

Unlike previous articles using Kylix with DB2, where you've built the entire application at design time with the IDE, you only need to start with a connection to the DB2 UDB database. The other steps are done in code, using meta information obtained from the database itself.

In order to build the connection to the DB2 UDB SAMPLE database, you need to use a TSQLConnection component. Start a new Kylix 3 project and place a TSQLConnection component on the form. Right-click on the SQLConnection component to edit the connection properties. The DriverName should be DB2, the Database name should be SAMPLE (or any other database name - the code in this article will work with any existing DB2 UDB database). Also specify the User_Name and Password; otherwise, you need to enter them at run time in the login dialog. You can test the validity of the connection properties by clicking on the "check" button, as can be seen in the following screenshot:

Preparing at design-time

Once the TSQLConnection component is set up correctly, you can add the other dbExpress components, and hook them up to each other, but without specifying a SQL statement, or adding any view data-aware controls. As the first additional component, place a TSQLDataSet component on the form, next to the TSQLConnection component. Point the SQLConnection property of the TSQLDataSet to the TSQLConnection component. Then, place a TDataSetProvider on the form, and point its DataSet property to the TSQLDataSet component.

This component is followed by a TClientDataSet component, with the ProviderName property set to the name of the TDataSetProvider component. Finally, drop a TDataSource component on the form, and connect its DataSet property to the TClientDataSet component. This will give you the TSQLConnection - TSQLDataSet - TSQLDataSetProvider - TClientDataSet - TDataSource chain that you've seen before when using Borland dbExpress with DB2 UDB database tables.

The main difference this time is that you don't specify a SQL statement inside the SQLDataSet component. Nor do you place any visual data-aware components on the form, since all that will be done dynamically at run time, based on metadata found in the DB2 UDB database and the user selections for tables and fields.
Retrieving TableNames

But first things first: let's retrieve the available tablenames from the database. Assuming the SQLConnection component can make a connection to the DB2 UDB database (either the SAMPLE database or any other database in this case), you only have to Open the connection explicitly, and you can then call the GetTableNames method. This method expects a TStrings variable as the first argument. Instead of declaring and using our own TStringList for this, you can assign the results directly to the ListBox's Items property, as shown in the code snippet below:

procedure TForm1.FormCreate(Sender: TObject);
begin
SQLConnection1.Connected := True;
SQLConnection1.GetTableNames(ListBox1.Items, False);
PageControl1.ActivePageIndex := 0
end;

The result of the code which is executed when the form is created (that is, in the OnCreate event handler) is that the TListBox control is filled with all available tables right when the application starts.
Analyzing field definitions

The next step is up to the end user. In order to select a specific table, and display the available fields, the end user has to double-click on the table in the ListBox control. This will fire an event handler that takes the tablename from the ListBox and retrieves the metadata for that table. From that metadata, you need to use the list of fieldnames, and place them in the second listbox, the TCheckListBox.

In order to do this, you can use the TSQLDataSet component, set the CommandType to ctTable and the CommandText to the name of the selected table from the first TListBox control (which can be found at ListBox1.Items[ListBox1.ItemIndex]). Then, it's important not to activate (or open) the TSQLDataSet, since that will send a select * from your selected tablename to the database, which is not what you want at this time. Instead, you should call the TSQLDataSet.FieldDefs.Update method, which will only retrieve the metadata for the field definitions, and not the actual record data for the selected table itself.

Using this field definition metadata, you can then loop through the individual field definitions and place the name of the field in the TCheckListBox (you could also include the type of the field, but that's something you'll use in the last step, when you'll create a suitable data-aware control for the selected field).

The source code snippet to retrieve the field definitions and update the TCheckListBox as shown below:

procedure TForm1.SelectTableClick(Sender: TObject);
var
i: Integer;
begin
ClientDataSet1.Active := False;
SQLDataSet1.CommandType := ctTable;
if ListBox1.ItemIndex >= 0 then
SQLDataSet1.CommandText := ListBox1.Items[ListBox1.ItemIndex];
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;

Note that you clear the TCheckListBox before you add new fieldnames to it, so the end user can continue to double-click on different tablenames and see new lists of fields appear in the TCheckListBox each time.

The following screenshot shows the effect of first double-clicking on the EMPLOYEE table in the TListBox on the left side of the form, and then checking the checkbox for the first 11 fields in the TCheckListBox on the right side of the form.

Generating dynamic SQL statement

The OnClick event handler for the Generate button (which can be seen in Figure 3) starts by setting the CommandType property of the TSQLDataSet to ctQuery. This time, you don't need to specify the TableName in the CommandText property, but the actual SQL command to select the checked fields from the specified table. You start by assigning SELECT to the CommandText property, and then loop through the items in the TCheckListBox control. Note that you do not need to retrieve the metadata from the selected table again, since the corresponding fieldnames were already added to the TCheckListBox, so you can just use the fieldnames from that control (and merely have to verify if their checked property is set to True in order to use each individual fieldname).

The variable comma in the code snippet below is needed to ensure that a comma is inserted before each field except for the very first field. That will result in a valid SQL statement with either one or more fields in the selected fields list.

procedure TForm1.btnGenerateClick(Sender: TObject);
var
i,Y: Integer;
comma: Boolean;
begin
ClientDataSet1.Active := False;
SQLDataSet1.CommandType := ctQuery;
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 ListBox1.ItemIndex >= 0 then
SQLDataSet1.CommandText :=
SQLDataSet1.CommandText + ' FROM ' + ListBox1.Items[ListBox1.ItemIndex];
//ShowMessage(SQLDataSet1.CommandText);

ClientDataSet1.Active := True;

PageControl1.ActivePageIndex := PageControl1.ActivePageIndex + 1
end;

When the list of fields is complete, you only need to add the FROM string, followed by the name of the database table, which can still be found in the TListBox control. After that step, you can set the Active property of the TClientDataSet component to True, which will activate the SQL command in the TSQLDataSet component and make the data available.
Generating dynamic data-aware contols

Right after the TClientDataSet is activated, you can again walk through the set of field definitions; this time not only to obtain the field names, but also the field types. Depending on the field type, you can generate different data-aware controls to represent the field values. As an example, the code below creates a TDBMemo control if the field is an ftMemo type, or a regular TDBEdit control otherwise. This can be extended for image or boolean fields as well, of course (you'll see more examples in the next article).

Based on the FieldDefs.DataType value, you either create a TDBMemo or a TDBEdit, passing the Self pointer as parameter to the constructor (the Form itself). This means that the Form will be the owner (of the memory) of this control. You also need to set the Parent property of the new control in order to specify the visual container to show the control. In this case, that's the third tabsheet of the PageControl, or TabSheet3.

You then need to assign values to the Left, Top, and Width properties to position the control, and finally the DataSource and DataField properties. As DataField, you can use the name of the field from the FieldDefs array again.

Y := 52;
for i:=0 to Pred(ClientDataSet1.FieldDefs.Count) do
begin
with TLabel.Create(Self) do
begin
Parent := TabSheet3;
Left := 8;
Top := Y;
Width := 110;
Alignment := taRightJustify;
Caption := ClientDataSet1.FieldDefs[i].Name
end;
if (ClientDataSet1.FieldDefs[i].DataType = ftMemo) then
begin
with TDBMemo.Create(Self) do
begin
Parent := TabSheet3;
Left := 126;
Top := Y - 4;
Y := Y + 82;
Width := 200;
DataSource := DataSource1;
DataField := ClientDataSet1.FieldDefs[i].Name;
end
end
else
with TDBEdit.Create(Self) do
begin
Parent := TabSheet3;
Left := 126;
Top := Y - 4;
Y := Y + 22;
Width := 200;
DataSource := DataSource1;
DataField := ClientDataSet1.FieldDefs[i].Name;
end
end; Summary

In this article, you've examined IBM DB2 Universal Database metadata in order to dynamically build views and forms. Specifically, I have used Borland Kylix 3 on Linux and the dbExpress data access drivers to analyze the 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.