When referential integrity constraints have been declared for database tables related by foreign keys, your application may have to apply SQL insert and delete update statements in a specific table order, depending on whether the sql type is insert or delete. When there is a master detail relationship, for insert, a master record must be inserted (if it does not exist) before the first detail record can be inserted; for delete, the last detail record must be deleted before the master record can be deleted.
Solutions Based on Hardcoded SQL
The developer can implement SQL (embedded in script, executed, or called as database stored procedure(s)) conditioned first, upon sql type required (update, insert, or delete) and second, the status of rows pending the update process. This can be implemented using a user-written update function, typically called by a Save or OK button .
Strategies for launching stored procedures from the sqlpreview event have appeared in numerous places. Reference 3 lists a good one. Powerbuilder provides The Advanced PowerBuilder Utilities: Stored Procedure Update. It automatically creates PowerScript statements that you can use in a DataWindow's SQLPreview event to override default DataWindow behavior and update the database through stored procedures.
A flexible combination strategy involves the use of the datawindow control’s sqlpreview event using a script like the following. This event executes after the update() call, once for each row pending update (and only for rows pending update). The update criteria for the datawindow is predefined, typically in the datawindow painter, or in script.
IF sqltype <> previewselect! THEN // Sqltype is previewupdate!, previewinsert!, or previewdelete! custom script 1 EXECUTE IMMEDIATE sqlsyntax; custom script 2 RETURN 2 // skip update for this row and continue END IF |
This accomplishes the same thing as an uncoded event but provides the developer the means to execute SQL script before and/or after the default update statement built by the datawindow control. Based on sql type or any other basis, the developer can use the datawindow update syntax for one table, such as the master table, and custom script for one or more other tables whether or not they are joined in the datasource. Furthermore, the developer can intercept any database error from the transaction object (sqlca) without affecting the return code for the update function, thus allowing some harmless errors to be ignored.
As we all probably know, a datawindow whose data source joins two or more tables can be expected, after an update function call, to update multiple rows with mixed sql types for one of the tables using predefined update criteria, without scripting the update criteria. With scripting, one can change (using modify or object syntax) the update criteria followed by a call to the update function, one table at a time. One of the first descriptions of this technique is found in Reference 1. A recent description is found in Reference 2.
The PFC Multi Table Update service provides the same capability in a more elegant service based form, integrated with other PFC datawindow services. It is convenient to use. But first study the source code in PFC example application objects, w_multitable, found in EXAMPFC.PBL, and n_pfc_cst_dwsrv_multitable, found in PFCDWSRV.PBL. Unfortunately the example as shipped, demonstrates only update, not insert or delete, but we make up for that.
Neither of these non SQL script approaches, with or without benefit of PFC services, describe any way to condition the sequence of tables updated upon the sql type (update, insert, or delete), as the referential integrity constraints might require.
In this article, I describe extentions to the PFC Multi Table Update Service that make it behave in a flexible manner for sql types update, insert, or delete. It shall then be clear how to apply the strategy in a non PFC, non hardcoded SQL environment (or how to modify the code in Reference 4) as well.
Last revised: February 15, 2004 03:58 AM.