InterBase in a Multi-tier World

InterBase in a Multi-tier World

by Craig Stuntz

presented by Wayne Niddery at BorCon 2002

Note: This article uses PNG images.

Modern browsers support PNGs. If you’re using an old browser, you’ll need to upgrade.

Introduction << Click here to skip past table of contents

IBX and DataSnap

State

Dealing with User Logins

Digression: Connection Pooling

Incremental Fetching

Parameterized Queries

Transaction Management

Transactions in DataSnap

Why It’s Never Appropriate to Manage Transactions on the Client

Using IBX Transactions in a DataSnap Server

Two Ways Clients Can Update Data

Atomicity and Multiple Datasets

Generators

InterBase Events

Instancing and Threading Options

Using Updateable VIEWs to Resolve Multi-table JOINs

Creating the Application Server and Thin Client Projects

Using Multiple Data Modules

Architecture

Implementation of Main Connection Data Modules

Creating the Application Server’s Main Connection Data Module

Creating the Client’s Main Connection Data Module

Implementation of Abstract Child Data Modules

Creating the Application Server’s Abstract Child Data Module

Using the Type Library Editor

Creating the Thin Client’s Abstract Child Data Module

Implementation of the Child RDMs

Creating a New Child RDM

Creating a New Client DM

Standard Events

References

---

Introduction

The topic of creating multi-tier clients and application servers could easily — and does — fill several books.  It is far too large for one presentation.  This paper, therefore, is not intended to be a comprehensive guide to the subject, but, rather, is designed to shed more light on some areas which existing references don’t cover in great detail.  In particular, this paper examines how to integrate InterBase into common patterns for multi-tier development.

Before reading this paper you should be generally familiar with the DataSnap framework in Delphi.  No attempt will be made to explain how to use TClientDataset!  (Note:  If you’re not familiar with the DataSnap architecture in Delphi, see the papers listed in the References section of this paper and the various applications in the DemosMidas directory of your Delphi installation.)  You should also be very comfortable working with InterBase.

So what will the paper cover?  

• Using multiple remote data modules and data module inheritance to partition and reuse code in both the client application and  the app server.
• Using InterBase-specific features such as generators.
• Understanding transaction management in DataSnap..
• Understanding state, especially as it pertains to database user security.
• Choosing a threading option which works with the InterBase client.

IBX and DataSnap

The examples in this paper use the InterBase Express components for connecting to InterBase.  The techniques presented should work well for any TDataset-compatible components which connect to InterBase, however. 

When using IBX components in a DataSnap server there are a couple of differences from the way these components are used in a two-tier application.  

The UniDirectional property should always be set TRUE.  When UniDirectional is FALSE (the default) the IBX datasets cache returned rows in memory.  In a DataSnap application, data will be cached on the TClientDataset on the client tier, so caching the same data twice uses memory and limits performance.

It is not necessary to use the TIBUpdateSQL component, or to fill in the DeleteSQL, InsertSQL, ModifySQL, or RefreshSQL properties.  The DataSnap resolver will concoct the appropriate SQL.  If you are not happy with the SQL created by DataSnap, write a TDatasetProvider.BeforeUpdateRecord handler and take over the update process in code.

The AutoStopAction property of TIBTransaction should be set to saCommit when the dataset(s) it’s connected to is driving a TDatasetProvider, as discussed in more detail below.

State

For an n-tier system to be robust and scaleable, it must use a stateless application server.  

State is defined as the stored values of an object’s properties or fields (instance variables).  To say that an application server is stateless does not mean that it has no properties or fields, however.  Rather, it means that there are no preconditions or postconditions for each method on the application server.  Clients are never required to set properties and then call a method, or to call a series of methods in succession.  Instead, the client can connect to any random instance of the application server (in a large-scale system, there may be lots of instances) and execute a method.  All data required for the method to work is passed as arguments, and all data returned from the method is passed back to the client as output arguments or a return value. 

It is much easier to write a stateless application server from the outset, even if statelessness is not required right away, than to retrofit a stateful application server later on to make it stateless. 

Dealing with User Logins

The requirement that a server is stateless throws a wrench into the traditional way an application deals with InterBase.  Most two-tier applications follow a process something like this:

1. Log in to InterBase
2. Perform operation A
3. Perform operation B
4. Log out

With a stateless application server, however, this process is more complicated.  We cannot set properties on the server for the username or password, because the server would no longer be stateless.  Two different clients with different usernames might need to use the same application server instance.

There are, however, a number of solutions to this issue.  Which one is appropriate for a particular project depends upon that project’s requirements and data model.  The developer must ask the following questions:

1. Is there a requirement to have identifying information about the user?  With some systems (e.g., an Internet search engine) there is no such requirement and all clients can use the same login.  With other systems (e.g., a medical records system) it is very important to know who the user is, what they are doing, and what they are allowed to see.
2. Is is necessary for each user to have an InterBase login (as opposed to some other form of identification and security)?  With some systems, such as smaller accounting systems, for example, InterBase security user or ROLE security can be used to determine which records an InterBase user can see.  With larger systems, such as an online banking system, the sheer number of users of the system (hundreds of thousands or millions of users) makes setting up and maintaining InterBase logins for each individual user impractical.
3. How does the client normally interact with the application server? 

   Does it typically execute a single method (such as synchronizing a single table to be used in briefcase mode later on), or does it typically execute a number of methods (such as interactively working with a number of tables)?

Here are a few different ways that client applications can pass identifying information about the user to the application server.

• The IB username and password for the current client can be passed with each method call to the application server.  The application server can either make a new connection to InterBase for the purpose of completing the method call and then disconnect at the end of the method, or it can pool connections.  When pooling connections, the application server either uses an existing connection for that username or makes and stores a new connection for that username at the time the method starts, but doesn’t disconnect when the method completes.  This way, the connection is available for subsequent method calls by the same user.  Obviously, the password should be stored and checked each time the same username is passed.
• A username and password (but not the InterBase username and password) can be passed along with each method call to the application server.  The application server verifies the user’s access rights via some system other than an InterBase logon, and connects to InterBase via a single InterBase logon regardless of the identity of the user.  In this system security is handled by the application server rather than by InterBase, so it is important that users can only connect to the application server and cannot connect to InterBase directly.
• A session ID can be passed in lieu of the username and password.  With this system, the application server provides a method which allows the client application to pass a username and password and receive a session ID in return.  Thereafter, the client application passes the session ID instead of the username and password.  This is no more or less secure than passing the username and password, but it can reduce the amount of data which must be sent with each method call.  Technically, this introduces state into the system, but practically speaking this is not a problem since the session information can easily be shared between multiple application servers by storing it in the database.  This system works equally well with InterBase and non-InterBase logons.

When writing a DataSnap server, use the OwnerData argument to CDS/DSP events to pass this sort of data between the client and the app server.

Digression: Connection Pooling

Connection pooling was briefly mentioned above.  It’s worth noting that logging in to InterBase does take more time than other calls to the IB client, for a couple of reasons.  The host IP address must be looked up, either in the hosts file or by the Domain Name Server.  Also, the IB server itself has to do some work, including verifying the username and password in the security database and reading some information from the system tables.

What this means is that if a client will be making a number of calls to the application server, or if there are a large number of clients, this logon overhead can become significant and logging on to InterBase each time a method is called on the application server is not a practical solution.  The solution for this is to pool InterBase connections.

A connection pooler is simply a list of database connections with some kind of multi-user synchronization to ensure that two application server instances do not attempt to use the same IB connection at the same time.  

Multiple threads in a single application server can safely use the InterBase client concurrently so long as no two threads attempt to use the same InterBase connection concurrently, and all connections are "remote."  "Remote" means that the database name includes a server name.  If the application server and the InterBase server are installed on the same machine, then use localhost as the server name when making the connection to InterBase.

Current versions of IBX include a connection pooling component called TIBConnectionBroker.  This component works well and currently powers Borland’s CodeCentral (http://codecentral.borland.com), which serves half a million users.  However, the component has a limitation which may or may not make it appropriate for a particular project: There is no provision for each client having its own InterBase logon.  Source code is provided, however, and it would be fairly simple to extend the component to handle multiple InterBase usernames.

Incremental Fetching

DataSnap is designed to support the creation of stateless servers, but there are certain features of DataSnap which are not stateless.  One such feature is incremental fetching.  There are two ways that a DataSnap client can fetch records:

• Fetch all records from a result set to the client in a single method call.
• Fetch enough records to fill a grid or other client need, and fetch more records later on (in a different method call to the application server) when needed.

The second method can improve performance, but because multiple method calls to the application server are required, making it work for a stateless server is not straightforward.  In addition to passing the number of records desired, the client must pass the starting record identifier, because it cannot be certain that no other clients have used the same instance of the application server in the meantime.  The application server must be able to, in one way or another, find the subset of records requested by the client and return them.

DataSnap by default fetches all records from the result set to the client in a single method call.  This means that by default DataSnap is stateless.  By setting TClientDataset.PacketRecords to a value other than -1, it is possible to tell DataSnap to fetch only a few records at a time.  However, the way that DataSnap implements this feature is to leave the dataset on the application server open.  When the client requests a new packet of records, DataSnap uses the current cursor position and reads forward the requested number of records.  This is very efficient for a two-tier application, but it is also stateful.

If an application requires incremental fetching but must also be stateless, some more work is required.  However, Dave Rowntree has done this work for you.  The IncFetching.pas file in the Midas Essentials pack includes subclasses of TClientDataset and TDatasetProvider which can do incremental fetching in a stateless manner.  This can still be inefficient with very large datasets.  If it is necessary to do stateless, incremental fetching with a very large dataset, the best solution would probably be to subclass Dave’s components and make them exploit the InterBase 6.5 ROWS feature. 

Parameterized Queries

In a two tier application, parameterized queries work like this: You can set the parameters at any time, but they only take effect when you Close and re-Open the dataset.  Ideally, n-tier applications would work the same way, and this seems to have been a design goal of DataSnap.  Param values must be sent to the application server each time a method is called for the same dataset in order to ensure that the same query is run.  For example, if a thin client first opens a result set (using TClientDataset.Open) and then later on refreshes its view of the same data (using TClientDataset.Refresh), it is necessary to re-send the original param values (even if the values stored in TClientDataset.Params have since changed), or the user will see a different dataset than they requested, if another client has used the application server with different param values in the meantime.

Unfortunately, this isn’t what DataSnap currently does, and it is important to understand this in order to write stateless servers which use parameterized queries.  In particular, the following methods "out of the box" are not safe to use with stateless application servers.  (Thanks again to Dave Rowntree for his research in this area.)

TClientDataset.Refresh;

TClientDataset.RefreshRecord;

Also, the following properties will cause DataSnap to fail to be stateless:

TDatasetProvider.Options.poFetchBlobsOnDemand

TDatasetProvider.Options.poFetchDetailsOnDemand

TDatasetProvider.ResolveToDataset

When writing a stateless application server, therefore, it is important to either 

1. Not use these methods (for example, replace a call to TClientDataset.Refresh with calls to Close, Open, and then Locate the original record), or…
2. Modify the VCL source code to make them pass the param values (see the source code for TCustomClientDataset.OpenCursor for an example of how this is done), or…
3. Pass parameter values in a different manner.  One easy way to do the latter is to write corresponding BeforeGetRecords events for both the TClientDataset and the corresponding TDatasetProvider.  Pass the parameter values in the OwnerData property.  

Here is an example of (3):

// In the AppServer’s RDM

procedure TrdmFoo.prvFooBeforeGetRecords(Sender: TObject;
  var OwnerData: OleVariant);
begin
  if VarIsType(OwnerData, varInteger) then begin

    qryFoo.ParamByName(’FOO’).AsInteger := OwnerData;
  end else begin
    // this should only happen when the client is open in the IDE
    // CDS.BeforeGetRecords does not run in this case

    qryFoo.ParamByName(’FOO’).AsInteger := 0;
  end; // if
  inherited;
end;

// In the Client’s DM

procedure TdmFoo.cdsFooBeforeGetRecords(Sender: TObject;
  var OwnerData: OleVariant);
begin
  OwnerData := iFoo;
end;

This works quite well and is probably the simplest solution if you’re working with just a few datasets.  If a particular applications requires hundreds of datasets on many different DMs, however, it could become a maintenance issue.  In that case, use one of the other two suggestions above.

Transaction Management

It is impossible to do any kind of access to IB without starting a transaction first. So you are always using a transaction when reading or writing from IB. The question is, when does the transaction start and end, and who controls it?  In an n-tier application, the answer should always be the application server.

Transactions in DataSnap

The term "transaction" can become muddled in a three-tier environment because it is used to refer both to database transactions and to COM+ (formerly known as MTS) transactions, or other distributed transaction systems such as XA.  The subject of COM+ transactions is enough of a topic for a session of its own (indeed, there was just such a session last year) and they’re not properly supported by any DB components other than ADO, so we won’t be covering them here.

When it comes to database transactions, DataSnap (as of Delphi 6, anyway) is a little wishy-washy.  During an ApplyUpdates, for example, DataSnap will explicitly start a transaction, apply each update, counting errors until the user-specified threshold is reached or there are no more updates to apply, and then explicitly commit or roll back the transaction as appropriate.  But when a dataset is opened, DataSnap doesn’t deal with transaction management at all, instead delegating it to the data access components.

DataSnap seems to presume the BDE’s model of using implicit transactions when explicit transactions are not used.  It would be better if DataSnap always used explicit transactions, but it doesn’t.  Because of this, early versions of the IBX components do not work well with DataSnap.  It is therefore important to get the latest version of the IBX components for your version of Delphi from CodeCentral before building a DataSnap server with IBX.

Why It’s Never Appropriate to Manage Transactions on the Client

As discussed above, any process which requires multiple calls to a given application server in a particular order is inherently stateful and therefore cannot scale to an environment where multiple, load-balanced application servers may handle different requests from a given client, or multiple clients may issue requests to the same server.  This alone is a good reason not to attempt to manage transactions in a client application.

From a less technical point of view, the interface to the app server should resemble the client’s needs.  Put another way, all implementation details of business processes should be inside the app server.  The client sends a message to the app server and learns whether the request succeeded or failed when the function call returns.  Transaction management is an implementation detail, albeit a very important one.

Using IBX Transactions in a DataSnap Server

Nested master and detail datasets must share a single TIBTransaction.  Beyond that, the developer is free to use a single TIBTransaction for all datasets on a data module or use one TIBTransaction for each dataset — it doesn’t matter, because the transaction will only be open as long as DataSnap is working with any individual dataset, and, with the exception of nested master/detail datasets, DataSnap does not work with multiple datasets concurrently.

Any data access components used to create a DataSnap application server need to be able to handle situations where transactions are explicitly started and situations where they are implicitly started (i.e., the dataset is opened without first starting the transaction it’s connected to) concurrently.  In a "normal" IBX program transaction control should always be performed explicitly, but this is not possible with DataSnap.  Therefore, a special property, AutoStopAction, has been added to TIBTransaction to ensure that it is well behaved when the DataSnap resolver is driving.

AutoStopAction should always be set to saCommit when building a DataSnap server.  This means that the transaction will Commit when the last dataset connected to it is closed.  In the DataSnap environment, this generally happens when the dataset is fetching data for the client.  DataSnap will open the dataset without explicitly starting a transaction.  This causes the transaction to implicitly start.  When all data has been fetched, DataSnap will close the dataset, and the AutoStopAction will cause the transaction to Commit, keeping the transaction very short.

AutoStopAction does not play a role when updates are implied.  In this case, the dataset is never opened.  Instead, the PSExecuteStatement is used to execute UPDATE, INSERT, and DELETE statements, and DataSnap explicitly manages transaction control.

Which InterBase transaction isolation mode is appropriate when building a DataSnap server?  For datasets to be exposed to the thin client via the IAppServer interface, the transactions will be so short that it almost doesn’t matter.  I tend to use READ COMMITTED because this is essentially what the user is getting anyway — a lot of very short transactions over time is conceptually similar to one long READ COMMITTED transaction.  For longer processes run on the app server and exposed to the client via a single method in the interface, use whichever transaction isolation mode is appropriate for the process in question.  When using IBX, set the transaction isolation mode by double-clicking the TIBTransaction component.

Two Ways Clients Can Update Data

Changes to data in the database made by the client (INSERTs, UPDATEs, DELETEs, running procs, etc.) can in general be handled in one of two ways on a DataSnap server:

1. Make the changes on the client using one or more TClientDataset instances and then call TClientDataset.ApplyUpdates.
2. Add a new method to the application server’s interface and call it from the client.

In general, TClientDataset is used when the end user needs to interactively work with data, and new methods added to the application server’s interface are used when a non-interactive process is run.

When TClientDataset.ApplyUpdates is called, DataSnap (and the dataset components you used to build your application server) will handle transaction control on the server for you, and no code is needed to deal with this.

When a new method is added to the application server’s interface, that method should explicitly handle database transactions within the scope of that method.  It should never be necessary for the thin client to call a series of methods on the application server in succession, because this simply won’t work with a stateless server.  Instead, the thin client should call a single method on the app server, and that method should start and commit the transactions it uses internally.

Important note for IBX users: Set AutoStopAction to saCommit only when the transaction is connected to a dataset which is connected to a TDatasetProvider.  All datasets not connected to a TDatasetProvider (this essentially means datasets whose data will not eventually find its way into a TClientDataset, and the most common situation for this is a dataset used by a method added to the application server’s interface) should be connected to a TIBTransaction with AutoStopAction set to saNone.  To repeat: DataSnap handles transactions when using a TClientDataset.  When not using a TClientDataset, the developer must handle transactions explicitly.

Atomicity and Multiple Datasets

This is fairly straightforward when working with updates to a single dataset — simply call ApplyUpdates and let DataSnap do the transaction management for you.  But what happens when it is necessary to ensure that updates to more than one dataset succeed or fail as a group?  It depends upon how the datasets are related.  There are two possible cases:

1. Nested master-detail datasets
2. Datasets which are not nested master/detail

Nested master/detail datasets are automatically handled by DataSnap.  Just call ApplyUpdates on the master dataset as usual.

Occasionally it is necessary to ensure that updates to unrelated (non nested master/detail) are atomic (in other words, either all updates succeed or none succeed).  In order to handle this situation and still have a stateless application server, it is necessary to introduce a single method on the application server which will apply updates to all of the providers in question with only one single method call.  DataSnap does not do this "out of the box."  Fortunately, there is sample code available which shows how this is done.  Take a look at the CDSUtil unit in the Midas Essentials Pack project.

Generators

The topic of "autoincrement" columns in a DataSnap server is discussed in more depth in Dan Miser’s paper, "How to Use AutoInc Fields with MIDAS."  This discussion will focus on generators in particular as the general case has already been covered in the referenced paper.

Generators are made to solve multi-user conflicts and, as such, would seem ideally suited to the multi-tier world.  For the most part they are, but it’s not immediately obvious at which point the generator should be called. 

Should the generator value be retrieved on the application server, by the client, or in a trigger on the table?  

The answer to this question, and the question of whether or not a generator should be used to generate primary key values, depends upon a couple of different factors.  Application designers should ask the following questions:

1. Will the multi-tier system use a single InterBase server, or will it use multiple, replicated InterBase databases on multiple servers?  In other words, do the keys need to be guaranteed unique within the database, or globally?
2. Does the thin client ever need to know the generated value?
3. If the thin client does need to know the generated value, when? 

   Before the record is posted, or afterwards?  If the answer is "afterwards," keep in mind that, practically speaking, there must be an alternate key on the table in order to find the record again after it is posted.

If a key value must be globally unique (as is required in a replicated environment), there are a few potential solutions.  A system can use GUIDs instead of generators to create key values (it’s fairly easy to write an InterBase UDF which returns a GUID, and a GUID can either be stored as a string or as binary data in a CHAR(16) CHARACTER SET OCTETS domain).  Alternately, a compound primary key can be used which includes both a generated ID and a database ID.  Since dialect 3 generators are 64 bit integers, it’s also possible for each DB to just have their generator offset from the other — DB number 1 starts its generators at zero, DB number two starts its generators at one trillion, etc.

If the thin client must know the generated ID values, there are two ways to handle this situation.  Obviously, the thin client should not directly connect to the DB to call the generator, but the application server can expose a method which takes a generator name as an argument and returns the next ID for that generator.  Using this technique, the client data module can call the app server’s generator method in an OnNewRecord event and fill in the "correct" ID value when the record is first created, before it is applied to the server.

The other way is to use a temporary value when the record is inserted on the client dataset, and then fill in the "real" value when the record is applied in the application server.  The usual way to do this is to use a negative number in the OnNewRecord event and then to replace that negative number with the "real" generated ID in the TDatasetProvider.BeforeUpdateRecord event.  In this case it is necessary to set TDatasetProvider.Options.poPropogateChanges in order to ensure that the new value is sent to the client when the ApplyUpdates is complete.  

If the ID value is generated by a trigger on the table, then getting this value back to the client is very difficult.  TDatasetProvider.Options.poAutoRefresh is not implemented, since there is no obvious and universal way to do so.  Since InterBase provides no way to return values from a trigger, the only way to return this data to the client would be to take over the entire insert process in TDatasetProvider.BeforeUpdateRecord and then re-locate the record using an alternate key in a separate query after it is posted to the database, and finally to pass this value back to the client.  Ugh!  It will be far easier to either get the generated value elsewhere, as described above, or give up on returning it to the client.

You don’t need to delete the trigger, however.  It still might be useful if users can access the DB outside of the context of the application server (for example, with IBConsole).  Just change the trigger so that it only populates the ID value if it’s NULL.  For example, the SET_EMP_NO trigger in employee.gdb can be changed to:

CREATE TRIGGER SET_EMP_NO FOR EMPLOYEE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (new.emp_no IS NULL) THEN BEGIN
    new.emp_no = gen_id(emp_no_gen, 1);
  END
END

InterBase Events

An InterBase event is pretty simple — it’s just a signal that the event has happened without any additional data.  So in order to surface IB events in the thin client, we simply need a way to pass this information through the middle tier.  It’s simple enough to add a TIBEvents component to the main remote data module on the middle tier.  

To notify the clients when an event has been received on the middle tier, use one of the three techniques presented by Bill Todd in the "Calling Back to the COM Client" section of his paper MIDAS and COM Tips and Tricks.  For an in-depth explanation of how COM events work, look at Binh Ly’s paper on the subject.

Instancing and Threading Options

Since COM instancing and threading options have been discussed in detail by other authors, this section will only discuss how these options affect InterBase.  Before continuing, readers not familiar with these options might want to look at Binh Ly’s explanation of instancing options and Vino Rodrigues’s explanation of threading options.

Depending upon the options chosen, an application server can be multithreaded.  InterBase’s client has two rules for multithreaded programs:

1. No two threads may use a single connection to InterBase concurrently.  In VCL terms, an InterBase connection is a single

   TIBDatabase, TDatabase, or TSQLConnection component.

2. The connection must use the remote access protocol, even if the IB server and the client are running on the same machine.

The second rule means that a database path like the following example:

D:InterBaseExamplesDatabaseEmployee.gdb

…cannot be used in a multithreaded application.  Instead, it is necessary to specify a server name.  Use "localhost" as the server name if the IB server and client are running on the same machine:

localhost:D:InterBaseExamplesDatabaseEmployee.gdb

So to satisfy the second rule we must ensure that we use a host name if the application server has the potential to create multiple threads under any load, even if the threads themselves are protected from each other.  Since it’s relatively easy to change the threading and instancing options, the safest thing to do is to always use remote connections when writing DataSnap servers.

To satisfy the first rule we must ensure that no two methods on any RDM instance may be called concurrently.  Since an InterBase connection is, essentially, instance data of the RDM, this means that free threading is out!  The RDM’s class factory must be told to use either the tmSingle or tmApartment TThreadingModel.  (Alternately, the IB connection could be protected with a critical section.  But this is a lot of work, and there is no performance advantage to doing this since it essentially is re-implementing the protections that COM already provides with tmAparatment.)

As of this writing (BDE 5.2), the InterBase driver for the BDE is not thread-safe.  Projects using BDE components must specify tmSingle as the TThreadingModel for their RDM’s class factory.  IBX componets are thread-safe and can use either tmSingle or  tmApartment.  The InterBase driver for dbExpress was not thread-safe as shipped with D6. 

However, an updated version of the driver has been posted to the D6 registered users page.

Using Updateable VIEWs to Resolve Multi-table JOINs

The traditional way to resolve multi-table JOINs which update multiple tables in DataSnap is to write a BeforeUpdateRecord event which takes over the update process, bypassing the DataSnap resolver.  (Multi-table JOINs which only need to update a single table can be dealt with simply by handling the TDatasetProvider.OnGetTableName event.)  

However, InterBase users have another option.  Instead of writing the JOIN in the TIBDataset.SelectSQL property, put it in a VIEW.  Then create triggers on the VIEW to make the VIEW updateable (see the InterBase Data Definition Guide chapters on VIEWs and triggers for details and examples).

You can then treat the VIEW in your application like it was a simple table, and not a JOIN at all.  DataSnap will never know the difference.

Creating the Application Server and Thin Client Projects

It is necessary to start separate projects in Delphi for the application server and the thin client.  The thin client is a normal VCL application.  Create this project by choosing File->New->Application from the Delphi main menu, and save the project under a sensible filename.

It is possible to create the project for the application server the same way, and, indeed, this is what most of the Delphi demo application servers do. 

But I’m going to take Bill Todd’s advice and create the application server as an ActiveX Library.  Since this is a DLL, the application server will not display a form on the screen when it runs, which is appropriate since app servers typically run unattended.  Close your thin client project and choose File->New->Other…, then select the ActiveX tab, and finally double-click ActiveX Library to create the app server project.  Save it under its own name.

In order to turn these skeletons into a useful application, we need to add data modules and business processes to the application server, and we need to add a user interface to the thin client application.  The data modules in  the application server project usually subclasses of TRemoteDataModule, so they’re generally referred to as remote data modules.  TClientDataset is used in the thin client project to buffer data from the app server, and I usually put TClientDatasets on a "normal" (that is, non-remote) data module on the client application.  I call these data modules client data modules.  Each client data module connects to a single remote data module; there is a one-to-one correspondence.

Using Multiple Data Modules

Architecture

The simplest possible three tier system would use a single remote data module and a single client data module connected to it, like this:

In a typical application, however, we will usually want to use multiple data modules on both the application server and client tiers.  The number of queries needed by the application may be too great to conveniently fit on a single data module, or it might make sense to put queries related to a single task on their own data module.  This turns out to be a somewhat complicated undertaking in a multi-tier project, so we’re going to spend some time developing a robust and scaleable architecture.

On the application server tier, there will be one data module encapsulating the connection to InterBase, and a number of other data modules which contain datasets (such as TIBDataset, TIBQuery, and TIBStoredProc) used for particular tasks.  We will call the data module which encapsulates the connection to InterBase (and does quite a bit more, as we will soon see) the "main"

remote data module, and the task-oriented data modules "child" data modules.  The child data modules will use the single connection to InterBase which is established on the main data module. 

Likewise, on the client tier there will be one data module which encapsulates the connection to the application server, and a corresponding task-oriented data module for each child remote data modules.  Accordingly, we’ll call these the main client data module and the child client data modules.  The child client data modules share the single connection to the application server which we established on the main client data module.  The architecture now looks like this:

It is immediately obvious that the child remote data modules have a lot in common with each other, and the same is true for the child client data modules.  So it makes sense to create an abstract ancestor for each, and to subclass it when creating the actual (concrete) data modules we’ll create in our application.  Readers familiar with UML might find the following diagram helpful:

Before we can create the Employee and Project data modules, then, we need to:

• Create the main connection data modules for the application server and the client
• Create the abstract child data modules for the application server and the client

Implementation of Main Connection Data Modules

The term "connection" can be confusing in an n-tier application framework, because there are two different types of connections we have to consider: The connection between the application server and the DB server, and the connection between the thin client and the application server.  In each case, we’ll use a single data module to encapsulate the connection, and then use child data modules to implement queries and business rules.  The child data modules will attach to each process’s single connection data module and use the connection established there.

Creating the Application Server’s Main Connection Data Module

The Application Server project should still be open in the IDE.  We need to add a TRemoteDataModule to the project to handle connecting to the InterBase server.  Later, we’ll create child data modules which use this connection to get actual work done.  Creating the RDM is simple enough:

1. Choose File->New->Other…, multi-tier tab, then choose Remote Data Module.
2. Specify a CoClassName name for the new RDM.  I usually give the name the prefix "rdm," so a typical CoClassName would be something like "rdmEmployeeInfo."
3. Leave the Instancing and  Threading Model drop-downs at the default settings ("Multiple" and "Apartment", respectively).  We’ll examine what these do later on.
4. Press OK.

Note that you don’t need to add any code to instantiate the RDM at runtime — this code has already been generated for you, in the initialization section of the RDM’s .pas file.  A component factory has been created so that when a client connects to the server you’re writing, the appropriate RDM will be instantiated.  All of this happens without any intervention required on your part.

Now add the components required to connect to InterBase.  Drop a TIBDatabase and TIBTransaction (from the "InterBase" tab) onto the RDM. 

Set them up as usual.  In this case we’re going to be using the Employee.gdb database which comes with InterBase.

1. Set the DefaultTransaction property of the TIBDatabase to the TIBTransaction component, and set the DefaultDatabase property of the TIBTransaction to the TIBDatabase.  
2. Double-click on the TIBDatabase, and enter the location of the Employee.gdb database (and the server name, if you’re connecting to a remote server).
3. Enter a username and password for your server in this dialog as well, and un-check Login Prompt.  We’ll discuss dealing with multiple user logins later.

Finally, choose Run->Register ActiveX Server from the Delphi menu.  This registers the app server on your development machine so that you’ll be able to use it when you write the client.  You’ll have to register the server when you deploy it on your customer sites as part of your installation routine.  Save the project and open up the client project.

Creating the Client’s Main Connection Data Module

The client app’s main connection DM is nothing more complicated than a normal Delphi TDataModule.  Choose File->New->Data Module from the Delphi menu.  Change the name to something sensible — I use dm as a prefix for

"standard" data modules and rdm as a prefix for remote data modules.  So one can easily tell that dmMainConnection and rdmMainConnection are going to be connected together when the system runs.

Next, we add a component which will make the connection to the application server.  For the purposes of this demo, I’m using TDCOMConnection, but Delphi lets you easily choose SOAP, CORBA, sockets, or other protocols if you need them.  Switch to the Object Inspector, drop down the ServerName combobox, and you should see a list of servers registered on your machine.  If you remembered to do the registration step described above, your new app server should be in the list.  The name will take the form of Project_Name.CoClassName, so in this case we look for "AppServer.rdmMainConnection" 

Once you’ve selected this, you can change the Connected property to TRUE.  When you do this, your application server will be started up.  This is important to understand, because if the app server is running you can’t recompile it.  What this means is that, although you can have both the app server and the thin client open in the IDE simultaneously, you won’t be able to compile the app server while the thin client is connected to it in the IDE.  In general, it’s easier to work on them separately, except for debugging.

In some cases, we might want to connect and disconnect from the application server repeatedly over the course of the program, but for the purposes of this demonstration we’ll maintain a DCOM connection for the duration of the client’s execution.  So add the following OnCreate/OnDestroy handlers to the client main connection DM:

procedure TdmMainConnection.DataModuleCreate(Sender: TObject);

begin
  DCOMConnection.Connected := TRUE;
end;

procedure TdmMainConnection.DataModuleDestroy(Sender: TObject);
begin
  DCOMConnection.Connected := FALSE;
end;

Finally, we need to add some code to instantiate and free the main connection data module.  This is a very simple application so we’ll just instantiate it in the FormCreate and let Delphi’s Ownership mechanism free the DM.  When we’re finished and we run the program, the following series of events will happen:

1. Delphi will auto-create the main form.
2. The main form’s OnCreate handler will create the main connection DM.
3. The main connection DM’s OnCreate handler will set the DCOMConnection component’s Connected property TRUE, causing it to connect to the server.
4. The DCOM mechanism built into Windows will look up the server in the registry and start up the application server, if it isn’t already running.  It will then instantiate the CoClass for the main connection RDM, using the class factory that Delphi automatically created when you created the RDM.
5. The user will work with the application, and eventually close the main form when they’re done working with the program.
6. By Ownership rules, the main connection DM on the thin client will be freed when the main form, its Owner, is freed.
7. The OnDestroy handler of the thin client’s main connection DM will disconnect the DCOMConnection component.
8. If nobody else is connected to the app server, Windows will shut it down.

Implementation of Abstract Child Data Modules

Thus far, we’ve created the main data modules for the thin client and for the application server.  We also have a main form, and a working three tier framwork, but it doesn’t do anything.  We’d like to create child data modules which use the established connections to perform real work with the database, but if we start doing this we will quickly discover that they have a lot in common.  All of the remote data modules need to use the existing connection to the database, and all of the thin client data modules need to use the existing connection to the application server, for example.  We’ll create abstract ancestor data modules which encapsulate this behavior, and inherit our concrete data modules (the ones which actually do the work) from these.

We need to create two different types of abstract child data modules: One for the app server and one for the thin client.  For each business task, we’ll create a pair of concrete subclasses of these, which work together at runtime.

Creating the Application Server’s Abstract Child Data Module

1. Open up the app server project.
2. Choose File->New->Other…, multi-tier tab, then choose Remote Data Module.
3. Specify a CoClassName name for the new RDM.  Call it rdmAbstractChild.
4. Change the Instancing to Internal.  Child RDMs can only be created by the main RDM, not by an external COM client.  This is important because it means that the child RDM will run in the same thread as the main connection RDM, so it’s OK for them to share an InterBase connection.  It also means that we’ll have to manually instantiate the child RDMs — more on this when we talk about the TSharedConnection component later on.
5. Leave the Threading Model at the default (Apartment) and press OK.

We now have a new child RDM, but to make it useful we’re going to add some abilities which the "real" child RDMs will use — namely, the ability to use an existing connection to InterBase.  We’re going to add a property to the abstract child RDM which will allow us to assign an existing InterBase connection and have it be automatically used by every dataset on the child RDM.

Key Point: When we deal with RDMs from outside the scope of the RDM itself (for example, when we are dealing with a child RDM from the scope of the main connection RDM, or when we are dealing with the main connection RDM from the scope of the thin client) we are working not with the object itself but with its interface.  If you’ve used Delphi interfaces before, this should sound familiar.  The only real difference between an RDM interface and Delphi interfaces in general is that we need to use the Type Library editor to alter RDM interfaces, because there is some important code which will be kept synchronized with the interface.  

Using the Type Library Editor

So in order to add this property to the abstract child RDM, we need to use the Type Library editor.  Choose View->Type Library from the Delphi menu. You should see that an interface has been created for your abstract child RDM.  In the tree view on the left, you’ll see both the interface itself and the CoClass name.  Don’t confuse these.  The interface name will be the same as the CoClass name with an ‘I’ prepended.  Select the Interface in the tree view, and click the drop down arrow to the right of the

"New Property" button at the top.  If you don’t know what the icons on the buttons mean, just float the cursor over them and read the tool tips.  Choose "Write only" — we need to assign the IBDatabase to the abstract child data modules, but we don’t ever need to read it.

When you click "New Property" you will see that two methods are added to your interface.  Just like regular Delphi interfaces, RDM interfaces have no state, so properties must be implemented with

"getter" and "setter" methods.  Click on either method and change the Name of the property to IBDatabase using the edit boxes on the right.

Now we need to look at the type of the property.  By default the Type Library editor uses standard COM types, so they may look a little unusual to those unfamiliar with COM.  You can tell the Type Library editor to use Pascal syntax instead by choosing Tools->Environment Options-Type Library, and changing Language to Pascal.  This becomes especially confusing in that it changes some aspects of how the Type Library editor works.  Since the default language is MIDL (COM types) instead of Pascal, I’ll stick with that for the purposes of this paper.  To change the type of a property, just select the appropriate COM type from the Type drop-down.  You can find a list of equivalent COM and Pascal types on page 34-12 of the D6 Developer’s Guide.

The immediate problem we face is that there is no "TIBDatabase" type in the drop-down.  This is because the "TIBDatabase" type is not built into COM.  But a TIBDatabase reference is just a pointer, and a pointer is just an integer, so we can just pass the value as an integer and cast it when necessary.  So leave the property’s type at the default, which is "long."

Before leaving the Type Library editor, click the "Refresh Implementation" button at the top.  You should do this whenever you’ve changed the type library for your project.

Finally, we need to implement our property in the class that supports it, namely TrdmAbstractChild.  The Type Library editor has already created the skeleton code for us; we just have to fill in the implementation:

uses
  IBCustomDataset, IBDatabase, IBSQL;

procedure TrdmAbstractChild.Set_IBDatabase(Value: integer);
var
  i: Integer;

begin
  for i := Pred(ComponentCount) downto 0 do begin
    if (Components[i] is TIBCustomDataset) then begin

      TIBCustomDataset(Components[i]).Database := TIBDatabase(Value);
    end else if (Components[i] is TIBTransaction) then begin
      TIBTransaction(Components[i]).DefaultDatabase := TIBDatabase(Value);
    end else if (Components[i] is TIBSQL) then begin

      TIBSQL(Components[i]).Database := TIBDatabase(Value);
    end; // if
  end; // for
end;

This method simply tells every dataset or TIBSQL on the DM to use the IBDatabase specified.  By setting the property we’ve created, everything on the DM will be connected to the correct InterBase connection.  We’ll actually set this property later on, when we create the child RDMs at runtime.

Before switching back to the client project register the app server by choosing Run->Register ActiveX Server from the Delphi main menu.

Creating the Thin Client’s Abstract Child Data Module

The abstract child data module for the thin client needs the ability to share the connection to the application server, which has been established on the thin

client’s main connection data module.  Delphi 6 includes a component, TSharedConnection, which makes this fairly easy.  

Open up the thin client project and create a new data module (File->New->Data Module).  Change the Name to dmAbstractChild.  Save your work.

I’m going to suggest adding an additional bit of indirection to the mix.  You will eventually be creating subclasses of this abstract child data module, and in those subclasses you would be connecting TClientDatasets to the TSharedConnection component.  What would happen if you wanted to get rid of the TSharedConnection component and replace it with something else — say, TSOAPConnection?  You’d have a lot of work on your hands!

Another new D6 component, TConnectionBroker, resolves this issue.  Add both a TSharedConnection and a TConnectionBroker component to your new DM.  Set the Connection property of the TConnectionBroker to the TSharedConnection.  You can connect the TClientDatasets in your child data modules to the TConnectionBroker component.  Then, if you want to use a different connection component in place of the TSharedConnection, you will have much less work to do!

Add the client’s main DM to the interface uses clause of the abstract child DM and then select the app server project’s main RDM as the ParentConnection on the TSharedConnection component.

Most of the framework to support the "actual" child data modules — the ones that do the real work in our application — is now in place.

Implementation of the Child RDMs

Creating a New Child RDM

This may seem very complicated at first, but it’s straightforward once you’ve gotten used to it.  Unfortunately, the Remote Data Module wizard and the Type Library editor don’t deal with inheritance as well as the rest of the Delphi IDE does, so we have to take care of some of this manually.  We also need to add a property to the main connection RDM for each child RDM we create — this is required by the TSharedConnection component, which we’re using on the client side.  I’ve broken the process down into seven stages:

Create the RDM Itself

1. Choose File->New->Other…, Multi-tier tab, then choose Remote Data Module.
2. Specify a CoClassName name for the new RDM.  I usually give the name the prefix "rdm," so a typical CoClassName would be something like "rdmEmployeeInfo."
3. Change the Instancing to Internal.  Child RDMs can only be created by the main RDM, not by an external COM client.
4. Leave the Threading Model at the default (Apartment) and press OK.

Change the Ancestor of the RDM Interface

1. Choose View->Type Library. 
2. In the Type Library Editor, find the interface for the RDM you just created.  Don’t confuse the CoClass with the interface, as both of them are shown in the Type Library Editor tree view.  The name of the interface is the name of the CoClass with an "I" prepended.  For example, if the name of the CoClass is "rdmEmployeeInfo," then the name of the interface will be "IrdmEmployeeInfo."
3. On the Attributes tab, change the Parent Interface from the default of IAppServer to IrdmAbstractChild.
4. Press the "Refresh Implementation" button to update the RDM’s .pas file.

Change the Ancestor of the RDM

1. Select the tab for the .pas file for your RDM in the Delphi code editor.
2. Add AbstractChildRDM to the interface uses clause of the unit.
3. Alter the class declaration by changing the ancestor class from TRemoteDataModule to TrdmAbstractChild.
4. Press the F12 key to switch to the form, then right click on the form and choose "View as Text" (or press Alt+F12).
5. Change the word "object" at the start of the DFM to "inherited"
6. Right click on the code editor and choose "View as Form" (or press Alt+F12 again).
7. Save the new RDM.

Add a Global Variable for the COM Object Factory

COM objects are instantiated by special objects called object factories.  The Remote Data Module Wizard adds code to the initialization section of the unit for the new RDM to create the COM object factory, but it doesn’t keep a reference to the COM object factory so that we can refer to it in code later on.  After we add a property to the Main RDM which will allow TSharedConnection on the client application to connect to the new Child RDM, we need to add a "getter" method for that property which actually instantiates the new COM object.  This will require that we have a reference to the appropriate COM object factory.  (You can also find the COM object factory using COM API calls, but this is the way that the Shared Connection example does it, so I’m sticking to that.)

1. Add a global variable to the unit for the new Child RDM of type TCOMObjectFactory.  The conventional place for this declaration is just below the class type declaration.  I usually name the COM object factory similarly to the RDM class.  So if the RDM class is "TrdmEmployeeInfo" the COM object factory variable declaration would read, "var EmployeeInfoObjectFactory: TCOMObjectFactory"
2. Find the line of code in the unit’s initialization section (at the bottom of the unit) which creates the COM object factory and insert your variable at the front.  For example, the new line of code might read:

EmployeeInfoObjectFactory := TComponentFactory.Create(ComServer, TrdmEmployeeInfo,
    Class_rdmEmployeeInfo, ciInternal, tmApartment);

Add "inherited" Calls to Methods From the Abstract Child RDM

The Type Library Editor doesn’t totally understand Delphi’s form inheritance and it will add function declarations (with empty implementations) for all methods defined in the Abstract Child RDM’s COM interface.  You can just delete the declarations and implementations and everything will work fine, but the Type Library Editor will put them back the next time you click the "Refresh Implementation" button.  Since this would prevent these

methods from working correctly if you didn’t notice it, the easiest way to ensure that everything works is to fill in the blank implementation created by the Type Library Editor with a call to the inherited method from the Abstract Child RDM.

1. For inherited procedures, just type the word "inherited;"
2. For inherited functions, you need to be a little more verbose, due to Object Pascal’s syntax requirements.  If a function’s declaration is "function Foo(const SomeArgument: integer): integer" for example, the correct way to call the inherited Foo is to type. "Result := inherited Foo(SomeArgument);"
3. Do this for each inherited function or procedure from the Abstract Child RDM

Add a New Property to the Main RDM for the New Child RDM to the Project’s Type Library

1. Choose View->Type Library.
2. Click IrdmMain to select it.
3. Click the drop-down arrow just to the right of the "New Property" button on the Type Library Editor, and choose "Read Only."
4. Give the new property a name, such as "EmployeeInfoDM" and change the Type of the property to IrdmAbstractChild.  Due to interface inheritance, you have three choices when selecting the Type of the property.  You could declare it to be of type IAppServer, IrdmAbstractChild, or IrdmEmployeeInfo.  Since access to the RDM on the client is mediated through TSharedConnection, the RDM connection will appear to be of type IAppServer until you cast it as something else, since TSharedConnection.GetServer returns an IAppServer reference.  So from the client’s perspective it doesn’t matter which type you choose.  On the server side, however, making the property of type IrdmAbstractChild allows us to instantiate all Child RDMs in exactly the same way, while at the same time using features of the RDM (such as a shared database connection) which go beyond what is defined in IAppServer.
5. Press the "Refresh Implementation" button.

Implement the Property

A new instance of the Child RDM is instantiated whenever the client application reads its corresponding property on the Main RDM.  This is usually done by using the TSharedConnection component on the client application.  You need to add code to the app server to actually instantiate the Child RDM whenever the property is read.  You create a new instance of the Child RDM using the CreateCOMObject method of the Child RDM’s COMObjectFactory.  Since CreateCOMObject returns a Result of type TCOMObject, you need to use the as operator to cast this result as IrdmAbstractChild.

1. Add the unit containing the new Child RDM to the Main RDM’s implementation uses clause.
2. Add code to the "getter" method for the new property (which the Type Library Editor created for you) to instantiate the new instance of your Child RDM.

The simplest possible example of instantiating a new Child RDM is:

function TrdmMain.Get_EmployeeInfoDM: IrdmAbstractChild;
begin
  Result := EmployeeInfoObjectFactory.CreateCOMObject(nil) as IrdmAbstractChild;

end;

However, it is probably necessary to set certain properties on the Child RDM before returning the Result, such as assigning a shared database connection.  You may have other properties in your own applications.  It is likely that most Child RDMs will share these properties, so it would be nice to encapsulate the setup of a "typical" Child RDM in a single method.  Since the only thing which will change is which COM object factory is used to instantiate the new Child RDM we can create a new, private method in the main RDM to create a Child RDM which takes the COM object factory as a parameter and do all of the "typical" setup there:

function TrdmMainConnection.GetChildRDM(
  const COMObjectFactory: TCOMObjectFactory): IrdmAbstractChild;
begin
  Result := COMObjectFactory.CreateCOMObject(nil) as IrdmAbstractChild;
  // set up new Child RDM

  Result.IBDatabase := Integer(IBDatabase);
end;

We can then change the getter method for our new Child RDM property to call this private method:

function TrdmMain.Get_EmployeeInfoDM: IrdmAbstractChild;
begin
  Result := GetChildRDM(EmployeeInfoObjectFactory);
end;

If the new Child RDM required any special setup not required by the other Child RDMs, we could do it in the getter method for that particular Child RDM.  Remember to add the unit containing the object factory variable (usually the same unit which contains the RDM itself) to the implementation uses clause of the main RDM.

Creating a New Client DM

Creating a concrete child DM on the client side is much, much easier.  Just subclass the abstract child DM using the IDE (File->New->Other, choose the tab for your project name, select the abstract child DM, and press OK) and then set the correct child DM using the ChildName property of the inherited TSharedConnection component.  

It is now possible to add TClientDataset components to the new DM.  When working with the TConnectionBroker component, set the ConnectionBroker property of TClientDataset in lieu of the RemoteServer property.  You can then choose a provider as usual.

Standard Events

With the abstract/concrete data module architecture described above in place, it is now possible to add methods to the abstract data modules which will reduce the amount of coding necessary as the application evolves from a solid concept to a large implementation.  

For example, every dataset into which the end user can insert data will need to deal with generators, and the code do do this will look pretty much the same everywhere, save for generator and field names.  It is therefore possible to save a lot of work by writing generic routines to handle this task.

We can reduce errors by writing other routines for each data module which will hook up the standard events to every appropriate component on the DM.  This will prevent a developer from inadvertently disconnecting one of the events at design time, and not realizing their mistake.

Let’s examine generic handling for generators; other standard events follow the same pattern.  This example uses the "temporary value" technique described in the Generators section, above, mostly because it is a good demonstration of how to use standard events.

To refresh, we need to set the ID value of the primary key field to a negative number when a new record is inserted.  It doesn’t really matter what value we set the field to since it’s going to be replaced on the application server when we apply updates, but we can’t use the same number for two different records since TClientDataset will enforce uniqueness of any column marked as a primary key.  On the application server side, we need to examine primary key values in the TDatasetProvider’s BeforeUpdateRecord event, and replace any negative values with "real" IDs from the appropriate generator.

So in order to make this system work we need:

1. A means of "generating" temporary IDs on the client DM.
2. A means of determining which column is the primary key for any dataset on the client and app server (single-column keys are presumed, here).
3. Standard events for both the client and app server to do the work described above.
4. A convenient way of connecting the standard events for all datasets.

Creating a "generator" for the abstract child data module is easy enough: Just declare a class instance variable (a private field) for the "generator" and a function to retrieve its value:

type
  TdmAbstractChild = class(TDataModule)
  […]
  private

    iGenerator: integer;
  protected
    function GetGeneratorID: integer;
  public
    constructor Create(AOwner: TComponent); override;
  end;

[…]

constructor TdmAbstractChild.Create(AOwner: TComponent);
begin
  inherited;
  iGenerator := 0;
end;

function TdmAbstractChild.GetGeneratorID: integer;
begin

  Dec(iGenerator);
  Result := iGenerator;
end;

Figuring out which TField represents the primary key on the RDM is pretty easy, too.  DataSnap requires that the ProviderFlags.pfInKey option be set on the TField in order to work correctly, so we can just look for that:

resourcestring
  PRIMARYKEYNOTFOUND1 = ‘Primary key for ‘;
  PRIMARYKEYNOTFOUND2 = ‘ not found.’;

type

  ERDMError = class(Exception);

function TrdmAbstractChild.FindKeyField(const ADataset: TDataset): TField;
var
  iField: integer;
begin
  Result := nil;
  for iField := 0 to Pred(ADataset.FieldCount) do begin

    if pfInKey in ADataset.Fields[iField].ProviderFlags then begin
      Result := ADataset.Fields[iField];
      Break;
    end; // if

  end; // for
  if not Assigned(Result) then begin
    raise ERDMError.Create(PRIMARYKEYNOTFOUND1 + ADataset.Name + PRIMARYKEYNOTFOUND2);
  end; // if

end;

If your data structures use complex (multi-column) primary keys you will need a different means of determining which column should get the generator value.  Looking at the TField.Index or a naming convention are two possible alternatives.

A similar strategy can be used for the TClientDatasets if we adopt the rule that the ProviderFlags of their TFields must always be set up to indicate the primary key value.  This represents some work for the developer, but it’s a lot less work than writing events for each and every dataset.  Again, if this strategy doesn’t suit you, feel free to invent your own way of identifying the column which should receive the generated value.  By encapsulating this problem in a single method it’s easy to change strategies down the road.

Now we require standard events to set the temporary ID on the client side and to substitute the "real" ID on the app server side:

// Client DM
procedure TdmAbstractChild.CDSOnNewRecord(Dataset: TDataset);
begin
  FindKeyField(Dataset).AsInteger := GetGeneratorID;
end;

The easiest way to handle generators on the app server side is to use a single generator for every table in the DB.  InterBase 6 SQL dialect 3 generators are 64 bit integers, so one need not worry about running out of values.  Since it is a common practice to use one generator per table, however, I’ll demonstrate one possible method of matching tables and generators:

// App server RDM
function TrdmAbstractChild.GetGeneratorID(const ADataset: TDataset): integer;

var
  sTableName: string;
begin
  {Unfortunately, IProviderSupport lacks a GUID, so there is no "safe"
   way to do this.  This technique is what the VCL does, however.  And
   we can take some comfort in the fact that DataSnap won’t work at all
   if the dataset *doesn’t* support IProviderSupport.  The cast does look a
   little weird, however…}
  sTableName := IProviderSupport(ADataset).PSGetTableName;
  if sTableName <> ” then begin

    sqlGenerator.Transaction.StartTransaction;
    try
      sqlGenerator.Close;
      sqlGenerator.SQL.Text := ‘SELECT GEN_ID( ‘ + sTableName + ‘_GEN ‘
        + ‘ FROM RDB$DATABASE ‘;
      sqlGenerator.ExecQuery;
      Result := sqlGenerator.Fields[0].AsInteger;
    finally
      sqlGenerator.Transaction.Commit;
    end; // try-finally
  end else begin
    raise ERDMError.Create(CANNOTDETERMINGENERATORNAME);
  end; // if

end;

procedure TrdmAbstractChild.DSPBeforeUpdateRecord(
       Sender: TObject;
     SourceDS: TDataSet;
      DeltaDS: TCustomClientDataSet;
   UpdateKind: TUpdateKind;
  var Applied: Boolean)
var
  fieldKey: TField;
begin
  if (UpdateKind = ukInsert) and Assigned(SourceDS) then begin

    fieldKey := FindKeyField(SourceDS);
    if DeltaDS.FieldByName(fieldKey.FieldName).NewValue < 0 then begin
      DeltaDS.FieldByName(fieldKey.FieldName).NewValue := GetGeneratorID(SourceDS);
    end; // if

  end; // if
end;

Finally, we need to hook up the events for each component.  Here’s the app server side:

procedure TrdmAbstractChild.AddStandardEvents;
var
  iComponent: integer;
  Provider: TDatasetProvider;
begin
  for iComponent := 0 to Pred(ComponentCount) do begin

    if Components[iComponent] is TDatasetProvider then begin
      Provider := TDatasetProvider(Components[iComponent]);
      if not Assigned(Provider.BeforeUpdateRecord) then begin
        Provider.BeforeUpdateRecord := DSPBeforeUpdateRecord;
        // make sure changes get back to CDS

        DSP.Options := DSP.Options + [poPropogateChanges];
      end; // if
    end; // if
  end; // for

end;

procedure TrdmAbstractChild.DataModuleCreate(Sender: TObject);
begin
  inherited;
  AddStandardEvents;
end;

In the event that we don’t want the generic event to be added to a particular TDatasetProvider (or TClientDataset, on the client side) we can simply add a different handler for the event at design time.  The standard event is only added when there is no other event in its place. 

References

Building Applications With ClientDataSet and InterBase Express — by Bill Todd.  An introduction to using TClientDataset from an InterBase point of view.  Covers TClientDataset features and error handling in much more detail than this paper.

Delphi Developer’s Guide, chapters 23-25 — by Borland Technical Publications.  This often-ignored manual is included with Delphi.

Design Considerations for MIDAS/DataSnap — by Vino Rodrigues.  A basic introduction to DataSnap including comparisons of protocols ([D]COM/CORBA/etc.) and threading models.

How to Use AutoInc Fields with MIDAS — by Dan Miser.  Although written primarily from a Paradox/SQL Server point of view, this is the only in-depth discussion of the subject I’ve found.

InterBaseExpress: Tips and Tricks — by Jeff Overcash.  This paper includes comments on different DataSnap (MIDAS) features supported by the IBX datasets.

Hanging Thread/Server problem — by Dan Miser. "If you use the ChildRDM architecture available in Delphi 6 in %DELPHI%\Demos\Midas\SharedConn, there is a possibility of the server hanging in memory after all clients have exited. This really only applies if you are using a COM object in another DLL and using that COM object as a ChildRDM in your main server EXE."

MIDAS and COM Tips and Tricks — by Bill Todd.  In addition to being a great sales pitch for using three-tier techniques even in a two-tier application, this paper contains an in-depth discussion of communication between application servers and clients using several different techniques.

techvanguards.com/com — Binh Ly’s site.  Everything you ever wanted to know about low-level COM and Delphi, with explanations which make sense and tutorials that work.

When do I need to buy a MIDAS License? — by John Kaster.  Not every DataSnap project requires a license! Note that owners of D7 Enterprise or Architect don’t ever need a DataSnap license.

Share This | Email this page to a friend

Posted by Craig Stuntz on June 17th, 2004 under Conference Papers, Delphi, InterBase |

Server Response from: dnrh1.codegear.com