Client Server Techniques

• Minimising network traffic
• Cached updates
• Stored Procedures
• see also:
  • Delphi Programming Main Index
  • Relational database management systems
  • Database access solutions
  • Client Server middleware tools
  • Two-tier client server
  • Multi-tiered client server
  • Database systems
  • Delphi database programming 1
  • Delphi database programming 2
  • Delphi data validation

Minimising network traffic:

• use TQuery with WHERE clause rather than a TTable
• use cached updates
• use stored procedures
• don't use RecordCount tools as this forces read of entire SQL database

Cached updates:

• enables you to retrieve data from a database, cache and edit it locally and then apply the cached updates to the database as a unit
• cons:
  • other apps can access & change the actual data on the server while your user is still editing their local copy
  • other apps cannot see any data changes made by your user until the changes are applied
  • inserting new records in tables that have an auto-incrementing primary key can cause problems if either:
    • using Paradox tables, updates do not always work as they should
    • inserting child records to these - how do you fill in a child foreign key when the foreign key is not yet known?
  • as the BDE uses Paradox files to store the temporary caches, this can create record locking problems on these temporary files with multi-users accessing the same BDE and same application executable file.
    • solve this by setting the BDE Paradox NetFile directory & Private Directory to a local temp directory such as c:\temp. (see Borland's FAQ4291D.txt)
    • unfortunately, this may not be possible if users also use Paradox files on a network and thus need a common shared directory for their NetFile directory.
• usage:
  • set a TDataset's cachedUpdates property to true or use a TClientDataset (which always uses cached updates)
  • create an update method, either:
    • use a TUpdateSQL component:
      • create SQL statements for its InsertSQL, DeleteSQL and UpdateSQL properties (right-click and use its editor)
      • if the Tdataset SQL contains more than one table, you will need to use a separate TUpdateSQL coponent for each table but as only one can be linked to the TDataset, the other(s) need to be coded via the TDataset's OnUpdate method.
    • create a custom OnUpdate method
  • create a mechanism for handling reconciliation of update errors:
    • call an ErrorReconciliation form in the TDataset's OnReconciliation method - will the ErrRec form work for TQuery as it does for TClientDataset as the exceptions are different????
  • to apply updates either:
    • call the TDatabase's ApplyUpdates (masterTDataset.name, DetailTDataset.name) method, this also calls the CommitUpdates method automatically
    • call the TDataset's ApplyUpdates then CommitUpdates to clear the cache
  • see documentation for usage with explicit transactional methods

Stored Procedures:

• SQL procedures stored on server with database metadata and activated to run on server
• allows summary analysis of large numbers of records on the server without having to send the records across the network
• cons:
  • different SQL RDBMs have different protocols creating migration difficulties