Gary's Delphi Client Dataset based application

Gary's Delphi Client Dataset Based Application

Features:

Security:
- Application-level security via a local password lookup table, thereby allowing ease of change of passwords without the database passwords requiring alteration or being revealed to users which would allow unauthorised database access via other products
Briefcase mode:
- allows bidirectional client side cursors without need for troublesome server side cursors which may cause rowlocks
- allows continous editing of data even when not connected to network or if network fails, when reconnect to network, the server database can then be updated. If other users have changed the same records then user is shown a reconciliation dialog so that he can cancel his changes or over-ride the other users' changes
- allows user to continue editing data at home on a notebook computer away from network
- automatic checking to open the most recently edited data - opens local data preferentially to server data if either:
  - local data has edits that have not been applied to server, or,
  - datetime local data last edited is same as or later than datetime server data last edited
Cached updates:
- minimises network traffic by only sending/receiving data when the user wishes to update the server, and if local data is same as server data, the local data is opened.
- minimises data record locks on server database, thereby allowing improved multi-user performance.
- data is stored locally as soon as posted, thus minimising data loss if computer or network crashes
- at any time user can view all his changes made to the database since last applied to server, and can undo each change that has been made if needed, or indeed cancel all changes made. These changes are irrespective of when the data was last saved locally or retrieved from the local data.
Data filtering, data bookmarking and built-in search capabilities
Editing of lookup tables
Support for various reports:
- Delphi quick reports via inherited base report for ease of use
- Crystal Reports
- other Delphi third party reports
Support for full Delphi capabilities as needed:
- OLE automation
- COM,DCOM, CORBA, etc
- multimedia
- nested detail tables within a master record if needed
Easy upgrade to multi-tier application using MIDAS:
- move the DMServer datamodule to a MIDAS server application
- run the server application to register it with Windows
- add a remoteServer component to the application and connect components to corresponding ones on the server application
Backend independence:
- changing the backend database is simple, as long as database structure is identical:
  - just change the BDE alias details (esp. if file based database such as Access, Paradox)
  - for more control with SQL databases, may wish to change the TTables to TQueries with TUpdateSQL components and statements, but this is not mandatory
Simultaneous multiple database backends:
- user can select which database they wish to access without having to exit the program
- thus for example, one can view databases from different campuses as long as they are accessible
Generic base application that needs minimal modification to create end-user application:
- add, link & configure appropriate data access components:
  - TTables in DMServer
  - TProvider & TClientDatasets in DMClients
  - TDatasource in FormData
- set application settings in FormMain OnCreate event
- set database options in FormMain radiogroup.onChange event
- add data-aware components to FormData for end-user editing needs
- add data validation as needed
- add reports as needed
- add help file if desired:
  - set help file in FormMain.OnCreate
  - add help file integer links to each control as needed

Limitations:

the password table is currently in Paradox format, mandating that the BDE be accessible from the client machine
explicit transaction control has not been incorporated but can be implemented with ease
the local data is stored in client dataset format without encryption and thus may pose a security risk, these files could be physically deleted as long as all edits have been applied to the server, however, that would mean that the network would have to be available to retrieve new data.
master detail tables if used require a little additional coding to ensure that updates to the server are applied in the correct sequence depending on whether or not the master record is a new record or not.
no AdHoc reporting or query builder embedded but can be added via:
- ReportBuilder
- Crystal Reports

Requirements:

Delphi 4 Client Server or higher (higher will need modifications to TProvider)
Infopower database components appropriate to the Delphi version
Files needed on client computer:
- Borland Database Engine (required for password table lookup as well as database)
- DBclient.dll (if using Delphi 4)
- Application executable file
- Password tables (in same directory as application executable)
- If application server embedded in application (default) or is on client computer then:
  - If database in MS Access then need appropriate level of MS Jet Engine installed
  - Server database client files (if needed eg. Sybase)
Files needed on server computer:
- Server database
- If converting application to n-tier then the following files will need to be on a server:
  - the application server executable
  - Borland Database Engine
  - Server database client files (if needed eg. Sybase)
  - If database in MS Access then need appropriate level of MS Jet Engine installed

Architecture:

Password Dialog form:
- initial form presented to user which controls whether user has access to application
Main form:
- allows user to select which database to use
- once database is opened, the form becomes the main application navigation object which opens the data form as well as the various reports as needed.
Data form:
- controls display and editing of data as well as saving data to local files and applying updates to server
Advance Search Dialog form:
- this form clones the main data so that user can search for previous occurrence of a patient and thereby prevent need for double entry of demographic data assuming demographic data has not changed
Datamodules:
- DMServer:
  - contains database connection components:
    - TDatabase1 which is the main connection to the database
    - TTables (or TQueries/TUpdateSQL for SQL databases) that connect to tables within the database and are responsible for the updating of the server
- DMClients:
  - contains the TClientDatasets and their TProviders for linking to the DMServer objects
    - these access and save local data as well as sending/receiving data to and from the server via the DMServer links
other dialog forms to assist searching, etc.

Problems with TClientDatasets and their solutions:

Updating joined tables:
- in general, the BDE does not allow you to directly update query with a joined table (ie. RequestLive will return false), there are several solutions to this:
  - avoid using joined tables that need to be updated
    - would probably need to use lookup tables in the TClientDataset which is a bit more cumbersome and may not be feasible if a lookup table is very large
  - create a view on the server and thus the BDE will regard this as one table and thus be updateable
    - users would have to be forced off the server to make future changes to the view
    - simplest as long as server supports updateable views
    - there may be performance issues if the view uses multiple tables but cannot be properly filtered using the where clause until after the view is accessed???
  - use multiple TUpdateSQL objects:
    - need to add code to the TQuery's OnUpdateData event to handle the multiple tables and their associated TUpdateSQL objects
    - there is a problem with the TClientDataSet managing the key fields for the update statement's where clause:
      - if only one table in the join needs updating:
        
        Delphi 5 and up:
        
        Method 1:
        
        Add persistent fields to the joined TQuery.
        
        Set the TField.ProviderFlags=[] for every field on the TQuery that you won't be updating.
        
        Write the following code in the DatasetProvider.OnGetTableName event to tell MIDAS which table you want to update. Keep in mind that this new event makes it easier to specify the table name, although you could do the same thing in Delphi 4 by using the DatasetProvider.OnGetDatasetProperties event.
        
        TableName := 'name of the table you wish to update';
        
        By doing this, you're telling the ClientDataset to keep track of the table name for you. Now when you call ClientDataset1.ApplyUpdates(), MIDAS knows to try and resolve to the table name that you specified, as opposed to letting MIDAS try and figure out what the table name is.
        
        Method 2:
        
        use a TUpdateSQL component that only updates the table of interest. This new feature of Delphi 5 allows the TQuery.UpdateObject to be used during the reconciliation process and more closely matches the process used in traditional client/server applications.
        
        Delphi 4:
        
        supposedly can use method 1 above but the OnGetDatasetProperties event does not have a tablename parameter, so I am not sure how one can set it.
      - multi-table updates:
        
        from Dan Miser:
        For more complex scenarios, such as allowing the editing and updating of multiple tables, you need to write some code yourself. There are two approaches to solving this problem:
        
        The Delphi 4 method of using DatasetProvider.BeforeUpdateRecord() to break the data packet apart and apply the updates to the underlying tables.
        Using the Delphi 5 method of applying updates by using the UpdateObject property.
        When using cached updates with a multi-table join, you need to configure one TUpdateSQL component for each table that will be updated. Because the UpdateObject property can only be assigned to one TUpdateSQL component, you needed to link all the TUpdateSQL.Dataset properties to the joined dataset programmatically in TQuery.OnUpdateRecord and call TUpdateSQL.Apply to bind the parameters and execute the underlying SQL statement. In our case, the dataset we're interested in is the Delta dataset. This dataset is passed as a parameter into the TQuery.OnUpdateRecord event.
        However, the problem in using this technique in MIDAS becomes readily apparent when you try to do this for the first time. The TUpdateSQL.Dataset property is declared as a TBDEDataset. Because the Delta dataset is a TDataset, we cannot make this assignment legally. Rather than give up and use the DatasetProvider.BeforeUpdateRecord method of applying updates, I created a TUpdateSQL component descendant that will work seamlessly when resolving multi-table joins. The key to writing this component was to change the Dataset declaration to TDataset, and override the Apply method so we can call our own version of SetParams that will bind parameters to the target TDataset. Additionally, SessionName and DatabaseName properties were exposed to allow the update to occur in the same context as other transactions. The resulting code for the TQuery.OnUpdateRecord event is shown in Figure 10.
        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;
```
 
```