Extending the sample procedure to pass a parameter
Now you can try passing a ProDataSet as a parameter, by building a new procedure in the AppBuilder to display the contents of your Order ProDataSet in a window, and calling the procedure that fills the ProDataSet from the new procedure. The AppBuilder uses a special database called Temp-DB to hold temp-table definitions. It uses these definitions to understand which fields are in which tables when you reference those fields in your code or use them to build windows with browses and fields in them. The AppBuilder contains a utility that provides support for creating and maintaining temp-table definitions using the Temp-DB to store the definitions. This section walks you through the process of using this utility to generate a temp-table include file that the AppBuilder can process. This procedure is a replacement for the original dsOrderTT.i include file that you built by hand in Introducingthe OpenEdge DataSet.
To generate the temp-table include:
1. If you already have a database called Temp-DB where you have stored temp-table definitions, you can continue to use it and make its definitions available to the AppBuilder by connecting the database at design time. If you have not done this, you must create an empty database called Temp-DB, place it in your ProPath, and then follow the rest of the steps in this procedure.
2. In the AppBuilder, create a new Window procedure.
3. From the Tools menu, select the TEMP-DB Maintenance Tool:
4. If the Temp-DB is not already connected, the AppBuilder prompts you to connect it:
Select Connect and click OK.
5. The first time you use the Temp-DB Maintenance Tool, it must add a special control table to the Temp-DB where it holds meta-information about all the temp-table definitions it manages. If you get this prompt, press OK to load the definitions for this meta-table:
Next you need to create temp-table definitions in the Temp-DB for the three temp-tables in your Order ProDataSet. You could import the include file you already have and start from that, but there is a good reason why you should not. Creating temp-table definitions that are defined to be LIKE the corresponding database tables is a simple shortcut, but it is not a good way to build definitions for a real application. It ties the temp-table definitions to any later changes that are made to the database tables, and it creates a requirement that the application database be connected at compile time, even though it should not be connected to the client side of the application at run time.
The Temp-DB Maintenance Tool can easily generate a temp-table definition that is initially the same as a database table definition, but it does this with each field independently defined, so that you can edit the definition to be exactly what the internal data definition should be, and remove any ongoing dependencies on the external table definition. For this reason, you will replace dsOrderTT.i in these steps.
The Temp-DB Maintenance Tool lets you create temp-table definitions based on database tables, or using your own field definitions, or any combination of the two. You do this in the editor part of the window. You can also import and manage any number of existing include files using the File Import tab, which displays all the temp-tables in the tool's browse control.
6. Right click in the editor to bring up its popup menu and select Table Definition > Insert, as shown:
Note: You can also access this option through the Edit menu.
7. Select the Order table from the sports2000 database as your first temp-table:
8. The tool builds a complete description of the Order table as a series of temp-table field and index definitions and displays it in the editor. Modify this to change the table name to ttOrder.
9. Add the three field definitions for the additional fields OrderTotal, CustName, and RepName as you did when you created the first version of dsOrderTT.i, as shown in the editor.
10. Remove all the index definitions except the unique primary index OrderNum.
11. Repeat these steps for the OrderLine table. Select Table Definition > Insert to select OrderLine and add its temp-table definition to the end of the editor contents. Rename the temp-table ttOline. Remove all but its primary index definition.
12. Repeat these steps again for the Item table. Name the temp-table ttItem and remove all but its unique primary index definition.
13. When you are done, click the Save button above the editor, as shown below. Select your dsOrderTT.i filename as the file to save the definitions to. The tool saves the include file, analyzes it, and displays the three temp-tables it contains in the browse, as shown:
The Use as Include toggle box above the editor (which is also shown as the Use Include browse column for the temp-tables) indicates that you want the tool to save the definition as an include file to be included in your AppBuilder procedures, rather than having the AppBuilder generate the temp-table definitions inline. This is beneficial for keeping your temp-table maintenance independent of the procedures the temp-tables are used in.
14. In the Definitions section of your new Window procedure, include the Order ProDataSet definition include file:
The AppBuilder will automatically generate a reference to the temp-table include file for you, when you bring those temp-tables into the procedure in a later step.
15. The temp-table definitions you just created are independent of any particular procedure that you build in the AppBuilder. To tell the AppBuilder that you want to use particular temp-table definitions in your new window, you use the Temp-Table Maintenance utility. To access this, click the Procedure settings button in the AppBuilder main window, as shown:
16. Click the Temp-Table definitions button (
17. ) in the Procedure Properties dialog. The Temp-Table Maintenance dialog appears, as shown:
18. Add a temp-table LIKE ttOrder from the Temp-DB database. Because you have already defined the temp-tables exactly as they should be, you do not need to change anything in the definition, including the temp-table name, as shown:
19. Check off the NO-UNDO toggle for the table. Your tables will want to be able to take advantage of the AVM undo capability when you allow updates to the tables.
20. Do the same for the ttOline and ttItem tables:
21. Click OK to exit the Temp-Table Maintenance dialog and then the Procedure Settings dialog.
22. Name the window dsOrderWin, and its default frame dsFrame.
23. Define several fill-in variables to display some of the ttOrder fields in: integer fill-ins iOrderNum and iCustNum, character fill-ins cCustName and cRepName, and a decimal field dOrderTotal.
24. Make each of these fields Enabled, and also set the Read-Only toggle box in the field property sheet for each of them except the iOrderNum field.
You will use iOrderNum to enter an Order Number to pass to the ProDataSet fill procedure. The other fields simply display the Order values that come back. Making them enabled but read-only gives them a field border that makes them easier to read.
25. Drop a browse object from the AppBuilder palette onto the window and use it to define a browse on the temp-table ttOline.
You do this by selecting Temp-Tables from the database list. The AppBuilder represents the temp-table definitions as if they were in a dummy database called Temp-Tables, as shown:
26. Click the Fields… button and add some or all of the ttOline fields to the browse.
27. Call the new browse OlineBrowse.
28. Create another browse for the ttItem table and add the ItemNum and ItemName fields to it. Name the browse ItemBrowse. When you are done, your window should look something like this:
Now you need to run a separate procedure that fills the Order ProDataSet with all the records for an Order, and then displays what that procedure returns.
29. Define this LEAVE trigger for the OrderNum field:
DO:
ASSIGN iOrderNum.
IF iOrderNum NE 0 THEN DO:
DATASET dsOrder:EMPTY-DATASET.
RUN fillDSOrder.p (iOrderNum, OUTPUT DATASET dsOrder BY-REFERENCE).
FIND FIRST ttOrder.
DO WITH FRAME dsFrame:
ASSIGN
iCustNum:SCREEN-VALUE = STRING(ttOrder.CustNum)
cCustName:SCREEN-VALUE = ttOrder.CustName
cRepName:SCREEN-VALUE = ttOrder.RepName
dOrderTotal:SCREEN-VALUE = STRING(ttOrder.OrderTotal).
{&OPEN-BROWSERS-IN-QUERY-{&FRAME-NAME}}
END.
END.
END.
If the user has entered an Order number, the trigger first empties the ProDataSet in case it still has data from a previous request. It then runs the procedure to fill the Order ProDataSet, passing in the Order number and getting the ProDataSet back as an OUTPUT parameter with the BY-REFERENCE qualifier. Passing it BY-REFERENCE eliminates the need to copy all the data from fillDSOrder.p back to the window procedure, but requires that you explicitly empty the ProDataSet first so as not to append data to the existing ProDataSet in the window procedure when the call is made locally.
The trigger then displays the Order fields and uses the AppBuilder-generated preprocessor to open the browses for OrderLines and Items.
30. Save this window procedure as dsOrderWin.w. Now you need to modify the fill procedure to accept the parameters.
31. Add these parameter definitions to fillDSOrder.p following the temp-table and ProDataSet definitions:
{dsOrderTT.i}
{dsOrder.i}
DEFINE INPUT PARAMETER piOrderNum AS INTEGER NO-UNDO.
DEFINE OUTPUT PARAMETER DATASET FOR dsOrder.
The parameters need to follow the ProDataSet definition because the OUTPUT parameter references the ProDataSet dsOrder.
32. Change the FOR EACH statement in the Order query to use the Order number passed in:
QUERY qOrder:QUERY-PREPARE("FOR EACH Order WHERE Order.OrderNum = " +
STRING(piOrderNum) + ", FIRST Customer OF Order, FIRST SalesRep OF Order").
33. Remove the FOR EACH:…DISPLAY blocks from fillDSOrder.p. Now when you run the window you can enter an Order number and see all its data, as shown:
The window shows that the ProDataSet parameter has returned, through a single handle, the fields from the ttOrder record, the set of OrderLines in the ttOline table, and all their Items in the ttItem table. When the ProDataSet comes back as an OUTPUT parameter in a remote call, all the local copies of its temp-tables are automatically populated.
The Order Total is not filled in yet because that is a calculated field. In the next chapter, you will learn about event procedures for ProDataSets and how to write code to calculate that Order Total value for each Order as it is filled. Later, you will also learn how to synchronize the OrderLine browse and the Item browse so that the Item browse is repositioned to the currently selected OrderLine.
