Delphi 2 - Delphi Client/Server Suite 2.0 Client/Server Database Architecture

By: Lance Devin

Abstract: Delphi Client /Server Suite incorporates integrated technologies that help solve your business needs.

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

B. SQL Monitor

Delphi Client / Server Suite 2.0 is the only RAD tool that integrates a native SQL monitor for testing, debugging and tuning SQL queries in Client / Server applications. This in turn increases developer productivity and application performance.

The SQL Monitor enables the developer to trace calls between the client and server. This information allows the developer to find problematic SQL statements and then optimize the SQL transactions. A series of trace options lets the developer customize the amount and type of information that is reported on. The SQL Monitor helps the developer know that the SQL in the application is being performed optimally, what is the SQL generated by the Borland Database Engine, if the Database Client Libraries are functioning properly, and if the database server is executing a run-away query. The additional capabilities to save and print the session log enables more thorough testing.

Delphi Client / Server Suite 2.0 is the only RAD tool that integrates a native SQL monitor for testing, debugging and tuning SQL in Client / Server applications.

C. SQL Explorer

The SQL Explorer provides the information center for your database management demands; it supports the creation and modification of tables, aliases, stored procedures, triggers and business rules through interactive SQL. This graphical tool is an integrated database schema and content management utility tailored to the needs of professional database developers.

The SQL Explorer, unique to Delphi, makes database administration easier and more intuitive than having to use a separate non-integrated tool. A simple to use graphical interface is a perfect way to represent the complex relationships that exist in a database server. The SQL Explorer presents schema information from Oracle, Sybase, InterBase, Informix, DB2 and others. The developer can drag and drop fields, tables, and stored procedures onto the Delphi application form to build Client / Server database applications quickly. The developer can also issue SQL statements directed to multiple servers and multiple databases.

The SQL Explorer is an integrated tool for administering SQL and PC Databases from within the Delphi Client / Server Suite 2.0 environment.
The SQL Explorer, also manages the Scaleable Database Dictionary. The simple to use interface enables the developer to easily define new domains of extended field attributes and then associate those to a field. The next time the field is used in an application, all the attributes are automatically applied.

D. InterBase NT - Relational Database

Delphi Client / Server Suite 2.0 includes a two user InterBase NT developer license. Developers can create standalone client / server applications using a scaleable relational database. When the volume of data, or size of application grows, both the InterBase relational database and the Delphi application will scale accordingly.

InterBase is Borland's high performance, cross platform SQL Server. InterBase is available on over 15 operating systems, including: Windows 3.1, Windows 95, Windows NT, NetWare, SCO, Sun OS, Sun Solaris, HP-UX, IBM AIX, SGI IRIX, etc. InterBase is ANSI SQL 92 entry level conformant, supports server events for event driven programming, and has an exceptional concurrency model for multiuser access. InterBase offers record level locking and due to its Multi-Generational Architecture delivers superior performance because database read operations do not block database write operations.

Local InterBase, also available in Client / Server Suite 2.0, provides Delphi developers with their own single user ANSI 92 SQL conformant server for prototyping and development of true client/server applications on Windows 95 or Windows NT. Local InterBase has all of the same functionality as the multiuser versions of InterBase available for NT and Unix, including transaction control, stored procedures, triggers, and even event alerters, which enable event driven programming. This means that development can occur on a laptop while on the train, airplane, or at the customer site, and that the final database to be used can be changed when the application is ready to be deployed.

Using Delphi Client/Server Suite 2.0, developers can design, prototype, and test their Delphi/InterBase applications on one machine. InterBase offers an exceptional Windows 95 GUI interface including configurable property sheets, native 32 bit tools like the Server Manager and Interactive SQL tools, and the complete documentation in Windows 95 Help.

InterBase ensure that data is always available due to their excellent multiuser performance, high security, and fast recovery features. InterBase is used in the aeronautical industries by companies like Boeing and Lockheed for manufacturing, by the Money Store and many other banking institutions, and in financial trading centers like the Philadelphia, Boston, and Russian Stock Exchanges. The common thread among all these customers is the need for excellent multiuser performance, high security, and fast recovery when system failures occur.

IV. Delphi Database Component Architecture

Summary: Delphi Database Component Architecture offers modular and complete control of:

  • Transactions and database connectivity
  • Queries, Stored Procedures, Tables and Result Sets
  • User Interaction Models
  • Information Reporting

Delphi's Database Component Architecture is a high level object oriented encapsulation of the Borland Database Engine Architecture. The object oriented architecture offers a simple application programming interface with three benefits.

  1. Database developers concentrate on getting the data models correct while the GUI design engineers concentrate on the application front end.

  2. Developers can concentrate on fulfilling a business need instead of having to build everything themselves. Developers can purchase components from third party vendors to extend Delphi functionality.

  3. The amount of time it takes to learn how to use Delphi database controls is significantly reduced in comparison to many other tools because the GUI design model is the same as the database design model.

A. Connectivity and Transactions

The Database Component allows the developer to programatically change the type of connection (SQL Pass Through Mode) that is maintained between an application and a database server. The ability to change the type of connection means that the complete power of the database server can be harnessed to improve the flexibility of an application. It also means that connections can be managed to reduce the number of user accesses seen by the database server resulting in hard cost savings.

The Database component can also maintain a persistent connection to a server so that a server connection operation does not have to be re-established each time. This speeds up applications.

There exist three different types of connectivity maintained by Delphi:

  1. Shared AutoCommit: The Borland Database Engine implements a navigation update methodology. Singleton transactions are created to maintain the strictest level of coordination between the client result set and the data in the underlying database tables.

  2. Shared NoAutoCommit: This mode means that the Borland Database Engine is aware of all SQL transactions on the server and will coordinate the client application result sets with the underlying data on the database server. However, commits of the singleton transactions are not automatic and must fall under programmatic control.

  3. Not Shared: The Borland Database Engine in this mode doesn't know what transactions are taking place on the server and acts merely as a conduit of SQL statements to a back-end. This means there is no coordination between the client result sets and the underlying database tables. The developer must refresh the client view into the data. What this offers is access to non-standard SQL statements supported by the individual database servers. It also allows for run-time maintenance of the database server from within an application.

The flexibility offered by programatically choosing the type of connection means that the developer can chose to optimize for performance, for minimal contentions, for cost and/or for the amount of server control.

Property editors let you easily set Complex database properties

B. Tables, Stored Procedure and Queries

Tables, Stored Procedures and Queries are the components of Client / Server applications. All three of these object types share certain characteristics and are therefore implemented from a common class known as a Dataset. The separation of the Dataset from connectivity and data visualization means that implementation of business rules does not impact any other areas.

Delphi Client / Server Suite 2.0 componentizes Client / Server database access for drag and drop creation of applications.

Because of the componentization of the Borland Database architecture business logic can be applied to Tables, Stored Procedures, and Queries by creating methods on Before and/or After events such as posts, deletes, inserts and edits. This allows you to create new objects for flexibility.

1. Filters:

After a general query of a database it is common to want to successively pare down the list of results or to move through the list based on further criteria. Filters offer a flexible mechanism for subsetting the result set either on the client or on the server. In this way, the developer can choose what will offer the highest performance with the most flexibility.

Filter expressions are easily written in the Object Pascal language and have no limitations that are inherent in 4GL languages with respect to scope and breadth of function creation.

Filters facilitate the way people work by allowing users to scan through data more effectively. Being able to drill into a result set is one way to turn data into information by mapping to the way people work.

2. Update Mode:

The UpdateMode property determines how Delphi will find records being updated in a SQL database. This property is important in a multi-user environment when multi-user updates can cause conflicts.

When a user posts an update, Delphi uses the original record values to find the record in the database. This approach is called optimistic locking. The Update Mode specifies which table columns Delphi uses to find the record. In SQL terms, Update Mode specifies which columns are included in the WHERE clause of an UPDATE statement. If Delphi cannot find a record with the original values in the columns specified (if another user has changed the values in the database), Delphi will not make the update and will generate an exception, so that the application can implement custom behavior, perhaps logging the results.

The Update Mode property may have the following values:

  • WhereAll (the default): Delphi uses every column to find the record being updated. This is the most restrictive mode.

  • WhereKeyOnly: Delphi uses only the key columns to find the record being updated. This is the least restrictive mode and should be used only if other users will not be changing the records being updated.

  • WhereChanged: Delphi uses key columns and columns that have changed to find the record being updated.

Using the Object Inspector to visually change the Update Mode provides easy control for updating records in database servers. The Update Mode reduces the conflicts that arise in large, heavily used Client / Server applications which results in higher performing applications.

Server Response from: ETNASC04