Release 10.1A: OpenEdge Development:
ProDataSets

> ## Overview

> If you run `PickOrder.w` and tab through all the filter fields without entering a value into one, you’ll see a small but noticeable delay before the `Order` browse displays the `Order`s. After all, Progress has to read nearly 4000 `Order` records from the database, plus their `Customers` and `SalesReps`, create a temp-table record for each of them, and buffer-copy the database records to the temp-table. Then it copies the ProDataSet definition and the entire contents of the `ttOrder` table to the window procedure. Considering the amount of work it’s doing, it’s pretty amazing that it doesn’t take a lot longer than it does. However, if the number of rows were even larger, or if you were running the support procedure across an AppServer connection on a different machine, the delay would be much greater.

> Generally, you should try to avoid giving your users the opportunity to browse through very large numbers of rows on the client, instead prompting them to filter the data in advance as the example window does. However, in some cases you need to move a potentially large number of rows from server to client, and it is often better to do it in batches so the user can see some of the rows before every database record has been read and copied into the temp-table and across to the client. This section extends the example from Chapter 7, " Advanced Events and Attributes."to show you a way of doing this. At the same time we’ll show you how to limit the number of fields copied into the temp-table. After all, the window is only showing four fields from the `ttOrder` table, so there is really no point in copying every field into the temp-table, and more significantly, passing all those field values across to the client where they will never be seen or used.

> | To update the code:

> 1. To get started, copy the `PickOrder.w` procedure to `PickOrderBatch.w`, and the `OrderSupport.p` to `OrderSupportBatch.p`.

> 2. Change the `RUN` statement in the Main Block of `PickOrderBatch.w` to start `OrderSupportBatch.p`.

> 3. In the `LEAVE` trigger for `cSalesRep` in `PickOrderBatch.w`, change the `RUN` statement so that you run a different support procedure if the user doesn’t enter a value into any of the filter fields, as shown:

>     IF cSelection NE "" THEN  /* There were selection criteria */
>           RUN fetchOrders IN hOrderProc (INPUT cSelection,
>                                          OUTPUT DATASET dsOrder).
>       ELSE    /* No selection so retrieve (the first) batch of rows. */
>           RUN fetchOrderBatch IN hOrderProc
>                               (INPUT 0,   /* Start at the first Order */
>                                INPUT "OrderNum,CustNum,SalesRep,OrderDate",
>                                OUTPUT DATASET dsOrder).

> The new procedure `fetchOrderBatch` takes an `Order` `Number` to start with, and a list of fields to populate the temp-table with. Since you want to start fresh when the user tabs out of the `SalesRep`, you just pass `0` as the starting point in the `Order` table. The four fields in the second parameter are the fields the browse uses; those are the only ones you need values for on the client.

> The `OUTPUT` parameter is the same ProDataSet as before.

> 4. Switch over to `OrderSupportBatch.p`. First you need a new definition at the top, as shown:

>     DEFINE VARIABLE cFieldList  AS CHARACTER  NO-UNDO.

> The `cFieldList` is the list of fields to include in the `ttOrder` table, passed over to `fetchOrderBatch`.

> 5. Write the `fetchOrderBatch` procedure. It needs the three parameters you saw in the `SalesRep` trigger. For example:

>     PROCEDURE fetchOrderBatch:
>          DEFINE INPUT  PARAMETER piLastOrder AS INTEGER    NO-UNDO.
>          DEFINE INPUT  PARAMETER pcFieldList AS CHARACTER  NO-UNDO.
>          DEFINE OUTPUT PARAMETER DATASET FOR dsOrder BY-VALUE.

> The selection that becomes the `where-clause` for the `Order` query needs to start with the first `Order` `Number` greater than the one passed in. For the call in the `SalesRep` trigger, this is the first `Order` in the database. In later calls, the `INPUT` parameter will be the highest `Order` `Number` retrieved so far.

> The `pcFieldList` parameter passed in is saved in the variable `cFieldList`, which can be seen throughout the procedure. For example:

>     ASSIGN cSelection = "OrderNum > " + STRING(piLastOrder)
>                 cFieldList = pcFieldList.

> 6. You use the `BATCH-SIZE` attribute on the `ttOrder` buffer to tell Progress to fill only a maximum of 20 rows into the `ttOrder` temp-table at a time, as shown:

>     hDataSet:GET-BUFFER-HANDLE(1):BATCH-SIZE = 20.

> 7. The next four lines are the same as in `fetchOrder`, and you can copy them from there:

>     hDataSet:EMPTY-DATASET.
>     hDataSet:GET-BUFFER-HANDLE(2):FILL-MODE = "NO-FILL". /* ttOline */
>     hDataSet:GET-BUFFER-HANDLE(3):FILL-MODE = "NO-FILL". /* ttItem  */
>     hDataSet:FILL().

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