Release 10.1A: OpenEdge Development:
ProDataSets

> ## Extending the samples to GET, SAVE, MERGE, and ACCEPT changes

> Now, you’ll extend `dsOrderWinUpd.w` to use these methods to return changes to the database. Note that you’ll 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. 4GL 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 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 hDSChanges AS HANDLE     NO-UNDO.
>     DEFINE VARIABLE hDSOrder   AS HANDLE     NO-UNDO.
>     DEFINE VARIABLE hQuery     AS HANDLE     NO-UNDO.
>     DEFINE VARIABLE hBuffer    AS HANDLE     NO-UNDO.

> 2. This series of statements in the `CHOOSE` trigger creates the change ProDataSet:

>     hDSOrder = DATASET dsOrder:HANDLE.
>     CREATE DATASET hDSChanges.
>     hDSChanges:CREATE-LIKE(hDSOrder).
>     hDSChanges:GET-CHANGES(hDSOrder).

> 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 Progress 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 Progress 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 hDSChanges   AS HANDLE     NO-UNDO.
>     DEFINE VARIABLE hAfterBuf    AS HANDLE     NO-UNDO.
>     DEFINE VARIABLE hBeforeBuf   AS HANDLE     NO-UNDO.
>     DEFINE DATA-SOURCE srcOline FOR OrderLine.
>     BUFFER ttOline:ATTACH-DATA-SOURCE(DATA-SOURCE srcOline:HANDLE).

> 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:

>     hDSChanges = DATASET dsOrder:HANDLE.
>     hAfterBuf = hDSChanges:GET-BUFFER-HANDLE("ttOline").
>     hAfterBuf:FIND-FIRST().

> You can then retrieve the corresponding before-table buffer in this way:

>     hBeforeBuf = hAfterBuf:TABLE-HANDLE:BEFORE-TABLE:DEFAULT-BUFFER-HANDLE.

> 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:

>     hBeforeBuf:FIND-FIRST().
>     MESSAGE "After: " hAfterBuf:BUFFER-FIELD("Price"):BUFFER-VALUE
>             "Before:" hBeforeBuf:BUFFER-FIELD("Price"):BUFFER-VALUE.

> 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’ll 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 Progress that this is a buffer reference. The `CASE` statement, however, doesn’t know what to expect, so you have to provide an explicit reference to `BUFFER` `ttOlineBefore` so that Progress 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 4GL 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:

>     BUFFER OrderLine:FIND-FIRST(DATA-SOURCE srcOline:SAVE-WHERE-STRING(1),
>                                     EXCLUSIVE-LOCK).

> Don’t 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’re 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’re 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.
> Design tip: 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’s 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’s a conflict. For example:

>     IF NOT BUFFER OrderLine:BUFFER-COMPARE(BUFFER ttOlineBefore:HANDLE) THEN
>            BUFFER ttOlineBefore:ERROR-STRING = "Someone else changed it.".

> If there’s no conflict with another change to the same database record, next you need to find the after-table row for this change and copy its values into the database:

>     ELSE DO:
>        FIND ttOline WHERE ROWID(ttOline) = BUFFER ttOlineBefore:AFTER-ROWID.
>        BUFFER OrderLine:BUFFER-COPY(BUFFER ttOline:HANDLE).

> It’s 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’re 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.

> Design tip: 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 end all the procedure blocks:

>              END.    /* END ELSE DO IF not changed by someone else. */
>           END.        /* END DO WHEN ROW-MODIFIED */
>        END CASE.
>     END.                /* 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’s go through the steps in 4GL 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.

> 8. Add the one temp-table buffer to the query:

>     hQuery:ADD-BUFFER(hDSChanges:GET-BUFFER-HANDLE(2)).

> Design tip: 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’re 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:

>     hQuery:ADD-BUFFER(hDSChanges:GET-BUFFER-HANDLE(“ttOline”)).

> But you must not just refer to the buffer name directly in this way:

>     /* DON’T refer to the buffer directly in this way: */
>     hQuery:ADD-BUFFER(“ttOline”).

> The reason is that if you don’t give Progress 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 have to direct Progress to use the buffer in the dynamic change ProDataSet. This is a by-product 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.

> Design tip: 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 prepend 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’re on the subject of buffer names, there’s 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 " +
>     hDSChanges: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:

>     hQuery:QUERY-OPEN().
>     hQuery:GET-FIRST().
>     hBuffer = hQuery:GET-BUFFER-HANDLE().

> 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’s 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’s 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.  /* END DO WHILE AVAILABLE */

> This technique has its problems, though. For one thing, it’s 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 won’t even match up.

> For this reason Progress provides the `ORIGIN-ROWID` attribute to point directly at the corresponding row in the origin ProDataSet. This value is assigned by Progress 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 have to be merging changes back into exactly the same ProDataSet instance as `GET-CHANGES` was run on. Otherwise, the temp-table `RowID` values will not match. Progress 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 4GL code and there is a need to verify that the ProDataSet you’re 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 `FIND` statement above:

>     /* 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).
>     */
>     BUFFER ttOlineBefore:FIND-BY-ROWID(hBuffer:ORIGIN-ROWID).
>     BUFFER ttOline:FIND-BY-ROWID(BUFFER ttOlineBefore:AFTER-ROWID).
>     BUFFER ttOline:BUFFER-COPY(hBuffer).

> 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 undeleted row in the after-table. This is all part of what Progress 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’re done with it:

>     hDSOrder:ACCEPT-CHANGES().
>     DELETE OBJECT hDSChanges.

> 14. Enable the `OrderNum` field again so the user can request another `Order` and disable the Save Changes 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:

>     ASSIGN iCustNum:SCREEN-VALUE  = STRING(ttOrder.CustNum)
>            cCustName:SCREEN-VALUE = ttOrder.CustName
>            cRepName:SCREEN-VALUE  = ttOrder.RepName
>            dOrderTotal:SCREEN-VALUE = STRING(ttOrder.OrderTotal)
>            BtnSave:SENSITIVE = FALSE.

> Here the `DO` block around the `ASSIGN` statement scopes the references to the `dsFrame`, so the `IN FRAME` phrase isn’t 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:

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095