Advanced PowerBuilder

Multiple Table DataWindow Update

A limitation of DataWindow is that, you can update only one table at a time. When you paint a DataWindow based on a single table, PowerBuilder automatically makes that table updateable. You can see, if you observe the DataWindow update characteristics in the DataWindow Painter.

However, if a DataWindow has more than one table, PowerBuilder doesn't automatically set the attributes for you—you can't even programmatically set the attributes for multiple tables simultaneously.

Fortunately, it doesn't mean that you can't update a multiple table DataWindow. You have an ultimate DataWindow function, Modify()function, especially for tasks like these. For example, let's take the simple join between product_master and trans tables, as shown below:

SELECT "trans"."tran_no", "trans"."item_no", "product_master"."product_description", "trans"."tran_qty", "product_master"."product_balance" FROM "product_master", "trans" WHERE ("product_master"."product_no" = "trans"."item_no")

In the script, if you update the DataWindow by calling Update(), which table will be updated? None. As explained earlier, PowerBuilder does not set update properties when a DataWindow contains multiple tables. You need to set one of those several tables updatable in the Rows/Update properties option. Let's say, you set trans table as updatable in the DataWindow painter. Now, if you call Update(), PowerBuilder will update trans table only. So, how would we go about updating the product_master?

The Update() function takes two parameters: the first is whether or not to call the AcceptText() function internally, and the second one determines whether or not to reset the row/column statuses.

Calling AcceptText() performs the necessary validations on the last column in the DataWindow and copies the data from the DataWindow's Edit buffer to the Primary buffer. For example, if the user edit the value of a column in a row and click on the Update button without pressing tab, what happens?

The ItemChanged event is not triggered because, the changed column never lost its focus. Please note that, the column looses its focus when the user moves to another column/row within the same DataWindow by pressing TAB key or using the arrow key or clicking on another column/row with the mouse button. In this example, (s)he never did any one of those, instead clicked on a button placed outside the DataWindow. It means, the DataWindow itself lost the focus, but not that particular column. Hence, ItemChanged event is never triggered. If that button has dw_1.Update() function and the first argument is FALSE, the changed value will never go to the database. That's why we should always either call AcceptText() before calling Update()or call the Update()function with TRUE as the first argument.

When the Update() is successful, PowerBuilder resets the status of all rows and columns to NotModified!.

You can see this in action if you issue an Update() call again. Since all rows currently hold NotModified! status, PowerBuilder doesn't generate SQL statements.

If you call Update( True, False ) or Update( False, False ), the flag isn't reset, and it wouldn't be changed until another Update() function with TRUE as the second argument is called to reset the flag. We can take advantage of this behavior by updating two or more tables while the status is unset:

int lUpdateStatus // Update the DataWindow, But, Don't reset the row status. lUpdateStatus = dw_trans.Update(true,false) If lUpdateStatus = 1 Then dw_trans.Modify("trans_tran_no.Update = No") dw_trans.Modify("trans_item_no.Update = No") dw_trans.Modify("trans_qty.Update = No") dw_trans.Modify("trans_tran_no.Key = No") dw_trans.Modify("trans_item_serial_no.Key = No") dw_trans.Modify( & "DataWindow.Table.updateable = ~"product_master~"") dw_trans.Modify( & "product_master_product_description.Update = Yes") dw_trans.Modify( & "product_master_product_balance.Update = Yes") dw_trans.Modify("product_master_product_no.Key = Yes") lUpdateStatus = dw_trans.Update(FALSE, TRUE) If lUpdateStatus = 1 Then Commit; Else RollBack ; MessageBox("Update","Error: " + SQLCA.SQLErrorText +& "~r" + "No changes made to database" ) End If dw_trans.Modify("DataWindow.Table.Updateable = ~"trans~"") dw_trans.Modify("trans_tran_no.Update = Yes") dw_trans.Modify("trans_item_no.Update = Yes") dw_trans.Modify("trans_tran_qty.Update = Yes") dw_trans.Modify("trans_tran_no.Key = Yes") dw_trans.Modify("trans_tran_serial_no.Key = Yes") dw_trans.Modify( & "product_master_product_description.Update = No") dw_trans.Modify( & "product_master_product_balance.Update = No") dw_trans.Modify( & "product_master_product_no.Key = No") Else MessageBox("Update", "Error: " + SQLCA.SQLErrorText + & "~r" + "No changes made to the database" ) RollBack ; End If

First, we called Update( True,False ), which will update the table and won't reset the row and column statuses. Then, we make all the columns in the trans not updateable and let PowerBuilder know not to use the primary key of trans for future updates.

Now, we set all the columns in the product_master table as updateable and also let PowerBuilder know which key it has to use for an update. When this stage is complete, we called Update( FALSE, TRUE ), but this time reset flag is TRUE, which will reset the row status for all columns after a successful update. Notice that the accepttext flag is FALSE in the second Update(), because we do not need to accept the text again since it was already done in the first call. Once all updates are successful, we need to set the DataWindow properties back, i.e., trans table as updateable and product_master table as not updateable, because if this script executes next time, it should exactly what it did now.

If you have more than two tables you can call Update( True, False ) for all tables except the last table, which should set the flag and cause all the tables to update.

Home	Previous Lesson: DataWindow Update Properties Next Lesson: Parent-Child Tables Data-Entry