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, 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(), it will update trans 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 determines whether or not to set the update flag. Calling AcceptText() performs the necessary validations on the last column in the DataWindow and copies the data from the DataWindow Edit buffer to the Primary buffer. When the Update() is successful, PowerBuilder resets all the row status 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 is called to reset the flag. We can take advantage of this behavior by updating two or more tables while the flag 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()
   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 call Update( True,False ), which will update the table and won't reset the flag. 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 call Update(), still without a flag, which will reset the row status for all columns after a successful update. 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.

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