Release 10.1A: OpenEdge Development:
ProDataSets

> ## Sample procedure: creating a view

> Because the caller is selecting a subset of the fields and rows, in this case the new ProDataSet has to copy data from the original one, rather than simply assigning new buffers to the existing temp-tables in their entirety.

> | To update the code:

> 1. Create the new procedure `fetchCustomTable` in `CodeSupport.p`. It takes the table name, field list, and selection `where-clause` as input parameters and returns the new dynamic ProDataSet, as shown:

>     PROCEDURE fetchCustomTable:
>         DEFINE INPUT  PARAMETER pcTable     AS CHARACTER  NO-UNDO.
>         DEFINE INPUT  PARAMETER pcFields    AS CHARACTER  NO-UNDO.
>         DEFINE INPUT  PARAMETER pcSelection AS CHARACTER  NO-UNDO.
>         DEFINE OUTPUT PARAMETER DATASET-HANDLE phFilterData.

> 2. Add the variables the procedure uses:

>     DEFINE VARIABLE iField  AS INTEGER    NO-UNDO.
>     DEFINE VARIABLE cField  AS CHARACTER  NO-UNDO.
>     DEFINE VARIABLE hTable  AS HANDLE     NO-UNDO.
>     DEFINE VARIABLE hQuery  AS HANDLE     NO-UNDO.
>     DEFINE VARIABLE hNewBuf AS HANDLE     NO-UNDO.
>     DEFINE VARIABLE hOldBuf AS HANDLE     NO-UNDO.

> 3. Create a dynamic ProDataSet and a dynamic temp-table to put into it, with the fields the caller requested:

>     /* Create a new dynamic ProDataSet based on the table and fields passed
>     in. */
>     CREATE DATASET phFilterData.
>     CREATE TEMP-TABLE hTable.
>     DO iField = 1 TO NUM-ENTRIES(pcFields):
>             cField = ENTRY(iField,pcFields).
>             hTable:ADD-LIKE-FIELD(cField,pcTable + "." + cField).
>     END.
>     hTable:TEMP-TABLE-PREPARE(pcTable).
>     hNewBuf = hTable:DEFAULT-BUFFER-HANDLE.
>     phFilterData:ADD-BUFFER(hNewBuf).

> 4. Create a dynamic query for the temp-table in the original ProDataSet and prepares it using the where-clause passed in:

>     /* Next create a dynamic query for the selection criteria passed in. */
>     CREATE QUERY hQuery.
>     hOldBuf = DATASET dsCode:GET-BUFFER-HANDLE(pcTable).
>     hQuery:ADD-BUFFER(hOldBuf).
>     hQuery:QUERY-PREPARE("FOR EACH " + pcTable + " WHERE " + pcSelection).

> 5. You can open the query yourself and buffer-copy all the rows that satisfy the selection into the new temp-table:

>     hQuery:QUERY-OPEN().
>     hQuery:GET-FIRST().
>     DO WHILE NOT hQuery:QUERY-OFF-END:
>             hNewBuf:BUFFER-CREATE().
>             hNewBuf:BUFFER-COPY(hOldBuf).
>             hQuery:GET-NEXT().
>     END.

> Or, you can create a dynamic Data-Source for the temp-table in the original ProDataSet and attach that Data-Source to the new temp-table buffer in the new ProDataSet. This shows how one ProDataSet table that has already been filled can be used as a Data-Source for a table in another ProDataSet. As in this example, this is appropriate if the original ProDataSet is filled with some set of generally useful data (and possibly, data that is expensive to regenerate and that needs to be used as a cache for the session), and if the second ProDataSet only wants a subset of its rows or fields. Remember that this approach does involve copying data from one ProDataSet to another.

> To use this method, remove or comment out the lines in the previous code section and replace them with this code:

>     CREATE DATA-SOURCE hCodeSource.
>     /* NOTE: hOldBuf is the source temp-table buffer, and the KEYS list is
>     not needed */
>     hCodeSource:ADD-SOURCE-BUFFER(hOldBuf, ?).
>     /* Because there is a specific query for selecting a subset of the
>     rows in the source temp-table, the procedure uses the dynamic query
>     defined above. Otherwise it could leave off the query and
>     get all rows automatically. */
>     hCodeSource:QUERY = hQuery.
>     /* Now when it attaches the Data-Source and fills the new ProDataSet
>     it gets rows from its Data-Source, which is the table in the original
>     ProDataSet. */
>     hNewBuf:ATTACH-DATA-SOURCE(hCodeSource).
>     phFilterData:FILL().
>     hNewBuf:DETACH-DATA-SOURCE().
>     This is the end of the alternative code to use the original ProDataSet
>     as a Data-Source for the custom subset. */

> 6. Delete the dynamic objects the procedure uses. Note that it's OK to delete the ProDataSet before returning because Progress delays the actual delete until the parameter has been returned. For example:

>     DELETE OBJECT phFilterData.
>     DELETE OBJECT hCodeSource.
>     DELETE OBJECT hQuery.
>     END PROCEDURE. /* fetchCustomTable */

> 7. Return to the window procedure `CodeWindow.w` to create a user interface for the custom ProDataSet.

> 8. Add these variables to the Definitions section:

>     DEFINE VARIABLE hCustomQuery  AS HANDLE     NO-UNDO.
>     DEFINE VARIABLE hCustomBrowse AS HANDLE     NO-UNDO.
>     DEFINE VARIABLE hCustomSet    AS HANDLE     NO-UNDO.

> You’re going to add some `Customer` fields to the window along with a Region combo box that lists the four regions in the US. When the user selects a region, the procedure runs `fetchCustomTable` to request a list of state codes and state names for that region. For simplicity’s sake, to reduce the size of the example somewhat, you’ll just use fields from the `Customer` table directly rather than a `Customer` ProDataSet, which, of course, would be the proper way to do things.

> 9. From the AppBuilder palette, select the DB Fields icon and drop the fields `CustNum`, `Name`, and `City` from Sports2000.Customer onto the window.

> 10. Select the combo box from the palette and create a combo box. It has the Object name `cRegion`, the Label `Region`, `5` Inner Lines, and the set of `List-Items` `<select>`,`East`,`West`,`Central`,`South`. The `<select>` choice prompts the user to select a region before seeing any `SalesReps` for it, as shown

> 11. Create another combo box called `cState`, with a label of `State` and `5` Inner Lines as well. It has no initial `List-Items`. The design window should now look roughly like this:

> 12. Code a `VALUE-CHANGED` trigger for the Region combo box.

> If the user makes a region selection, the trigger runs `fetchCustomTable`, requesting a ProDataSet with the `ttState` table, two of the three fields from the table, and only those `SalesReps` where the `Region` matches the one chosen, as shown:

>     DEFINE VARIABLE hStateBuf  AS HANDLE     NO-UNDO.

>     IF cRegion:SCREEN-VALUE NE "<select>" THEN
>         DO:
>             RUN fetchCustomTable IN hCodeSupport (INPUT "ttState",
>                 INPUT "State,StateName",
>                 INPUT "Region = '" + cRegion:SCREEN-VALUE + "'",
>                 OUTPUT DATASET-HANDLE hCustomSet).

> It empties the State combo in case this is not the first request, creates a dynamic query for it, and adds each `StateName` that came back in the ProDataSet from `fetchCustomTable` to the `List-Items` for the State combo. It makes the first one the current choice, and deletes the query now that it’s done with it, as shown:

>     cState:LIST-ITEMS = "".  /* Empty the old list if any. */

>     CREATE QUERY hCustomQuery.
>     hStateBuf = hCustomSet:GET-BUFFER-HANDLE("ttState").
>     hCustomQuery:ADD-BUFFER(hStateBuf).

>     hCustomQuery:QUERY-PREPARE("FOR EACH ttState").
>     hCustomQuery:QUERY-OPEN().
>     hCustomQuery:GET-FIRST().

>     DO WHILE NOT hCustomQuery:QUERY-OFF-END:

>        cState:ADD-LAST(hStateBuf:BUFFER-FIELD("StateName"):BUFFER-VALUE).
>                 hCustomQuery:GET-NEXT().
>     END.
>     cState:SCREEN-VALUE = cState:ENTRY(1).
>     DELETE OBJECT hCustomQuery.
>     END.

> Now your procedures are finished. When you run the window, the standard AppBuilder-generated code opens a `Customer` query and retrieves the first `Customer` for you because you added fields from that table to the window. You can then select a `Region` and see a list of all the `States` in that region to choose from, as shown:

> This illustrates how a ProDataSet that is filled with a set of useful data can be divided in many ways by other procedures that need various subsets of the data in the same session or another session. Data held in a ProDataSet in a client session can act as a cache for visual objects or client-side business logic that needs to view or use the data or a subset of the data. Any object in the same session can define its own query to browse or otherwise use a subset of the rows in the data.

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