Multi-tier Techniques

Problem/Question/Abstract:

Row-level Business Rules, Real Transaction Processing ,and More

Answer:

One problem many developers encountered while building multi-tier applications with Delphi 3 was implementing business rules in the middle-tier application server. You could create an OnUpdateData event handler for the TProvider component and validate all the records in the update (delta) packet sent to the application server by a call to ApplyUpdates in the client. However, using OnUpdateData required you to accept or reject all the records as a set. The only alternative was to use the TUpdateSQLProvider component, which Inprise made available with the Delphi 3.02 release. TUpdateSQLProvider wasn't an official part of the VCL, but it added an OnUpdateRecord event to the events provided by TProvider, giving you record-by-record control of the update process.

Delphi 4 has solved this problem in a very different way. In Delphi 4, the TProvider component has a new property -ResolveToDataSet. ResolveToDataSet is False by default, which provides the same behavior as Delphi 3. When ResolveToDataSet is True, updates are applied using the dataset component the provider is connected to. This causes all the dataset's events to fire as though the changes were being made manually or in code. Now, you can enforce business rules by blocking inserts, deletes, or posts with an exception, just as you would in a two-tier application.

The sample EbSrvr application that accompanies this article demonstrates this using the BeforePost event handler from the OrderTable object:

procedure TEbServer.OrderTableBeforePost(DataSet: TDataSet);
begin
  with DataSet do
    if FieldByName('ShipDate').AsDateTime <
      FieldByName('SaleDate').AsDateTime then
      raise Exception.Create(
        'Ship date must be greater than sale date.');
end;

This code raises an exception if the ShipDate is less than the SaleDate. In Delphi 3, raising an exception in the provider's OnUpdateData event handler caused an exception in the client application at the call to ApplyUpdates. However, raising an exception in one of the Before event handlers when ResolveToDataSet is True doesn't cause an exception in the client. Instead, the client's ReconcileError event is fired just as with any error generated by the VCL code in the server, or by the database server itself. This is a vast improvement because all errors, regardless of source, can be handled in the same way in the client.

The sample application uses the Reconcile Error dialog box from the Object Repository to handle all errors. If you try to post an order record whose ShipDate is less than the SaleDate, the error will appear in the Reconcile Error dialog box with the message text that you passed to the exception's constructor in the application server. The disadvantage of setting ResolveToDataSet to True is somewhat slower performance. [Note: EbSrvr and the other example projects discussed in this article are available for download; see end of article for details.]

Delphi 4 includes a new component -TDataSetProvider - that performs the same function as TProvider. However, it always applies updates to a dataset component in the middle-tier application. TDataSetProvider doesn't have the ability to apply updates directly to a database server by passing the dataset component. TDataSetProvider doesn't use the BDE, so it's the ideal choice if you're building a multi-tier application using a non-BDE database.

Controlling the Application Server

There are two ways for the client to control the application server in a multi-tier application: the first is by passing parameters to a query or stored procedure; the second is by executing custom methods on the server. Passing parameters to a TQuery, TStoredProc, or TTable in the application server was implemented in Delphi 3 using the Provider property of TClientDataSet to call the Provider's SetParams method passing as a parameter a variant array containing the parameters.

Delphi 4 provides a new way to do the same thing, as shown by code from the QClnt sample application (see Figure 1). This code is from the JobCodeCombo combo box's OnChange event handler.

procedure TMainForm.JobCodeComboChange(Sender: TObject);
begin
  with MainDm.EmployeeCds do
  begin
    { If the parameters have not been fetched from the server, fetch them. }
    if Params.Count = 0 then
      FetchParams;
    { Set the parameter values. }
    Params.ParamByName('Job_Code').AsString :=
      JobCodeCombo.Text;
    Params.ParamByName('Job_Grade').AsInteger :=
      StrToInt(JobGradeEdit.Text);
    { If the Employee client dataset isn't active, open it. Opening the dataset sends the parameters to the server. If it's already open, call SendParams to send the new parameter values to the server and refresh the dataset. }
    if not Active then
      Open
    else
    begin
      SendParams;
      Refresh;
    end; // if
  end; // with

  if not MainDm.SalaryCds.Active then
    MainDm.SalaryCds.Open;
end;
Figure 1: Setting parameters in the application server's query.

TClientDataSet now includes a Params property. You can fetch the parameters from the TQuery or TStoredProc component on the server at design time or run time. At design time, right-click the TClientDataSet and choose Fetch Params. At run time, call the FetchParams method.

The preceding code first checks the TClientDataSet.Params.Count property to see if any parameters have been fetched. If not, FetchParams is called. Once the parameter names and types have been fetched from the server, you can assign values to each parameter using the Params.ParamByName method.

You can send the parameters to the application server in one of two ways. If the TClientDataSet is closed, simply open it. Opening the ClientDataSet automatically sends the current parameter values to the application server. If the ClientDataSet is already open, call its SendParams method to send the new parameters to the application server. The server will automatically close the query or stored procedure, assign the new parameter values, then reopen the query. After calling SendParams, be sure to call the ClientDataSet's Refresh method so it will retrieve the new records from the application server.

Calling custom methods on the application server is no different than calling a custom method in any other automation server. The first step in implementing a custom method in the server that can be called from the client is to add the custom method to the server's interface using the Type Library Editor. Open the Type Library Editor by selecting Type Library from the View menu, then click on the interface to select it, as shown in Figure 2.


Figure 2: The Type Library Editor with the server's interface selected.

Click the New Method button to add as many methods as you need, and to add any parameters and a return value if required. Finally, click the Refresh Implementation button to update the type library interface unit, and add the new methods' stubs to the remote data module's unit. In the previous example, two new methods, FilterOn and FilterOff, were added to the interface. These methods can now be called from the client, using the connection component's AppServer property.

Early Binding

By default, the MIDAS connection components use late binding when calling methods of the application server. Although late binding works regardless of the type of connection between the client and the application server, it's slower than early binding. Also, early binding provides compile-time error checking of all your interface method calls. Early binding is only available if you use DCOM for the connection.

To use early binding, you must obtain an interface reference, which is simply a pointer to the interface's vtable (virtual method table), for the application server's interface. This is a two-step process. First, cast the connection component AppServer property to IUnknown or IDispatch to get an interface reference. This converts the AppServer property from a variant of variant type varDispatch to an interface reference. Next, cast the IUnknown or IDispatch interface to the interface type of the application server. This causes COM to call QueryInterface and return a reference to the application server's vtable. In the code in Figure 3, both casts are performed in a single statement. This code calls the custom FilterOn method that was added to the application server's interface using the Type Library Editor.

procedure TEcMainForm.ByName1Click(Sender: TObject);
var
  IServer: IEbServer;
begin
  with MainDm do
  begin
    IServer := IDispatch(EbConn.AppServer) as IEbServer;
    IServer.FilterOn;
    CustomerCds.Refresh;
  end;
end;
Figure 3: Calling a server method with early binding.

IEbServer is the application server's interface and IServer is an interface reference variable. EbConn is the TDCOMConnection component. To initialize the interface reference variable IServer to point to the interfaces virtual method table, the AppServer property is first cast to IDispatch, then to IEbServer. Once the interface variable has been initialized, it can be used to call the methods of the interface using early binding.

Although you cannot use early binding if you aren't using DCOM for the connection, you can improve performance compared to late binding by using the application server's dispatch interface. The code in Figure 4 is nearly identical to the previous example, except that the connection component's AppServer property is cast to the dispatch interface type, IEbServerDisp.

procedure TEcMainForm.ByCustomerNumber1Click(
  Sender: TObject);
var
  IDispServer: IEbServerDisp;
begin
  with MainDm do
  begin
    IDispServer :=
      IDispatch(EbConn.AppServer) as IEbServerDisp;
    IDispServer.FilterOff;
    CustomerCds.Refresh;
  end;
end;
Figure 4: Calling server methods using the dispatch interface.

Controlling the Client

The application server in a multi-tier application can call methods in the client application. This allows events that occur in the server to call event handlers in the client. The first step in allowing the server to call methods in the client is to add another interface to the server's type library, as shown in Figure 5.


Figure 5: Adding the IEbClient interface to the server.

This figure shows the type library for the sample EbSrvr application after adding a second interface, IEbClient. After adding the second interface, select it and add all the client methods that the server will call. In this case, a single method named ConfirmFilter was added to IEbClient. The client application will include an object that implements this interface.

Next, add a method to the server's interface, which takes a reference to the client interface as its only parameter. In Figure 5, this method is called ConnectClient. ConnectClient assigns its interface reference parameter to a variable that is added to the server's remote data module. The code in Figure 6 is the type declaration for the server's remote data module after the private member variable ClientConnection has been added.

type
  TEbServer = class(TRemoteDataModule, IEbServer)
    Database1: TDatabase;
    CustomerTable: TTable;
    CustomerProv: TProvider;
    OrderTable: TTable;
    OrderProv: TProvider;
    procedure EbServerCreate(Sender: TObject);
    procedure EbServerDestroy(Sender: TObject);
    procedure OrderTableBeforePost(DataSet: TDataSet);
    procedure CustomerProvGetDataSetProperties(
      Sender: TObject; DataSet: TDataSet;
      out Properties: OleVariant);
  private
    ClientConnection: IEbClient;
  protected
    function Get_CustomerProv: IProvider; safecall;
    function Get_OrderProv: IProvider; safecall;
    procedure FilterOn; safecall;
    procedure FilterOff; safecall;
    procedure ConnectClient(const Client: IEbClient);
      safecall;
    function IsDatabase: WordBool; safecall;
  end;
Figure 6: The server's remote data module with the ClientConnection added.

ClientConnection is the server's reference to the object that implements the IEbClient interface in the client. With this interface reference, the server can call any method of the interface. In this application, the code in Figure 7 is added to the server's FilterOn and FilterOff methods to confirm to the client that the filter on the Customer table is either on or off.

procedure TEbServer.FilterOn;
begin
  CustomerTable.Filtered := True;
  if CustomerTable.Filtered then
    ClientConnection.ConfirmFilter('Filter Is On')
  else
    ClientConnection.ConfirmFilter('Filter Is Off');
end;

procedure TEbServer.FilterOff;
begin
  CustomerTable.Filtered := False;
  if CustomerTable.Filtered then
    ClientConnection.ConfirmFilter('Filter Is On')
  else
    ClientConnection.ConfirmFilter('Filter Is Off');
end;
Figure 7: Callbacks to the client to confirm the filter state.

This code shows the use of the ClientConnection interface reference variable to call the ConfirmFilter method in the client application. On the client side, an object that implements the IEbClient interface must be added to the client application, as shown in the following:

TCallBack = class(TAutoIntfObject, IEbClient)
  procedure ConfirmFilter(const Msg: WideString); safecall;
end;

The TCallback object descends from TAutoInftObject, which provides support for the IDispatch interface. The implementation code for the ConfirmFilter method simply assigns the Msg parameter to the Caption property of a label on the client's main form so the user can see the message.

The heart of the callback mechanism is in the OnCreate event handler for the client application's main form (see Figure 8).

var
  ServerTypeLib: ITypeLib;
  TypeLibResult: HResult;
  CallBack: IEbClient;
  ...
    // Set up the callback interface.
  TypeLibResult := LoadRegTypeLib(LIBID_EbSrvr, 1, 0, 0,
    ServerTypeLib);
  if TypeLibResult <> S_OK then
  begin
    MessageDlg('Error loading type library.',
      mtError, [mbOK], 0);
    Exit;
  end; // if
  // Create an instance of the TCallback object.
  CallBack := TCallback.Create(ServerTypeLib, IEbClient);
  // Get an interface reference to the server.
  Srvr := IDispatch(MainDm.EbConn.AppServer) as IEbServer;
  // Pass the interface handle of the TCallback object to
// the server.
  Srvr.ConnectClient(CallBack);
  {...}
Figure 8: Creating the Callback object in the client main form's OnCreate event handler.

The variable declarations are global to the main form's unit. This code begins by calling the Windows API function LoadRegTypeLib to load the server's type library. The parameters are:

The type library's GUID.
The type library's major version number.
The type library's minor version number.
The national language code of the library.
An interface reference variable of type ITypeLib that is initialized by the call to point to the type library.

LoadRegTypeLib returns a result code indicating whether the type library was successfully loaded or not. If the library is successfully loaded, an instance of the TCallback automation object is created. The type library and interface that the TCallback object implements are passed as parameters to its constructor, and the returned value is assigned to the interface reference variable Callback. Next, a reference to the server's interface, IEbServer, is obtained by casting the connection component's AppServer property to the interface type. The final step is the statement:

Srvr.ConnectClient(CallBack);

which calls the server's ConnectClient method passing the interface reference variable for the TCallback object as a parameter. As you have seen, the server can now use this interface reference to call any method of the TCallback object that is a member of the IEbClient interface.

Controlling What the Client Sees

While letting the server call methods in the client is a very powerful way to implement a two-way exchange of information, there are other ways to control what the client sees. There are three ways to limit which fields the client application sees. The first is to use a query as the dataset in the application server and only select the fields the client application should see.

The second method is to create persistent field objects using the Fields Editor in the server application and only create field objects for those fields the client application should see. Note that if you create calculated or lookup fields in the Fields Editor, they will be sent to the client as read-only fields. There is a potential problem with limiting fields using the Fields Editor because you must include the entire primary key if the client will edit, delete, or insert records so that the record will be uniquely identified. If you need to include the primary key so the record can be edited, but do not want the client application to have access to one or more of the primary key fields, you can select the field object in the Fields Editor and use the Object Inspector to change the field object's ProviderFlags property to include the pfHidden flag. The effect is similar to rendering a field invisible in a grid by setting its Visible property to False. The field is there, but it cannot be accessed.

You can also add information to the data packets the server provides to the client. This can be any type of information, and you can specify that it also be included in the delta packets returned to the server when the client applies updates. This means the server can send a round-trip message to itself. The sample EbClnt and EbSrvr applications use this technique to send the current Filter property for the Customer table to the client for display.

To place additional information to the data packets sent by the provider component, begin by creating an event handler for the provider's OnGetDataSetProperties event. The event handler gets three parameters: the first is Sender, the second is DataSet, and the third is Properties. DataSet is a pointer to the dataset that supplies the provider's data. Properties is an OleVariant in which you place all the additional information you want included in the data packet. Properties must be a variant array, and must include three elements for each attribute you add to the data packet. The first element of the array is a string that contains the attribute's name. The second is a variant that contains its value, and the third is a Boolean value, which is True if you want the attribute returned in the delta packets.

To add more than one attribute to the data packet, make Properties a variant array of variant arrays, as shown in the code from the EbSrvr sample program in Figure 9.

procedure TEbServer.CustomerProvGetDataSetProperties(
  Sender: TObject; DataSet: TDataSet;
  out Properties: OleVariant);
begin
  Properties := VarArrayCreate([0, 1], varVariant);
  Properties[0] :=
    VarArrayOf(['Filter', DataSet.Filter, True]);
  Properties[1] :=
    VarArrayOf(['Filtered', DataSet.Filtered, False]);
end;
Figure 9: Adding information to the data packet.

This code adds two attributes to the data packet. The first contains the value of the dataset's Filter property, and the second the value of the dataset's Filtered property. Note that the Filter attribute is returned to the server in the delta packets. On the client side, use the TClientDataSet's GetOptionalParam method to retrieve the value of any attribute from the data packet. The following code is from the U.S. Only menu item's OnClick event handler:

FilterStringLabel.Caption := CustomerCds.GetOptionalParam('Filter');

This code retrieves the value of the Filter attribute as a variant, and assigns it to the FilterStringLabel's Caption property.

Real Transaction Control for Local Tables

Although Inprise claims there is transaction support for local Paradox and dBASE tables, it's not true. Transaction support requires the database always be left in a consistent state, i.e. either all or none of the changes that are part of a transaction will occur. For this to happen, the database must roll back all active transactions upon restart after a crash. Transactions for local tables are not rolled back after a crash; instead, any changes that were posted will still exist in the database even though the transaction was never committed.

There is a second major problem with local table transactions. The only transaction isolation level supported is tiDirtyRead, which can lead to serious problems. Suppose a physician is entering treatment information for a patient and accidentally enters drug therapy information for the wrong patient. If the record is posted, the change will now be visible to all other users even though the transaction is not committed. What happens if another user prints a list of drugs that must be administered at this point? Even though the physician reviews his/her entries and rolls back the transaction, the patient will still receive the wrong medication.

A much better solution when working with local tables is to use TClientDataSet for all data entry. This is easy to do in Delphi 4: Simply drop a TProvider and a TClientDataSet on a form or data module that already has a TTable connected to the table you want to edit. Set the DataSet property of the TProvider to the table, then set the Provider property of the TClientDataSet to the TProvider. This simple approach assumes that TClientDataSet can hold all the table's data in memory. If that's not the case, you will have to use ranges, filters, or queries to restrict the set of records the user works with at one time.

Why is TClientDataSet a better solution than local table transactions? First, consider what happens if one user changes a record, posts the change, then another user looks at the same record. The second user will see the unchanged version of the record until the first user calls the TClientDataSet's ApplyUpdates method to "commit" his or her transaction. This means that you effectively have read committed transaction isolation instead of dirty read transaction isolation.

Now consider what happens if a user makes several changes and his or her system crashes. Because both the data and changes made using a TClientDataSet are held in memory until ApplyUpdates is called, they will all be lost. This effectively gives you automatic rollback on restart after a crash. The only time you are vulnerable is during the brief interval between the moment you call ApplyUpdates and when the changes have actually been written to disk. If the workstation crashes while the writes are taking place, the database may be either inconsistent or corrupt; however, because the time that the database is in an inconsistent state is very short, the chances are small. By comparison, when you use local table transactions, the database is in an inconsistent state from the time the user posts the first change of the transaction until the user commits the transaction. This could be several minutes for a transaction that involves manually changing several records. The LclTran demonstration application shows an example of using TClientDataSet with local tables.

Using TClientDataSet for Flat-file Applications

TClientDataSet is a great tool for building single-user database applications that deal with modest amounts of data. It provides all the features of a relational database - except query support - with nothing to install or configure on the user's machine. All you have to do is distribute the dbclient.dll file with your program. The big limitation of TClientDataSet is that it holds all the data in memory. However, that is not as bad as it sounds when you consider that 100,000 records, 100 bytes in length, require 10MB of memory.

Using TClientDataSet for single-user, flat-file applications is much easier in Delphi 4. One of the most onerous aspects of writing a flat-file application in Delphi 3 is that the only way to create your data tables is in code. In Delphi 4, you can drop a TClientDataSet on a form or data module, and define your tables interactively using the Fields Editor. Simply double-click the TClientDataSet to open the Fields Editor and add new data fields. When you're done, right-click the TClientDataSet and choose Create DataSet from the context menu.

Another new feature useful in flat-file applications is the ability to use nested datasets. When you create a master TClientDataSet, you can add fields in the Fields Editor whose type is DataSet. To use the nested dataset, add another TClientDataSet to your project and set its DataSetField property to the field of type DataSet that you added to the master TClientDataSet. Now, open the Fields Editor for the detail TClientDataSet and add the fields for the detail dataset. Note that you don't have to add a foreign key field to link the detail to the master, because the detail is actually contained by the master.

One problem you may encounter is trying to add another field to a table after you have created the dataset. You can add the field in the Fields Editor, but you'll get an error when you right-click the TClientDataSet and choose Create Dataset. To overcome this, select the TClientDataSet and open the FieldDefs property in the Object Inspector by clicking its ellipsis button. In the Collection Editor, select all the field definitions and delete them. Now, right-click the TClientDataSet and choose Create DataSet to recreate all the field definitions, including the new field.

The sample Phone application demonstrates this technique. This is a simple, two-table application. The master contains people, and the detail contains phone numbers. One of the advantages of using nested datasets is that both the master and detail are saved in a single file, phone.ffd. The code from the Save Changes menu item's OnClick event handler first posts any unposted changes in both datasets, then merges the changes and saves the PersonCds to the phone.ffd file (see Figure 10). Note that only the Person dataset is saved, because it contains the numbers dataset.

procedure TMainForm.SaveChanges1Click(Sender: TObject);
begin
  { If there are unposted records post them. }
  if MainDm.PersonCds.State in [dsEdit, dsInsert] then
    MainDm.PersonCds.Post;
  if MainDm.NumberCds.State in [dsEdit, dsInsert] then
    MainDm.NumberCds.Post;
  { Merge the changes in Delta with the data and save it. }
  with MainDm.PersonCds do
  begin
    MergeChangeLog;
    SaveToFile('phone.ffd');
  end;
end;
Figure 10: Saving the master and detail tables in a single file.

There is, however, a big disadvantage to using nested datasets. You cannot search the entire detail dataset for a record. This would be a serious problem in a Customer/Orders relationship, where searching the entire Orders dataset by order number to find a specific order would be useful. Even in the sample Phone application, it might be nice to search for a person by phone number when you are trying to reconcile your long distance phone charges.

Maintained Aggregates

Maintained aggregates are another new feature of TClientDataSet in Delphi 4. They allow you to maintain the sum, count, min, max, or average of any number of fields in a TClientDataSet. What's even more valuable is that they support groups and expressions. You can use any index to group the aggregates. In the case of a composite index, you can also specify the number of fields to group on.

There are two ways to create and use maintained aggregates. The first is through the Aggregates property of TClientDataSet. Before creating any aggregates, be sure to set the AggregatesActive property of the TClientDataSet to True. You can set this property at any time - but don't forget it, or your aggregates won't work. Next, click the ellipsis button in the Aggregates property to open the Collection Editor. Then, press [Insert] to add an aggregate. Finally, set the aggregate's properties in the Object Inspector. Again, set the aggregate's Active property to True so you don't forget. Give the aggregate object a meaningful name, then enter an expression for the Expression property. The expression can use the Sum, Min, Max, Avg, or Count operators on a single field, or on an expression involving two or more fields. For example:

Sum(TaxRate * SaleAmount)

is a valid expression, as is:

Sum(Price) - Sum(Cost).

To group using an index, set the IndexName property to the index to use, and set the GroupingLevel property to the number of the last field in the index to group on. For example, if you have an index on the Country, Region, and SalesTerritory fields, set the GroupingLevel to 2 to group by Region. Setting the GroupingLevel to 0 disables grouping, so the aggregate will use all the records in the dataset. The sample Phone application uses an aggregate named TotalRecords to display the total number of records that have been saved.

To use the aggregate value, use the Aggregates property's Find method to get the value, as shown in Figure 11. The expression:

PersonCds.Aggregates.Find('TotalRecords').Value;

finds the named aggregate and calls its Value method to retrieve the current value of the aggregate.

procedure TMainForm.FormCreate(Sender: TObject);
begin
  TotalLabel.Caption :=
    IntToStr(MainDm.PersonCds.Aggregates.Find(
    'TotalRecords').Value) + ' saved records';
end;
Figure 11: Using the Aggregates.Find method.

Another alternative is to create an aggregate field using the Fields Editor. Add a new field to the TClientDataSet and choose Aggregate for the field type. Also, click the Aggregate radio button, then click OK to add the field. Select the field in the Fields Editor, then set the Name, Active, Expression, IndexName, and GroupingLevel properties. You can now access the aggregate like any other field in the dataset. This technique is particularly useful because it allows you to display the value of the aggregate using data aware controls without writing code.

Conclusion

Delphi 4 brings a host of new features for multi-tier application developers, but even more important are the new features of TClientDataSet that are available to all developers. Whether you're developing an enterprise multi-tier application, a traditional two-tier client/server system, or a file-server-based program, TClientDataSet offers you maintained aggregates, the briefcase model, better caching than cached updates, better local table transactions than the built-in local table transactions, nested-table, flat-file applications without the BDE, and more.

This is a whole new way to develop any application.


Component Download: http://www.baltsoft.com/files/dkb/attachment/Multi-tier_Techniques.zip