DELPHI basic course: Delphi client server application development (3) [3]

Declare procedure getdatabasenames (list: tstrings)

The GetDatabaseNames method clears the contents of the list and writes the names of all BDE aliases and application-defined aliases to the list.

GetDriverNames method

The declaration process gets the driver name (list: tstrings)

The GetDriverNames method clears the contents of the list and writes the names of drivers currently installed by BDE into the list.

GetDriverParams method

Declare procedure getdriverpams (constdrivername: string; List: t string)

The GetDriverParams method clears the contents of the list and writes the default parameters of the driver named DriveName to the list.

GetTableNames method

Declaration process gettablenames (constdatabasename mode: strings

Expand systemtable: boolean; List: t string)

The GetTableNames method deletes the contents of the list and writes the names of all tables in the database named DatabaseName into the list mode parameter, which will limit the table names. For SQL servers, setting system tables to True will get system tables and user tables. For non-SQL databases, setting Extensions to True will include extensions in the table name.

Handle client/server transaction control

There are two ways to manage transaction control in database applications using implicit control and explicit control.

● Use the properties and methods of the TDatabase component for explicit control.

● Use the transitive SQL of TQuery component to control transactions.

Delphi also supports local transaction processing of Paradox and dBASE tables.

Overview of transaction control

When creating a database application with Delphi, Delphi provides transaction control for all database access.

A transaction is a set of operations, and all their operations on one or more databases must be successfully executed before committing. If one of the operations fails, all operations will fail, that is, the transaction is atomic.

Even if a hardware failure transaction occurs, the consistency of the database should be guaranteed. When multiple users are allowed to access concurrently, the transaction will also maintain the integrity of the data.

For example, an application may update the ORDERS table to indicate that it accepts orders for a commodity, so it should also update the Innertory table to reflect the decrease in inventory. If a hardware error occurs after the first update and before the second update, the database will be in an inconsistent state because the inventory situation does not reflect the order situation. Under transaction control, two expressions will be submitted at the same time, and if one expression fails, it will be rolled back.

Use implicit control

By default, Delphi provides implicit transaction control for applications through BDE. When the application is under implicit transaction control, Delphi performs implicit transaction control on each record written in the dataset. It commits each independent write operation, such as Post and Append Record.

It is easy to use implicit transaction control, which ensures the minimum conflict of record update and the consistent view of database. On the other hand, because every row of data written into the database needs transaction control, implicit transaction control will lead to too busy network and poor application performance.

If we adopt explicit transaction control, we can choose the most effective time to start committing and terminating transactions, especially in the development of multi-user environment, where client applications run and access remote SQL servers.

Using explicit transaction control

There are two ways to control the transaction of Delphi database application, one is cooperative, and the other is independent.

● Methods and properties of using TDatabase components.

● Using transitive SQL in TQuery component is only valid in Delphi Client/Server Suite version. SQL Links directly transfers SQL expressions to process SQL or ODBC servers.

The advantage of using the methods and properties of the TDatabase component is that it provides clear and portable application functions independent of a specific database or server.

The main advantage of using transitive SQL is that you can use the advanced transaction management functions of a specific server.

Methods and properties of using TDatabase

The following table lists the methods and properties used for transaction management in TDatabase widgets and their usage.

Table T Database Method Table for Explicitly Controlling Transactions

━━━━━━━━━━━━━━━━━━━━━━━━

Method or property? use

────────────────────────

Commit Commit data modification and terminate the transaction.

Rollback cancels data modification and terminates the transaction.

StartTransaction starts the transaction.

TransIsolation indicates the level of independence of a transaction.

━━━━━━━━━━━━━━━━━━━━━━━━

StartTransaction Commit and Rollback are methods called by applications at runtime to start transaction control transactions and save or discard data modifications.

TransIsolation is an attribute of the TDatabase component, which is used to control how different transactions acting on the same table interact.

(1) Entrepreneurship

When you start a transaction, all subsequent expressions that read and write the database occur in the context of the transaction. Every expression is a part of it. Any modification made by any expression is either successfully submitted to the database or each modification is cancelled. Consider the problem of bank transmission on ATM. When a customer decides to transfer funds from a deposit account to a payment account, two modifications must be made in the bank database records.

● The deposit account must be debited.

● Payment account must be credited.

If one of the operations cannot be completed for some reason, then no operation should happen, because these operations are related and they should happen in the same transaction.

In order to start a transaction in Delphi application, you need to call the StartTransaction method in the TDatabase component.

database interbase start transaction；

All subsequent data operations occur in the context of the latest transaction until the transaction is explicitly terminated by calling Commit or Rollback.

So how long should we keep trading? Ideally, the longer the transaction is active, the more users will access the database at the same time. In the life cycle of a transaction, more concurrent transactions start and end at the same time, so it is more likely to conflict with other transactions when trying to commit changes.

(2) Submit the transaction

In order to make permanent modifications, the transaction must be committed using the commit method of the TDatabase component. Committing the expression will save the changes to the database and terminate the transaction. For example, the following expression will terminate the transaction started in the above example.

Interdatabase submission

Submit calls should be placed in a try…except expression. If the transaction cannot be committed successfully, you can handle the error and retry the operation.

(3) Turn back a transaction.

In order to cancel the database modification, the transaction must be undone by rolling back. Rollback restores the modification of the transaction and terminates the transaction. For example, the following expression will reverse a transaction.

Rollback between databases;

Rollbacks usually occur when

● Exception handling code

Low button or menu event code, such as the user clicked the cancel button.

(4) Use the TransIsolation attribute.

TransIsolation property describes the independence level of TDatabase component transactions. The independence level of a transaction determines how it interacts with other transactions on the same table. Before changing or setting the value of TransIsolation, you should be familiar with transactions and transaction management in Delphi.

The default value of TransIsolation is tiReadCommitted. The following table summarizes the possible values across isolation and describes their meanings.

The meaning of table TransIsolation attribute value

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Independent hierarchical meaning

──────────────────────────────────────

TiDirtyRead allows reading uncommitted modifications written to the database by other concurrent transactions. Uncommitted changes are not permanent and can be restored at any time. At this level, your transaction is the least independent of modifications made by other transactions.

TiReadCommitted only allows reading database modifications committed by other concurrent transactions, which is the default independence level.

IRepeatableRead allows a single database read transaction to view changes made to the same data by other concurrent transactions. This level of independence ensures that your transaction reads one record at a time and the view of the records does not change. At this level, your transaction is completely independent of changes made by other firms.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Various database servers support these independence levels to varying degrees, and some do not support them at all. If the server does not support the requested isolation level, Delphi will adopt a higher isolation level. Please refer to the following table for the independence levels supported by various servers.

If the application uses ODBC to interact with the server, the ODBC driver must support the independence level.

Use transitive SQL

In order to use transitive SQL to control things, you must

● Use Delphi client/server suite.

● Install the correct SQL Links driver.

● Configure the network protocol correctly.

● Ability to access the database on the remote server.

● Set the SQLPASSTHROUGH mode not to be shared with BDE configuration tools.

With transitive SQL, you can use TQuery TStoredProc or TUpdateSQL components to send SQL transaction control expressions directly to the remote database server BDE. BDE itself does not handle SQL expressions. Using transitive SQL allows users to gain the advantages of SQL server directly, especially when the controls are non-standard.

SQL PASS THROUGHMODE describes whether BDE and transitive SQL * * * have the same database connection. In most cases, SQLPASSTHROUGHMODE is set to SHARED AUTOMIT. However, if you want to transfer SQL transaction control to the server, you must use the BDE configuration tool to set BDE's SQLPASSTHROUGHMODE to NOT SHARED. In addition, an independent TDatabase component must be established for the TQuery component that transmits SQL transaction control expressions.

Use local transactions

BDE also supports local transactions on Paradox and dBASE. From a code point of view, there is no difference between local transactions and transactions on remote database servers.

When the transaction of the local database table starts, the update operation is recorded in the log. Each log record contains an old record buffer. When a transaction is active, updated records are locked until the transaction is committed or rolled back. During rollback, the old record is applied to restore the updated record to its original state.

Using stored procedures

Overview of TStoredProc components

A stored procedure is a database server-based program that accepts input parameters and returns the results to the application. The TStoredProc component operates on stored procedures in a database on a remote server. A stored procedure is a collection of expressions. As a part of the server, stored procedures perform a series of repetitive database-related tasks on the server and transmit the results to client applications, such as Delphi database applications.

The TStaredProc component enables Delphi database applications to execute stored procedures on the server.

Operations that usually act on a large number of records in database tables and use statistical or mathematical functions are the first choice for stored procedures. By transferring these repetitive computing tasks to the server, the performance of database applications can be improved.

● Make full use of the processing power and speed of the server.

● Reduce the number of network transmissions.

For example, consider the standard deviation of an application that needs to calculate a single value in a large number of records. If this function is executed in Delphi application, all the records used in the calculation must be obtained from the server, which will inevitably lead to network congestion, because the application only needs the final return value representing the standard deviation, so it is more effective for the stored procedures on the server to read data, perform calculations and pass the values to the application.

Lishi Xinzhi/Article/program/Delphi/20 13 1 1/25 13 1