Extending the samples to GET, SAVE, MERGE, and ACCEPT changes
Now, you will extend dsOrderWinUpd.w to use these methods to return changes to the database. Note that you will make a whole series of changes to the procedures. In some cases, one change replaces a change made earlier. We do this to show how you can use the low-level methods and attributes, and then how the higher-level methods can do a lot of the work for you, replacing the code you wrote the first time through. The versions of these procedures that are saved with the other examples represent the final state of the procedures. ABL statements used in earlier stages of a procedure's development are commented out so that you can examine them if you need to. However, you should build your own versions of the procedures step by step in order to learn to use all the different levels of statements and attributes available to you.
To extend the dsOrderWinUpd.w procedure:
1. In dsOrderWinUpd.w, Remove or comment out the entire FOR EACH block with the MESSAGE statement in the CHOOSE trigger for BtnSave, and add these variable definitions to the top of the block:
DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE hDSChanges AS HANDLE NO-UNDO.
DEFINE VARIABLE hDSOrder AS HANDLE NO-UNDO.
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
2. This series of statements in the CHOOSE trigger creates the change ProDataSet:
The CREATE DATASET statement simply allocates a structure for the ProDataSet definition and points the handle hDSChanges at that structure.
The CREATE-LIKE method creates the dynamic temp-tables and Data-Relations in that structure. If you provide a prefix as a second argument to CREATE-LIKE, then the temp-table names are prefixed by that string. Otherwise, they have the same names as in the original ProDataSet.
The GET-CHANGES method copies all the before-table rows and their after-table partners into the dynamic change ProDataSet.
3. Turn the TRACKING-CHANGES flag off for the ttOline table, before passing it to the update procedure for processing, as shown:
TEMP-TABLE ttOline:TRACKING-CHANGES = FALSE.
The MERGE-CHANGES method that you will use later to merge any final field updates back into the ttOline table the browse is displaying requires that TRACKING-CHANGES be false. In any case, you need to set it off at some point as part of preparing for the next set of changes the user makes.
4. Add a statement to run a new business logic procedure that accepts the changes and writes them back to the database, as shown:
RUN updateOrder.p (INPUT-OUTPUT DATASET-HANDLE hDSChanges BY-REFERENCE).
The change ProDataSet is an INPUT-OUTPUT parameter so that the window procedure gets back any final changes to the data, as well as any messages. The BY-REFERENCE qualifier tells the AVM to share the same ProDataSet instance when updateOrder.p is run locally.
5. Define the update procedure updateOrder.p:
/* updateOrder.p -- accepts a ProDataSet and saves changes to the
OrderLine to the database. */
{dsOrderTT.i}
{dsOrder.i}
DEFINE INPUT-OUTPUT PARAMETER DATASET FOR dsOrder.
Because the procedure uses the static temp-table and ProDataSet definitions, it can receive the dynamic change ProDataSet that was passed into it as a DATASET-HANDLE using the static DATASET parameter form. If this procedure is run locally within the same session as the window, as it is in this simplified example, then the ProDataSet is passed by reference so that updateOrder.p is actually pointing to the dynamic ProDataSet as it was created in the window procedure. If the same call is made remotely across an AppServer connection, then the BY-REFERENCE qualifier is ignored and the ProDataSet is marshaled across the network in both directions. The net result is the same, so this single call lets the AVM run the procedure in the most efficient way whether or not the application is actually distributed.
The update procedure needs these variable definitions. You will need the Data-Source definition for the OrderLine table later in the procedure. Define and attach the Data-Source srcOline, as shown:
DEFINE VARIABLE hAfterBuf AS HANDLE NO-UNDO.
DEFINE VARIABLE hBeforeBuf AS HANDLE NO-UNDO.
DEFINE VARIABLE hDSChanges AS HANDLE NO-UNDO.
Just to verify that the right data got transferred into the change ProDataSet, you can get the handle to the ProDataSet and the after-table buffer for ttOline. For example:
This is a bit convoluted, as you have to go from the after-table buffer to its temp-table handle, then to the before-table temp-table handle, and then from there to the before-table's default buffer handle. Alternatively, you can accomplish the same thing with the BEFORE-BUFFER or AFTER-BUFFER attribute:
hBeforeBuf = hAfterBuf:BEFORE-BUFFER.
Remember that the BEFORE-TABLE and AFTER-TABLE attributes are on the temp-table handles and return a temp-table handle. The BEFORE-BUFFER and AFTER-BUFFER attributes are on the buffer handles and return a buffer handle.
You can likewise find the first row in the before-table and display the Price from both buffers to show that the procedure got the right records, as shown:
You can save the updateOrder.p procedure and run the window to confirm this. Select an Order, modify the price of one of its OrderLines and click Save Changes:
Because there is a static definition of the ProDataSet and its temp-tables, you can access the before-table and its buffer directly by name. This code begins a CASE statement to process different kinds of changes:
/* Alternatively you can refer to the before-table and its buffer by
name because they are statically defined. */
FOR EACH ttOlineBefore:
CASE BUFFER ttOlineBefore:ROW-STATE:
WHEN ROW-MODIFIED THEN
DO TRANSACTION ON ERROR UNDO, LEAVE:
In this example, you will just handle modified rows, not creates or deletes. Defining the scope of the transaction is your responsibility. There are so many different ways in which you might want to handle multiple related changes. You can accept each successful change and reject the ones that fail. You can reject the entire set of updates if anything fails. Or you can define anything in between. In this example each modified row is a separate transaction. This is appropriate if they are independent to the extent that you are not leaving the database in an invalid state if you allow some changes to be committed while returning an error status to be corrected for others.
Also, remember that as always, the default buffer name for a temp-table is the same as the temp-table name. Depending on which one you are referring to, you might need to qualify the reference with the keyword BUFFER or TEMP-TABLE. In this case, the FOR EACH statement always expects a buffer name, so there is no need to qualify the name to tell the AVM that this is a buffer reference. The CASE statement, however, does not know what to expect, so you have to provide an explicit reference to BUFFER ttOlineBefore so that the AVM knows to look for the ROW-STATE attribute on the buffer, not its temp-table. Remember, also, that the keyword ROW-MODIFIED evaluates to the integer value 2, which is the actual value the ROW-STATE attribute returns.
For each modified row, you need to assign the changes back to the database. To demonstrate what the SAVE-ROW-CHANGES method does for you, you can do the same work "by hand" in ABL statements so that you understand all the steps.
First, you need to find and lock the database record that was used to populate the changed row. In this case, the unique key is composed of the OrderNum and LineNum fields:
/* This is what SAVE-CHANGES will do for us. */
FIND OrderLine WHERE OrderLine.OrderNum = ttOlineBefore.OrderNum AND
OrderLine.LineNum = ttOlineBefore.LineNum EXCLUSIVE-LOCK.
In the general case, it can be difficult to assemble the proper where-clause to retrieve the database record. This is what the SAVE-WHERE-STRING attribute on the Data-Source is for. You can substitute that value, which in this case is the same as the string in the FIND statement above, starting with the keyword WHERE. In order to access the SAVE-WHERE-STRING attribute, you must first attach the Data-Source. You did this at the top of the procedure. With that done, this statement can replace the FIND statement in the last code block:
Do not be confused by these two related Data-Source attributes:
SAVE-WHERE-STRING is the where-clause needed to retrieve the right database record to match a before-table record that you are using as the basis of an update. This is why it compares the database buffer with the before-table buffer to identify a match. The SAVE-WHERE-STRING attribute requires an argument, which is the index of the database buffer you are trying to retrieve. In this example, OrderLine is the first (and only) database buffer for the Data-Source srcOrder.
By contrast, FILL-WHERE-STRING is the where-clause needed to retrieve the right child database records for the current parent record when doing an automated FILL. In the where-clause for ttOline, for instance, FILL-WHERE-STRING joins the parent ttOrder temp-table to the OrderLine database table.
Use the attribute values such as SAVE-WHERE-STRING and FILL-WHERE-STRING wherever possible to generate ProDataSet-specific code for you. This way your procedures will be more flexible, more reusable, and less prone to error if the underlying table definitions change.
You need to compare the before-table record with what is in the database to make sure no one else changed it since your procedure read it into the original ProDataSet. You can set the ERROR-STRING attribute for the row if there is a conflict. For example:
IF NOT BUFFER OrderLine:BUFFER-COMPARE(BUFFER ttOlineBefore:HANDLE) THEN
BUFFER ttOlineBefore:ERROR-STRING = "Someone else changed it.".
If there is no conflict with another change to the same database record, next you must find the after-table row for this change and copy its values into the database:
It is important to examine why we used the BUFFER-COMPARE and BUFFER-COPY methods on the buffer handles here rather than the BUFFER-COMPARE and BUFFER-COPY statements. After all, we are dealing with static buffers, so the statements would have been usable.
The reason, as you should recall, is that the BUFFER-COMPARE and BUFFER-COPY methods have been extended to use the Data-Source field mapping list and field include list when they are used to compare a ProDataSet temp-table buffer to its Data-Source buffer. In this case the definition of ttOline is simple enough that the static statements would have worked correctly. There are no field name changes between OrderLine and ttOline, and no limited list of fields to include in the copy or compare. But in other cases where there is a field mapping or an include field list, the static statements would not work unless you went to the trouble of including the field mapping and include list as options on the static statement. If you use the methods instead, this is done for you.
Use the dynamic BUFFER-COPY and BUFFER-COMPARE methods wherever possible to copy rows into and out of ProDataSet temp-tables. Even if a table definition has no field mapping or include field list, your copy and compare will continue to work without change in the future if the table definitions ever change.
You need to retrieve any changes made by database triggers into the temp-table in preparation for returning it to the caller. To do this, you release the database buffer to force any triggers to fire, and then re-read the record, NO-LOCK this time, and buffer-copy it back into the after-table. You can use the SAVE-WHERE-STRING attribute again to find the record, this time NO-LOCK. For example:
/* Force execution of any triggers. */
VALIDATE OrderLine.
BUFFER ttOline:BUFFER-COPY(BUFFER OrderLine:HANDLE).
RELEASE OrderLine.
You then end all the procedure blocks with the END statement:
END. /* ELSE DO IF not changed by someone else. */
END. /* DO WHEN ROW-MODIFIED */
END CASE.
END. /* FOR EACH ttOlineBefore */
6. Go back to the CHOOSE OF BtnSave trigger in the window procedure dsOrderWinUpd.w.
It receives back the modified ttOline rows as part of the dsOrder INPUT-OUTPUT parameter. You have to merge these final changes back into your original ProDataSet so that they show up in the user interface. The MERGE-CHANGES method can do this for you, but again let us go through the steps in ABL code to confirm what the method will do for you.
7. Create a dynamic query to walk through the after-table for OrderLines in the change ProDataSet, as shown:
CREATE QUERY hQuery.
Because you used a dynamic ProDataSet to hold the changes by using the CREATE DATASET statement and the CREATE-LIKE method, all references to the ProDataSet need to be dynamic.
You can refer to the buffer in the GET-BUFFER-HANDLE method by position, as in this example, or by name ("ttOline" in this case) as you did in updateOrder.p. If you are writing general-purpose code that needs to be reusable for a variety of ProDataSets, then the position option is more flexible, because it does not hardcode the buffer name into the procedure.
One other important thing to note here is that you must not refer to the buffer name directly without getting it through its dynamic ProDataSet. That is, you can refer to the buffer name relative to the ProDataSet, like this:
But you cannot refer to the buffer name directly in this way:
/* Do not refer to the buffer directly in this way: */
hQuery:ADD-BUFFER("ttOline").
The reason is that if you do not give the AVM any context for the reference to ttOline, it locates the static temp-table definition for ttOline, which is part of the original static ProDataSet dsOrder. This is the wrong buffer in this case. You must direct the AVM to use the buffer in the dynamic change ProDataSet. This is a result of the fact that it is acceptable to have multiple objects with the same name within a procedure if no more than one is statically defined, but you must refer to the dynamic objects that share the name through their handles or the handles of their parents. So in this example, any unqualified reference to ttOline refers to the static temp-table's buffer. Any references to the dynamic ttOline must be through a handle.
As these examples show, you will often need to refer to multiple different temp-tables and buffers with the same name when you are working with ProDataSets. Make sure that you properly reference dynamic references so that they point to the proper table or buffer.
If you had used the prefix argument to add a string to the beginning of each temp-table name, then you could safely refer to the buffer by name in an ADD-BUFFER method, because the name would be unique.
While we are on the subject of buffer names, there is one more thing you should understand about the buffer names in these dynamic ProDataSets. The before-table and its buffer in any dynamic ProDataSet are given the name "BI" plus the after-table name (up to 32 characters). So, for example, the before-table for ttOline in a dynamic ProDataSet that you CREATE-LIKE dsOrder is named BIttOline. If you had specified the prefix argument such as "chg" to the CREATE-LIKE method, then the before-table name would be chgBIttOline.
9. Prepare the dynamic query to walk through the after-table rows, as shown:
hQuery:QUERY-PREPARE("FOR EACH " + DSChanges:GET-BUFFER-HANDLE(2):NAME).
Here again the statement merits a brief discussion of the alternatives.
The QUERY-PREPARE method requires a string that evaluates to FOR EACH ttOLine. You could do this just by passing that literal string to QUERY-PREPARE directly. In the example above, the reference is more indirect, going through the buffer handle of the ProDataSet. This kind of reference is useful when you want to be able to reuse the same code for potentially different temp-table names.
You might wonder why it would be acceptable to refer to ttOline directly in the QUERY-PREPARE method, as in hQuery:QUERY-PREPARE("FOR EACH ttOline"), when it is not acceptable to refer to this dynamic buffer directly by name in the preceding ADD-BUFFER method, as in hQuery:ADD-BUFFER("ttOline"). The reason is that the ADD-BUFFER method has no context for the name. You could be adding any ttOline buffer to this dynamic query. This is why you need to make sure that it uses the right one by referencing it through its parent ProDataSet handle. However, having done this, the QUERY-PREPARE method can use the name of the buffer without a problem because it knows that this query is for the particular ttOline buffer that was established by the ADD-BUFFER method.
10. Open the after-table query and position to the first row in the table, as shown:
Instead of getting the buffer handle from the query, you could also get it from the ProDataSet reference that was used to build the query. For example:
/* Alternative way of getting the query buffer handle. */
hBuffer = hDSChanges:GET-BUFFER-HANDLE(2).
Note that in the first instance, GET-BUFFER-HANDLE is operating on the query, which in this case has only one buffer. This makes the buffer number argument optional. The alternative uses the same method name to extract the second buffer handle (for ttOline) relative to the ProDataSet it is a part of.
11. Walk through the rows in the query and locate the corresponding row in the original ttOline table. Buffer-copy the final values to the original ProDataSet table.
Here is how to do this with specific code to use the key fields from ttOline to identify the appropriate row in the other table:
DO WHILE hBuffer:AVAILABLE:
FIND ttOline WHERE ttOline.OrderNum =
INTEGER(hBuffer:BUFFER-FIELD("OrderNum"):BUFFER-VALUE)
AND ttOline.LineNum =
INTEGER(hBuffer:BUFFER-FIELD("LineNum"):BUFFER-VALUE).
BUFFER ttOline:BUFFER-COPY(hBuffer).
hQuery:GET-NEXT().
END. /* DO WHILE AVAILABLE */
This technique has its problems, though. For one thing, it is difficult to generalize. This FIND statement is very specific to the OrderLine table and its key fields. Second, if the key field values have been assigned by the update logic (as is often the case for a newly created record), then the key values will not even match up.
For this reason, the AVM provides the ORIGIN-ROWID attribute to point directly at the corresponding row in the origin ProDataSet. This value is assigned by the AVM for every modified row when you execute the GET-CHANGES method specifically, so that you can identify the right rows in the origin ProDataSet at the time of a merge. Naturally, you must merge changes back into exactly the same ProDataSet instance as GET-CHANGES was run on. Otherwise, the temp-table ROWID values will not match. The AVM uses the ORIGIN-HANDLE attribute internally when it executes the MERGE-CHANGES method to verify this. You can use ORIGIN-HANDLE yourself if you are doing the work of MERGE-CHANGES in your own ABL code and there is a need to verify that the ProDataSet you are merging changes back into is the appropriate one.
The ORIGIN-ROWID attribute is set for both the before-table and after-table rows in the change ProDataSet, so that you can start with either one. It holds the ROWID of the before-table row in the origin ProDataSet. If you are applying final changes back to modified or created records, then you need to get to the corresponding after-table row in the origin ProDataSet. This makes identifying the right row a two-step process.
Here are the statements that can replace the previous FIND statement:
/* This is where the ORIGIN-ROWID lets me get to the right ttOline record
in the base ProDataSet. */
/* FIND ttOline WHERE ttOline.OrderNum =
INTEGER(hBuffer:BUFFER-FIELD("OrderNum"):BUFFER-VALUE)
AND ttOline.LineNum =
INTEGER(hBuffer:BUFFER-FIELD("LineNum"):BUFFER-VALUE). */
From the after-table buffer for ttOline in the change ProDataSet (which is the handle hBuffer), you use a dynamic FIND-BY-ROWID to position the before-table for ttOline in the origin ProDataSet to the before-image of the same row. Then you reference this record's AFTER-ROWID to identify and find the after-table row in ttOline in the origin ProDataSet. This is the row you want to copy final field changes to. The BUFFER-COPY method does this.
This two-step process is unavoidable. First, you need to back out a delete that has failed on the server in the origin ProDataSet. To do so, you must locate the before-table row in the origin ProDataSet to delete it after you re-create the un-deleted row in the after-table. This is all part of what the AVM does for you when you use the MERGE-CHANGES method.
12. Synchronize the top-level query (on ttOrder), as shown:
/* This forces the relation queries to re-open and refresh the browse. */
BUFFER ttOrder:SYNCHRONIZE().
This is necessary to force the relation queries to re-open and refresh the browse. Using the preprocessor {&OPEN-QUERY-OlineBrowse} would not work because the query the browse is using is the query on the Data-Relation, not the static ttOline query generated by the AppBuilder.
13. Use the ACCEPT-CHANGES method to clear all the before-table records and to accept the new values in any changed after-table records as the starting point for any further changes. Delete the dynamic change ProDataSet now that you are through with it:
14. Enable the OrderNum field again so the user can request another Order and disable the SaveChanges button until there are more changes to save. Also, turn the TRACKING-CHANGES flag back on for ttOline to capture any further changes that are made to this Order before another Order is selected. For example:
/* Re-enable the Order Number to select another Order. Also, set
TRACKING-CHANGES back to TRUE to capture any further changes made to
this Order. */
ASSIGN
iOrderNum:SENSITIVE IN FRAME dsFrame = TRUE
SELF:SENSITIVE = FALSE
TEMP-TABLE ttOline:TRACKING-CHANGES = TRUE.
Because this is the trigger for the button, you can refer to it as SELF, and no frame qualifier is needed.
15. Go into the property sheet for BtnSave and make it initially disabled. It will be enabled by the code when there are changes to save.
16. In the ROW-LEAVE trigger for OlineBrowse, enable the Save button if the row was modified, as shown:
IF OlineBrowse:MODIFIED THEN
ASSIGN
INPUT BROWSE OlineBrowse
{&ENABLED-FIELDS-IN-QUERY-OlineBrowse}
/* Disable the Order Number until changes are saved. */
iOrderNum:SENSITIVE IN FRAME dsFrame = FALSE
BtnSave:SENSITIVE IN FRAME dsFrame = TRUE.
17. In the LEAVE trigger for field iOrderNum, disable the Save button when a new Order is selected:
Here the DO block around the ASSIGN statement scopes the references to the dsFrame, so the IN FRAME phrase is not needed.
If you save this and rerun the window procedure, you can see the effect of the code that writes the changes back to the database.
18. Select one or more OrderLines, change the Price (and also Qty and Discount if you like), and choose the Save Changes button.
You see the confirmation that the records were written back to the database, because the Extended Price field, which is calculated by a database trigger procedure, it changed to reflect the new Price, Qty, and Discount. For example:
Changing the Price and Qty of line 2 recalculates the Extended Price, as shown:
SAVE-WHERE-STRING is the where-clause needed to retrieve the right database record to match a before-table record that you are using as the basis of an update. This is why it compares the database buffer with the before-table buffer to identify a match. The SAVE-WHERE-STRING attribute requires an argument, which is the index of the database buffer you are trying to retrieve. In this example, OrderLine is the first (and only) database buffer for the Data-Source srcOrder.
