How the Developer Uses the New Feature
The developer only need add a small amount of additional code is needed on the application window and datawindow control objects to make use of the new feature.
The corresponding scripts are shown below:
Event: w_multiupdate - dw_multi::constructor
// Revised Constructor Event Script dw_multi.of_SetTransObject ( SQLCA ) dw_multi.Retrieve ( ) of_SetMultiTable ( TRUE ) STRING ls_table STRING ls_keycols[] /* ------ Notes on Registering the Updates --------- For Of_SetTabEnableCodes, argument containing U, I, and/or D will enable sqlpreview update for that sqlpreviewtype (update, insert, or delete, respectively) on the pending row. For any string not containing U, I, and/or D, for a null string, or for an actual null value, update is disabled for the pending row. For any table, if Of_SetTabEnableCodes call not made or if commented out, the update is disabled for the pending row. */ // ------ Register the Updates --------- // 1. Register Update Characteristics for "sales_order_items" Table ls_table = "sales_order_items" ls_keycols[1] = "sales_order_items_id" ls_keycols[2] = "sales_order_items_line_id" this.inv_MultiTable.of_AddToUpdate (ls_table, ls_keycols ) this.inv_MultiTable.of_SetTabEnableCodes ('D') // sp 9/22/97
// 2. Register Update Characteristics for "sales_order" Table ls_table = "sales_order" ls_keycols[1] = "sales_order_id" ls_keycols[2] = '' this.inv_MultiTable.of_AddToUpdate (ls_table, ls_keycols ) this.inv_MultiTable.of_SetTabEnableCodes ('UID') // sp 9/22/97
// 3. Register Update Characteristics for "sales_order_items" Table ls_table = "sales_order_items" ls_keycols[1] = "sales_order_items_id" ls_keycols[2] = "sales_order_items_line_id" this.inv_MultiTable.of_AddToUpdate (ls_table, ls_keycols ) this.inv_MultiTable.of_SetTabEnableCodes ('UI') // sp 9/22/97
|
Notice that the update criteria is set three times and each table registration block includes the new function, of_SetTabEnableCodes.
Notice that the update criteria is set three times and each table registration block includes the new function, of_SetTabEnableCodes.
The required sqlpreview script is shown below:
Event: w_multiupdate - dw_multi::sqlpreview
//Optional custom script here // Application of New Feature (using extended n_cst_dwsrv_multitable object) IF isvalid(this.inv_multitable) THEN // Disable (or skip) update on this row if return is false IF not(this.inv_MultiTable.function & of_EnableSqlPrevOnRow(sqltype,sqlsyntax)) THEN RETURN 2 END IF |
The call to of_EnableSqlPrevOnRow as shown determines whether the pending row should be processed. The decision is made in the user object, n_cst_dwsrv_multitable, based on the sql type and the table specified in the current update criteria.
Transaction control (commit or rollback) was implemented by extending the pfc_Endtran event provided for this purpose:
Event: w_multiupdate::pfc_Endtran
INT li_rtn = 1 IF ai_update_results = -1 THEN IF sqlca.of_rollback() < 0 THEN li_rtn = -1 messagebox("Update Status", "Update Failed - Rollback")
ELSEIF ai_update_results = 1 THEN IF sqlca.of_commit() < 0 THEN li_rtn = -1 END IF RETURN li_rtn |
The optional custom script for this particular application may also be of interest. It causes sqlpreview to skip any attempt to duplicate a master record, to delete the same master record more than once, or to delete a master record when there exists one or more related child records. Without these preemptive checks, a database error would unconditionally stop the update process and rollback the transaction.
Under the Hood: How We Extended the Multi Table Update Service Object
The extended user object n_cst_dwsrv_multitable from PFEDWSRV.PBL is attached. Modifying the PFC object instead of the PFE object was not an option. Had it been, we would have avoided copying down to the PFE descendent, for the purpose of overriding, two functions, unmodified, except for one line. The two functions were one of the of_Update functions and of_RestoreUpdateSettings function.
We modified of_Update() to create an instance variable to expose the local loop index variable, li_tabidx, a reference to the current update criteria. This information was needed by the datawindow controls sqlpreview event to determine the current criteria.
The following is the line that was added to the of_Update function:
ii_tab_idx = li_tabidx |
We then added two new functions, of_EnableSqlpreviewOnRow and of_SetTabEnableCodes.
The new instance variables and functions are listed below:
Instance Variables: n_cst_dwsrv_multitable
INT ii_tab_idx STRING is_tabenablecodes[] |
Function: n_cst_dwsrv_multitable::of_enablesqlprevonrow
// Access Level public // Returns boolean // Arguments a_sqltype sqlpreviewtype // a_sqlsyntax string // Enable/disable (== skip) processing of current row in sqlpreview // event. If there are no codes, disable. BOOLEAN lb_rtn = false STRING ls_test INT li_upperbound IF a_sqltype = previewselect! THEN RETURN true li_upperbound = upperbound(is_tabenablecodes) IF li_upperbound >= ii_tab_idx THEN ls_test = upper(is_tabenablecodes[ii_tab_idx]) CHOOSE CASE a_sqltype CASE previewdelete! IF pos(ls_test,'D') > 0 THEN lb_rtn = true CASE previewinsert! IF pos(ls_test,'I') > 0 THEN lb_rtn = true CASE previewupdate! IF pos(ls_test,'U') > 0 THEN lb_rtn = true END CHOOSE //STRING ls_msg = 'DISABLE',ls_msg2 //IF lb_rtn THEN ls_msg = 'ENABLE' //ls_msg2 = ls_msg+" the following update statement: ~n"+a_sqlsyntax //messagebox("Activity of New Feature",ls_msg2)
END IF RETURN lb_rtn |
Function: n_cst_dwsrv_multitable::of_settabenablecodes
//Access Level public //Returns integer //Arguments as_sqltype string INT li_rtn = 1 LONG ll = 1 ll = upperbound(istr_newupdate) IF ll > 0 THEN li_rtn = ll is_tabenablecodes[li_rtn] = as_sqltype ELSE li_rtn = -1 END IF RETURN li_rtn |
Horwith, Michael, DATABASED ADVISOR, "PowerBuilder Pro Column: Updating Multiple Tables from a Datawindow - Here’s an alternative to the hard-coded solution", December 1994, pp 114-119
ADVANCED DATAWINDOWS (Course), "Unit 6: Modifying DataWindows Dynamically, Updating Multiple Tables From One DataWindow", Powersoft Corporation, pp 6-13
McDonald, Sara, POWERBUILDER DEVELOPER’S JOURNAL, SYBASE SQL CORNER, "Using Sybase Stored Procedures the Right Way", October 1995, pp 41 - 44. See listing 10.
POWERSOFT FAXLINE SYSTEM, "Doing Updates Against Multiple Database Tables from the Same Datawindow (dwModify)", Document 42014, 1/29/96.
Last revised: February 15, 2004 03:58 AM.