OpenEdge Development: Progress 4GL Handbook

Creating and Using Dynamic Temp-tables and Browses

This chapter continues the discussion of dynamic data management objects with information on how to create and use dynamic temp-tables and dynamic browses.

Using a dynamic temp-table, you can build up a table of any fields your application needs, some of which can be derived from database fields, and some or all of which can be completely independent of any database table. You can then define a dynamic buffer and a dynamic query to manage the temp-table data. Using a dynamic browse, you can define at run time not just what query a browse should display data for, but also what columns it should display and in what order.

This chapter includes the following sections:

Creating and using dynamic temp-tables
Temp-table parameters
Creating and using dynamic browses
Building a comprehensive example
Dynamic programming considerations

Creating and using dynamic temp-tables

Temp-tables are one of the most important and useful constructs in an OpenEdge application. A temp-table lets you define in-memory storage for any number of rows of any combination of fields you require. A temp-table can be a mirror of a single database table, or it can be the result of a join of tables. It can contain a subset of selected fields from one or more tables, or it can contain fields that don’t map to any database fields at all. And it can be any combination of these things. In general, temp-tables give you two basic capabilities:

A temp-table lets you create business logic that is independent of the particular structure of the underlying data source.
A temp-table is the mechanism you use for passing records from one OpenEdge session to another, in particular from an AppServer session where the database resides to a client session where the user interface for the application is.

This chapter expands on your knowledge of temp-tables by introducing you to dynamic temp-tables. It also introduces you to the attributes you can access and the methods you can invoke using a handle to either a static or dynamic temp-table.

Finally, this chapter details the various ways in which you can pass a temp-table from one procedure or one session to another.

As with other objects, you can get the handle to a static temp-table and use that handle to query its attributes, using this syntax:

tt-handle = TEMP-TABLE tt-name:HANDLE.

Having said this, however, a handle to a static temp-table is less useful than a handle to most other static objects, for two reasons:

There are only a few attributes you can access through a temp-table’s handle, and none of these are settable for a static table.
None of the temp-table methods are usable for a static temp-table.

These methods define the fields and indexes for the temp-table. You cannot change or extend the fields or indexes for a static temp-table.

To create a dynamic temp-table, use this statement:

CREATE TEMP-TABLE tt-handle.

There are no other options on the CREATE TEMP-TABLE statement. You specify everything about the table after you create it. Thus, the CREATE statement really does nothing more than set up the tt-handle variable to be a handle for a temp-table structure to fill in later.

A dynamic temp-table can be in one of three states. When you first create it, using just the CREATE TEMP-TABLE statement, it is said to be in the clear state. That is, the temp-table handle has been allocated but there is no definition for the table yet. After you start to use the temp-table methods to define the table’s fields and indexes, the table is said to be unprepared. This means that the table definition is not yet complete and you cannot start to use the table. After you complete the definition using its methods, you use a special TEMP-TABLE-PREPARE method to signal to Progress that the definition is complete. This effectively freezes the definition and allows you to start to use the table to store data. At this point, the temp-table is in the prepared state.

Temp-table methods

You define every aspect of a dynamic temp-table through its methods. Using these methods, you can define it to be like another table, to inherit individual field definitions from another table, or to create fields that have no relationship to fields in other tables. You can also define one or more indexes for the temp-table. You can execute a number of these methods in sequence to build up a temp-table that you then finalize and use. All the methods return a logical success flag. You should check this flag if it is possible that the arguments to the method might be invalid or that the method might fail for some other reason.

CREATE-LIKE method

If you want to create a temp-table LIKE a database table or another temp-table with all its fields and at least one of its indexes, use this method to copy all fields from the source table to the temp-table definition, along with index definitions:

tt-handle:CREATE-LIKE( { source-handle-exp | source-name-exp }     [, source-index-name-exp ] ).

You define the source table by either passing its handle or its name. The name expression can be either the table name as a quoted literal or a character expression that evaluates to the table name. A source table can be either a database table for a currently connected database or another temp-table whose scope makes it available to the procedure with the CREATE-LIKE method.

If you do not pass the optional second argument, then the temp-table inherits all the index definitions of the underlying table. If you want to specify only one index from the underlying table initially, you can pass its name as a character expression as the second argument. For example, this sequence of statements creates a dynamic temp-table with all the fields from the Customer table, along with just the Name index:

DEFINE VARIABLE hTT AS HANDLE     NO-UNDO. CREATE TEMP-TABLE hTT. hTT:CREATE-LIKE("Customer","Name").

You can only use the CREATE-LIKE method once for a temp-table definition, when the temp-table is in the clear state. Thus, if you use this method, it must be the first method you invoke for the temp-table. If you want fields from additional tables in the temp-table, you use the ADD-FIELDS-FROM method to add them. If you want to add more indexes from the source table, you can use the ADD-LIKE-INDEX method to do this.

ADD-FIELDS-FROM method

If you want to add a subset of fields from another table to a temp-table, you use the ADD-FIELDS-FROM method:

tt-handle:ADD-FIELDS-FROM( { source-handle-exp | source-name-exp}     [, except-list-exp ] ).

You can also use this method as many times as you need to in order to add fields from one or more additional tables to a temp-table that you’ve already started to build using the CREATE-LIKE or another ADD-FIELDS-FROM method.

As with the CREATE-LIKE method, you pass either the source table handle or an expression representing its name. If you want to exclude some fields from being copied into the temp-table definition, pass a comma-separated list of these field names as the optional second argument. If you add a field whose name is already in the target temp-table, Progress ignores the duplicate field and does not add it to the temp-table.

This example adds all fields from the SalesRep table except the MonthQuota and Region fields to the fields from the Customer table already in the temp-table:

DEFINE VARIABLE hTT AS HANDLE NO-UNDO. CREATE TEMP-TABLE hTT. hTT:CREATE-LIKE("Customer","Name"). hTT:ADD-FIELDS-FROM("SalesRep","MonthQuota, Region").

Both the Customer and SalesRep tables have a SalesRep field with the SalesRep’s initials. Because this field is already in the temp-table from the Customer table, it is not added from the SalesRep table, and no error results.

ADD-LIKE-FIELD method

If you want to add fields from another table to the temp-table individually, or you need to rename fields as you add them, use the ADD-LIKE-FIELD method once for each field:

tt-handle:ADD-LIKE-FIELD( tt-field-name-exp ,     { source-buffer-field-handle-exp | source-db-field-name-exp } ).

This method takes two arguments:

The name of the field in the temp-table, as a character expression.
An identifier of the source field, which can be either its buffer-field handle or an expression evaluating to its name, with the table name qualifier (and, if necessary, its database name qualifier).

This example adds the Region field from the SalesRep table, which was not added in the ADD-FIELDS-FROM method, and renames it to Area:

DEFINE VARIABLE hTT AS HANDLE     NO-UNDO. CREATE TEMP-TABLE hTT. hTT:CREATE-LIKE("Customer","Name"). hTT:ADD-FIELDS-FROM("SalesRep","MonthQuota"). hTT:ADD-LIKE-FIELD("Area", "SalesRep.Region").

ADD-NEW-FIELD method

If you want to add one or more fields to a temp-table definition that are not derived from a specific other database or temp-table field name, use the ADD-NEW-FIELD method:

tt-handle:ADD-NEW-FIELD( tt-field-name-exp , data type-exp [ , extent-exp [, format-exp [, initial-exp [, label-exp [, column-label-exp] ] ] ] ] ).

The first argument is the field name in the temp-table. The second is its data type. These two arguments are required. You can also define other attributes optionally, in this order:

The extent of the field, if it has one.
The format of the field.
The field’s initial value.
The field’s label.
The field’s column-label.

Because these arguments are position-dependent, you must include values for any intervening arguments you don’t specify. You don’t need to include commas or values for optional arguments following the last one you specify. For example, this statement adds an integer field called Sequence to the temp-table, with a format of "9999" and an initial value of 1000:

DEFINE VARIABLE hTT AS HANDLE      NO-UNDO. CREATE TEMP-TABLE hTT. hTT:CREATE-LIKE("Customer","Name"). hTT:ADD-FIELDS-FROM("SalesRep","MonthQuota"). hTT:ADD-LIKE-FIELD("Area", "SalesRep.Region"). hTT:ADD-NEW-FIELD("Sequence", "INTEGER",0,"9999",1000).

ADD-LIKE-INDEX method

You’ve already seen how a temp-table can inherit either one or all of the indexes from an underlying table, in the CREATE-LIKE method. You can also add indexes one at a time using other methods. The first of these is ADD-LIKE-INDEX. This method adds a single index to the temp-table that is derived from an existing index on another table. You specify the name you want the index to have in the temp-table, the name of the index in the source table, and either the buffer-handle to the source table or an expression holding its name:

tt-handle:ADD-LIKE-INDEX( tt-index-name-exp , source-index-name-exp     {, source-buffer-handle-exp | source-db-table-name-exp } ).

This example adds the CustNum index to the temp-table, in addition to the Name index that was added in the CREATE-LIKE method:

DEFINE VARIABLE hTT AS HANDLE NO-UNDO. CREATE TEMP-TABLE hTT. hTT:CREATE-LIKE("Customer","Name"). hTT:ADD-FIELDS-FROM("SalesRep","MonthQuota"). hTT:ADD-LIKE-FIELD("Area", "SalesRep.Region"). hTT:ADD-NEW-FIELD("Sequence", "INTEGER",0,"9999",1000). hTT:ADD-LIKE-INDEX("CustNum","CustNum","Customer").

ADD-NEW-INDEX method

If you want to add an index to a temp-table that isn’t derived from an index in another table, use the ADD-NEW-INDEX method:

tt-handle:ADD-NEW-INDEX( index-name-exp [, is-unique [, is-primary     [, is-word-index ] ] ] ).

You supply the index name and up to three optional logical values that indicate whether the new index has enforcement of unique values, whether it is the primary index of the temp-table, and whether it is a word-index. (A word index is a special index type on a CHARACTER field that allows Progress to retrieve records based on any word the field contains.)

ADD-INDEX-FIELD method

After you use ADD-NEW-INDEX, you invoke the ADD-INDEX-FIELD method once for each field in the new index. You pass the name of the index, the name of the field to add, and an optional third argument that evaluates to asc for ascending (the default) or desc for descending:

tt-handle:ADD-INDEX-FIELD( index-name-exp ,field-name-exp [ , mode-exp ] ).

If there are multiple fields in the index, the order in which you invoke ADD-INDEX-FIELD for the fields determines their order within the index.

This example adds a new unique index called SeqIndex to the temp-table and adds the Sequence field to it:

DEFINE VARIABLE hTT AS HANDLE      NO-UNDO. CREATE TEMP-TABLE hTT. hTT:CREATE-LIKE("Customer","Name"). hTT:ADD-FIELDS-FROM("SalesRep","MonthQuota"). hTT:ADD-LIKE-FIELD("Area", "SalesRep.Region"). hTT:ADD-NEW-FIELD("Sequence", "INTEGER",0,"9999",1000). hTT:ADD-LIKE-INDEX("CustNum","CustNum","Customer"). hTT:ADD-NEW-INDEX("SeqIndex", YES). hTT:ADD-INDEX-FIELD("SeqIndex", "Sequence").

TEMP-TABLE-PREPARE method

Once you have invoked all the methods that you need to fully define the temp-table fields and indexes, you must use the TEMP-TABLE-PREPARE method to finalize the definition before you use the temp-table. You must pass an argument to the method that gives the temp-table a name, as in this example:

DEFINE VARIABLE hTT AS HANDLE      NO-UNDO. CREATE TEMP-TABLE hTT. hTT:CREATE-LIKE("Customer","Name"). hTT:ADD-FIELDS-FROM("SalesRep","MonthQuota"). hTT:ADD-LIKE-FIELD("Area", "SalesRep.Region"). hTT:ADD-NEW-FIELD("Sequence", "INTEGER",0,"9999",1000). hTT:ADD-LIKE-INDEX("CustNum","CustNum","Customer"). hTT:ADD-NEW-INDEX("SeqIndex", YES). hTT:ADD-INDEX-FIELD("SeqIndex", "Sequence"). hTT:TEMP-TABLE-PREPARE("CustSequence").

Once you have invoked this method, the temp-table is in the prepared state. You can now begin to use it, creating and deleting records in the temp-table, opening queries on the table, and so forth. You cannot use the temp-table in any way until you prepare it. After you use the TEMP-TABLE-PREPARE method, you cannot invoke any of the other methods to change its definition except the CLEAR method discussed next.

CLEAR method

If you want to reuse a temp-table handle for a different temp-table definition, you can invoke the CLEAR method on the handle. The CLEAR method takes no arguments. It empties the temp-table if there are any records in it and completely erases its definition, returning the handle to the clear state. At this point, you can begin to build up a definition again from scratch. There is no way to undo parts of a dynamic temp-table definition, and there is no way to extend its list of fields and indexes once it has been prepared.

Temp-table attributes

This section describes the attributes you can access through a temp-table handle.

NAME attribute

This is the name of the temp-table. For a static temp-table, it is the name you gave it in the DEFINE TEMP-TABLE statement. For a dynamic temp-table, it is the name you specify in the TEMP-TABLE-PREPARE method. Thus, the NAME attribute is not defined for a dynamic temp-table until you have prepared it.

PREPARED attribute

This LOGICAL attribute returns true if the temp-table has been prepared and false otherwise. It always returns true for a static temp-table.

DEFAULT-BUFFER-HANDLE attribute

Every temp-table, whether static or dynamic, has a default buffer. This attribute holds the handle of that buffer. You use the buffer to create or delete records in the table. The default buffer has the same name as the temp-table, just as the default buffer for a database table has the same name as the table. The default buffer handle is not assigned until the temp-table is prepared.

PRIMARY attribute

This CHARACTER attribute holds the name of the primary index for the table. You can inherit the definition of the primary index from another table, in the CREATE-LIKE method, or you can define a temp-table index to be primary. This is the index the records in the table are sorted on, if there are no other sort criteria or if another index is not used to satisfy selection criteria of a WHERE clause on the table. You can set the value of the PRIMARY attribute for a dynamic temp-table only if the temp-table has not yet been prepared.

UNDO attribute

This LOGICAL attribute is true if the temp-table has undo properties and false if it is NO-UNDO. Because the static DEFINE TEMP-TABLE statement allows you to specify the NO-UNDO keyword on the definition, just as you can for variables, the default value of UNDO for a static temp-table is true. For a dynamic temp-table, the default value of UNDO is false. You can set the UNDO attribute for a dynamic temp-table only before it has been prepared.

Temp-table buffer methods and attributes

In addition to the default buffer handle that is an attribute of every temp-table, you can create one or more dynamic buffers for a dynamic temp-table. Use this syntax:

CREATE BUFFER buffer-handle FOR TABLE tt-handle:DEFAULT-BUFFER-HANDLE     [ BUFFER-NAME buffer-name ] [ IN WIDGET-POOL pool-name ].

Note that it is necessary to include the reference to the DEFAULT-BUFFER-HANDLE. For example, you can create a second dynamic buffer for the temp-table in the current example with these statements:

DEFINE VARIABLE hTT AS HANDLE     NO-UNDO. DEFINE VARIABLE hTTB AS HANDLE     NO-UNDO. CREATE TEMP-TABLE hTT. hTT:CREATE-LIKE("Customer","Name"). hTT:ADD-FIELDS-FROM("SalesRep","MonthQuota"). hTT:ADD-LIKE-FIELD("Area", "SalesRep.Region"). hTT:ADD-NEW-FIELD("Sequence", "INTEGER",0,"9999",1000). hTT:ADD-LIKE-INDEX("CustNum","CustNum","Customer"). hTT:ADD-NEW-INDEX("SeqIndex", YES). hTT:ADD-INDEX-FIELD("SeqIndex", "Sequence"). hTT:TEMP-TABLE-PREPARE("CustSequence"). CREATE BUFFER hTTB FOR TABLE hTT:DEFAULT-BUFFER-HANDLE     BUFFER-NAME "CustSeq2".

Now you have two dynamic buffers for the temp-table, one called CustSeq that you get “for free” along with the temp-table definition and a second that you created yourself.

The dynamic buffer methods that you learned about earlier are essential to using dynamic temp-tables and their buffer. In particular, you will use the BUFFER-CREATE method on the buffer handle to create records in the dynamic temp-table, BUFFER-DELETE to delete them, BUFFER-RELEASE to release them, and BUFFER-COPY to copy fields from one or more database tables into newly created records in the temp-table.

There are a couple of additional buffer methods and attributes specific to their association with temp-tables, however.

TABLE-HANDLE buffer attribute

This attribute returns the handle of the temp-table the buffer is associated with. For a buffer not associated with a temp-table, it returns the unknown value. Therefore, the temp-table’s DEFAULT-BUFFER-HANDLE points to its default buffer, and that buffer’s TABLE-HANDLE attribute points back to the temp-table. In addition, the TABLE-HANDLE attribute of any other buffer defined or created for the temp-table, such as CustSeq2, also points back to the temp-table.

EMPTY-TEMP-TABLE buffer method

It is much more efficient to empty a temp-table in one statement rather than in a FOR EACH loop. To empty a static temp-table you can use the EMPTY TEMP-TABLE tt-name statement. With the handle of a buffer for the temp-table, you can also use the EMPTY-TEMP-TABLE method on the buffer. It takes no arguments. Remember that while you can have multiple buffers associated with a temp-table, just as you can with a database table, there is only one set of records in the temp-table. The multiple buffers simply allow you to have pointers to multiple different records in the temp-table at the same time. Thus, you can use the EMPTY-TEMP-TABLE method on any buffer for the temp-table and the result is the same.

Changing field attributes in a temp-table buffer

You can change certain field attributes in a dynamic temp-table after you have prepared it, including the label, format, help, and column-label. You do this by using the DEFAULT-BUFFER-HANDLE and setting one or more field attributes on a buffer-field. This is only possible with dynamic temp-tables.

For example, adding this code to the previous example changes the label and format of the CustNum field:

DEFINE VARIABLE hTT      AS HANDLE     NO-UNDO. DEFINE VARIABLE hCustNum AS HANDLE     NO-UNDO. DEFINE VARIABLE hTTB     AS HANDLE     NO-UNDO. CREATE TEMP-TABLE hTT. hTT:CREATE-LIKE("Customer","Name"). hTT:ADD-FIELDS-FROM("SalesRep","MonthQuota"). hTT:ADD-LIKE-FIELD("Area", "SalesRep.Region"). hTT:ADD-NEW-FIELD("Sequence", "INTEGER",0,"9999",1000). hTT:ADD-LIKE-INDEX("CustNum","CustNum","Customer"). hTT:ADD-NEW-INDEX("SeqIndex", YES). hTT:ADD-INDEX-FIELD("SeqIndex", "Sequence"). hTT:TEMP-TABLE-PREPARE("CustSequence"). CREATE BUFFER hTTB FOR TABLE hTT:DEFAULT-BUFFER-HANDLE     BUFFER-NAME "CustSeq2". ASSIGN hCustNum = hTT:DEFAULT-BUFFER-HANDLE:BUFFER-FIELD("CustNum")        hCustNum:LABEL = "Test"        hCustNum:FORMAT = "999999". MESSAGE "Label: " hCustNum:LABEL          "Format: " hCustNum:FORMAT VIEW-AS ALERT-BOX.

The MESSAGE shown in Figure 21–1 confirms that the field format and label have changed.

Figure 21–1: Changed field attributes message

If you look at the CustNum label and format through the second dynamic buffer, the CustSeq2 buffer, you see that the format and label of the second buffer have the same value as in the default buffer even though the field in the default buffer was only changed after the second buffer was created.

To illustrate an important point about multiple temp-table buffers:

Change the message statement to display the attributes through the second buffer:

ASSIGN hCustNum = hTT:DEFAULT-BUFFER-HANDLE:BUFFER-FIELD("CustNum")        hCustNum:LABEL = "Test"        hCustNum:FORMAT = "999999". MESSAGE "Label: " hTTB:BUFFER-FIELD("CustNum"):LABEL SKIP        "Format: " hTTB:BUFFER-FIELD("CustNum"):FORMAT VIEW-AS ALERT-BOX.

The label and format are changed, even as seen through the second buffer:



Add a line of code that sets the label of the CustNum field in the second buffer:

hTTB:BUFFER-FIELD("CustNum"):LABEL = "Okay". ASSIGN hCustNum = hTT:DEFAULT-BUFFER-HANDLE:BUFFER-FIELD("CustNum")        hCustNum:LABEL = "Test"        hCustNum:FORMAT = "999999". MESSAGE "Label: " hTTB:BUFFER-FIELD("CustNum"):LABEL SKIP        "Format: " hTTB:BUFFER-FIELD("CustNum"):FORMAT VIEW-AS ALERT-BOX.

None of the changes made to the default buffer are seen in that buffer:



So the rule is that any changes you make to fields through the default buffer are also seen in any additional dynamic buffers for the temp-table, if no other changes have been made through the other buffer. Otherwise, the alternate buffer’s field attributes are initialized to the state of the fields at the time the alternate buffer is created. In that case, any changes made to fields through the alternate buffer are not seen in the default buffer, and any other changes made to the default buffer after that remain separate from the alternate buffer.

Cleaning up a dynamic temp-table

When you’re done using a dynamic temp-table, you should delete it just as you should any other object. Use the DELETE OBJECT tt-handle statement to do this. The default buffer is deleted along with the temp-table. You cannot use the DELETE OBJECT statement on the temp-table’s DEFAULT-BUFFER-HANDLE, but you must use it on any additional dynamic buffers created for the temp-table.

Extending the example to create and display records

This chapter concludes with a comprehensive example of using dynamic queries, buffers, temp-tables, and browses. The final example is saved as h-testDynTT.p.

To extend the current example to get some data into the temp-table and display it:

Add a HANDLE variable for a dynamic query and some variables to display data in:

/* Procedure h-testDynTT.p -- test dynamic temp-table    methods. */ DEFINE VARIABLE hTT    AS HANDLE     NO-UNDO. DEFINE VARIABLE hTTBuf AS HANDLE     NO-UNDO. DEFINE VARIABLE hQuery AS HANDLE     NO-UNDO. DEFINE VARIABLE iSeq  AS INTEGER    NO-UNDO INIT 1000. DEFINE VARIABLE cName  LIKE Customer.NAME       NO-UNDO. DEFINE VARIABLE iCNum  LIKE Customer.CustNum    NO-UNDO. DEFINE VARIABLE cRep   LIKE SalesRep.RepName    NO-UNDO.

After the TEMP-TABLE-PREPARE method, capture the DEFAULT-BUFFER-HANDLE in a variable:

hTT:TEMP-TABLE-PREPARE("CustSequence"). hTTBuf = hTT:DEFAULT-BUFFER-HANDLE.

Define a static FOR EACH block to populate the temp-table with Customer and SalesRep values for Customers in New Hampshire, as well as a unique sequence value:

/* Populate the temp-table with values from the database. */ FOR EACH Customer WHERE State = "NH", SalesRep OF Customer:     hTTBuf:BUFFER-CREATE().     hTTBuf:BUFFER-COPY(BUFFER Customer:HANDLE).     hTTBuf:BUFFER-COPY(BUFFER SalesRep:HANDLE).     hTTBuf:BUFFER-FIELD("Sequence"):BUFFER-VALUE = iSeq.     iSeq = iSeq + 1. END.

Create a dynamic query for the temp-table buffer, prepare it to iterate through all the records in the temp-table, and open it:

/* Now create a query for the temp-table buffer and display values. */ CREATE QUERY hQuery. hQuery:SET-BUFFERS(hTTBuf). hQuery:QUERY-PREPARE("FOR EACH CustSequence"). hQuery:QUERY-OPEN(). hQuery:GET-FIRST().

Walk through the query’s result list and copy some fields from the temp-table into the variables you defined. Display the values in a frame:

REPEAT WHILE NOT hQuery:QUERY-OFF-END:     ASSIGN iSeq = hTTBuf:BUFFER-FIELD("Sequence"):BUFFER-VALUE            cName = hTTBuf:BUFFER-FIELD("Name"):BUFFER-VALUE            iCNum = hTTBuf:BUFFER-FIELD("CustNum"):BUFFER-VALUE            cRep = hTTBuf:BUFFER-FIELD("RepName"):BUFFER-VALUE.     DISPLAY iSeq FORMAT "9999" LABEL "Sequence"             iCNum             cName FORMAT "X(20)"             cRep FORMAT "X(20)" WITH FRAME CustSeqFrame 12 DOWN.     hQuery:GET-NEXT(). END.

In a more general-purpose procedure, you could create dynamic fill-ins for each of the fields you want to display and capture their format, label, and other attributes from the buffer fields of the temp-table.
Run the procedure:



The primary index for the temp-table is the Sequence index, so the records come back in that order.

Temp-table parameters

There are several different ways you can pass a temp-table as a parameter between two procedures. It is important that you understand the differences between these, because what Progress does in the background to pass a temp-table from one place to another is complex and involves a considerable amount of overhead that is not visible to your application code directly. After all, a temp-table isn’t just a single data value. It’s a whole set of rows of data values, packaged up as if it were a database table. Not only that, but Progress can pass the entire description of the table along with the data, and it is critical to understand when you need to include the description and what the benefits are.

Using the TABLE form

When you pass a static temp-table between two procedures, you can use the INPUT TABLE, INPUT-OUTPUT TABLE, and OUTPUT TABLE forms you learned about earlier. To review them, in the RUN statement in the calling program, you define a temp-table parameter in this way:

[ INPUT ] TABLE temp-table-name | { INPUT-OUTPUT | OUTPUT } TABLE temp-table-name [ APPEND ]

In the procedure that is called, you define the table as a parameter in this way for an INPUT or INPUT-OUTPUT parameter:

DEFINE [ INPUT ] | INPUT-OUTPUT    PARAMETER TABLE FOR temp-table-name [ APPEND ].

For an OUTPUT temp-table parameter returned from a called procedure, you use this syntax:

DEFINE OUTPUT PARAMETER TABLE FOR temp-table-name.

Remember that you must include a compatible static definition of the temp-table on both sides of the call. This means that at least the number and data types of fields must match in the two table definitions.

What happens when you pass a temp-table in this way? Progress packages up a description of the table and its fields and indexes, along with all the data in all the rows of the temp-table, and marshals it in a single stream from one procedure to the other. This means that the temp-table is copied into the procedure on the OUTPUT side of the call. To understand the implications of this, consider the two basic ways in which one procedure can call another.

Passing the TABLE within a session

Within a single run-time session, if one procedure calls another and passes a temp-table as a parameter, you wind up with two complete copies of the temp-table and all its data within that session. This is unnecessary in many cases and can be expensive. It takes a lot of memory to copy a large table, and (relatively speaking) a lot of time to copy it. In many cases, your application doesn’t need two copies of the table. For this reason, you should generally avoid passing the table itself as a parameter when you can, and simply pass its HANDLE. Remember that for any kind of object, if you make the handle of the object available to another procedure within the session, the other procedure can access that object, its data, and its attributes regardless of where the object was defined or created. This holds true for a temp-table as well. Using the temp-table handle your procedure can get at its buffer, along with all the data in the table.

When should you consider passing the TABLE within a session? If both procedures use static temp-table definitions and static Progress 4GL language statements to access the table, then you need to pass the table itself instead of its handle. If you pass the handle, then the procedure that receives the temp-table can only manipulate it through its handle, using dynamic buffer and buffer field methods and attributes. If the two procedures have compatible but different definitions of the same table with, for example, different field names for the fields in the same relative positions within the temp-table buffer, you can successfully pass this table from one procedure to the other. In the course of copying it, Progress moves the data into the fields as they are defined in the procedure that receives the table.

If you need to pass the table itself because your procedures need to manage it with static 4GL statements, then do what you can to minimize the amount of data you pass. If the procedure is only going to look at a subset of the records in the temp-table (for example, those that have been changed since they were created or since the receiving procedure last saw them), then it might be more efficient to create a second smaller temp-table containing only those records that really need to be passed. If you simply keep in mind that there is an overhead to passing the table, this should influence your design and programming to minimize the overhead.

Passing the TABLE between sessions

If you need to pass a temp-table from one session to a completely separate session, and at least you have a static temp-table definition on the sending side, then you should use the TABLE form. Object handles aren’t meaningful between sessions because they define memory locations within a single session. And when you pass a handle you are passing only a pointer to that memory location, not the contents. For this reason, if you need to pass a static temp-table from an AppServer session (that, for example, reads data out of the database and loads it into the table) to a client session that needs to use the data without a database connection, then use the TABLE form. You have no choice but to copy all the data from one session to the other. Again, the two temp-table definitions do not need to be identical. They need to have the same signature, the same number of fields, and the same data types in the same order.

Using the HANDLE form

Within a single session, you can simply pass a handle to the temp-table, and you should do this wherever possible. This is far faster than passing and copying the table. In deciding whether to use the TABLE or HANDLE parameter form, you need to decide whether it is important for the receiving procedure to operate on the temp-table as a static object. As with other object types, you can access and manipulate a temp-table using just its handle, but you have to do it with dynamic, handle-based statements. You can write a static FIND or FOR EACH statement against a temp-table handle. Think about how best to balance efficiency against ease of programming in each case where you need to pass temp-table parameters.

Using the TABLE-HANDLE form

The third parameter form is unique to temp-tables, and this is the TABLE-HANDLE. You can use a TABLE-HANDLE to either pass or receive a dynamic temp-table, just as you would use the TABLE parameter form to pass a static temp-table.

To run a procedure and pass a temp-table to or from the procedure using a table handle, use this syntax:

RUN procedure ( { [INPUT] | OUTPUT | INPUT-OUTPUT }                  TABLE-HANDLE tt-handle ).

In the called procedure, you define the parameter like this:

DEFINE { [INPUT] | OUTPUT | INPUT-OUTPUT }           PARAMETER TABLE-HANDLE tt-handle.

The tt-handle handle itself is exactly the same value you would pass as an ordinary HANDLE. However, the TABLE-HANDLE keyword tells Progress to pass not just the handle value but the entire definition and contents of the table as well, in exactly the same form as the TABLE parameter form uses.

You use the TABLE-HANDLE form to pass a dynamic temp-table and its description to another procedure, or to receive a dynamic temp-table, presumably from a procedure in another session on the other side of an AppServer connection, where you cannot simply pass the HANDLE.

The flexibility the TABLE-HANDLE form provides you is extremely valuable. For example, you might have procedures running on the server that represent business logic defined against static tables. On that side of the application, you can build static temp-tables that include database fields, calculated fields, and other elements. You can then pass the temp-table to the client using the TABLE parameter form, since you have a static temp-table definition locally.

On the client side of the application, you might have general purpose procedures to retrieve temp-tables from the server, perhaps to display data, allow updates, and do other client-side processing that might apply to many different tables received from the server. A general-purpose procedure that has no specific single temp-table definition can receive the table as a TABLE-HANDLE. It receives the entire table definition and data from the caller and can access it through the handle.

All combinations of TABLE and TABLE-HANDLE are valid. You can pass a static table using the TABLE form and receive it as a dynamic TABLE-HANDLE. You can pass a TABLE and receive it as a static TABLE of the same or compatible definition. You can pass a dynamic TABLE-HANDLE and receive it as a static TABLE, and you can pass a dynamic TABLE-HANDLE and receive it as a dynamic TABLE-HANDLE on the other side.

Use the TABLE-HANDLE form when the temp-table is not defined locally and you need to access it in a general way through its handle and buffer handle. Use the TABLE form when the temp-table is defined locally with a static DEFINE statement.

To review, Table 21–1 summarizes the possible combinations of temp-table parameter definitions in the procedure making the call and the procedure being called, along with their effects on the temp-table.

Table 21–1: Temp-table parameter definitions

Caller RUN statement form	Callee parameter form	Parameter mode	Result
TABLE ttXYZ	TABLE ttXYZ	INPUT	Static temp-table ttXYZ in the caller is copied to the static definition of ttXYZ in the callee. The table definition is passed along with the data for validation only.
TABLE ttXYZ	TABLE-HANDLE hTT	INPUT	Definition and data of static temp-table ttXYZ in the caller are copied to the callee, which constructs a dynamic definition using handle hTT and loads the dynamic table with the data.
TABLE-HANDLE hTT	TABLE ttXYZ	INPUT	Definition and data of dynamic temp-table whose handle is hTT in the caller are copied to the callee, which receives the data into its static definition ttXYZ.
TABLE-HANDLE hTT	TABLE-HANDLE hTT	INPUT	Definition and data of dynamic temp-table whose handle is hTT in the caller are copied to the callee, which receives the definition and uses it to construct a dynamic temp-table using handle hTT, then loads the data into this dynamic table.
TABLE ttXYZ TABLE ttXYZ TABLE-HANDLE hTT TABLE-HANDLE hTT	TABLE ttXYZ TABLE-HANDLE hTT TABLE ttXYZ TABLE-HANDLE hTT	OUTPUT	All the same combinations are supported. Nothing is passed in to the callee. The definition of the table and its data are passed back in the same form from callee to caller when callee returns. For the OUTPUT TABLE form, the definition is used to validate compatible temp-table definitions; for the OUTPUT TABLE-HANDLE form, it is used to construct the temp-table in the caller.
TABLE ttXYZ TABLE ttXYZ TABLE-HANDLE hTT TABLE-HANDLE hTT	TABLE ttXYZ TABLE-HANDLE hTT TABLE ttXYZ TABLE-HANDLE hTT	INPUT-OUTPUT	Once again, the same combinations are supported. The table definition and data are passed in from caller to callee. Callee can make changes to the data in the table, which is returned by being copied back to the caller.

Creating and using dynamic browses

Especially when you start using dynamic temp-tables and defining their fields at run time, you will want to be able to define a dynamic browse objects to display their contents. You can also use a dynamic browse if you have a dynamic query on a database table that isn’t known until run time, or anytime the column list of the browse needs to be definable at run time.

Much like a dynamic temp-table, you define a dynamic browse in stages, first defining the browse object itself with the CREATE BROWSE statement, and then adding columns to it in separate statements.

At run time, you can also add columns to a static browse control and define the query for a static browse.

You create the browse with this statement:

CREATE BROWSE browse-handle [ IN WIDGET-POOL pool-name ]      [ ASSIGN attribute = value [ , ... ].

As with other dynamic objects, if you don’t assign a value to the attributes in the CREATE statement you can set them later on using the browse handle.

Attributes you can set for a dynamic browse include:

X and Y, or COLUMN and ROW — The position of the browse.
WIDTH — The width of the browse in characters.
DOWN — The number of rows to display in the browse viewport.
TITLE — A title for the browse object.
FRAME — The handle of the frame to parent the browse to.
QUERY — The handle of the associated query for the browse.
SENSITIVE — Set to true to enable the browse. Note that this attribute enables only the browse object itself, allowing the user to scroll and otherwise manipulate the browse. You enable individual browse columns separately.
VISIBLE or HIDDEN — Set VISIBLE to true to force the browse to be viewed. Set HIDDEN to false to make sure the browse is viewed when its container is viewed.
READ-ONLY — Set to true to make all columns read-only. Set to false to allow individual columns to be enabled.
SEPARATORS — Set to true to get lines between the browse columns.
ROW-MARKERS — Set to true to have row markers at the beginning of each row, or false to remove them.

You must set a browse’s query handle before you can add columns to the browse, and you must parent it to a frame before you can visualize it.

A dynamic browse always has the NO-ASSIGN quality, meaning that Progress is not able to automatically save changes you make to enabled columns in the browse. This is because the browse is completely defined at run time, and Progress can’t anticipate and supply default behavior for column assignment at compile time. However, in a distributed application you would never make direct changes to a database table from a browse anyway, so the utility of the static browse that can do auto-assignment of changes is very limited.

Adding columns to the browse

After you create the browse you must add columns to it individually. There are three methods you can use to do this: ADD-COLUMNS-FROM, ADD-LIKE-COLUMNS, and ADD-CALC-COLUMN. These are much like the dynamic temp-table methods: ADD-FIELDS-FROM, ADD-LIKE-FIELD, and ADD-NEW-FIELD.

ADD-COLUMNS-FROM method

The ADD-COLUMNS-FROM method takes a table name (which can be a database table or a temp-table) and an optional list of fields to omit from the browse:

browse-handle:ADD-COLUMNS-FROM ( table-handle | table-name-exp     [, except-list] ).

This method returns true if it succeeds and false otherwise.

As with the similar temp-table methods, you can specify the table using its handle or using a character expression that evaluates to its name.

The method creates a column in the browse for every field in the table you pass, except those in the except-list.

ADD-LIKE-COLUMN method

The ADD-LIKE-COLUMN method adds a single column to the browse whose attributes are taken from a field either in a database table that is connected when the procedure is compiled or in a temp-table that is in scope when the method is encountered:

buffer-handle:ADD-LIKE-COLUMN (fieldname-exp | buffer-field-handle     [, pos] ).

You can also pass an optional second argument that specifies the position of the column within the browse. If you don’t specify the position, the column is added to the end of the column list for the browse.

This method returns the handle of the browse column created. You might want to save off the browse column handle, or you can access it later through the browse handle.

ADD-CALC-COLUMN method

The ADD-CALC-COLUMN method adds a single column to the browse that is not derived from a field in a database table or temp-table. You can use this method to add a field you use to display a calculated value:

buffer-handle:ADD-CALC-COLUMN( data type-exp , format-exp ,     initial-value-exp , label-exp [, pos] ).

This method also returns the handle of the browse column created. You must specify a data type, format, initial value, and label for the column. You can also optionally specify an ordinal position within the list of browse columns. If you don’t specify the position, each new column is added to the end of the list.

Extending the test procedure with a dynamic browse

In this section, you’ll apply a few of the things you just learned to add a dynamic browse to the procedure with the dynamic temp-table.

To extend the sample procedure with a dynamic browse:

Open the h-testDynTT.p procedure and save a new version of it as h-testDynBrowse.p.
Add a variable definition at the top for a browse handle and also a frame definition for a frame to hold the browse:

DEFINE VARIABLE hBrowse AS HANDLE      NO-UNDO. DEFINE FRAME BrowseFrame WITH SIZE 80 BY 10.

Remove the REPEAT block from the procedure that walks through the temp-table records using the dynamic query and displays them. Instead, you’ll display them in a dynamic browse.
Add a CREATE BROWSE statement after the QUERY-PREPARE. It will be the only object in the frame, so it can be at ROW 1 and COLUMN 1. Its WIDTH needs to be slightly less than the frame to allow for the frame border. Parent it to the frame and assign the dynamic query to it. Make it SENSITIVE and VISIBLE, and give it SEPARATORS between columns but no ROW-MARKERS at the beginning of the row:

CREATE BROWSE hBrowse     ASSIGN ROW = 1 COL = 1            WIDTH = 79 DOWN = 10            FRAME = FRAME BrowseFrame:HANDLE            QUERY = hQuery            SENSITIVE = YES            SEPARATORS = YES            ROW-MARKERS = NO            VISIBLE = YES.

To get started, try adding all the columns from the temp-table buffer except for a handful that you exclude, using this statement:

hBrowse:ADD-COLUMNS-FROM(hTTBuf, "SalesRep,Country,address,Address2,State").

Note that you cannot pass the dynamic temp-table name as the table identifier for this method. Even though you do give the temp-table a name when you prepare it, that name is not available to the method. You must pass the handle to a buffer for the temp-table, either its DEFAULT-BUFFER-HANDLE or another buffer you’ve defined for it.
Enable everything in the frame and wait for the user to close the window so that the user can manipulate the browse when the window comes up:

ENABLE ALL WITH FRAME BrowseFrame. WAIT-FOR CLOSE OF CURRENT-WINDOW.

Run the procedure. You should see all the Customers in New Hampshire with all the fields except the ones you excluded:

To try adding specific columns to the browse:

Remove the ADD-COLUMNS-FROM method and instead add these four statements to add these four columns to the empty browse:

hBrowse:ADD-LIKE-COLUMN(hTTBuf:BUFFER-FIELD("Sequence")). hBrowse:ADD-LIKE-COLUMN(hTTBuf:BUFFER-FIELD("CustNum")). hBrowse:ADD-LIKE-COLUMN(hTTBuf:BUFFER-FIELD("Name")). hBrowse:ADD-LIKE-COLUMN(hTTBuf:BUFFER-FIELD("RepName")).

Run the procedure again. You should see just the four columns you specified:

Browse columns and validation expressions

This section discusses the relationship between validation expressions and browse colmns.

To see an example of this relationship:

Add the SalesRep field to the column list, with this statement:

hColumn = hBrowse:ADD-LIKE-COLUMN(hTTBuf:BUFFER-FIELD("SalesRep")).

Run the procedure. You should see this error message:



The reason for this error is that in the Sports2000 database there is a validation expression defined on the Customer.SalesRep field, which the temp-table column has inherited. This validation uses a CAN-FIND expression to check to make sure that the value in the SalesRep field matches the SalesRep field in a record in the SalesRep table. The field SalesRep.SalesRep (it’s somewhat confusing that the table name and field name are the same) is the primary key for this value. Customer.SalesRep is the foreign key. Progress cannot process a CAN-FIND for a dynamic browse column, so if you want to include such a column, you need to exclude the column validation.
To do this, add a statement to blank out the validate expression before you add the column to the browse:

hTTBuf:BUFFER-FIELD("SalesRep"):VALIDATE-EXPRESSION = "". hColumn = hBrowse:ADD-LIKE-COLUMN(hTTBuf:BUFFER-FIELD("SalesRep")).

To see another variation, add a calculated column to the browse. This column will hold the difference between the Customer CreditLimit and Balance fields. It’s a DECIMAL field with no extent and a label of Available. Place the new column in position 4 within the list of browse columns:

hColumn = hBrowse:ADD-CALC-COLUMN("DECIMAL","ZZ,ZZZ,ZZ9.99",0,"Available",4).

To populate the field for each row in the query, you need to define a ROW-DISPLAY trigger for the browse. You can add the trigger as a separate ON ROW-DISPLAY OF hBrowse statement, or in a TRIGGERS block at the end of the CREATE BROWSE statement:

CREATE BROWSE hBrowse       .       .       .   VISIBLE = YES   TRIGGERS:     ON ROW-DISPLAY        hColumn:SCREEN-VALUE =           STRING(hTTBuf:BUFFER-FIELD("CreditLimit"):BUFFER-VALUE         - hTTBuf:BUFFER-FIELD("Balance"):BUFFER-VALUE).   END.

When the trigger executes, the handle of the column must be in the hColumn variable. You need to define the ROW-DISPLAY trigger before the calculated field is added to the browse, and before the query is opened. You can modify the SCREEN-VALUE and certain other attributes of any field within the trigger.
Move the QUERY-OPEN method after the statements that create the browse and its columns so that rows are initialized properly with the calculated value:

hColumn = hBrowse:ADD-CALC-COLUMN("DECIMAL","ZZ,ZZZ,ZZ9.99",0,"Available",4). hQuery:QUERY-OPEN().

If you open the query before you create the browse and its trigger, the first rows of the query appear in the browse viewport, but the calculated field isn’t assigned to them. Only when you scroll down through the browse do the values for the calculated field appear. This is why it’s important to define the trigger and add the column before you open the query.
Run the procedure:

Adding columns to a static browse

You can use the ADD-COLUMNS-FROM, ADD-LIKE-COLUMN, and ADD-CALC-COLUMN methods to add columns to a static browse as well. Such a browse automatically becomes a NO-ASSIGN browse, just as a dynamic browse is.

You can also modify the query of a browse at run time, including the query of a static browse. If the query navigates a table with the same field list as the original query, you can change the query without any visible effect, other than to substitute one set of records for another. There is no need to rebuild the browse. However, if the field list in the table managed by the query is different from the previous query, Progress clears the browse column list and you have to rebuild it using the dynamic methods.

Query methods for use with browses

This section describes the two query methods you can use with browses.

CREATE-RESULT-LIST-ENTRY method

This method creates an entry in the result list for the current row. You use the CREATE-RESULT-LIST-ENTRY method in conjunction with new browse rows or new query rows to synchronize the data with the query.

DELETE-RESULT-LIST-ENTRY method

This method deletes the current row of a query's result list.

The dynamic CALL object

Sometimes you need to be able to run a number of different procedures from the same place in your application. These might be, for example, procedures that handle different values for a parameter or a field in specialized ways.

You can make the name of a procedure to run a variable by using the VALUE function in a RUN statement:

RUN VALUE(cProcName) (parameters).

The one significant limitation of this dynamic name is that you must fully specify the parameter list in the source procedure. Therefore, you must use the same dynamic name in all the possible procedures you might run. In some limited cases, you might need to build a general purpose procedure that is prepared to run any procedure with any parameter list. This would certainly not be for ordinary situations, but only for a specialized tool that acts as a gateway for a number of different procedure calls that all must be handled in a consistent way.

Progress provides a dynamic CALL object to handle this need. Using the CALL object, you can specify the procedure name, the number of parameters, their types and values, and other information as run-time attributes of this dynamic object, and then invoke it to do the RUN. You can also use a dynamic CALL object to access object attributes dynamically.

Because your use of this object is likely to be limited to very special situations, it is not described here. The complete syntax for dynamic CALL, along with some simple examples, is in OpenEdge Development: Progress 4GL Reference and online Help.

Building a comprehensive example

This section examines a more comprehensive example of a useful set of procedures that include dynamic objects to display data from any database table or temp-table. It serves as a summary and review of all the material in this and the previous chapters on dynamic objects. The code is somewhat too substantial to describe in full, so the material refers to selected parts of the procedures. You can then examine the final procedures in the examples directory.

The main procedure is h-dbbrowser.w, which puts up a window where you can select a database (if there’s more than one connected), a table, and one or more fields from that table. The procedure displays those fields in a dynamic browse. Figure 21–2 shows an example using the Customer table from the Sports2000 database.

Figure 21–2: Result of running h-dbbrowser.w

Look at some of the code that makes this happen.

The main block of h-dbbrowser.w runs an initializeObject procedure and then a custom enableUI:

DO ON ERROR  UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK    ON END-KEY UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK:   RUN initializeObject.   RUN enableUI.   IF NOT THIS-PROCEDURE:PERSISTENT THEN    WAIT-FOR CLOSE OF THIS-PROCEDURE. END.

Creating a dynamic browse and customizing its display

The initializeObject procedure creates a dynamic browse whose columns are filled in when you select fields for a table. It also defines a ROW-DISPLAY trigger that fires as each row in the browse is displayed:

/* Create the dynamic browser here */   CREATE BROWSE hBrowse     ASSIGN       ROW = 8       COLUMN = 2       WIDTH = 86       DOWN = 12       VISIBLE = NO       ROW-MARKERS = NO       SEPARATORS = YES       COLUMN-RESIZABLE = YES       COLUMN-MOVABLE = YES       NO-VALIDATE = YES       FRAME = FRAME {&FRAME-NAME}:HANDLE /* The trigger clause is here to do the alternate line color in the broswe */     TRIGGERS:       ON ROW-DISPLAY PERSISTENT RUN rowDisplay IN THIS-PROCEDURE.     END TRIGGERS   .

The ROW-DISPLAY event lets you intercept the display of each row in the browse to change colors, formats, or calculated values. If you look at the trigger procedure that handles that ROW-DISPLAY event, you see that it is responsible for alternating the browse rows between white and gray. The lRow logical variable is defined in the Definitions section for the main procedure block so that its value is maintained between calls to rowDisplay:

/* Static toggle switch for the line color */ DEFINE VARIABLE lRow AS LOGICAL NO-UNDO.

The rowDisplay procedure sets foreground and background colors depending on the setting of lRow:

/* If the toggle is yes */    IF lRow THEN      ASSIGN       iBGColor = 8 /* Set the background color grey */       iFGColor = 0 /* and foreground color black *     .   ELSE      ASSIGN       iBGColor = 15 /* else background color white */       iFGColor = 0 /* and foreground color black */     .

It then assigns these colors for each cell in the row. There’s no attribute that lets you do this for the entire row at once:

/* Iterate through the list of browse columns */   DO iCount = 1 TO NUM-ENTRIES(cBrwsCols):     /* Convert the string value to a handle */     hBrwsCol = WIDGET-HANDLE(ENTRY(iCount,cBrwsCols)).     IF VALID-HANDLE(hBrwsCol) THEN     DO:       hBrwsCol:BGCOLOR = iBGColor. /* Set the cell's background color */       hBrwsCol:FGCOLOR = iFGColor. /* and it's foreground color */     END.   END.

Finally, it reverses the value of lRow in preparation for the next call:

lRow = NOT lRow. /* Set the toggle opposite */

Reading the database metaschema data

Next, take a look at the getListItems procedure, which populates the combo boxes and the selection list in the window.

This procedure is run, for example, to populate the list of fields for a selected database table. It receives the filename or filenames and a query definition as input parameters.

This is an opportunity to share a bit of information about how Progress defines tables and fields in its database. All these definitions are stored in a set of database tables called the metaschema. These tables hold all the information about the database tables that make up your application. All these special table names begin with an underscore (_), which is not valid for your application tables and helps distinguish them from ordinary database tables:

Information about database tables is stored in the table named _File.
Information about fields in those tables is stored in the table named _Field.
Information about indexes in stored in _Index, and the cross-references that associate fields with indexes is in _Index-Field.

For example, when you select the Customer table in the window, getListItems is run with this table list and query selection:

The _File and _Field tables are joined using a field in the _Field table called _File-Recid and the RECID of the _File record. The RECID is an integer Progress record identifier that was used in earlier times as the standard way to uniquely identify a database record. It has since been superceded by the RowID that you’ve been introduced to in this book, which is more portable to other databases and has other advantages. In any case, the RECID still exists and is used as the mechanism for joining tables in the metaschema. You should avoid using it in application code because the RowID is now the preferred row identifier. The _sys-field field tells you if this is flagged as a system field that isn’t intended to be seen as part of the application information.

There’s just one more piece of information to share about the metaschema tables. Each _File record has a _File-Num field. For tables that are part of your application schema, the _File-Num is greater than 0 and less than 32K. For metaschema tables, the _File-Num is less than 0. For virtual system tables, which you can learn more about in the database administration documentation, the _File-Num is greater than 32K. This also helps identify which tables are specific to your application and which are the support tables Progress uses to manage your application tables.

Knowing about the metaschema tables can help you understand the relational database structure better, and can be useful in cases where you want to browse or display all the tables in your database. However, using this information is only for advanced users and for special situations like this example.

Populating a selection list dynamically

GetListItems uses the table list and query to ask the getQuery procedure to generate a dynamic query for it. It then gets the field values for each record in the query (all the fields in the Customer table in this case), and uses them to populate the selection list:

/* Add this row to the list box */         ihListBox:ADD-LAST(cLabel,cValue).

Finally, it makes the first item in the list the selected item:

/* Set the value of the list-box to be the first entry in the list */   IF ihListBox:NUM-ITEMS > 0 THEN      ihListBox:SCREEN-VALUE = ihListBox:ENTRY(1).

Creating a dynamic query

Now take a look at getQuery. It first creates a dynamic query, to pass back as an OUTPUT parameter (hence the naming convention ohQuery):

/* Create the query and assign the handle to the output parameter variable */   CREATE QUERY ohQuery.

Then it walks through the list of buffer names passed in, creates a dynamic buffer for each one, and adds each buffer to the query:

/* Create a buffer for this table */     CREATE BUFFER hTable FOR TABLE cTableName.     /* Add this buffer to the query */     ohQuery:ADD-BUFFER(hTable).

Finally, it prepares the dynamic query using the FOR EACH statement passed in:

/* Prepare the query */   ohQuery:QUERY-PREPARE(icForEach).

This is a general-purpose mechanism for creating not only any dynamic query, but also all the buffers it uses, without defining anything in advance. It’s prepared to work against any list of tables for any database.

It’s also a good idea to look at the cleanupQuery function, which reminds you to always delete dynamic objects when you’re done with them. It deletes the query and then all the dynamic buffers the query used:

/* Now delete the query object */      deleteObject(hQuery).   /* Now iterate through the string */      DO iCount = 1 TO NUM-ENTRIES(cBuffers):   /* convert each entry to a handle */      hBuffer = WIDGET-HANDLE(ENTRY(iCount,cBuffers)).      /* and pass the handle to deleteObject */         deleteObject(hBuffer).      END.

Creating a dialog box procedure

You can also use the h-dbbrowser window to define a temp-table with any fields you want, from one or multiple database tables, and then populate that temp-table with a query.

To create a dialog box procedure:

Choose the Get Temp-Table button. A dialog box appears where you enter the following information:
A list of database tables to base your temp-table on.
A FOR EACH statement to use to populate the temp-table.
A list of fields to add to the temp-table. You should qualify these fields with their database field names, but the temp-table fields will just use the base field names. The temp-table also gets a row counter field called iCount.
A name for the temp-table.
A maximum number of rows to retrieve into the temp-table.
Here’s an example that joins Order to Customer and selects the OrderNum, OrderDate, CustNum, and Customer.Name fields for Orders less than 100:



Choose OK in this dialog box and select all its fields in the main window. You see the contents of the temp-table in the dynamic browse:



Here are a few interesting parts of this code:
The getTempTable procedure runs a separate dialog box procedure called h-dlgtt.w, built in the AppBuilder from the Dialog template (that is, by selecting New Dialog in the AppBuilder). This is the Temp-Table Query dialog box used to define the OrdCust temp-table above. It is a fairly standard dialog box procedure, which prompts for some field values and returns them as OUTPUT parameters ON CHOOSE of the OK button. You can use a dialog box such as this anytime it is necessary to block other input in your application until the user has provided information the dialog box is prompting for.

Making a call to an AppServer

The code next checks the toggle box in the window that signals whether to retrieve the temp-table data from a separate AppServer session. A discussion of the details of how to use the OpenEdge AppServer is beyond the scope of this book, but this one example gives you a flavor of how straightforward it is to separate your client session from an AppServer session on another machine where the database is connected.

If the AppServer toggle box is set, the procedure creates an AppServer object, which has a handle like any other Progress object, and uses it to connect to the AppServer:

CREATE SERVER hAppServer. lAns = hAppServer:CONNECT("-H localhost -S 5162 -AppService asbroker1").

The local hostname, server port number, and AppService name that are the parameters to the CONNECT method are the defaults for connecting to a local AppServer session for testing purposes.

If the AppServer flag was not set, then the procedure sets the hAppServer variable to be the handle of the local session:

/* Otherwise set the AppServer handle to be the SESSION handle */   ELSE     hAppServer = SESSION:HANDLE.

This signals OpenEdge that there is no AppServer running, but that all requests to this AppServer handle should just be run within the local session.

Then any RUN statement, such as the following, can run a procedure ON SERVER hAppServer and have it run identically, whether there is really a separate AppServer session or not:

RUN h-gettemptable.p ON SERVER hAppServer  (cTables, cForEach, cFields, cTableName, iRows, OUTPUT TABLE-HANDLE hTable).

As you can see, running a procedure on an AppServer is just like running it within your session, except for the ON SERVER syntax. Even with this syntax, you can run the same procedure locally with no change to your code whatsoever, just by using SESSION:HANDLE as the alternative value for the server handle.

Creating a dynamic temp-table

Next, look at a few parts of procedure h-gettemptable.p.

The main block creates a dynamic query for the database tables requested (such as Order and Customer). The createBuffers procedure creates a dynamic buffer for each of those tables.

The getFieldHandles procedure assembles a list of the handles to the fields requested (such as Order.OrderNum).

The createTempTable procedure creates a dynamic temp-table called hTT, and adds iCount to it as its first field:

/* Create the temp table */   CREATE TEMP-TABLE hTT.  /* Add a count field - we need this for the sequence so that we get the records     back in the order that they were created */   hTT:ADD-NEW-FIELD("iCount","INTEGER").

It then walks through the list of database field handles and adds a field to the temp-table like each field:

/* Iterate through the list of field handles in the cTableFields */   DO iCount = 1 TO NUM-ENTRIES(cTableFields):     /* Convert the current entry to a handle */     hField = WIDGET-HANDLE(ENTRY(iCount,cTableFields)).     /* Add a field to the temp table like this field with the same name as this       field */     hTT:ADD-LIKE-FIELD(hField:NAME,hField).   END.

It then adds an index on iCount, so that the records remain in the order they were created:

/* Add a primary unique index to the temp-table */   hTT:ADD-NEW-INDEX("pudx",True,True).   /* Add the counter field to the index */   hTT:ADD-INDEX-FIELD("pudx","iCount").

Finally, it prepares the temp-table, which freezes its definition and enables you to add records to it:

/* Prepare the temp-table with the name the user chose */    hTT:TEMP-TABLE-PREPARE(icTableName).

Next, the h-gettemptable.p procedure populates the temp-table in the populateTempTable internal procedure. Handle hTTBuf holds the handle to the temp-table’s default buffer:

/* Store the handle to the buffer for the temp-table */    hTTBuff = hTT:DEFAULT-BUFFER-HANDLE.

The procedure prepares the database query with the FOR EACH statement entered in the dialog box, opens it, and retrieves the first record:

/* Prepare a query using the string the user provided */   hQuery:QUERY-PREPARE(icForEach).   /* Open the query */   hQuery:QUERY-OPEN().   /* Get the first result in the result set */   hQuery:GET-FIRST().

For as many rows as there are in the query, or as many rows as you asked for, whichever is less, the procedure creates a temp-table row using the buffer handle:

/* Iterate for as many rows as the user has chosen, or until the query is      off-end */   REPEAT iCount = 1 TO iiRows WHILE NOT hQuery:QUERY-OFF-END:     /* Create a temp-table record */     hTTBuff:BUFFER-CREATE().

Because you selected individual fields from possibly multiple tables in the database to add to the temp-table, it doesn’t do a BUFFER-COPY to move fields from the database records to the newly created temp-table row. Instead, the next block of code uses the BUFFER-FIELD and BUFFER-VALUE attributes on both the database buffer fields and the temp-table buffer fields to move values from one to the other.

The procedure then releases each temp-table row, iterates through the query until the REPEAT condition fails, and then closes the query:

/* Release the temp-table record */     hTTBuff:BUFFER-RELEASE().     /* Get teh next result in the query */     hQuery:GET-NEXT().   END.   /* Close the query */   hQuery:QUERY-CLOSE().

Using the SESSION handle to identify dynamic objects

One final procedure to look at in h-dbbrowser.w is listTempTables. This procedure populates the drop-down list of available temp-tables after you have built one or more of them using the dialog box. It illustrates the usefulness of the SESSION handle as a way to get at various kinds of objects that you’ve created since your session started. In this case, the session keeps track of a list of all dynamic buffers created in the session. The FIRST-BUFFER attribute gives you the first one. The NEXT-SIBLING attribute walks through the list. If a buffer has a TABLE-HANDLE, then it is a buffer for a temp-table, and the procedure adds it to the drop-down list:

hBuffer = SESSION:FIRST-BUFFER.   /* Walk through the list of buffers that belong to Dynamic Tenp Tables*/   DO WHILE VALID-HANDLE(hBuffer):     IF VALID-HANDLE(hBuffer:TABLE-HANDLE) THEN     DO:         /* Convert the handle to a string */         cHandle = STRING(hBuffer:TABLE-HANDLE).         /* If the handle is not in cList, add it */         IF NOT CAN-DO(cList,cHandle) THEN           cList = cList + (IF cList = "" THEN "" ELSE ",")                 + cHandle.     END.     /* Go on to the next Buffer */     hBuffer = hBuffer:NEXT-SIBLING.   END.   RETURN cList.    /* Function return value. */

This comprehensive example has used just about every aspect of programming with both dynamic visual objects and dynamic database objects. Remember that dynamic programming like this is valuable when you need to create a single procedure to use in a variety of situations, just as this window can display fields from any table.

Dynamic programming considerations

Using a dynamic object lets you make everything about the data run-time defined, including the table name, the WHERE clause for the records you select, and so on. It’s important to note here some basic principles of dynamic programming that pertain specifically to transaction management.

The basic rule of dynamic programming is that, because you are defining a procedure’s behavior at run time, it is not possible for Progress to provide as much default behavior as it does for a statically defined procedure or to give you compile errors for constructs that are incorrect. You have much more responsibility to make sure that your program functions properly at run time with the entire range of possible data input that can drive its dynamic behavior. Because of this, there are a few basic rules you need to strictly adhere to when you’re doing dynamic programming. These rules apply to static programming as well, but they are more essential in a dynamic procedure. Partly, this is because a dynamic procedure provides less predictable default behavior and has more flexibility in what it might be expected to do at run time. Also, it is because you cannot use tools such as the LISTING file to confirm the record scope and transaction scope for your procedure, because Progress cannot determine them with certainty until run time.

Here are the rules:

It is essential that you explicitly define every transaction using the TRANSACTION keyword in a block header. Because dynamic programming is largely independent of the normal block structure of Progress, your transaction scope is likely to be very unpredictable (and possibly larger than you expect).
Make sure you do not find a record with an EXCLUSIVE-LOCK or otherwise reference any buffer outside your defined transaction in such a way that you force the transaction scope to be larger than you expected. For example, if you find a record, then start a transaction block with a DO TRANSACTION header, and then update that record, Progress forces the scope of the transaction at run time to be larger than the DO TRANSACTION block. The scope will likely encompass the entire procedure, and your procedure will hold records and locks longer than you want.
Always use the dynamic BUFFER-RELEASE method on each buffer handle to release the record when you are finished with it. Again, this is because the default release handling is block-oriented and dynamic procedures are not tied to blocks as firmly as static procedures are.
Use the dynamic BUFFER-COPY method to copy multiple fields from one buffer to another, rather than assigning values to fields individually. This is also true for a static buffer in that using an ASSIGN statement for more than a few fields in a buffer is more expensive than copying the entire buffer. It’s especially true for dynamic programming, however, because in a static program you can at least ASSIGN multiple fields in a single statement, whereas in dynamic programming you can only assign a single field at a time.
When you run one procedure from another in a dynamic program, be especially careful that you do not reference record buffers or other objects in such a way as to adversely affect your transaction scope. This mistake is very easy to make because you can reference an object that is defined anywhere in your application if you have access to its handle. This kind of access can drastically change transaction scope at run time in ways you cannot easily predict just by casually looking at the block structure of your procedures.
There is little reason to use SHARE-LOCKS in any case, but it is especially unwise to ever use a SHARE-LOCK in a dynamic procedure. The transition from SHARE-LOCK to EXCLUSIVE-LOCK and back is obscure in the best of cases and very much so in a dynamic procedure. Using SHARE-LOCKs can lead to locks being held much longer than you expect.

Once again, these are valuable guidelines for any procedure, but especially so for one that uses dynamic statements.

The next and final chapter provides more guidelines on how to best develop applications with the Progress 4GL.