Friday, September 12, 2008

Use Delphi 8 visual controls that bind to DB2 data tables

Original Source click here

Level: Intermediate

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

08 Apr 2004

This article uses Delphi 8 for .NET and the data-aware controls from the Visual Component Library to bind to DB2 UDB tables.
Show developerWorks content related to my search: DelphiShow developerWorks content related to my search: Delphi


Introduction

In this article, I will use Borland® Delphi™ 8 for the Microsoft® .NET Framework (abbreviated to "Delphi 8 for .NET") and the data-aware controls from the VCL (Visual Component Library) to bind to IBM® DB2® Universal Database™ (UDB) tables. Then I'll build an SQL JOIN statement, and also show how to send updates to records that are the result of this JOIN.

Delphi 8 produces native .NET executables, but I will also show how to use Delphi 7 to compile the project to a native Win32 (non-.NET) executable.

In previous articles using Delphi 8 for .NET, I've used the Borland Data Provider (BDP) to build WinForms and ASP.NET Web Forms and Web Services applications. This time, the purpose is to build an application using Delphi 8 for .NET that can be compiled to a safe, managed .NET executable as well as a native Win32 executable. As data access technology, you'll use dbExpress, and as visual controls you'll use the VCL.

Note that you need to apply Update #2 of Delphi 8 in order to add the DB2 support to dbExpress in Delphi 8 for .NET (DB2 support is also available using the BDP for .NET when building Windows Forms or Web Forms applications, as shown in previous articles).



Back to top


Starting with VCL for .NET

Where the Windows versions of Delphi 1 through 7 include the VCL, Delphi 8 for .NET includes VCL for .NET. From a developer point of view, the use of the VCL and VCL for .NET is similar - so applications that make use of the VCL can be migrated from Win32 to .NET (and vice versa) with considerable ease.

To start a new VCL for .NET application with Delphi 8, simply do File | New - VCL Forms Application. Save the project in file DB2VCL. At the end of this article, you'll see how to modify the project (in only a few places) to turn it into a cross-platform project that can be used to produce not only a managed .NET executable but also a native Win32 executable.



Back to top


Connecting using dbExpress to DB2 UDB

Once the new VCL for .NET project is created, you need to build the connection to the DB2 UDB database. As usual, you'll work with the tables from the DB2 UDB SAMPLE database, which can be created using the "First Steps" application after you've installed DB2. This SAMPLE database contains a number of tables that offer different kinds of fields: from simple string, integer, and float fields, to formatted memos and image fields. This is a perfect way to test the capabilities of the data access layer, as well as the visual controls to display and work with these database tables and fields.

As you saw in the first article on using dbExpress with IBM DB2, you need a TSQLConnection component to connect to the SAMPLE database. So, place this component on the empty VCL Form, and right-click on it to start the dbExpress Connections Editor. In here, select the DB2Connection name, and configure it for the DB2 UDB SAMPLE database by specifying SAMPLE as Database value, and a valid user as User_Name. If you don't specify a password here, you'll get the password dialog in your application.

Before you actually use these settings, you can click on the Text button to test the connection (and make sure that the SAMPLE database tables can be used by the specified User_Name), as can be seen in Figure 1.


Figure 1. Successfully Connected in dbExpress Connection Editor
Figure 1. Successfully Connected in dbExpress Connection Editor

When you've verified that a connection can be made successfully, you can close the dbExpress Connections editor again.

Note from the figure above that I didn't enter a password in the Connections Editor, which means that the users of the application need to log in to the database themselves. This will be handled by a built-in Login dialog. In order to explicitly enable this dialog, you can set the LoginPrompt property of the SQLConnection component to True (see Figure 2).


Figure 2. SQLConnection in Object Inspector
Figure 2. SQLConnection in Object Inspector

In Figure 2, note the values for the LibraryName and VendorLib properties. The LibraryName points to the Delphi 8 for .NET dbExpress driver for DB2 UDB, while the VendorLib is the DB2 client DLL from IBM itself. You need to make sure these files are deployed to the client machine at deployment time (for more details, see the end of this article).



Back to top


Defining the query

When the SQLConnection component is configured and ready to be used, you can use a number of components to retrieve data from the DB2 UDB SAMPLE database. Specifically, you can use the TSQLQuery, TSQLTable or TSQLStoredProc components, or the more flexible TSQLDataSet component. The latter is a more generic component that can act as a Query, Table, or StoredProc based on the value of the CommandType property. This makes it a more powerful component than the TSQLQuery, TSQLTable, or TSQLStoredProc, so I always use the TSQLDataSet component instead.

Place a TSQLDataSet component on the form, and point its SQLConnection property to the SQLConnection component that is connected to the DB2 UDB SAMPLE database.

Now, set the CommandType property to ctQuery, and double-click on the CommandText property, which produces the CommandText Editor (see Figure 3). Here, you can specify a SQL query to retrieve all employees that have also provided a valid RESUME (in ASCII format) as well as a PICTURE (in bitmap format).


Figure 3. CommandText Editor
Figure 3. CommandText Editor

The SQL statement for our example application using the DB2 UDB SAMPLE database is as follows:

SELECT * FROM EMPLOYEE E
LEFT OUTER JOIN EMP_PHOTO P
ON (E.EMPNO = P.EMPNO)
LEFT OUTER JOIN EMP_RESUME R
ON (E.EMPNO = R.EMPNO)
WHERE
(R.RESUME_FORMAT = 'ascii') AND (P.PHOTO_FORMAT = 'bitmap')

Note that without the WHERE-clause, you would get all employees - even the ones that did not submit a resume or photo (due to the fact that I'm using a "left outer join"). However, that will also produce up to six records for people who submitted a resume and photo since it appears that resumes and pictures are available in three different formats, including plain ASCII for the resume, bitmap format for the picture (as well as GIF). Since the controls that you'll use in a moment can display text in ASCII format and images in bitmap format, I've added these selections to the WHERE-clause.

Obviously, in the end I could also have rewritten the SQL statement as follows (without using the left outer join):

SELECT * FROM EMPLOYEE E, EMP_PHOTO P, EMP_RESUME R
WHERE (E.EMPNO = P.EMPNO) AND (E.EMPNO = R.EMPNO)
AND (R.RESUME_FORMAT = 'ascii') AND (P.PHOTO_FORMAT = 'bitmap')

This approach is different compared to the first article using dbExpress (and Delphi 7) to work with DB2 tables, where I used TSQLDataSet to work with the EMPLOYEE table, and in the second article used another TSQLDataSet to work with the EMP_RESUME and EMP_PHOTO table, creating a master-detail relationship with the records in the ClientDataSets. This time, however, I wanted to define the relationship with an SQL JOIN statement (and also show how to update records that result from such an SQL JOIN statement).



Back to top


Retrieving the data

The dbExpress components (TSQLTable, TSQLQuery, and TSQLStoredProc, as well as TSQLDataSet) return a unidirectional, read-only dataset. Which is not a good way to display the data visually, since it means you would not be able to browse backwards in a list (or change any data in the tables).

Before you can actually use the result of the SQL statement, you need to add two more components: a TDataSetProvider and TClientDataSet. The DataSet property of the TDataSetProvider must be assigned to the TSQLDataSet component, and the ProviderName property of the TClientDataSet must be connected to the TDataSetProvider. The effect of this is that the read-only, unidirectional contents of the TSQLDataSet (with the resulting records of the LEFT OUTER JOIN) will be collected and placed inside the TClientDataSet component. The latter will act as an in-memory dataset that you can use as a very fast means to browse through the resulting records (and make modifications, as you'll see in a moment).

There's one more component you need as a gateway between the TClientDataSet and the visual data-aware controls, and that's the TDataSource component. The DataSet property of this component should point to the TClientDataSet component, and all visual data-aware components (covered in the next section) can connect their DataSource property to the TDataSource component in order to get access to the data from the SQL query on the DB2 UDB SAMPLE database.



Back to top


Building the user interface with VCL

You can now use the Data Controls from the Tools Palette that are included with VCL for .NET. There are several data controls, from the simple TDBEdit to the more advanced TDBCtrlGrid. The table below lists them all, including a short description.

Data ControlDescription
TDBGriddata-aware Grid (showing multiple fields or multiple records at the same time)
TDBNavigatordata-aware navigator (used to position "current record")
TDBTextdata-aware label, read-only, single field of current record
TDBEditdata-aware edit, single field, current record
TDBMemodata-aware memo field, multi-line edit, single field, current record
TDBImagedata-aware image, binary BLOB field, current record
TDBListboxdata-aware listbox with string items in Items property, single text field, current record
TDBComboboxdata-aware combobox with string items in Items property, single text field, current record
TDBCheckboxdata-aware checkbox, most often used for boolean fields, current record
TDBRadioGroupdata-aware radiogroup, with caption stored in Items and the actual values in Values, single text field, current record
TDBLookupListboxdata-aware listbox with values taken from another (lookup) table, single text field, current record
TDBLookupComboboxdata-aware combobox, with values taken from another (lookup) table, single text field, current record
TDBRichEditdata-aware edit/memo for RTF content
TDBCtrlGridcontrol grid (see paper for detailed usage example)

With the exception of the TDBGrid and TDBCtrlGrid controls (which work on entire records), all data-aware controls use the DataField property to point to the individual field of the dataset that is displayed. Obviously, the TDBNavigator also only operates on the dataset itself, because it's the means to navigate and not a control that displays data itself.

Like the TDBGrid, the TDBCtrlGrid can show more than one record. But where the TDBGrid shows each record on a single line, the TDBCtrlGrid offers you a rectangle panel where you can position your data-aware and non-data-aware controls in any way you want. You can then use the RowCount and optionally even the ColumnCount (used less often) to specify how many panels are shown. Each panel fills the data-aware controls with the values from a different record, so you can build your own custom grid this way.

You cannot use all data-aware controls inside a TDBCtrlGrid, however. You cannot use a TDBGrid, TDBNavigator, TDBListbox (but you can use a TDBCombobox), TDBRadioGroup, TDBLookupListBox, TDBRichEdit, or another TDBCtrlGrid inside a TDBCtrlGrid. That's not really a problem, since there are enough data-aware controls left to build your own data-aware control grid.

As an example, I've used the TDBCtrlGrid and gave the RowCount and ColumnCount both a value of 2 (to show 4 records at the same time), and placed a TDBText, a normal TDBEdit, a TDBCombobox, as well as a TDBMemo and TDBImage component on top of the panel. The RowCount is set to 3, so you'll get three rows of this custom control grid. I've also placed a TDBRadioGroup and a TDBListbox outside of the TDBCtrlGrid, and added a normal TDBGrid and finally a TDBNavigator to complete the picture (see Figure 4 for the details at design-time).


Figure 4. Delphi for .NET IDE and VCL for .NET Designer
Figure 4. Delphi for .NET IDE and VCL for .NET Designer

All data-aware controls should have their DataSource property set to the TDataSource component, after which you only have to select the correct FieldName. For the TDBText component, I'm using the EMPNO field (the key - which should be read-only, unless you're adding a new employee to the database). For the TDBFirstName component I'm using the FirstName field, and the TDBComboBox is connected to the SEX field, with the Items list filled with "F" and "M" strings. The TDBMemo is connected to the RESUME (in ASCII format), and the TDBImage is connected to the PICTURE (which is in bitmap format - it cannot display GIF files, hence the need for the where clause in the SQL query you constructed earlier).

Outside of the TDBGrid, I've connected the TDBRadioGroup to the SEX field as well. This time, you can use both the Items and the Values property, and I've set the (display) Items to "Female" and "Male" again, and the (field) Values to "F" and "M". You can also use this capability to translate the strings (for the user of the application), while still using "F" and "M" values inside the database.



Back to top


Running on .NET

Running the application compiled with Delphi 8 for .NET produces the result that can be seen in Figure 5. This is a native .NET executable, meaning that it requires the .NET Framework 1.1 to be installed.


Figure 5. Delphi 8 VCL for .NET applications
Figure 5. Delphi 8 VCL for .NET applications

As you can see in Figure 5, four employees were found with a resume in ASCII format and a picture in bitmap format. The TDBCtrlGrid gives an overview in a 2x2 format, as configured. Users of this application can view some general EMPLOYEE detail in the normal TDBGrid, and the more visual details (picture and resume), that cannot be shown in the regular TDBGrid, inside the TCtrlGrid.



Back to top


Updating SQL JOIN queries

Being able to see the result from the JOIN statement is one thing, but people often want to make modifications as well. In the case of multi-table join queries, there is no easy way to send updates to individual tables. You have to explicitly decide which table will get the update, and make sure that only the fields that belong to this table are being incorporated in the generated update command.

For this example, let's assume that you only want to update the more general EMPLOYEE information, meaning that only the EMPLOYEE table will get updated, and not the EMP_PHOTO or EMP_RESUME tables. In order to specify the specific tablename, you must click on the DataSetProvider component, go to the events tab of the Object Inspector, select the OnTableName event, and write the following code for this event handler:

procedure TForm1.DataSetProvider1GetTableName(Sender: TObject;
DataSet: TDataSet; var TableName: string);
begin
TableName := 'EMPLOYEE';
end;

The next step involves specifying which fields are part of the SQL update statement that will be generated. For this, you need the TSQLDataSet component. Right-click on it to start the Fields Editor, which by default shows no fields (all fields are used implicitly).

Right-click in the Fields Editor and do Add All Fields to get the complete list of fields, as can be seen in Figure 6.


Figure 6. Fields Editor
Figure 6. Fields Editor

The first 14 fields are from the EMPLOYEE table, but the next three are from EMP_RESUME, and the last three fields are from EMP_PHOTO. These last six fields that don't belong to the EMPLOYEE table should not be used in the update statement. This can be specified by selecting these fields - one after each other - and for each field set the pfInUpdate flag of the ProviderFlag property to False. This will ensure that they are no longer used in the Update statement.



Back to top


Calling the update

All this is fine, but it is only a preparation for a successful update. You still need to call the update, using the ApplyUpdates method from the TClientDataSet component. The easiest way is to hook it to the OnAfterPost event handler, as follows:

procedure TForm1.ClientDataSet1AfterPost(DataSet: TDataSet);
begin
(DataSet as TClientDataSet).ApplyUpdates(0)
end;

Obviously, similar steps need to be taken for the deletion and insertion of records, but I'll leave that as exercise for you.



Back to top


Monitoring the SQL

Should you encounter problems with the update statement, then you need to find out where things went wrong. The obvious place to start is the OnReconcileError event handler of the TClientDataSet, which will be fired if the call to ApplyUpdates fails. This can happen when the record has been changed by another user, for example.

You can also get a more general database error, perhaps because a field value was in an incorrect format, or because the update statement was not correctly formatted. Especially for the SQL issues, you can use the TSQLMonitor component to - literally - monitor the SQL that the client sends to the DB2 UDB SAMPLE database.

Drop a TSQLMonitor component from the dbExpress category of the Tool Palette. You can use the AutoSave and FileName property to specify the output of the communication between dbExpress client and the DB2 UDB database. As soon as you set the Active property of TSQLMonitor to True, it will start to monitor and log the messages in the logfile. However, not all messages are of equal value, so instead of using AutoSave, I'd rather use a more flexible approach saving only the messages that I'm looking for. For this, you can use the OnTrace event handler of the TSQLMonitor component, and implement it as follows:

var
f: Text;

procedure TForm1.SQLMonitor1Trace(Sender: TObject;
var CBInfo: SQLTRACEDesc; var LogTrace: Boolean);
begin
if Pos('DB2 - ',CBInfo.pszTrace) <> 1 then
writeln(f,#13#10,DateTimeToStr(Now),#13#10,CBInfo.pszTrace)
end;

initialization
Assign(f,'c:\DB2dbx.txt');
Rewrite(f)
finalization
Close(f)
end.

Note that there's some extra code around the OnTrace event handler, to declare a logfile f, and initialize f to point to a local logfile (each client will write to the same logfile, so if you plan to run more than one client instance on your machine you may want to think of something else here).

Inside the OnTrace event handler, you get the actual CBInfo record that is used to write text to the logfile. Since you want to write it yourself, you can check the contents of the pszTrace message. Most messages start with DB2 and are only notifications that something is being done or called. The messages that do not start with DB2 are usually SQL statements, and these are the important ones to display, since these may be incorrect and in need of fixing (as you can see in the implementation above).

Specifically, if you didn't specify that the last six fields of the TSQLDataSet component would NOT be part of the update statement, then you would get an incorrect SQL update command. This can be checked and verified using the TSQLMonitor, which is always handy but is only available in the Enterprise editions (or higher) of Delphi, Kylix, and C++Builder.



Back to top


Deploying on .NET

When it comes to deployment, the Delphi 8 for .NET project is compiled with references to the VCL for .NET assemblies. This results in a 12K executable, but one that requires the VCL for .NET assemblies to be deployed as well. The list of all required assemblies can be found in the Delphi 8 Project Manager, as can be seen in Figure 7.


Figure 7. Project Manager
Figure 7. Project Manager

If you right-click on the Borland assemblies, you can select either Copy Local or Link in Delphi Units. The former will copy the assembly to the project directory (so it's easier to deploy the .exe and .dlls from the same directory), while the latter will link the assemblies inside your executable, so you don't need to deploy them. For each Borland assembly you can decide if you want to deploy it, or link it inside the final executable, resulting in an executable between 12K (when deploying all assemblies) or 2.5 MB (when all assemblies are linked inside the executable).

Either way, you also have to deploy the DB2 dbExpress driver in file dbexpdb2.dll.



Back to top


Compiling for Win32

Where Delphi 8 produces native .NET executables, Delphi 7 produces native Win32 executables. And the data-aware controls that you used with Delphi 8 as part of VCL for .NET, which originally came as part of VCL for Delphi version 7 and earlier. And since the VCL and VCL for .NET are highly compatible (from a developer's point of view - that is, for the "users" of the VCL components), you can make a copy of the project and with a few changes compile it to a native Win32 application to access the DB2 UDB SAMPLE database tables. This is shown in Figure 8.


Figure 8. Delphi 7 VCL (for Win32) Application
Figure 8. Delphi 7 VCL (for Win32) Application

Source code for both projects is included with this article, so you can compare them to see the few minor changes that were needed.



Back to top


Summary

In this article, I have used Borland Delphi 8 for .NET and the VCL (Visual Component Library) to bind to IBM DB2 Universal Database tables, and perform some SQL queries on the EMPLOYEE, EMP_PHOTO, and EMP_RESUME tables from this SAMPLE database.

Delphi 8 produces native .NET executables, but I also showed that it's easy to use Delphi 7 to compile the project to a native Win32 (non-.NET) executable.




Back to top



0 Comments: