1102-1104

Previous Table of Contents Next

Page 1102

When SQL is parameterized, it should be prepared only once. It can then be executed repeatedly with different parameters. The code in Listing 49.1 illustrates the use of the Prepare and ExecSQL methods , as well as transaction control provided by the TDatabase object.

Listing 49.1. Dynamic SQL and transaction processing in Delphi.

 {this code is executed once} qryAddCust.SQL.Add(`INSERT INTO CUSTOMERS (LAST_NAME, FIRST_NAME) `); qryAddCust.SQL.Add(`VALUES (:Last, :First)'); qryAddCust.Prepare; {this code can be executed many times} dbOracle.StartTransaction; qryAddCust.Params[0].AsString(txtLast.Text); qryAddCust.Params[1].AsString(txtFirst.Text); try     qryAddCust.ExecSQL;     dbOracle.Commit; except     dbOracle.Rollback; end; 

The ExecSQL method should be used only for statements that do not return a result set. For SELECT statements, you should use the Open method. After a result set is returned from the database, the TQuery object has an array of TFields objects, which can be accessed in much the same way as TParam objects. Listing 49.2 demonstrates the methods used to retrieve result sets from a TQuery object.

TIP

The right mouse button has special uses for many objects. Right-clicking a TQuery object displays a pop-up menu that you can use to access two dialog boxes that are specific to designing a TQuery. You can use the Fields Editor and Define Parameters dialog boxes to build queries visually.

Listing 49.2. Using the query object to retrieve results without bound controls.

 qryGetCusts.Open; while (qryGetCusts.EOF = False) do begin     lstFullName.Add(Concat(qryGetCusts.Fields[0].AsString, + `,'                            + qryGetCusts.Fields[0].AsString));     qryGetCusts.MoveBy(1); end; qryGetCusts.Close; qryGetCusts.SQL.Clear; 

Page 1103

Several important properties and methods that apply to the TQuery are illustrated by Listing 49.2. The EOF and BOF properties are Boolean values, set to True when the record pointer is positioned at end-of-file or at beginning-of-file. The properties AsString, AsInteger, and so on apply to the TFields objects in much the same way as they are applied to TParam objects, only they are typically used to read rather than set values. You can use MoveBy to move the record pointer any number of records in the current direction, (which is forward by default). You can also use First, Last, Prior, and Next to position the record pointer. The Close method should always be called when the result set is no longer needed, and the Clear method of the SQL property should be used to clean up when an application sets this property dynamically at runtime.

Several other important TQuery properties are not demonstrated by the code example in Listing 49.2. You can use the RequestLive property to create a cursor that is updatable and refreshes automatically. However, numerous restrictions can prevent this property from being used. In most cases, this should be set to False even when it is supported because it is likely to increase network traffic and database contention . The UpdateMode property is applicable only to live result sets and is used to set the requirements for matching records on updates. When an application does not need scrollable cursors, you should set the Unidirectional property to True. This improves performance, particularly when scrollable cursors are not supported by the driver. In these cases, Delphi attempts to emulate a scrollable cursor, potentially causing it to read the entire result set before returning from the Open method.

One property that should not be overlooked is the Database property. This value must correspond to the Database property of a connected TDatabase object. The Database property should not be confused with the DataSource property, which is used to locate unbound parameters at runtime through a TDataSource object ”which can be very useful in creating relationships between queries.

You use the TDataSource component to bind data-aware controls to result sets. Delphi includes a full complement of data-aware controls, including a grid, radio buttons , checkboxes, lists, and drop-down combos . The DataSet property of the TDataSource object is used to specify the query or table that it uses to bind results to controls. The AutoEdit property is applicable only to live results sets. When set to True, this property forces the underlying result set into Edit mode when a bound control is modified. When communicating with remote database servers, using the AutoEdit property for updating records is generally unacceptable because transaction control is lost.

Bound controls are most useful in displaying read-only result sets or when used in conjunction with a separate query to apply an update to an existing record. A record can be retrieved and displayed with minimal code using bound controls, but to use the transaction methods of the TDatabase component, you must write code to apply any updates using a separate query.

A TDataSource component does not have its own set of TField objects; it simply accesses an existing result set to supply information to bound controls. Controls are bound to a TDataSource

Page 1104

through the DataSource and DataField properties. The DataSource must correspond to an existing TDataSource object's name , and the DataField property should correspond to a column in the underlying query or table. If these properties are set correctly, bound controls are populated automatically when the TQuery or TTable is opened and the TDataSource is enabled.

The simplest way to implement this technique requires no code at all. You can place a TTable object on the form with its Database and TableName properties set and its Active property set to True. Next, you place a TDataSet object on the form and set its DataSet property to the name of the TTable object. Finally, you place a data-aware TDBGrid on the form and set its DataSource property to the name of the TDataSource object. At runtime, as soon as the application connects to the database, the table is read and the grid is populated. This simplicity is one of the great attractions to using bound controls.

However, the use of bound controls is a questionable technique, particularly in MDI applications. For bound controls to display data, the query or table object must remain open; this prevents the object from being used to apply other transactions or queries. If you have several MDI windows concurrently open with bound controls, using multiple TQuery or TTable objects can be expensive in terms of workstation, network, and server resources. Although it requires more handwritten code, using a query to populate controls and closing the query immediately is a safer and more efficient approach.

A better use of the TDataSource object involves the creation of dynamic SQL. In addition to accessing data from TQuery and TTable objects, you can use the TDataSource object to provide parameters to TQueries at runtime. You use the DataSource property of the TQuery object to specify the name of a TDataSource to check when attempting to resolve unbound parameters. If the name of an unbound TQuery parameter matches a column name in a TDataSource data set, the value of the matching column at the current record position is used as the parameter. For example, an accounts receivable system might need to display summary records of outstanding accounts and also provide transaction details as requested . This can be accomplished using two TQueries and a TDataSource that use information supplied at runtime. One TData query might provide the summary level information in a read-only data-aware grid, including the account number. Right-clicking a particular account might be used as the mechanism to display a pop-up menu with the account details for the selected account. The SQL for the TQuery object used to retrieve the detail information might be defined at design time as

 SELECT DATE, DEBIT_AMT, CREDIT_AMT FROM TRANSACTIONS WHERE ACCT_NO = :acct_no ORDER BY DATE 

You can use a TDataSource object to supply the parameter value to this query. The DataSet property of the TDataSource object should be set to the summary-level TQuery, which uses the following SQL:

 SELECT ACCT_NO, ACCT_NAME, BALANCE FROM ACCTS_REC ORDER BY ACCTS_REC 

Previous Table of Contents Next