Delphi/BCB로 클라이언트 서버 개발하기

Richard F. Lussier
Apogee Information Systems, Inc.

역: 정우철

Delphi/BCB는 현존하는 RAD 개발 툴 중에 가장 혁신적이며, 뛰어난 성능을 지니고 있다. 데이터베이스 개발 부분에 있어서는 특히 최고 성능의 볼랜드 데이터베이스 엔진(BDE)을 사용하고 있고 시각적으로 데이터를 보면서 개발할 수 있는 데이터-인식 컴포넌트들을 가지고 있다.

볼랜드 데이터베이스 엔진

가장 중심 엔진은 물론, 볼랜드 데이터베이스 엔진(BDE)이다. BDE는 볼랜드의 데이터베이스 기술을 하나로 요약하여 is Delphi, C++ Builder, JBuilder 등 볼랜드의 모든 툴에서 사용할 수 있게끔 만들어 놓은 엔진이다. BDE는 유니버설로 동작되며, 기존의 파라독스나 dBASE에 대해서도 드라이버들을 제공한다. BDE는 프론트 엔드 툴과 데이터베이스 툴 사이에 잘 작동된다. 특히 원시 드라이버 Microsoft SQL Server, Informix, DB2, Paradox, dBASE, text , Oracle, InterBase, Sybase뿐만 아니라 Microsoft ODBC 드라이버들에 대해서도 전혀 문제없이 동작된다. 데이터 타입은 대부분 BDE에서 호환되며, BDE의 API를 직접 호출하여 저 수준에서 프로그래밍도 가능하다.

BDE는 또한 이기종의 데이터 소스에 대해 다중 접속을 제공한다. 이 기능은 똑같은 애플리케이션 내에서 다른 데이터베이스 시스템을 사용하는 개발자들에게 유익한 기능이다. 다양한 데이터베이스 접속 기능은 델파이/BCB와 볼랜드 데이터베이스 엔진을 더욱 밀착시키는 역활을 수행한다. 비록 이것이 강력한 기능을 제공한다손 치더라도 조그마한 테이블에서는 문제없이 동작되지만 어마어마하게 큰 데이터셋에 대해서는 다른 접근 방법(미들웨어)을 사용하는 것이 좋은 방법이다.

투명한 확장성(Scalability)

BDE에서 가장 중요한 개념은 데이터베이스 알리아스이다. 알리아스는 데이터베이스 참조의 이름이다. 애플리케이션에서 데이터베이스 참조란 알리아스 이름을 사용한다. 이렇게 이름을 정할 경우 클라이언트 애플리케이션들을 데이터베이스를 쉽게 접근할 수 있다. 알리아스는 BDE에서 쉽게 설정할 수 있다. 예를 들면, 로컬 파라독스에 기반한 애플리케이션을 만들었을 때, 알리아스를 만들었다고 하자. 후에 이 프로그램을 클라이언트 서버 프로그램으로 업그레이드하기 위해 오라클로 포팅할 경우, 여기서 해줄 일은 알리아스 변경이다. 일반적으로 애플리케이션에서는 조그마한 변경만이 필요하고 그 밖에 해 줄 일은 없다.

데이터베이스에 연결하기

클라이언트 애플리테이션을 개발하는 데 있어 첫번재로 필요한 일은 데이터베이스에 연결하는 일이다. 대부분의 서버는 성공적인 데이터베이스 연결전에 로그인을 먼저 수행한다음 인증을 거쳐야 한다. 델파이와 BCB는 TDatabase 컴포넌트를 제공해 이와같은 데이터베이스 연결을 관장한다. TDatabase는 Client/Server 애플리케이션에서 인증의 기능뿐 아니라 확장된 추가 기능들을 가지고 있다. 데이터베이스에 관련된 컴포넌트들은 모두 이 컴포넌트에 연결되며 만약 명시적으로 이 컴포넌트를 사용하지 않더라도 델파이와 BCB는 기본적으로 가상의 TDatabase 컴포넌트를 내부적으로 지니고 있다. 따라서 개발자는 이 컴포넌트를 서버에 연결하는 데 사용했다면 모든 쿼리에 대해서는 오직 하나의 리소스만을 사용하게 되므로 굉장히 효율적인 일을 처리할 수가 있게 된다.

TDatabase 컴포넌트를 사용하기 위해서는, the DatabaseName property must be set to a name other components in the application will use to reference the database. In general, I use a generic DatabaseName like "AppDB" to help distinguish it from real Aliases created in the BDE.

The AliasName property is then set to the alias that describes the connection to the database (i.e. the server to connect to, the database name, etc.). Alternatively, instead of using an alias, the DriverName property can be set. This would inform the BDE of the type of driver the database component will use. More often than not, the AliasName is set so that you can easily control the Alias settings through the BDE Administration Utility.

The Params property, which is a String List, can be used to specify options for the connection. If an Alias is being used, Param entries can be added to override the default alias options. If instead the DriverName property is specified, the Params property can be used to set the properties needed to connect to the specified driver. For example, the following code sets a Database connection to a Microsoft SQL Server without using an Alias:


Database1.DatabaseName:='InvestDB';
Database1.DriverName:='MS SQL Server';
Database1.Params.Add('DATABASE = invest');
Database1.Params.Add('SERVER NAME = buzz');
Database1.Params.Add('USERNAME = sa');
Database1.Params.Add('PASSWORD = ');
Database1.Active:=True; 

The property KeepConnection is used to hold the Database connection open even if there is no active dataset objects. Setting this property to True avoids having Delphi release the database connection when no dataset is currently open. If it is set to False and all the datasets attached to the database are closed, the database object will drop the connection to the database server. This will require re-establishing the server connection when the dataset is reopened. Setting KeepConnection to True usually improves application performance but on the other hand, when set to False, the number of concurrent user connections required on the database server is decreased. Usually the KeepConnection property should be set to True.

To open the database, the Connected property is set to True or the Open method is called. If the server requires users to login, Delphi will prompt for the User Name and Password. You can control the server login with a database component's LoginPrompt property and its OnLogin method. Controlling login is essential to server security, and may be required by your remote server.

LoginPrompt specifies whether your users are prompted to log in to a database server the first time your application attempts to connect to a database requiring a login. If True (the default), your application displays a standard Login dialog box. The Login dialog box prompts for a user name and password. A mask symbol (an asterisk by default) displays for each character entered in the Password edit box.

If you set LoginPrompt to False, the standard Login dialog box is not displayed at run time when an application attempts to connect to a database server that requires a login. Instead, your application must provide the user name and password, either through the Params property or the OnLogin event for the database component. At design time, the standard Login dialog box appears when you first connect to a remote database server if LoginPrompt is False and the Params property does not contain user name and password entries.

Note: Storing hard-coded user name and password entries in the Params property or in code for an OnLogin event can compromise server security. A savvy programmer can use tools to "look" into the compiled executable code and may be able to discern the user name and password. While unlikely, this is a potential security breach.

The Params property is a string list containing the database connection parameters for the BDE alias associated with a database component. Some typical connection parameters include path statement, server name, schema caching size, language driver, and SQL query mode.

Instead of hard coding the parameters, the onLogin event could call a custom built login form and then put the user input User Name and Password into the LoginParams. Below is a sample login form and the onLogin event code that will be executed when the database is activated:

procedure TInvDataMod.dbInvestLogin(Database: TDatabase;
  LoginParams: TStrings);
begin
        frmLogin := TfrmLogin.Create(nil);
  if frmLogin.ShowModal = mrCancel then
  begin
         frmLogin.Close;
    Halt;
  end;

        // Set the database parameters from the login screen.
  with dbInvest do
  begin
    Params.Clear;
    Params.Add('User Name=' + frmLogin.UserName);
    Params.Add('Password=' + frmLogin.Password);
  end;
end;

TTable and Tquery

The most fundamental components for database development are from the TDataSet family. Specifically, they are the TTable and TQuery objects. In addition there is a TStoredProc object which will be covered later in the Stored Procedure section. The TTable object provides live connectivity to database tables using the Borland Database Engine. It allows access to every row and column in an underlying database table, whether it is from Paradox, dBASE, an ODBC-compliant database such as Microsoft Access, or a SQL database on a remote server, such as InterBase, Sybase, or SQL Server. You can view and edit data in every column and row of a table, you can work with a range of rows in a table, and you can filter records to retrieve a subset of all records in a table based on filter criteria you specify.

The other important component is the TQuery. A query component encapsulates an SQL statement that returns a set of related rows and columns from one or more database tables. SQL (pronounced "ess-cue-ell") is an industry-standard relational database language that is used by most remote server-based databases, such as Sybase, Oracle, InterBase, and MS SQL Server. Delphi query components can be used with any database available through the BDE.

A query component differs from a table component in two significant ways. It can:

  1. Access more than one table at a time (called a "join" in SQL).
  2. Automatically access a subset of rows and columns in its underlying table(s), rather than always returning all rows and columns and requiring you to set ranges and filters to restrict row access.
  3. The data that is returned by a query component depends on the selection criteria you specify in the component's SQL property at design time or at run time. The selection criteria is in the form of an SQL statement, and can be Static, where all parameters in the statement are specified at design time, or Dynamic, where some or all of a statement's parameters are set at run time.

In Client/Server development, SQL queries are generally used to get data from the database (i.e. read only). Delphi's TQuery component is different. Given the proper SQL queries, the result set can be modified. By default, a TQuery always returns a read-only result set. Set the RequestLive property to True to request a live result set. The BDE will then return a live result set if the SELECT syntax of the query conforms to the syntax requirements for a live result set. If RequestLive is True, but the syntax does not conform to the requirements, the BDE returns a read-only result set (for local SQL) or an error return code (for passthrough SQL). If a query returns a live result set, Delphi will set the CanModify property to True.

When using a SQL database, it is generally better to use TQuery components instead of TTable components whenever possible. TTable components look at the whole table on the server. Most Client/Server procedures require a subset of the table data. Because of this, using a TQuery is much more efficient. Also, when using TTables, the developer is relying on the BDE to generate the SQL needed to retrieve the proper records. This may mean that inefficient SQL is used. By using TQuerys, the developer can control the exact SQL that will be transmitted to the server.

Using a TQuery is easy. First set the DatabaseName. Most likely this will be the DatabaseName of a Database component (i.e., "AppDB"). Then the SQL property needs to be set with the proper SQL statement. The query can then be opened. In order to use data aware controls, a TDataSource object needs to used.

The TDataSource component is the interface between a dataset component (TQuery, TTable) and data-aware controls on forms. TDataSource attaches to a dataset through the Dataset property. Data-aware controls, such as database edit boxes and data grids, attach to a TDataSource through their DataSource properties. Usually there is only one data source for each dataset component, but there can be as many data source components connected to a dataset as required.

In addition to encapsulating standard SQL features, query components also offer a powerful ability not found on most remote database servers or in most front-end development tools: the ability to create "heterogeneous joins," queries against more than one server or table type. This allows the developer to pull data from an Oracle database and join it to data residing on a MS SQL Server (or any other database that the BDE supports). This can be very useful is a company's primary data warehouse is on one platform, but a particular department's data is on another.

A major drawback to using heterogeneous joins is performance. How does the BDE provide this type of functionality? Well, if you think about it, there's really no magic here. In order to resolve the join, the data must be pulled into a common database format. Once that is done, the join operation can easily take place. That format is local Paradox tables. Thus, before the join can take place, the data from both tables must be pulled down into these temporary tables. If the source tables are large, this can take a very long time to execute. In reality, heterogeneous joins are only practical for smaller tables.

Below is an example of a query that could be used in a Tquery object:

select  t1.investor_id, t1.lname, t1.fname, 
t2.closing_dt, t2.portfolio_val
from   ":investdb:investors" t1, 
   ":investloc:porthist.db" t2
where  t1.investor_id = t2.investor_id

In this example, a local paradox table that holds portfolio performance history (porthist.db) joined to a MS SQL Server database table (investors) that holds investor information.

TQueries in Action

Now lets put all of this to work. Below is a simple form connected a MS SQL Server sample database.

The TDatabase component's AliasName is set to "investdb". The DatabaseName is set to a new name "AppDB". The TQuery's database name is set to "AppDB" as well. Next, the SQL property needs to be set. For this example we'll select all investors from the investors table who's country is "USA". The TQuery's SQL property is set to:

SELECT * FROM investors WHERE country = 'USA'

The Query can then be opened by either calling the Open method or setting Active to true. TQuery also has another method for executing the SQL statement called ExecSQL. Use this method if the SQL statement will not return any rows (i.e. UPDATE, INSERT or DELETE queries). After the query is set a TDataSource component connected to the TQuery and a TDBGrid component to the TDataSource

Parameterized Queries

The above query is static. It will return only investors in the USA. But suppose we wanted the user to select the country in which the investors are located? Fortunately the TQuery component supports parameters. The parameter is used to substitute a value in a SQL WHERE clause. Delphi is smart enough to know by the type if the value should be surrounded by quotes or not. A parameter is indicated by placing a ':' in front of it. The SQL statement for investors modified to support a parameter would look like this:

SELECT * 
FROM investors
WHERE country=:country

Now we can drop a TComboBox on the form and fill it with countries. When the user selects a new country from the ComboBox, the new parameter value can be set. To set the parameters, the TQuery has two methods Params and ParamByName. Param is used to set the parameter by index. The ParamByName is used to set the parameter using a string which is the name. The ComboBox's onChange event is shown below using the ParamByName method:


procedure TfrmParamQry.cbCountryChange(Sender: TObject);
begin
  with qryInvest do
  begin
    DisableControls;
    Close;
    ParamByName('country').AsString := cbCountry.Text;
    Open;
    EnableControls;
  end;
end;

Stored Procedures

If there are a group of SQL statements that need to be executed simultaneously or a single statement that will be run many times, they can be placed in a stored procedure. A stored procedure is a pre-compiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit. Stored procedures are housed within a database, can be executed with one call from an application, and allow user-declared variables, conditional execution and other powerful programming features.

The primary advantages of stored procedures are:

  1. Precompiled, preoptimized SQL code. This can save significant time during execution, especially if the stored procedure is executed repeatedly.
  2. The procedure runs on the Server. The server is usually a under-utilized machine that has a good deal of resources (processors, memory, disk subsystem). Running complex operations on the server can help balance the processing load between the client and server.
  3. Reduced network traffic. Only the results of the stored procedure are returned to the client. This can be as much or little as is needed. If the processing were to be done on the client, then all the data would have to be transported to the client over the net.
  4. Security. Only people with EXECUTE rights to the stored procedure can run them. In addition, any table the stored procedure updates does not have to be granted to the user. Thus, direct table updates can be revoked from the user, but execute rights to the stored procedure can be granted.
  5. Portability. By moving a portion of the application code from the client to the server, moving the application from one server to another or (forgive me!) one development tool to another can be enhanced. Most servers support stored procedures, and even though there is no official ANSI standard on stored procedure syntax, most follow a very similar syntax.

Stored procedures can be called with a TQuery component but Delphi contains a special Dataset component called TStoredProc. The TStoredProc component enables applications to execute server stored procedures. Set the DatabaseName property to specify the database in which the stored procedure is defined. Set the StoredProcName to the name of the stored procedure on the server. A stored procedure has a Params array for its input and output parameters, similar to a TQuery component. The order of the parameters in the Params array is determined by the stored procedure definition. An application can set the values of input parameters and get the values of output parameters in the array similar to TQuery parameters. You can also use ParamByName to access the parameters by name. If you are not sure of the order of the input and output parameters for a stored procedure, use the Parameters Editor by clicking on the Params property in the inspector. A picture of the Parameters Editor is shown below:

Before an application can execute a stored procedure, you must prepare the stored procedure, which can be done:

  • At design time with the Parameters Editor.
  • At run time with the Prepare method.

A stored procedure can return either a singleton result or a result set with a cursor, if the server supports it. Execute a stored procedure with either the ExecProc method if the stored procedure returns a singleton result (one row), or the Open method if the stored procedure returns a result set (multiple rows).

At the end of this paper we will build an investment application to demonstrate the Client/Server capabilities of Delphi. The application is a stock brokerage program that allows the user to configure investor accounts and buy or sell stocks for the selected investor. MS SQL Server 6.5 will be used as the database server. The database (using the alias InvestDB) has three main tables. The investors table contains the list of clients each of which has a unique InvestorId. The holdings contains an entry for each stock the investor own (stored as Ticker) and the number of Shares the investor owns of that stock. The security_info table contains detailed information for each stock stored by Ticker. The Invest application will use a data module called InvestDataModule to hold the query, stored procedure and database objects in a common place so all forms can access them.

Since each client needs a unique InvestorID it is an ideal routine to make into a stored procedure. The stored procedure called up_get_next_investor_id increments the investor id counter and returns the new value. The investor application will use a TStoredProc component to get the new InvestorID. The code to get the new id is shown below:

InvestDataModule.InvestIDStoredProc.Close();
InvestDataModule.InvestIDStoredProc.Open();
invId=InvestDataModule.InvestIDStoredProc.FieldByName("NextInvestorID").AsInteger; 

In this case, we are retrieving a result set (only one record). Sometimes, the stored procedure can be used to run a process on the server with no result set returning. In this case, like a Tquery, you can supply parameters to the stored procedure and use the ExecSQL command instead of Opening the stored procedure.

Linked DataSets

One of the features that makes Delphi great for Client/Server development is the ability to link datasets together. For example, if a TTable was set listing all investors and a TQuery hooked up to it, one would be able to query the holdings for that account. The great thing about linked datasets is that Delphi will automatically re-execute the SQL query for each account, filling in the query's parameters with values from the TTable. Delphi knows what values to place in the parameters by looking for the parameters name in the list of fields in the TTable. Linked queries work with any combination of datasets (Tables, Queries and Stored Proc).

To make the above example work, we place a TTable connected to the investor table and attach a TDataSource. We can also add a few edit boxes to display some of the fields data and a TDBNavigator to scroll through the investor table. Next a TQuery is added. The SQL looks like this:

SELECT * FROM holdings
WHERE investor_id=:investor_id 

When the TQuery's datasource property is set to the TTables datasource, Delphi will examine the SQL and replace any parameters with fields from the TTable. The naming of the parameters is important. In the above example, ':investor_id' was used as the parameter because the investor table has a field with that name. Now a TDataSource and TDBGrid can be attached to the query to display the holdings. The main investment sample application form (described below) is an example of such a form. Here, the investor query is linked to the holdings query via the investor_id field.

Triggers

Most Client/Server databases support triggers. A trigger is a stored procedure that is automatically invoked whenever a specified event (INSERT, UPDATE, or DELETE) modifies a table. A trigger can query other tables and it can include complex Transact-SQL statements. Triggers are primarily useful for enforcing complex business rules or requirements, such as whether to insert an order based on a customer's current account status. Triggers are also useful for enforcing referential integrity, preserving the defined relationships between tables when you enter or delete records in those tables.

In the investment application, a trigger can be used to make sure that all the holding records for an investor are deleted when their investor record is removed. This will make sure there are no orphaned holding records. The trigger SQL looks like this:

CREATE TRIGGER investor_delete_trigger ON investors 
FOR DELETE 
AS 
DELETE FROM holdings
WHERE investor_id=(SELECT investor_id FROM deleted)

The trigger will occur on the DELETE event. MS SQL Server has a special table for use by triggers called the deleted table. This can be referenced in the trigger's SQL. In this way we can get the deleted records investor_id and delete records in the holdings table with that id. All the Delphi application needs to do is delete the investor record. The SQL Server does the rest.

Triggers have several implications for Delphi developers. By using triggers, less code is needed on the client-side. Also, I/O traffic between the server and the client is reduced because the server can handle actions without having interaction with the client. It is very important that the Client/Server developer understands the capabilities of the back-end server and utilizes both the front-end application and back-end server for their respective strong points.

Locking and Databases

An area vital to proper database application development is the issue of multi-user concurrency. Changes made to the data by multiple users must be managed correctly. Two different approaches have been developed to handle this issue. Delphi natively supports whichever locking scheme applies to the back-end data source being used by your application.

Pessimistic Locking

One of the areas in multi-user database application development that is very familiar to developers is record-locking. By locking out others, the front end application can ensure that only one user is updating a particular record at a time. These locks are set as a flag on the record (in the internal structure), or, as is the case in Paradox databases, a separate file is kept that tracks the locks in the various tables. This form of locking is called "pessimistic" record locking. The name implies that the database assumes that more than one user will try to write to a record at the same time and puts up the blockade to prevent that event from happening. This type of locking is usually used with file based database (i.e. Paradox, dBase).

Optimistic Locking

SQL database servers automatically and transparently lock data as required, although database server products vary in the type of locks used and how granular the lock is. For example, some servers provide individual record locks while others can only lock a group, or page, of records. Also, some servers provide automatic record versioning or database snapshots so that other copies of data being modified can be read by clients instead of waiting for a modification to finish.

This type of locking for Client/Server databases is known as "optimistic" record locking. In this scheme, a lock is not placed on a record when the client requests to see that record. Instead, optimistic locking allows a client to make changes to a local copy of the record without the performance and concurrency penalty incurred by asking the server for a lock over the modification duration. When the client modifications are finished, the current SQL server record is first checked to make sure no changes have occurred to the record, then the modifications are completed. If the record was changed, an optimistic lock failure occurs. The client is notified that the requested operation cannot be performed because someone else has changed the data. The client can then inspect the new data and decide whether or not to make changes at that time. Because server data cached on the client can immediately become out of date at the server, Delphi always performs optimistic locking when using SQL drivers. This protects the client against inadvertently changing data that has never been inspected. The operation is said to be "optimistic" because it assumes that no other client will change the record, but then makes sure of that as the final change is sent to the SQL server.

Client/Server systems use optimistic locking because the number of records in these databases tend to be very large (many millions in some cases). The likelihood of two people updating the same record at the same time is usually slim. In LAN based environments, the number of records is usually much smaller. In this case, it is far more likely that two users will update the same record at the same time and pessimistic locking will prevent that.

Transaction Management

An important concept in Client/Server databases is the notion of "transactions". A transaction is a logical unit of work. The unit may be composed of several different steps, but those steps taken together make up one process. The ability to stipulate what steps make up a transaction and when a transaction is completed is critical. If a multi-step process is started, but for some reason cannot be completed (i.e., the power goes off), then the entire process will be considered incomplete and once the server is back online, the process can be undone.

The classic example of a transaction is the transfer of funds from one account to another in a bank. A customer goes to an ATM machine and transfers $500 from their Checking account to their Savings account. As described above, this is a transaction, or one logical unit of work (the transfer), that is made up of two distinct steps and operations. If a customer is in the middle of such a transfer (withdrew the $500 from checking) and the computer goes down, then the customer will be short $500 when the system comes back up because the money never made it to the savings account. This can lead to some seriously unhappy customers! Of course, the problem is solved through the use of transaction processing. With the proper commands, this situation would never have occurred. When the system came back online, it would recognize the incomplete transaction and "roll back" the checking withdrawal, thus returning the system to its prior and correct state.

Server-based Transactions

Delphi supports transaction management in two ways. The Delphi TDatabase component includes the StartTransaction method to start a transaction in the current database. All modifications and enhancements are then logged. If errors are encountered while manipulating the data, the changes can be canceled using the Rollback method. Otherwise, the data is posted to the database using the Commit method. The TransIsolation property allows the programmer to specify a Transaction Isolation Level when using SQL servers. There are three possible levels:

  • Repeatable Read - Other transaction changes to previously read data are not seen.
  • Read Committed - Other transaction committed changes can be read.
  • Dirty Read - Uncommitted changes can be read.

Support for these depends on the server. The default is Read Committed with most servers. Below is an example of how transactions are used. A transaction is started on the database, an SQL statement is executed and then the transaction is committed. If an error occurs, the transaction is rolled back and the database returns to the state it was in before the transaction. Exception handling is used to trap any errors.

Cached Updates and UpdateSQL

Another way to define transactions under Delphi is by using a feature called cached updates. By setting a TTable or TQuery component's CacheUpdates property to true, Delphi will store any changes to the database's data locally instead of writing them out immediately when a row is saved. Changes are accumulated and then posted using the ApplyUpdates method. The developer controls when the updates are actually applied to the server's database, usually after a certain amount of time or a certain number of changes have been made.

Cached updates are useful for many reasons. Because the data is saved and then posted all at once, this can reduce network traffic. Cached updates can also be used as transactions.. When using the ApplyUpdates method to post the changes to the database, an implicit transaction is used. If there is a problem posting the changes, for example if one of the modified records is locked by another user, the ApplyUpdate method will return false. In this case, the changes are still cached even if the ApplyUpdate fails so the updates can be re-tried. The changes can also be discarded using the CancelUpdates method.

A good use of cached updates is to allow users to tentatively modify the data which can be discarded as a group. For instance, in a budgeting application, a user could modify various numbers to see how they would affect the budget. The numbers would not be posted to the database until the user specifies that the data is correct. Otherwise, the user could revert back to the old numbers by abandoning the changes.

The key difference between these two methods is that transaction logging actually makes the changes as they happen, but allows you to undo them if desired. With cached updates, the changes are not necessarily made until they are posted.

The TUpadateSQL Component

TUpdateSQL is an update component that uses prepared SQL statements to update a dataset. An update component actually encapsulates three TQuery components. Each query component performs a single update task. One query component provides an SQL UPDATE statement for modifying records; a second query component provides an INSERT statement to add new records to a table; and a third component provides a DELETE statement to remove records from a table. You associate a TUpdateSQL component with a dataset by setting the dataset's UpdateObject property. The dataset automatically uses the TUpdateSQL component when cached updates are applied.

When you place an update component in a data module or on a form, you do not see the query components it encapsulates. They are created by the update component at runtime based on three update properties you supply at design time:

  • ModifySQL specifies the UPDATE statement.
  • InsertSQL specifies the INSERT statement.
  • DeleteSQL specifies the DELETE statement.

The ModifySQL property is an SQL statement that is executed when the cached update is a modification of an existing record. The InsertSQL property is an SQL statement that is executed when the cached update is the insertion of a new record. DeleteSQL is an SQL statement that is executed when the cached update is the deletion of a record. All three properties support an extension to normal parameter binding for cached updates: You can prefix any field name with 'OLD_' to retrieve the value of the field as it was before cached updates were enabled. This extension lets you access the old field values, generally for use in creating the WHERE clause of the SQL statement.

The ModifySQL property returns the string list containing the SQL statement to be used when applying the cached modification of a record. Generally, such an SQL statement would be an UPDATE statement. You can use the UpdateSQL editor (by double-clicking the TUpdateSQL component) to generate an appropriate SQL statement. UpdateMode specifies which columns Delphi uses to find the record. In SQL terms, UpdateMode specifies which columns are included in the WHERE clause of an UPDATE statement.

The InsertSQL property returns the string list containing the SQL statement to be used when applying the cached insertion of a record. Generally, such an SQL statement would be an INSERT INTO statement. You can use the UpdateSQL editor (by double-clicking the TUpdateSQL component) to generate an appropriate SQL statement.

The DeleteSQL property returns the string list containing the SQL statement to be used when applying the cached deletion of a record. Generally, such an SQL statement would be a DELETE FROM statement. You can use the UpdateSQL editor (by double-clicking the TUpdateSQL component) to generate an appropriate SQL statement.

At runtime, when the update component is invoked by a dataset, the update component

  1. Selects an SQL statement to execute based on the UpdateKind property for the dataset to which the update component belongs. UpdateKind specifies whether the current record is modified, inserted, or deleted.
  2. Provides parameter values to the SQL statement.
  3. Executes the SQL statement to perform the specified update.

What is most important to Client/Server applications is that the TUpdateSQL component also provides a way to use Delphi's cached updates support with read-only datasets. For example, you could use a TUpdateSQL component with a "canned" query to provide a way of updating the underlying datasets, essentially giving you the ability to post updates to a read-only dataset.

The TDataSet objects have events that can be used to make using the TUpdateSQL object more robust. The OnUpdateRecord event is called for each record that needs to be updated. Using this event, custom update actions can be programmed.

In the OnUpdateRecord event the UpdateKind parameter indicates the type of update to perform. When using an update component, you need to pass this parameter to its methods and properties that you access. You may also need to inspect this parameter if your handler performs any special processing based on the kind of update to perform. The UpdateAction parameter specifies whether the update was successfully applied or not. The default value is uaFail. Unless you encounter a problem during updating, your event handler should set this value to uaApplied before exiting. If you decide not to update a particular record, set the value to uaSkip to preserve unapplied changes in the cache. If you do not change the value for UpdateAction, the entire update operation for the dataset is aborted.

The cached updates can be applied using two different methods. Each dataset has an ApplyUpdates and CommitUpdates method. The ApplyUpdates will execute all of the updates for each of the records. The CommitUpdates method is used to notify the dataset that all updates have been successfully applied and that they can be removed from the cache.

Alternatively, the Database objects ApplyUpdate method can be used to apply more than one TDatasets cached updates at once. The ApplyUpdate method accepts a list of TDatasets to be updates. It explicitly starts a transaction for the database, applies the updates, commits the transaction and then commits the cached updates.

An example of ApplyUpdates sequence for a TQuery and a similar one for TDatabase is shown below. This example shows how two TQuery objects can be updated within a transaction. Notice the TDatabase version only requires one line of code:

TQuery

Database1.StartTransaction();
Query1.ApplyUpdates();
Query2.ApplyUpdates();
Query1.CommitUpdates();
Query2.CommitUpdates();
Database1.Commit();

TDatabase

Database1.ApplyUpdates([Query1,Query2]);

Sample Application

The investment application was built using most of the concepts presented above. The main form allows users to scroll through the list of investors and view their holdings. A TQuery and a couple TDBEdit controls are used to display the investors. A second query uses a linked dataset to the main query to display the current investor's holdings. Another TQuery is used to display the list of all securities available for purchase. The form is shown below:

A good deal of work occurs in the buy and sell buttons. The program must first check to insure that an integer has been entered in the shares edit control. Once this is done, a call to the stored procedure up_updshares is executed with all pertinent information. Since this stored procedure does all the updating and checking, no other action is needed on the client side. When the stored procedure is completed, the holdings query is refreshed.

Delphi Code:

procedure TfrmMain.btnBuyClick(Sender: TObject);
begin
PostTransaction(InvDataMod.qryTickers.FieldByName('ticker').AsString, 'B');
end;


procedure TfrmMain.btnSellClick(Sender: TObject);
begin
PostTransaction(InvDataMod.qryHoldings.FieldByName('ticker').AsString, 'S');
end;


procedure TfrmMain.PostTransaction(ticker: String; TransType: String);
begin
  with InvDataMod.spUpdShares do
  begin
    Close;
    ParamByName('@investor_id').AsInteger := 
 InvDataMod.qryInvest.FieldByName('invest_id').AsInteger;
    ParamByName('@newshares').AsInteger := IntToStr(edtShares.Text);
    ParamByName('@price').AsFloat := 
         InvDataMod.qryTickers.FieldByName('price').AsFloat;
    ParamByName('@ticker').AsString := ticker;
    ParamByName('@trancost').AsString := 15.00;
    ParamByName('@trantype').AsString := TransType;
  try
 ExecProc;
  except
     ShowMessage('Error: Unable to process transaction request.');
  end;  end;
end;

up_updshares Stored Procedure Code:

create procedure up_updshares
 @investor_id int,
 @ticker  char(8),
 @newshares int,
 @price  float,
 @trantype char(1),
 @trancost float
as
begin


 begin transaction


 if @trantype = 'B' 
 begin
  /* Adjust the investor table */
  update  investors
  set cash_amt = cash_amt-(@price * @newshares)- @trancost,
   portfolio_val = portfolio_val - @trancost
  where investor_id = @investor_id



  /* Update/Insert into the holdings table */
  if exists (select * from holdings 
    where investor_id = @investor_id and ticker = @ticker)
  begin
   update holdings
   set avg_price = ((shares * avg_price) + 
(@newshares * @price)) / (shares + @newshares),
    shares = shares + @newshares
   where investor_id = @investor_id and
    ticker = @ticker
  end
  else
  begin
   insert into holdings
   values (@investor_id, @ticker, @newshares, @price)
  end
 end
 else
 begin
  /* Adjust the investor table */
  update  investors
  set cash_amt = cash_amt + (@price * @newshares) - @trancost 
   portfolio_val = portfolio_val - @trancost
  where investor_id = @investor_id



  /* Update/Insert into the holdings table */
  if exists (select * from holdings 
    where investor_id = @investor_id and ticker = @ticker)
  begin
   update holdings
    set shares = shares - @newshares
   where investor_id = @investor_id and
    ticker = @ticker


   delete
   from holdings
   where investor_id = @investor_id and
    shares <= 0
  end
  else
  begin
   rollback transaction
   return
  end end


 /* Write out the transaction history record */
 insert into invest_transact
values (@investor_id, @ticker, getDate(), @trantype, @newshares, @price)



 commit transaction   
end

The Investor Maintenance form also uses a TQuery to display the Investor Accounts. A TUpdateSQL component is used to make modifications to the investor table. A TStoredProc component is used to retrieve an investorID for new investors when they are added to the database. When an investor is deleted, a trigger is used to delete all of the holding records for the deleted investor, along with other investor information. The Investor Maintenance form is shown below:

여기에 있는 모든 예제는 www.apogeeis.com.로부터 다운로드 받을 수 있다.

빠르게 변화하는 개발 툴 시장에서 델파이와 BCB는 전통적인 툴과 새로운 RAD 툴과의 간격을 좁힐 수 있게 도화준다. 개발자들은 강력한 클라이언트/서버 애플리케이션을 만들 수 있고 이러한 기본 개념과 기술을 가진다면 얼마든지 필요한 개발이 가능하리라 본다.