Delphi Client / Server Suite 2.0
Client/Server Database Architecture (cont.)

• V. Borland Database Engine (BDE) Architecture
  1. Borland Database Engine Componentized Architecture
  2. High Performance Native Drivers
  3. Transaction Models
     1. Navigational Updates
     2. New Cached Updates
  4. Transactions and Delphi Exception Handling
  5. Heterogeneous Database Access
  6. 32-bit Windows Support
• VI. Conclusion

V. Borland Database Engine (BDE) Architecture

• High Speed Native Drivers to Database Servers.
• A componentized architecture for robust applications.
• Flexible transaction models
• Heterogeneous database access
• Full 32-bit operating system support

As the core engine, the Borland Database Engine provides unparalleled compatibility and efficiency in accessing data stored in ORACLE, Sybase, Informix, InterBase, DB2, and MS SQL Server using high speed SQL Links native drivers, from dBASE and Paradox data formats using a native engine, or from other formats using the ODBC standard interface. The Borland Database Engine is also the heart of Borland's own database products for the Windows environment: Paradox for Windows, and Visual dBASE. Borland Database Engine is also field proven: it has been in commercial applications since 1990 and tested by well over two million users. The Borland Database Engine is designed from the ground up to be fast, flexible, powerful, scaleable and extensible.

A. Borland Database Engine Componentized Architecture

The new 32-bit Borland Database Engine consists of the following components that will allow for the fastest Client / Server applications and application scalability:

• A common database query engine that supports Structured Query Language (SQL). The Borland Database Engine has been optimized to efficiently deal with the semantics of both set-oriented SQL and record-oriented navigational databases through a single and unified engine. As a result, customer applications can fully benefit from the strengths of both access modes while offering performance and scalability. The new 32-bit query engine has full SQL 92 compliance and offers new functionality such as transactional support for local databases.

• High Performance Native Drivers: SQL Links with SQL 92 compliant access to Oracle, MS SQL Server, Sybase System10, Sybase System4, Informix, DB2, AS400, and InterBase. Native drivers provide the fastest way to connect to database servers by writing directly to Database Client Services.

• Paradox and dBASE drivers. These core database drivers can be queried using the new common database query engine. It also supports a full transactional model which allows multiple record updates to be grouped then rolled back or committed to the underlying table.

• An ODBC socket that allows access to any remote or PC data source for which an ODBC driver is available; such as Access, Terradata and /or, IMS datasources. Applications access all the features of Borland Database Engine even when using an ODBC driver. These features include: navigational access to data, new Cached Update transactional model, bi-directional cursors, and cross database operations.

• Shared System Services that provide mechanisms for data buffering, memory management; sort, query and batch functionalities; cursor and operating system abstractions; and more. These system services relieve the developer of many burdens, for example:
  1. Translate functions for seamlessly moving and scaling data from say, InterBase on NT to Oracle on a UNIX machine without having to translate data types. It also supports upsizing of data from PC tables in Paradox and dBASE.

  2. Memory or disk size allocations when caching database server result sets in order to prevent operating system errors.

• Borland Database Engine API: Delphi Client / Server Suite 2.0 has encapsulated much of Borland Database Engine into high level objects that can be edited visually in the design environment. More advanced functionality can accessed via the complete API of the Borland Database Engine. This means that there are no barriers to writing extremely powerful database applications.

• Internationalization: Applications are increasingly being created for an international market place to expand business potential and meet the needs of customers more readily. The Borland Database Engine has been designed to assist developers in creating and deploying applications for international markets. The Borland Database Engine comes with more than 50 language drivers. This helps an application developer in isolating language-specific help messages in a separate DLL. The sorted order, capitalization, accent and other language-specific features are supported by the Borland Database Engine as well.

B. High Performance Native Drivers

• Server Specific Optimizations: affect the speed that applications open and affect the interaction between the end-user and the application.

• New Functionality: Delphi Client / Server Suite 2.0's new integrated SQL Explorer takes advantage of new SQL Link schema inquiry functions that give additional information about the database server: Integrity constraints, function lists, stored procedure definitions, domains, synonyms, and view definitions. The developer can now use Delphi Client / Server Suite 2.0 to administer all aspects of a complete Client / Server application.

• Optimized Batch Operations: The new SQL Links optimizes network traffic on batch operations by performing them on the server when source and destination tables are on the same machine. There is no need to move the data from the Server to the Local machine and back to the server. It is also possible for the developer to control how many modified records to include in a batch.

• Governors have been added to limit the number of rows that are transferred to the client by using the MAX ROWS SQL setting. This allows developers to work in a draft mode to speed the application development process.

C. Transaction Models

While updating a database server, the developer must be aware of locking mechanisms imposed by the server and the implications these locks have on users of an application. Locking is important because it creates contentions between users for ownership of a data record when doing everyday database operations: delete, modify, insert, etc. The longer a lock exists, the higher probability that a contention will occur and disrupt normal work-flow.

Another area for the developer to be aware of is the coordination between the server data and the client result set. The highest level of coordination means that the server will have the most up-to-date information at any one time whereby a lower level of coordination means that data on the server becomes out-of-date. The level of synchronization or detachment between the client and the server is now provided for in two ways with Cached Update or Navigational Update modes.

1. Navigational Updates

a) Benefits:

1. Navigational Updates ensure that the result set of a SQL query on a client is kept in tight coordination with the underlying tables on a database server by making transactions atomic. It maintains the highest level of integrity between the server and the client.

2. Navigational Updates with Optimistic Locking reduces the amount of time a record, table, index page or record page is in a lock state by maintaining a copy of the record on the client and updating a user request in a SQL transaction with a single record change (singleton transaction).

b) Implementation:

Delphi and the Borland Database Engine implement a locking strategy called optimistic locking. The client requests a remote record, and gets a buffered copy of it to work with without instantiating a lock on the server. When a user attempts to modify the record with a delete, insert or modify statement, a singleton transaction is initiated. Within this SQL Transaction, the copied result set record is compared to the data that's in the underlying table. This means that there are two possible results for the comparison and thus the singleton transaction.

1. If the comparison doesn't find any changes or discrepancies in the underlying record, your edits are posted to the table.

2. If the comparison finds a change to the underlying record in the table your changes are ignored and an exception is raised indicating that someone else modified or deleted the record while you were working on it. Your application can then implement custom exception handling code, for example, to log the errors in a database or retry the update.

Delphi offers options for comparing the client copy of the record and the underlying table data. The developer can do a check against the complete old row, check the unique ID plus changed columns, or check just the unique ID.

2. New Cached Updates

a) Benefits:

1. When there are a high volume of users with many transactions being made to a database server, Cached Update significantly lowers the number of contentions that will occur thereby reducing the wait times for users.

2. Cached Update significantly lowers the number of record contentions imposed by Page Level Locking, Table Locking and Page Index Locking by reducing the amount of time that a contiguous or non-contiguous set of records are in a locked state.

3. Cached Update reduces the amount of network traffic between a client and a server by batching and packaging multiple communications into one communication.

4. Cached Updates enable the developer to separate user interaction from the underlying database records. This offers the ability to intercept transactions before they reach the database server and substitute customized updating code. In this way, transactions can be further optimized and developers given more control. For example, by turning on cached updates, user interaction with a join query, an otherwise non-live query, can be intercepted and parsed into separate table transactions with query plans.

b) Implementation:

The Cached Update mechanism reduces record contentions by buffering server requests on the client; thereby not imposing any locks on the record, record page, index page, or table. The cache intercepts and stores inserts, modifies and deletes made by the user to any given record. The cached record updates are then batched to the server within the context of a SQL Transaction.

Locks are typically implemented on a database server to forbid the same record being read and modified at the same by two different users. Since no record locks are held before the Cached Update send operation, there is a possibility that the same records are also being updated by another user within the time scope of your off-line transaction. This means there are three possibilities for the successful completion of a cached update:

1. All cached operations are submitted successfully.

2. Any one cached operation fails and the rest of the cached operations are aborted and rolled back. An exception is then raised so that custom code can then be implemented.

3. Any one cached operation fails and the developer can choose to skip or fix it within a callback event.

This implementation means that the developer is in complete control.

D. Transactions and Delphi Exception Handling

Robust Exception Handling: To make your applications robust, your code needs to recognize SQL exceptions, user exceptions and other system exceptions when they occur and respond to them accordingly. Delphi provides you with a mechanism to handle errors in a consistent manner that allows the application to recover without losing or corrupting data. Delphi pre-defines over 50 exception types (e.g. Access Violation, Math Error, Database errors) and allows the developer to define their own exception objects using inheritance of the base Exception class.

As a result, Delphi applications can recover from errors that in other p-code systems would result in an Access Violation; the result of which could lead to database corruption and frustrated users.

The combination of exception handling and transaction management provides for clean and readable code that is easily maintained. This code example below demonstrates the protection of a SQL transaction from any type of error without unnecessarily complex code to check the database server return codes, system resources, user errors, etc.

Procedure Transact_Except (Sender : TObject);
   begin
   try       { Should something fail in any code block statement, jump
             immediately to the Except clause. } 

        Database1.StartTransaction;    
        Query1.SQL.Clear;   
                                             {subtract from selected account}
        Query1.SQL.Add 
                (Format ('update accounts' + 'set balance = balance - %s)   
                where acct_num = %s ', [edit1.text, Table1[acct_num])); 
        Query1.ExecSQL;   
        Query1.SQL.Clear;
                                              {add to selected account} 
        Query1.SQL.Add 
                (Format ('update accounts' + 'set balance = balance + %s)
                where acct_num = %s ', [edit1.text, Table1[acct_num])); 
        Query1.ExecSQL;   
        Database1.Commit;

     except                                   {if error jump here}
         On e: DatabaseError do begin         {if database error do this}
                 database1.Rollback;
                 ShowMessage (e.message + 'Record changed before transaction')
         end; 
         On MathError do begin                {if math error do this}
               database1.Rollback; 
               ShowMessage(e.message + 'Illegal Math Operation'); 
               raise; 
        end;
   end {except block}
end; {procedure}

E. Heterogeneous Database Access

Delphi's Database Form Expert assists the developer in creating homogeneous or heterogeneous Client / Server database applications without writing complex code. It uses the Borland Database Engines linked cursor mechanism to create powerful master­detail database applications automatically generating the proper queries, forms and Delphi code.

Delphi's Database Form Expert creates master-detail applications easily

F. 32-bit Windows Support

• Multithreading within a single application. You can run multiple queries in the background within Borland Database Engine while using Borland Database Engine features in the foreground.

• Preemptive multitasking-multiple applications can run simultaneously and can access the same database files without work-flow interruption.

• Shared memory manager and shared buffer manager for lower resource usage and higher performance.

• Long filenames: You can give Borland Database Engine files long, descriptive names-up to 260 characters-that may contain spaces.

Multitasking queries and updates means you won't have to wait for naturally asynchronous operations. Taking advantage of the operating system means that applications can take on larger scope with more advanced functionality.

VI. Conclusion

The Borland Database Engine offers the strongest foundation for building client /server database applications. The Delphi Client / Server Suite 2.0 component architecture includes high level objects that encapsulate the Borland Database Engine and allows the developer to concentrate on application building. The new Database Application Architecture takes these abstractions a step further and provides a solid framework for delivering reusable, scaleable and high performing applications in a team environment.

Delphi Client / Server Suite 2.0 meets the needs of the corporation, the end user and the developer by incorporating leading edge technology that let's you develop better applications in less time than with any other tools.