Resolving Joins with MIDAS 3

By: John Kaster

Abstract: Dan Miser describes some convenient ways to resolve joins using MIDAS 3

Resolving Joins with MIDAS 3

By Dan Miser

Resolving data to a MIDAS app server is usually as easy as calling ClientDataset1.ApplyUpdates. However, when the source dataset to which you are trying to resolve is a join, extra work needs to be done to resolve your changes to the database. For this paper, I will be using the following 3 tables:

CREATE TABLE EMP (EMPNO DOUBLE PRECISION,
        LASTNAME VARCHAR(20),
        FIRSTNAME VARCHAR(20),
        HIREDATE DATE);

CREATE TABLE FTEMP (EMPNO DOUBLE PRECISION,
        SALARY DOUBLE PRECISION,
        VACATION DOUBLE PRECISION);

CREATE TABLE PTEMP (EMPNO DOUBLE PRECISION,
        HOURLYRATE DOUBLE PRECISION);
This highly normalized table structure allows you to store data common to all employees in the EMP table, while using other tables to store information about full-time employees and part-time employees in their own tables.

Updating a single table

Many times, a join is used to act as a lookup to display data from a secondary table along with the data from the primary tasble. In this case, the primary table will be the EMP table, and the secondary table will be the PTEMP table. One possible use for this would be to permit a manager to view hourly rate information for an employee, but not allow that manager to modify the salary of the employee. That functionality would be in the domain of the Human Resources department after reams of paper-work gets submitted.

The SQL to accomplish this task is:

select e.*, p.hourlyrate from Emp e, PTEmp p
  where e.empno=p.empno
Any changes made on the client to the HOURLYRATE field should be ignored, as it is just for display. If you were to call ClientDataset.ApplyUpdates at this point, you would be met with a "General SQL error. Column unknown HOURLYRATE" error. This happens because MIDAS has no idea what to do with the HOURLYRATE column since it does not exist in the EMP table. In previous versions of MIDAS, the table name could not be determined during the resolving phase as well. So in order to update the data of the EMP table, we need to do the following:
  • Add persistent fields to the TQuery that represents the join.
  • Select all of the fields that are in the PTEMP table (in this case, HOURLYRATE), and set all of the elements in the TField.ProviderFlags property to false.
  • Add a OnGetTableName event to the TDatasetProvider, and use the following code:
    procedure TJoin1Server.prvJoinGetTableName(Sender: TObject;
      DataSet: TDataSet; var TableName: String);
    begin
      TableName := 'Emp';
    end;
    
An alternative approach is possible if you use Delphi 5, since MIDAS will use the UpdateObject of the TQuery component during the resolving phase. Therefore, you can avoid the above steps by adding a TUpdateSQL component and assigning the TQuery.UpdateObject property to point to the TUpdateSQL component. For more information on how to use the TUpdateSQL component, see the online help file or the manuals.

Regardless of which approach you used, you can now call ClientDataset1.ApplyUpdates, and MIDAS will only resolve to the table you specified in the OnGetTableName event. Furthermore, MIDAS will only write data to the columns that have the ProviderFlag.pfInUpdate set to true.

Updating multiple tables

If you need the ability to resolve both tables back to the database, you have to do some more work. Fortunately, we can automate much of the process by writing a custom TUpdateSQL component. Prior to Delphi 5, the only way to resolve to multiple tables was to use the Provider.BeforeUpdateRecord event to break the data packet apart and apply the updates manually to the individual tables. Take a look at ClientDataset as a Replacement for Cached Updates for some background on this, along with the code to resolve the join using the BeforeUpdateRecord technique.

Delphi 5's ability to use the TQuery.UpdateObject during the resolving phase may lead you to assume that you can use the TUpdateSQL component to resolve to multiple tables in the same manner that you would when using cached updates. Namely, to place one TUpdateSQL component on the Remote DataModule per table, and assign the TUpdateSQL.Dataset properties to the joined dataset. When using cached updates, you would call TUpdateSQL.Apply in the TQuery.OnUpdateRecord event to bind the parameters of the TUpdateSQL component and execute the proper SQL statement. Fortunately, Delphi 5 now calls the TQuery.OnUpdateRecord event during the resolving phase.

However, the problem with this approach is that TUpdateSQL.Dataset property is declared as a TBDEDataset, and we need to work with the Delta dataset, which is based on TPacketDataset - another branch of the TDataset heirarchy. So when MIDAS passes the Delta dataset into the TQuery.OnUpdateRecord event, you cannot legally assign UpdateSQL1.Dataset := Dataset. At this point, you could use the same solid technique from Delphi 4 and resolve the join in the Provider.BeforeUpdateRecord event, but the easier and more consitent way would be to remove the TBDEDataset dependency from the TUpdateSQL component.

The declaration of the component is listed here:

type
  TDSUpdateSQL = class(TUpdateSQL)
  private
    FDatabaseName: string;
    FSessionName: string;
    FDataset: TDataset;
  protected
    procedure SetParams(UpdateKind: TUpdateKind); virtual;
  public
  published
    procedure Apply(UpdateKind: TUpdateKind); override;
    property Dataset: TDataset read FDataset write FDataset;
    property DatabaseName: string read FDatabaseName write FDatabaseName;
    property SessionName: string read FSessionName write FSessionName;
  end;
You can see that the component is rather simple. We redclare the Dataset property to be of type TDataset, introduce DatabaseName and SessionName properties to allow the update to occur within the the same session, and override the Apply method to take control of parameterizing the SQL properly. There is also a static override of the SetParams method. We cannot use TUpdateSQL.SetParams because that method uses FDataset, which as you recall, is declared as TBDEDataset. Perhaps in a future version of Delphi, the TUpdateSQL.Dataset property will be declared as a TDataset, rendering this component all but obsolete.

Let's see how this component works by using the following SQL for this example:

select * from Emp e, FTEmp f
  where e.empno=f.empno
We want to allow the user to update any field in the entire record set. When using the TDSUpdateSQL component, the code for resolving the join back to both tables appears below, and looks very similar to the code used for cached updates:
procedure TJoin2Server.JoinQueryUpdateRecord(DataSet: TDataSet;
  UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
begin
  usqlEmp.SessionName := JoinQuery.SessionName;
  usqlEmp.DatabaseName := JoinQuery.DatabaseName;
  usqlEmp.Dataset := Dataset;
  usqlEmp.Apply(UpdateKind);

  usqlFTEmp.SessionName := JoinQuery.SessionName;
  usqlFTEmp.DatabaseName := JoinQuery.DatabaseName;
  usqlFTEmp.Dataset := Dataset;
  usqlFTEmp.Apply(UpdateKind);

  UpdateAction := uaApplied;
end;

The samples created in this article are available in CodeCentral. In order to run the samples, create a BDE alias named JOINS and point it to the JOINS.GDB file in the DATA directory. In order to run the JOIN2 sample, be sure to install the DSUpdateSQL component first.

About the Author

Dan Miser is a long-time Delphi user, speciailizing in multi-tier application design and development. He is active on the Borland newsgroups, where he serves as a proud member of TeamB. Dan is a frequent speaker at the Borland Conference, and was a contributing author to "Delphi 5 Developer's Guide".

You can visit his Web site at http://www.distribucon.com/.


Server Response from: ETNASC04