In this month's column I'll show you how to use the PFC multitable update service. This service, as you might expect, allows the DataWindow to update more than one table. Multitable update, one of the least used PFC services, has been around since version 5.0. I'll demonstrate how you can use it, how it works and pitfalls you may find along the way.

How the DataWindow Updates
Before I introduce the DataWindow multitable update service, it's important to understand the default way that DataWindows handle updates. When a DataWindow is created, it contains a list of items called Update Properties. These can be viewed via the Rows >> Update Properties menu item within the DataWindow painter (see Figure 1). Simply put, this is where you, the programmer, tell this DataWindow how the updates are going to work. A more descriptive list of the Update Properties is given below.

Allow Updates
This checkbox specifies whether or not this DataWindow will be updatable. Obviously, if you want the user to be able to update the DataWindow, the box must be checked. If the box isn't checked and an update is attempted, a PowerBuilder system message box will be displayed, informing the user that updates can't be made to this DataWindow. Also, if this button is left unchecked, the DataWindow won't maintain a delete buffer.

Where Clause for Update/Delete
You can indicate here how the DataWindow will determine if an update was successful. This groupbox is used to specify which of three techniques should be used to construct the Where clause on the Update statement to implement optimistic locking. These techniques vary in the degree of data integrity they ensure. (See Table 1 for a more detailed description.)

Where Clause Criteria - Key Columns
The Key criterion specifies that only the key column(s) must match their original value(s) for the update to be performed. The major concern with this technique is the potential for lost updates.

In Figure 2, Users 1 and 2 retrieve a row at the same time and both intend to modify the last name in the orders table. If User 1 makes the change, and User 2 is unaware that the data has already been updated, any changes from User 2 will overwrite the change made by User 1 - in other words, the database update will be successful...and never mind that the changes made by User 1 are overwritten. If it so happened that Users 1 and 2 retrieved the data at the same time and one of them updated the key column (in this case the order number), the next attempt to update would fail because the key column has changed.

This technique is commonly used in single-user applications or when dealing with many-to-many tables where all columns make up the primary key.

Where Clause Criteria - Key and Modified Columns
The Key and Modified Columns criterion specifies that the key column(s) and the columns for which the values are being modified must match their original values for the update to be performed.

Users 1 and 2 retrieve a row at the same time, both intending to modify the ship name. User 1 changes the ship name from Bates to Foy. The update is successful because both the key and the modified columns match their original values. However, when User 2 attempts to change the ship name to Hendry, the update will fail (see Figure 3). Since the ship name is now Foy, while on User 2's end it was expected to be Bates, User 2's entry won't overwrite the change by User 1. The database column ship_name no longer matches its original value.

This update technique has a distinct advantage. If a user has updated a value in the database, this value can't be written over by another user. However, the technique also has a drawback (doesn't everything?). Consider the following scenario. Users 1 and 2 retrieve the same row at the same time. User 1 changes the ship name from Bates to Foy, while User 2 changes the ship city from Boogers Holler to Wayne. Both updates would be successful, but neither user would be aware that the other had made changes. The result would be inconsistent data (see Figure 3A).

Where Clause Criteria - Key and Updatable Columns
The Key and Updatable Columns criterion specifies that the key column(s) and all updatable columns must match their original values for the update to be performed. In Figure 4, User 1 changes the last name from Bates to Foy. The change is successful. However, when User 2 attempts to change the ship city from Boogers Holler to Wayne, the update fails. Even though the particular change that User 2 made wouldn't write over the change made by User 1, the update fails because User 1 changed an updatable column (ship_name). While this technique offers greater consistency in the database, it's achieved at the cost of a greater potential for failed updates.

Optimistic Locking Errors
If any changes to the database fail because of how the programmer defined the DataWindow Update Properties, the DataWindow DBError event is fired and a PowerBuilder-generated message is displayed to the user (see Figure 5).

Table to Update
This portion of the Update Properties dialog box specifies which table is updatable. Notice that only one table can be updatable. What if you want more than one table to be updated? Well, unless you want to write some pretty complicated PowerScript to modify the DataWindow's Update Properties, you're out of luck. Nowhere within the DataWindow painter can you specify that more than one table should be affected.

Before the DataWindow multitable update service was available, programmers were forced to write many DataWindow modify functions to change the DataWindow.Table.properties of the DataWindow. For those who actually want to try this, look up the UpdateTable, UpdateWhere and UpdateKeyInPlace properties. But believe me, trying to programmatically change the Update Properties of a DataWindow will make you want to throw your PC down the stairs.

Multitable Update Service
We can now move on to the focus of this month's column: How can you use the PFC multitable service to update more than one database table? Let's take the following scenario. A DataWindow contains columns from three tables. The user is allowed to update at least one column from each of the three (see Figure 6). The updatable columns are as follows:

Adding the Code
Just like any of the DataWindow services, the multitable service must be turned on. In the DataWindow constructor event, This.of_setMultiTable(TRUE) will turn on the service for the DataWindow.

The next step is to determine what tables we need to update, what identifies a record in a table as a primary key, what columns we'll allow the user to update, and what Update Properties (as discussed above) the DataWindow will use. When we've gathered all of that information, we'll put the code behind the Update command button.

The first table we want to update is the sales order table. The primary key of the sales order table is the sales_order_id column. Believe it or not, this is all you need to start coding. Put the following code behind the command button Clicked event:

String ls_table
String ls_key_cols[]

// Update the sales_order table
ls_table = "sales_order"
ls_key_cols[1] = "sales_order_id"
If IsValid(dw_1.inv_multitable) then
dw_1.inv_multitable.of_register(ls_table,ls_key_cols[])
End If
dw_1.Event pfc_update(TRUE,TRUE)

The most important part of this block of code is the of_register function. This function informs the multitable service that any update properties specified in the DataWindow painter will be overridden with those supplied in our PowerScript. In the example above, we're saying that we want to update the sales order table and the key is sales_order_id.

The of_register function has three signatures.

Signature 1:

of_register(string as_table, string as_key_cols[]).

In this signature, the programmer supplies the table to be updated and the key column(s) for the table. By default, all columns for that table are considered updatable. Also, the service assumes that you want to use Key and Updatable for the DataWindow Update Properties when building the Where clause. Finally, the service assumes that when performing the update, the row will first be deleted with a DELETE SQL statement, then reinserted with an INSERT SQL statement. In our examples we'll use this signature.

Signature 2:

of_register(string as_table, string as_key_cols[], string as_updatable_cols[]).

This signature is similar to the first. The only difference is that specific updatable columns are being passed to the service. Use this signature when you don't want all of the columns in a table to be updatable.

Signature 3:

of_register(string as_table, string as_key_cols[], string as_updatable_cols[],boolean
ab_key_inplace, integer ai_where_option).

Use this signature when you want to supply every detail about how the table will be updated. Table 2 provides a more detailed look at the syntax.

The Rest of the Code
Now that we've specified the code to update the sales_order table, we can add the code to update product and sales_order_items. See Listing 1 for the complete code.

About the Code
There are a couple of things worth noting in Listing 1. First of all, we have two key columns from the sales_order_items table. This is because the sales_order_items_id and the sales-_order_items_line_id uniquely identify each row. You may have noticed that the ID column in the sales_order_items_id table isn't visible in the DataWindow. Even though we don't display it to the user, it's part of the DataWindow selection criteria because we need to use it as a key.

When we're updating the product table, the array that holds the key columns needs to be reset. That's because the product table has only one key while the previous table has two.

Just before the event is over, changes are saved to the database and the pfc_update event is used. When this event is fired, the update logic is redirected to the multitable update service. A regular DataWindow update function simply won't work.

While we're talking about the PFC... remember, the use of most PFC services requires the entire application to be PFC-based. Mixing PFC components with non-PFC components, with few exceptions, is a recipe for disaster. To use the DataWindow multitable service, your entire application should be built on PFC components.

Final Note
I hope you've found this month's column useful. Please remember that PowerBuilder Developer's Journal is your magazine. Readers have requested every topic that has been presented thus far. If you'd like to suggest a topic, please e-mail me. Jim Burbank, who works at Texaco in Houston, first suggested this month's topic. Hope this helped you, Jim!