1072-1074

Previous Table of Contents Next

Page 1072

Figure 46.3.
This dialog box is used
to define update and
delete behavior for a
DataWindow.

Most of the options should be self-explanatory. Of the available options, the Where Clause for Update/Delete and Key Modification radio button groups are particularly significant. In essence, the DataWindow uses an optimistic locking scheme, allowing other users to update records that are displayed in the DataWindow. The Where Clause is very important in determining how conflicting updates are handled. In most cases, Key and Updateable Columns should be selected in this group . When this option is selected, if the key and updateable columns in the table do not match the original values selected, the update will fail. When this occurs, the DataWindow should be refreshed, or some other mechanism should be designed to allow the user to view the modified record. The user can then make an informed decision before blindly overwriting changes made by another user . This prevents a "last change wins" scenario from occurring. In nearly all situations, the selection for Key Modification should be moot, because the primary key will not be in the list of updateable columns.

CAUTION

Modifying key values is very dangerous, because it can result in serious referential integrity problems. If a key value must be modified, the proper integrity constraints should be enforced by the database. In many environments, foreign key constraints are not enforced by the database for performance reasons. In these situations, the database relies on the application to enforce the primary key constraint, and it would be particularly dangerous to allow a key to be modified if it is referenced by another table.

The DataWindow provides InsertRow(), Update(), and DeleteRow() functions. It is important to note that if a DataWindow will be used for these operations, the SetTransObject() function should be used in order to maintain control over transaction processing.

In most cases, cursors are preferable for performing these operations on open result sets. Although the DataWindow is essentially an abstraction of a cursor, declaring a cursor will simplify the code for these operations and make it more readable and thus easier to maintain. For

Page 1073

example, the cursor declared in Listing 46.3 could be redeclared to perform updates based on user input, as in Listing 46.4.

Listing 46.4. Declaring a cursor, retrieving results, and updating rows in place.

 DECLARE order_det CURSOR FOR     SELECT order_no, item, price     FROM order_details     ORDER BY order_no, item_no;     Long    iOrder;     String  szItem;     Dec{2}  dPrice;     OPEN order_det;     Do While (SQLCA.sqlcode = 0)         FETCH order_det INTO :iOrder, :szItem, :dPrice;         IF (szItem = lb_Item.Text) THEN             UPDATE order_details SET price = DEC(:sle_new_price.Text)                 WHERE CURRENT OF order_det;         END IF     Loop     CLOSE order_det;     IF (SQLCA.sqlcode = 100) THEN         SQLCA.Commit;     ELSE         SQLCA.Rollback;     END IF 

A much better approach than either DataWindows or cursors would be to use stored procedures to process the transactions on the server side. This would relieve the application of the burden of transaction control, and it would greatly reduce the network traffic produced by in-place cursor operations executed from the client application. Before an Oracle-stored procedure can be called from Oracle, it must be declared. The following code fragment declares and calls a stored procedure that performs the same operation as Listing 46.4:

 DECLARE PROCEDURE update_price FOR updt_price(:iItem, :dPrice); EXECUTE update_price; 

Note that in the previous example, update_price is a PowerBuilder alias for the Oracle procedure updt_price, and it is assumed that iItem and dPrice are PowerBuilder variables that are visible within this scope. In this case, no arguments need to be provided when EXECUTE update_price is called. These two lines of code replace about a dozen lines in Listing 46.4 and enable the database to perform the entire operation. This could eliminate a considerable amount of network traffic, and it allows transaction control to be handled by the procedure, instead of the client application. The many advantages of using stored procedures make it difficult to justify any other means of applying transactions in Oracle.

Page 1074

However, an embedded SQL statement could be used to perform the update, as illustrated in the following:

 UPDATE order_details SET price = :dPrice WHERE item_no = :iItem USING sqlca_sps; IF (sqlca_sps.sqlcode = 0) THEN     sqlca_sps.Commit; ELSE     sqlca_sps.Rollback; END IF 

The preceding example illustrates two possible arguments for using embedded SQL in preference to stored procedures: It is easier to read and understand, and it will work for other RDBMSs. The preceding fragment also demonstrates the USING clause, which identifies a transaction object other than the default SQLCA.

One of PowerBuilder's primary strengths is its variety of methods for communicating with the database. In this section, a select few of the basic concepts have been presented to illustrate the alternatives. As a result, some of the more advanced topics have been omitted, including discussions of the DynamicStagingArea and the DynamicDescriptionArea system variables, which are used to store additional database information for prepared SQL, as well as dynamic SQL parameter information. They are rarely referenced in code by most programmers, but it is important to know that they exist.

Summary

In addition to numerous methods for communicating with a database, PowerBuilder's object-oriented features are its primary strength. It supports inheritance, multiple inheritance, and polymorphism, and has a number of extremely useful predefined system objects in addition to the SQLCA. The message object is arguably the most significant of these system objects. In conjunction with the capability to create user-defined events, the message object allows for a very clean and efficient means of communicating data between objects that maps very well into the underlying Windows API. In addition to providing attributes to store variables with standard data types, user-defined objects and structures can be placed in a message object, which can then be sent to another form or control.

The capability to pass data from instance to instance is particularly important in MDI applications, and sending messages is the best way to accomplish this task.

As mentioned in the introduction, the library painter is a unique and useful feature, as a reference if nothing else. It allows developers to browse the PowerBuilder class hierarchy, including all object attributes and functions. For sites that use PVCS, programmers can check source code in and out of revision control. With the Enterprise edition, PowerBuilder can also be used to generate C++ classes compatible with Watcom's C++ compiler.

Despite its many strengths, the PowerBuilder development environment can be somewhat frustrating. It is very modal, and every time a script is edited, it is recompiled, so all external references must be resolved. This adds additional complexity to team development. If one

Previous Table of Contents Next