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).

Back to top

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:

Back to top

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.

In order to separate the metadata information and selection from the dynamically built views with a data grid an data control, I've used a TPageControl component, as can be seen in the screenshot below:

The Meta Data TabSheet consists of a TListBox control on the left, which will be filled with the tablenames from the DB2 UDB SAMPLE database, and a TCheckListBox control on the right, which will be filled by the available fieldnames from the selected table.

The Generate button will prepare and execute the SQL statement, and place the resulting data in a grid (on the Data Grid tab) or in individual data-aware controls (in the Data Controls tab).

Back to top

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.

Back to top

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.

The next step consists of generating the SQL statement to select only the checked fields from the specified table (in this case EMPLOYEE), and then display the results in a datagrid as well as data-aware controls.

Back to top

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.

As an example, the second page of the PageControl can contain a TDBGrid control with the DataSource property pointing to the TDataSource component, as shown in the screenshot below:

This is still only a TDBGrid, and a more powerful display can be made in the last step, where you'll dynamically create and add data-aware controls.

Back to top

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;

Note that apart from the TDBMemo or TDBEdit, I'll also create a TLabel control at the left side of the data-aware control. The result of the selection of the first 11 fields of the EMPLOYEE table can be seen in the following screenshot:

In order to allow us to select another table, or another set of fields, you should also be prepared to remove the TLabel, TDBEdit, and TDBMemo controls that were added in a previous cycle. Since the only TLabel, TDBEdit, and TDBMemo controls that will exist on our form are the result of the previous code snippet (that is, there are no TLabel, TDBEdit, or TDBMemo controls that were added at design time), you can safely loop through the Components array of the form and Free all TLabel, TDBEdit, and TDBMemo controls.

  for i:=Pred(ComponentCount) downto 0 do // clean-up first (reverse count)
  begin
    if Components[i] is TLabel then (Components[i] as TLabel).Free
    else
    if Components[i] is TDBEdit then (Components[i] as TDBEdit).Free
    else
    if Components[i] is TDBMemo then (Components[i] as TDBEdit).Free
  end;

Note that you have to walk through the list of components backwards (back to 0), since removing one will otherwise decrease the count and potentially result in a crash. Also note that the else statements are needed, since a call to Free will make the next check to the same component index an invalid check.

Including this code snippet right before the call to activate the TClientDataSet will ensure that all previously added TLabel, TDBEdit, and TDBMemo controls will have been freed right before you create the next set of dynamically added controls.

The result, as shown in Figure 5, is a special view of all selected fields from the selected table, including a TDBNavigator (which was added at design-time for this example) to navigate through the records in the table. Note that there are no special buttons to call the ApplyUpdate, UndoLastChange, or RevertRecord methods from the TclientDataSet, in order to enable you to send updates back to the database, or undo edits done in the client application. Those options are left as an exercise for you; see the two articles in the references list for more information.

Back to top

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.

Next time

In a follow-up article, you'll learn how to use 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).

Back to top

Download

Name	Size	Download method
source.zip	7 KB	FTP | HTTP

Information about download methods

Resources

• The Big Switch: Moving from Windows to Linux with Kylix 3
  
  
• Kylix 3 for C++ and IBM DB2 UDB v8.1 on Linux
  
  

About the author

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Starting from scratch
• Preparing at design-time
• Retrieving TableNames
• Analyzing field definitions
• Generating dynamic SQL statement
• Generating dynamic data-aware contols
• Summary
• Download
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers