Try OpenEdge Now
skip to main content
Advanced Read Operations : ProDataSets as a data access layer : Caching complex derived data in a ProDataSet

Caching complex derived data in a ProDataSet

As you saw in the temp-table definitions, there are calculated fields in the SalesRep temp-table. We need a procedure called postRepRowFill to generate those calculated fields. To be sure, the calculations are not very complex in this case, but we use this as a placeholder for a more serious application where a table of complex data that is relatively expensive to derive is loaded into a ProDataSet once so that it can be used throughout a session.
There are various alternatives to this approach. If the derived data is common to all use cases, then it can be calculated once each time the data it depends on is changed, for example, by database trigger procedure code, and stored in permanent database tables of its own. But in many cases the calculations are specific to the immediate user of the data. For example, a price sheet might depend on various factors that change from session to session, including who the current Customer is, who the user is, what the nature of the product requirements are, and so forth. In a case like this, a ProDataSet instance can provide reusable calculations that can be kept available for the user's session or until a different Customer or product line is selected.
As with all FILL event procedures, this example receives the ProDataSet as an INPUT parameter, passed by reference. There are also several local variables, as shown:
PROCEDURE postRepRowFill:
The ProDataSet supports several different code tables. You need to get the handle to the SalesRep table. For example:
hSalesRep = phDataSet:GET-BUFFER-HANDLE("ttSalesRep").
It's a good idea to keep any references to the specifics of the ProDataSet structure like this as flexible as possible. In this case, the ttSalesRep table is the first one in the ProDataSet, but referencing it as GET-BUFFER-HANDLE(1) can give you maintenance headaches as definitions change. Also you might find yourself able to reuse parts of this code with a very different ProDataSet structure if you don't assume any unnecessary dependencies. In some cases even the table name could be a parameter.
The first calculated field is the total Annual Quota for each SalesRep. To calculate this, you total the 12 elements of the MonthQuota array, as shown:
  DO iMonth = 1 TO 12:
    dTotalQuota = dTotalQuota + SalesRep.MonthQuota[iMonth].

  hSalesRep:BUFFER-FIELD("AnnualQuota"):BUFFER-VALUE = dTotalQuota.
Remember that because the temp-table definitions are not included, you need to reference the fields dynamically through the buffer handle. You need to decide when this makes the code too complex without sufficient benefit of flexibility.
Next, you calculate the Total Balance of all the SalesRep's Customers:
  FOR EACH Customer WHERE Customer.SalesRep =
    dTotalBalance = dTotalBalance + Customer.Balance.
  hSalesRep:BUFFER-FIELD("TotalBalance"):BUFFER-VALUE = dTotalBalance.
END PROCEDURE. /* postRepRowFill */
The other entry points are functions that attach and detach the ProDataSets. The attachDataSet function takes the ProDataSet handle as an INPUT parameter, sets the callback procedure for the AFTER-ROW-FILL event for the ttSalesRep table, and attaches the Data-Sources, as shown:
    ("AFTER-ROW-FILL", "postRepRowFill", THIS-PROCEDURE).
    (DATA-SOURCE srcRep:HANDLE, "SalesRep.SalesRep,RepCode").
    (DATA-SOURCE srcState:HANDLE).
END FUNCTION. /* attachDataSet */
Nothing special happens here except mapping the SalesRep field in the database table to the RepCode field in the temp-table.
Note these facts about the HANDLE parameter to this function:
*You can pass a HANDLE of a ProDataSet to a user-defined function, but you cannot pass a ProDataSet using the DATASET or DATASET-HANDLE parameter forms. This is simply not supported. It is likely that if you find yourself needing the actual ProDataSet to be instantiated, or you need to reference more of the ProDataSet than a single field or other element that you pass in as a parameter, you are probably overstepping the bounds of what is appropriate for a function as opposed to an internal procedure. Remember that because a function can appear anywhere within a larger expression or where-clause, there are some restrictions on the kinds of data manipulation that are permitted within a function, where indexes and transactions are concerned. Try to limit your use of functions to concise operations that return a useful value that you would want to reference in an expression. (This recommendation is really independent of ProDataSets, but can serve to explain why passing a DATASET to a function was not deemed essential to support.) As you can see from the example, you can still access anything within the ProDataSet through its handle.
*Remember the difference between the DATASET-HANDLE form in the event procedure postRepRowFill and the HANDLE form in the function. Both parameters are in fact handles, but in the former case the AVM is prepared to pass in the entire ProDataSet definition and data. In the latter case only the handle itself is passed. Because the AVM internally passes the ProDataSet to the event procedure by reference, what happens is effectively the same as when the HANDLE is passed to the function: the AVM simply supplies the handle of an existing ProDataSet that is defined somewhere else. You can reference any element in the ProDataSet by walking through it by starting with its handle. But because the effects of this could be extremely different if the call were remote, the AVM requires that the parameters be of the appropriate form. You can supply a static DATASET parameter or a dynamic DATASET-HANDLE parameter to pass the ProDataSet itself, but if the caller passes just the HANDLE, then the called procedure (or function) must also define the parameter as a HANDLE.
The detachDataSet function simply detaches the Data-Sources, as shown:
END FUNCTION. /* detachDataSet */