Let us create a simple example to see how these dynamic statements work together. Create a new procedure called DynamicDataSet.p. This procedure:
Takes several input parameters that define the elements of a ProDataSet
Creates the ProDataSet
Prepares a query for its top-level table
Fills the ProDataSet
Returns it to the caller
The ProDataSet can have any number of buffers, but (for the sake of simplicity) creates a single Data-Relation between the top two buffers. Any additional buffers are considered independent buffers not related to others in the ProDataSet.
Here are the parameters for the procedure:
/* DynamicDataSet.p -- creates a dynamic DataSet and Data-Sources, fills it
for a key value passed in, and returns it. */
DEFINE INPUT PARAMETER pcBuffers AS CHARACTER NO-UNDO.
DEFINE INPUT PARAMETER pcFields AS CHARACTER NO-UNDO.
DEFINE INPUT PARAMETER pcSources AS CHARACTER NO-UNDO.
DEFINE INPUT PARAMETER pcSourceKeys AS CHARACTER NO-UNDO.
DEFINE INPUT PARAMETER pcKeyValue AS CHARACTER NO-UNDO.
DEFINE OUTPUT PARAMETER DATASET-HANDLE phDataSet.
The parameters provide the following information:
pcBuffers — A list of buffer handles expressed as a comma-delimited string for temp-tables in the caller to be included in the ProDataSet
pcFields — A list of fields to define the relation between the first two buffers passed in
pcSources — A list of database table names to use as Data-Sources for the buffers, one for each buffer
pcSourceKeys — A list of key fields for the Data-Source tables, one for each Data-Source
pcKeyValue — A key value for the top-level table to use to fill the ProDataSet
phDataSet — The procedure returns the dynamic ProDataSet as an OUTPUT parameter using the DATASET-HANDLE form, so that the caller can inspect and use the ProDataSet
In this first example, the ProDataSet is actually constructed from static temp-tables defined in the calling procedure, so it will work only within a single session. Later you will take advantage of additional dynamic ProDataSet methods and attributes to separate the caller from the called program entirely.
You need these local variables in the procedure:
DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE hDataSource AS HANDLE NO-UNDO.
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE iEntry AS INTEGER NO-UNDO.
The first executable statement in the procedure creates a dynamic ProDataSet.
The procedure then walks through the list of temp-table buffer handles passed into it as a comma-separated string and adds each in turn to the ProDataSet by converting each string back into a handle and executing the ADD-BUFFER method for it. For example:
CREATE DATASET phDataSet.
DO iEntry = 1 TO NUM-ENTRIES(pcBuffers):
phDataSet:ADD-BUFFER(WIDGET-HANDLE(ENTRY(iEntry, pcBuffers))).
END.
Next, the procedure adds a single Data-Relation to the ProDataSet, using the first buffer as the parent and the second buffer handle as the child. The GET-BUFFER-HANDLE method returns the ProDataSet's temp-table buffers in the same order in which they were added. The pcFields parameter defines the parent and child fields to use to establish the relation, as shown:
Next, the procedure walks through the list of source tables for the ProDataSet. For each one, it creates a dynamic Data-Source. It then creates a dynamic buffer for the table and uses ADD-SOURCE-BUFFER to add it to the dynamic Data-Source.
The final statement of this group uses a sequence of handle attributes to do several steps. Once again GET-BUFFER-HANDLE returns the handle of the temp-table buffer in the ProDataSet that corresponds to the Data-Source. The statement then uses this handle to attach the Data-Source to that buffer, as shown:
DO iEntry = 1 TO NUM-ENTRIES(pcSources):
CREATE DATA-SOURCE hDataSource.
CREATE BUFFER hBuffer FOR TABLE ENTRY(iEntry, pcSources).
hDataSource:ADD-SOURCE-BUFFER(hBuffer, ENTRY(iEntry,pcSourceKeys)).
phDataSet:GET-BUFFER-HANDLE(iEntry):ATTACH-DATA-SOURCE(hDataSource).
…
There is no problem with using the same buffer handle for each dynamic buffer and the same handle for each dynamic Data-Source because as each is added to the ProDataSet, the dynamic ProDataSet can keep track of them internally. Attributes and methods such as GET-BUFFER-HANDLE let you walk through the ProDataSet after it has been built so that you can identify all of its components.
Now the procedure needs to prepare a database query for the table that is the source for the first, top-level temp-table in the ProDataSet so that the ProDataSet can be filled with all the records related to a single top-level record.
For this entry, the procedure creates a dynamic query and adds the dynamic buffer for this table's database source table as the query's buffer. Then it constructs a QUERY-PREPARE string from the table name, the key field for the table, and the key value to use to populate the ProDataSet. (Note that for simplicity the procedure expects only one key field for each Data-Source.) It makes this dynamic query the query for the first Data-Source, as shown:
IF iEntry = 1 THEN DO:
CREATE QUERY hQuery.
hQuery:ADD-BUFFER(hBuffer).
hQuery:QUERY-PREPARE("FOR EACH " + ENTRY(1, pcSources) +
" WHERE " + ENTRY(1, pcSourceKeys) +
" = " + pcKeyValue).
hDataSource:QUERY = hQuery.
END. /* END DO IF iEntry = 1 */
This ends the loop that walks through the list of Data-Sources.
Finally, the procedure issues a FILL on the ProDataSet handle, then deletes the dynamic query and each dynamic Data-Source, as shown:
phDataSet:FILL().
DELETE OBJECT hQuery.
DO iEntry = 1 TO phDataSet:NUM-BUFFERS:
hBuffer = phDataSet:GET-BUFFER-HANDLE(iEntry).
DELETE OBJECT hBuffer:DATA-SOURCE.
END.
After this method executes, the procedure returns the dynamic ProDataSet to the caller as an output parameter.
Now it is time to write a procedure, DynamicMain.p, that calls this one. This version of the procedure defines some static temp-tables for the ProDataSet to use, along with a handle to hold the ProDataSet that is returned to it. For example:
/* DynamicMain.p -- gets DynamicDataSet.p to create, fill,
and return a dynamic DataSet for these temp-tables. */
DEFINE TEMP-TABLE ttCust NO-UNDO LIKE Customer.
DEFINE TEMP-TABLE ttOrder NO-UNDO LIKE Order.
DEFINE TEMP-TABLE ttSalesRep NO-UNDO LIKE SalesRep.
DEFINE VARIABLE hDataSet AS HANDLE NO-UNDO.
Here is the statement that runs the DynamicDataSet procedure:
The first parameter is a list of the buffer handles of this procedure's static temp-tables. Once again, this simplifies the example to the extent that these handles could, of course, not be passed across an AppServer call to be used on the server. Later in this chapter, you will see a fully dynamic version.
The second parameter is a list of key fields for the parent and child of the ProDataSet's one Data-Relation.
The third parameter is a list of the database source tables.
The fourth parameter is a list of the (single) key fields for each of those tables.
The fifth parameter is the key value to use to the top-level table, in this case Customer number 1.
The final OUTPUT parameter receives the ProDataSet back from the other procedure.
The following is a series of simple FOR EACH statements showing what we get back:
FOR EACH ttCust:
DISPLAY ttCust.CustNum ttCust.Name.
END.
FOR EACH ttOrder:
DISPLAY ttOrder.CustNum ttOrder.OrderNum.
END.
FOR EACH ttSalesRep:
DISPLAY ttSalesRep.SalesRep ttSalesRep.RepName.
END.
And finally, remember to delete the dynamic ProDataSet that has been returned, as shown:
DELETE OBJECT hDataSet.
When your procedure creates a dynamic ProDataSet or receives a dynamic ProDataSet as an OUTPUT parameter, you must remember to take responsibility for deleting it when you are done using it. If you specify BY-REFERENCE in the parameter, your procedure might not know for sure whether it "owns" the ProDataSet or not. If the called procedure is run locally, then the calling procedure is actually using the ProDataSet owned by the called procedure. If the same procedure is run across an AppServer connection, then the ProDataSet is copied across the network and a new dynamic ProDataSet is created in the calling procedure. In such a case, you should use the statement DELETE OBJECT dataset-handle NO-ERROR to delete the object only if it is created for the calling procedure. The AVM prevents the calling procedure from deleting a ProDataSet passed BY-REFERENCE from another local procedure, and the NO-ERROR keyword suppresses the warning error in this case.
Run the main procedure to see what you get back. First is the single Customer record that satisfies the top-level query, as shown:
Next come all of its Orders, as shown in this screen shot:
These are identified by the AVM based on the dynamic Data-Relation between these two tables. The AVM has constructed a query automatically that selected just these Orders for the ProDataSet.
The SalesReps come last. Because there is no Data-Relation defined for this table, and no query for it either, the AVM retrieves all the SalesReps and loads them all into the ProDataSet, as shown:
Takes several input parameters that define the elements of a ProDataSet
