OpenEdge Development: Progress 4GL Handbook

Using Dynamic Queries and Buffers

In the previous chapter you learned how to create dynamic versions of visual objects to increase the flexibility of your application’s user interface. You also learned how to use handles to access the attributes and methods of those objects as well as their static counterparts. In this chapter you’ll learn how to do the same with data management objects such as queries and buffers. The next chapters continue with a discussion of temp-tables and the browse. The same principles apply to visual objects, so the concepts should already be familiar to you.

Using a dynamic query, you can postpone until run time the tasks of defining what table or tables a query should use, and what the selection criteria should be for the result set the query manages. Using a dynamic buffer, you can likewise allow the application to identify at run time what table a buffer should store data for.

Putting these dynamic objects together with dynamic temp-tables and browses gives you the ability to define almost any aspect of your application at run time, when this is necessary. Although you shouldn’t make all your queries and buffers dynamic any more than you should make all your user interface objects dynamic, having the ability to do so allows you to build general purpose tools for certain parts of your application that might need to deal with a wide variety of user requirements in a consistent way. Being able to do this from a single procedure with dynamic objects is greatly preferable to writing and compiling many different procedures that do essentially the same job for different tables or fields.

It’s important to note that these dynamic features are not meant to replace all the static statements in earlier versions of the Progress 4GL, or to be the standard way to write code for all new applications. There is a significant performance cost to using dynamic data management statements, which can be well worth the cost when the flexibility is needed but an unnecessary expense and complication, otherwise. If your tables, fields, and queries are known and fixed when you write the procedure, then there is no need to use dynamic statements to manage them. Likewise, if you have a static object, such as a query, on a specific table or tables but need to modify just the WHERE clause at run time or access some of its attributes and methods, you can easily do this through a handle attached to the static object. This keeps your code simpler and more efficient.

This chapter and the one that follows include straightforward but fairly comprehensive examples that show the power of dynamic data management objects, allowing you to browse data from any database table or temp-table you care to define. By the end of these two chapters, you’ll have a solid understanding of some of the most powerful features in the Progress 4GL and how best to use them in your applications.

This chapter includes the following sections:

Using dynamic queries and query handles
Using dynamic buffers and buffer handles

Using dynamic queries and query handles

As with other dynamic objects, you can create a dynamic query with a form of the CREATE statement. But also as with other object types, you can attach a handle variable to a static query and use almost all of its methods and attributes to manipulate the query at run time.

The syntax for the CREATE QUERY statement is:

CREATE QUERY query-handle [ IN WIDGET-POOL pool-name ] .

There is no ASSIGN option on the CREATE QUERY statement, primarily because most of the operations you can perform on a dynamic query are methods, not attributes.

You can also attach a handle variable or field to a static query:

query-handle = QUERY query-name:HANDLE.

Query methods and attributes

Once you create a dynamic query or assign a handle to a static one, you can retrieve (and, in some cases, set) information about the query using its methods and attributes. This section provides a summary of those methods and attributes. Unless otherwise noted, they all apply to handles for both static and dynamic queries.

SET-BUFFERS method

This method takes a buffer handle or a comma-separated list of buffer handles and sets the query’s buffer list to those buffers. It returns true if the operation succeeded and false otherwise.

You can pass a buffer reference in one of several ways:

Using the BUFFER buffer-name:HANDLE syntax, which provides a handle for a static buffer on a known table.
Using a handle variable or field that you associate with a static buffer in a separate statement, such as hBuffer = BUFFER buffer-name:HANDLE.
Using the handle for a dynamic buffer, where you name the table only at run time. You’ll learn about how to do this in the "Extending the test window to use a buffer handle" .

For example, this sequence of statements creates a query and sets its buffer list to the Order and Customer buffers:

DEFINE VARIABLE hQuery AS HANDLE      NO-UNDO. CREATE QUERY hQuery. hQuery:SET-BUFFERS(BUFFER Order:HANDLE, BUFFER Customer:HANDLE).

This is the equivalent of defining a static query for those buffers, as you could do with this statement:

DEFINE QUERY OrderCust FOR Order, Customer.

You could then assign a handle to the static query in the same way, for example:

DEFINE VARIABLE hQuery AS HANDLE      NO-UNDO. DEFINE QUERY OrderCust FOR Order, Customer. hQuery = QUERY OrderCust:HANDLE.

This raises the basic question of when to use static and when to use dynamic queries. Typically, unless your procedure is of such general use that you do not know the tables or buffers it will use until run time, you can define a static query and then use its handle to modify it as needed at run time. This is the case if, for example, you need to modify the WHERE clause or sort order of the query in a variety of ways at run time. If you start with a static query, you can use any combination of the static statements you’re familiar with to manipulate it, such as GET FIRST, OPEN QUERY, and so forth, or their dynamic equivalents, which are introduced in the following section. If you start with a dynamic query, you can use only dynamic methods to manipulate it.

The one aspect of a static query you cannot modify at run time is its buffer list, so the SET-BUFFERS method applies only to dynamic queries.

ADD-BUFFER method

This method takes a single buffer handle as an argument and adds it to the list of buffers for the query. In this way, you can have a programming loop that adds a sequence of buffers to a query one at a time, when that is appropriate and when your query needs to join two or more buffers. It returns true if the operation succeeded and false otherwise.

For example, you can use the ADD-BUFFER method to add a third buffer to the query that already has the Order and Customer buffers assigned to it:

DEFINE VARIABLE hQuery AS HANDLE      NO-UNDO. CREATE QUERY hQuery. hQuery:SET-BUFFERS(BUFFER Order:HANDLE, BUFFER Customer:HANDLE). hQuery:ADD-BUFFER(BUFFER SalesRep:HANDLE).

Like the SET-BUFFERS method, ADD-BUFFER is supported only for dynamic queries for the same reason that you cannot modify the buffer list of a static query.

NUM-BUFFERS attribute

This attribute returns the number of buffers for the query:

DEFINE VARIABLE hQuery AS HANDLE      NO-UNDO. CREATE QUERY hQuery. hQuery:SET-BUFFERS(BUFFER Order:HANDLE, BUFFER Customer:HANDLE). hQuery:ADD-BUFFER(BUFFER SalesRep:HANDLE). MESSAGE "This dynamic query has "       hQuery:NUM-BUFFERS "buffers." VIEW-AS ALERT-BOX.

Figure 20–1 shows the result.

Figure 20–1: Number of buffers message

GET-BUFFER-HANDLE method

Given the number of buffers in the query, you can walk through them and retrieve each handle in turn, using the GET-BUFFER-HANDLE method, which takes the sequential buffer number as a parameter, as in this example:

DEFINE VARIABLE hQuery    AS HANDLE     NO-UNDO. DEFINE VARIABLE iBufNum   AS INTEGER    NO-UNDO. DEFINE VARIABLE cBufNames AS CHARACTER  NO-UNDO. CREATE QUERY hQuery. hQuery:SET-BUFFERS(BUFFER Order:HANDLE, BUFFER Customer:HANDLE). hQuery:ADD-BUFFER(BUFFER SalesRep:HANDLE). DO iBufNum = 1 TO hQuery:NUM-BUFFERS:   cBufNames = cBufNames + hQuery:GET-BUFFER-HANDLE(iBufNum):NAME        + " ". END. MESSAGE "This query uses buffers " cBufNames VIEW-AS ALERT-BOX.

Figure 20–2 shows the result.

Figure 20–2: Names of buffers message

NAME attribute

As you know, only static objects have names, so the NAME attribute only applies to static queries:

DEFINE VARIABLE hQuery AS HANDLE     NO-UNDO. DEFINE QUERY OrderCust FOR Order, Customer. hQuery = QUERY OrderCust:HANDLE. MESSAGE "This is a static query named " hQuery:NAME VIEW-AS ALERT-BOX.

Figure 20–3 shows the result.

Figure 20–3: Name of static query message

QUERY-PREPARE method

Once you’ve defined the buffers for a dynamic query, the next step is to provide it with a FOR EACH statement that it should use to retrieve and order data when you open the query. The QUERY-PREPARE method takes the FOR EACH statement as its argument. It returns true if the operation succeeded and false otherwise. You can use QUERY-PREPARE to define the record selection for a dynamic query or to change the selection for a static one. This sample code prepares the dynamic query for the Order, Customer, and SalesRep tables:

DEFINE VARIABLE hQuery    AS HANDLE     NO-UNDO. DEFINE VARIABLE iBufNum   AS INTEGER    NO-UNDO. DEFINE VARIABLE cBufNames AS CHARACTER  NO-UNDO. DEFINE VARIABLE lSuccess  AS LOGICAL    NO-UNDO. CREATE QUERY hQuery. hQuery:SET-BUFFERS(BUFFER Order:HANDLE, BUFFER Customer:HANDLE). hQuery:ADD-BUFFER(BUFFER SalesRep:HANDLE). lSuccess =     hQuery:QUERY-PREPARE("FOR EACH Order WHERE OrderStatus = 'Ordered', "                        + "FIRST Customer OF Order, "                        + "FIRST SalesRep OF Order "                        + "BY SalesRep"). IF NOT lSuccess OR ERROR-STATUS:NUM-MESSAGES NE 0 THEN DO:     /* Deal with possible errors in the Query Prepare. */ END.

There are a few important comments to make about this example:

Remember that when you use a one-to-one join in a FOR EACH statement for a query, you must include the FIRST or EACH keyword in each table phrase, as is shown in the following code example. Since there is just one Customer for each Order, and one SalesRep for each Order, there is no need to iterate through potentially multiple Customers or SalesReps for an Order, so the FIRST keyword suffices. In a FOR EACH statement in a block header, the FIRST keyword would be optional, as in this example:

FOR EACH Order WHERE OrderStatus = "Ordered",     Customer OF Order, SalesRep OF Order:     DISPLAY OrderNum Order.CustNum Customer.NAME SalesRep.RepName. END.

If you know exactly what the WHERE clause and BY clause are when you write the procedure, you might not need to use dynamic constructs at all. More realistically, you use the QUERY-PREPARE method when you don’t know the selection and sort criteria until run time. In the "Extending the sample window to filter dynamically" , you’ll build an extension to the Customer and Orders window that shows this flexibility more realistically.
Since you normally use the QUERY-PREPARE method in cases where the WHERE clause is truly variable, it is essential that you always assign the result of the method to a logical variable, and then check the value of that variable and the ERROR-STATUS:NUM-MESSAGES value to be sure that the prepare succeeded. If it doesn’t and you continue without intercepting the error, your procedure generates a whole series of error statements as you attempt to open and use the query.

PREPARE-STRING attribute

Once you have prepared a query, you can verify what its current FOR EACH statement is using the PREPARE-STRING attribute, which returns the effect of the most recent QUERY-PREPARE method, as in this example:

MESSAGE hQuery:PREPARE-STRING VIEW-AS ALERT-BOX.

Figure 20–4 shows the result.

Figure 20–4: PREPARE-STRING message

The PREPARE-STRING attribute applies only to dynamic queries. It does not return the FOR EACH statement used in an OPEN QUERY statement for a static query. The example in the "Using the SELF handle to identify an object in a trigger" uses this attribute to save off the current FOR EACH statement for a query to restore it if the user’s attempt to replace it with a different one fails.

QUERY-OPEN method

Once you have prepared a query you need to open it, using the QUERY-OPEN method:

hQuery:QUERY-OPEN().

The QUERY-PREPARE and QUERY-OPEN methods together accomplish what the OPEN QUERY statement does for a static query. The two methods are separated out to enable you to define the selection and sort criteria for a query separately from opening it. You might want to reopen the same query several times with the same FOR EACH statement, for example, to capture changes to the underlying data the FOR EACH statement retrieves. Having two separate methods gives you this flexibility. It also allows you to verify that the FOR EACH statement in a QUERY-PREPARE is valid before you try to open the query.

You can use the QUERY-PREPARE and QUERY-OPEN methods with static queries as well, even after the query has been opened one or more times with a static OPEN QUERY statement. Here’s an example that extends the static query shown earlier:

DEFINE VARIABLE hQuery    AS HANDLE     NO-UNDO. DEFINE VARIABLE cSalesRep AS CHARACTER  NO-UNDO. DEFINE QUERY OrderCust FOR Order, Customer.  /* Static definition */ hQuery = QUERY OrderCust:HANDLE.        /* Capture the handle */ OPEN QUERY OrderCust FOR EACH Order WHERE Order.SalesRep = "BBB",     FIRST Customer OF Order.    /* static OPEN */ MESSAGE "Static OPEN can't show PREPARE-STRING: "  /* This is UNKNOWN */     hQuery:PREPARE-STRING VIEW-AS ALERT-BOX. CLOSE QUERY OrderCust. /* Code to ask the user for a SalesRep could go here... */ cSalesRep = "DKP". /* Now use the dynamic methods to re-prepare and re-open the query. */ hQuery:QUERY-PREPARE("FOR EACH Order WHERE SalesRep = '" +                       cSalesRep + "', FIRST Customer OF Order"). hQuery:QUERY-OPEN(). MESSAGE "QUERY-PREPARE sets PREPARE-STRING:" SKIP     hQuery:PREPARE-STRING VIEW-AS ALERT-BOX.

The first alert box, shown in Figure 20–5, confirms that you can’t use the PREPARE-STRING attribute on a static OPEN.

Figure 20–5: Static OPEN query message

But the one in Figure 20–6 shows that you can use it when you prepare and open even a static query using the dynamic methods.

Figure 20–6: Dynamic methods with OPEN query message

Using the QUOTER function to assemble a query

Notice the use of the single quotation marks embedded in the strings that are assembled to build up the FOR EACH statement:

hQuery:QUERY-PREPARE("FOR EACH Order WHERE SalesRep = '" +                       cSalesRep + "', FIRST Customer OF Order").

The first literal ends with a single quote mark, which precedes the SalesRep initials. This, in turn, is followed by another literal that begins with a single quote mark to balance the first one. This kind of assemblage of strings with and without quote marks can get very tricky and lead to code that is hard to read and programming errors that are hard to identify. Here, for example, you need to remember not only to put the quote marks into the statement before and after the cSalesRep value, but also to use single quotes so that they don’t interfere with the use of double-quotes around the literals themselves. You could reverse these and use double quotes inside single quotes, but either way you need to be sure to balance them and embed them properly.

Progress provides a helpful function to assist you in assembling these kinds of strings, the QUOTER function. If you insert values of any data type into a string, such as this QUERY-PREPARE as arguments to the QUOTER function, Progress assembles the string properly so that you don’t need to worry about where the embedded quote marks go. For instance, here’s an alternative version of the QUERY-PREPARE method that uses QUOTER:

hQuery:QUERY-PREPARE("FOR EACH Order WHERE SalesRep = " +                      QUOTER(cSalesRep) + ", FIRST Customer OF Order").

In Figure 20–7, Progress inserts quote marks around the SalesRep initials for you.

Figure 20–7: Example QUOTER function message

You can use QUOTER to avoid parsing errors for numeric values as well as string values. For example, DECIMAL formats in parts of the world outside the United States typically reverse the meaning of the decimal point or period/full stop (.) and comma (,) characters. If you use QUOTER on all such values, Progress assembles a query string that compiles properly without causing confusion to the syntax analyzer, which must interpret whether the “.” character is a decimal point, a separator between thousands in a numeric value, or the end of a statement.

QUERY-CLOSE method

There’s a dynamic method to close a query as well:

hQuery:QUERY-CLOSE().

QUERY-CLOSE returns true if the operation succeeded and false otherwise. As with the CLOSE QUERY statement, you don’t need to use QUERY-CLOSE if you are immediately again preparing and reopening a query, but you should close a query when you are done using it. You can freely mix and match static OPEN and CLOSE statements and QUERY-OPEN and QUERY-CLOSE methods for static queries but, as with all the query syntax, you can use the static statements only for static queries.

QUERY-OFF-END attribute

This attribute corresponds to the QUERY-OFF-END function for static queries. It returns true if the query is no longer positioned to a record in the result set, either because you have proceeded beyond the last row or moved backward beyond the first row.

IS-OPEN attribute

This LOGICAL attribute returns true is the query is open and false otherwise.

Navigation methods

There is a dynamic navigation method for each of the corresponding GET statements: GET-FIRST, GET-NEXT, GET-PREV, and GET-LAST. There is also a GET-CURRENT method that corresponds to the GET CURRENT statement, which again retrieves the current record from the database, normally to check to see whether it has been changed since you last read it.

These methods can take optional arguments that you can use to specify the lock mode (NO-LOCK, SHARE-LOCK, or EXCLUSIVE-LOCK) and wait mode (if it is NO-WAIT). The default lock mode is SHARE-LOCK. You will generally want to change this default to specify either NO-LOCK or EXCLUSIVE-LOCK, depending on whether you need to prepare for it to be changed and protect the record against changes by other users.

Here’s a completion of the simple procedure used throughout this section, showing the QUERY-PREPARE, QUERY-OPEN, and GET-NEXT methods and the use of the QUERY-OFF-END attribute:

DEFINE VARIABLE hQuery    AS HANDLE     NO-UNDO. DEFINE VARIABLE iBufNum   AS INTEGER    NO-UNDO. DEFINE VARIABLE cBufNames AS CHARACTER  NO-UNDO. DEFINE VARIABLE lSuccess  AS LOGICAL    NO-UNDO. CREATE QUERY hQuery. hQuery:SET-BUFFERS(BUFFER Order:HANDLE, BUFFER Customer:HANDLE). hQuery:ADD-BUFFER(BUFFER SalesRep:HANDLE). lSuccess =      hQuery:QUERY-PREPARE("FOR EACH Order WHERE OrderStatus = 'Ordered', "                        + "FIRST Customer OF Order, "                        + "FIRST SalesRep OF Order "                        + "BY SalesRep"). IF NOT lSuccess OR ERROR-STATUS:NUM-MESSAGES NE 0 THEN DO:     /* Deal with possible errors in the Query Prepare. */ END. hQuery:QUERY-OPEN(). REPEAT WHILE NOT hQuery:QUERY-OFF-END:     hQuery:GET-NEXT().     DISPLAY Order.OrderNum Customer.NAME FORMAT "x(20)"         SalesRep.RepName WITH FRAME OrderFrame 10 DOWN. END. hQuery:QUERY-CLOSE(). DELETE OBJECT hQuery.

Figure 20–8 shows the result.

Figure 20–8: Result of query methods and attributes example

Reposition methods

There are also reposition methods that match the corresponding REPOSITION statements you’ve seen earlier in the book. These include:

REPOSITION-FORWARD(n) — Repositions the query n rows forward within the result set.
REPOSITION-BACKWARD(n) — Repositions the query n rows backward within the result set.
REPOSITION-TO-ROW(n) — Repositions the query to row n within the result set.
REPOSITION-TO-ROWID(rowid [, ...] ) — Repositions the query to the row whose buffers have the RowIDs passed to the method.

INDEX-INFORMATION attribute

This attribute allows you to retrieve information about the indexes Progress uses to retrieve the records that satisfy the selection criteria. There is also an INDEX-INFORMATION function for static queries, but the dynamic method is especially useful as part of a procedure that defines selection criteria at run time for either a statically defined or dynamic query. If there is variability in the WHERE clause your procedure accepts or generates at run time (perhaps based on a specific user request), the record retrieval might be highly inefficient if the query requires searching nonindexed field values for large tables. The INDEX-INFORMATION method takes a single INTEGER argument, which is the join level you want information for. For example, a value of 1 means that you want index information for the first table in the join.

If Progress is able to use one or more index brackets to satisfy the query so that it does not have to read all the records in a table, the method returns a comma-separated list of the indexes used. Progress uses more than one index to resolve a complex query if this results in the smallest number of nonindexed fields being searched.

If Progress is unable to use an index to reduce the number of records read because the selection involves nonindexed fields or fields that are not the primary components of a multi-component index, then it returns the "WHOLE-INDEX" string, followed by a comma, followed by the name of the index Progress uses to navigate the records. This is normally the primary index of the table.

You can use the INDEX-INFORMATION method to warn users of potentially inefficient queries or prevent them from executing queries that can’t use an index.

You must prepare the query before you can query its INDEX-INFORMATION.

NUM-RESULTS and CURRENT-RESULT-ROW attributes

These attributes correspond to the static query functions of the same names that you’re already familiar with. They return the number of rows in the query’s current result list and the current row position within that list, respectively.

Cleaning up a dynamic query

Like any other dynamic objects, you should always delete a dynamic query when you’re done with it. If you create a query in a named widget pool, or if there is an unnamed widget pool associated with the procedure where you create the query, then you can use the widget pool mechanism to control when the query is deleted. Otherwise, it’s important to use the DELETE OBJECT statement to delete the query object when you’re done using it.

However, if you need another dynamic query, perhaps for a different buffer, then it is good practice to reuse an existing query object rather than deleting one query and then creating another one. Reusing an existing dynamic query object is much more efficient than creating a new one each time you need one. You can change the buffers of an existing query object with the SET-BUFFERS method and then use the other methods, such as QUERY-PREPARE, to change the use of the same query object entirely. You should still be sure to delete the object when you’re done with it.

Extending the sample window to filter dynamically

You already extended the h-CustOrderWin4.w version of the sample window once in "Using Queries," to filter the records for a particular state. In that version of the procedure, the LEAVE trigger on the New State field reopens the Customer query based on the state abbreviation entered:

OPEN QUERY CustQuery FOR EACH Customer WHERE Customer.State =       cState:SCREEN-VALUE BY Customer.City.

A static OPEN QUERY statement suffices, because the WHERE clause is known. The only variable is the state abbreviation, and the procedure can plug that into the OPEN QUERY statement.

You should use the dynamic QUERY-PREPARE and QUERY-OPEN methods when your procedure needs more flexibility than this. To show how this can work, you can extend the filtering to apply to every field in the Customer table that the window displays. You can change the procedure to let the user filter the Customers based on a value you enter in any of the Customer fields. You can then add a filter button to the window that blanks out the Customer fields and enables them. When the user enters a value in any one of the fields, the code re-prepares and reopens the query to filter on the value for that field.

To make these changes:

Open h-CustOrderWin1.w and save it h-CustOrderWin8.w.
Disable all the Customer fields that are displayed.
Remember that you can select the fields, open the Properties Window, and set Enabled to false for all of them at once. Making them initially disabled allows you to enable them for input only when you want to filter on a value in a field.
Remove the phrases from the query that filter on State = “NH” and sort by City.
Because the user will be filtering on any field, it makes sense to remove the initial filtering and sorting that this version of the procedure from "Introducing the OpenEdge AppBuilder." If you double-click on the frame, its property sheet opens where you can access the Query Builder to make the changes. Select the Where and Sort radio set options in turn to make the changes.
Drop a button onto the frame next to the navigation buttons. Name it btnFilter and give it a Label of Filter.
Define a CHOOSE trigger for the Filter button. The trigger code needs to blank out all the Customer fields and enable them for input.
What code can you write to do this? The quickest way is to set the SCREEN-VALUE of each field to “” and to set the SENSITIVE attribute to yes. (Remember that the SCREEN-VALUE is the value displayed in the frame and that SENSITIVE is the attribute name for the Enabled property of an object.):

ASSIGN Customer.CustNum:SCREEN-VALUE = ""        Customer.Name:SCREEN-VALUE = ""        Customer.Address:SCREEN-VALUE = ""        Customer.City:SCREEN-VALUE = ""        Customer.State:SCREEN-VALUE = ""        Customer.CustNum:SENSITIVE = YES        Customer.Name:SENSITIVE = YES        Customer.Address:SENSITIVE = YES        Customer.City:SENSITIVE = YES        Customer.State:SENSITIVE = YES.

This code certainly works, but does it make you a little uncomfortable? What happens if you later add a field to the frame or remove one? Then you’ve got a maintenance problem on your hands. Try identifying the fill-ins dynamically instead, using what you learned in the previous chapter.
Define a variable scoped to the procedure to hold the field handles in the Definitions section:

/* Local Variable Definitions --- */ DEFINE VARIABLE cFillIns AS CHARACTER  NO-UNDO.

Add code so that the CHOOSE trigger builds up a list of all the handles of the objects in the frame that are fill-in fields, if the list hasn’t already been built:

DO:     DEFINE VARIABLE hContainer AS HANDLE     NO-UNDO.     DEFINE VARIABLE hField     AS HANDLE     NO-UNDO.     DEFINE VARIABLE iField     AS INTEGER    NO-UNDO.     IF cFillIns = "" THEN     DO:         /* Identify all the fill-ins so they can be enabled, disabled,            and cleared. */         ASSIGN hContainer = FRAME CustQuery:HANDLE                hContainer = hContainer:FIRST-CHILD. /* The field group */         hField = hContainer:FIRST-CHILD.         DO WHILE VALID-HANDLE(hField):            IF hField:TYPE = "Fill-In" THEN                 cFillIns = cFillIns +                 (IF cFillIns = "" THEN "" ELSE ",") + STRING(hField).            hField = hField:NEXT-SIBLING.         END.     END.

Whether the field list has already been built or not, the procedure needs to walk through those fields and blank them and disable them:

DO iField = 1 TO NUM-ENTRIES(cFillIns):         ASSIGN hField = WIDGET-HANDLE(ENTRY(iField, cFillins))                hField:SCREEN-VALUE = ""                hField:SENSITIVE = YES.     END.

Now if you later change the fields on the screen, you won’t have to change this code. That’s part of the value of dynamic programming!

Defining a trigger block for multiple objects

Next, you need to define a LEAVE trigger for every field to filter the query based on what the user entered into that field.

This seems tedious, doesn’t it? You shouldn’t have to define the same trigger code for every Customer field, and in fact you don’t. First, you need to learn a little trick about using the trigger code in the Section Editor. Although the field name the trigger applies to is displayed in a special drop-down list, you can add more fields to the top of the editable portion of the window, before the DO keyword, as shown in Figure 20–9.

Figure 20–9: Example LEAVE trigger

To define a trigger block for multiple objects:

Add the code shown in Figure 20–9.
This code defines the same trigger for all the fields. In the Progress 4GL syntax both the event name and the object name can be comma-separated lists.
Define a variable to hold the query handle and one to hold a prepare-string:

DEFINE VARIABLE hQuery     AS HANDLE     NO-UNDO.   DEFINE VARIABLE cPrepare AS CHARACTER    NO-UNDO.

Add the code that walks through the list of field handles, just as the Filter trigger does, in order to disable them. You need a variable for a field handle and a variable for a counter:

DEFINE VARIABLE iField   AS INTEGER    NO-UNDO.   DEFINE VARIABLE hField   AS HANDLE     NO-UNDO.

You may ask whether it would be simpler and more efficient to define these variables once, in the procedure’s Definitions section, because they are used locally in both the Filter button trigger and the field LEAVE trigger. You should not do this. If you do, the leftover values from one procedure or trigger’s use of the variables remain defined until the next trigger or procedure uses the variables, increasing the chances that a block of code will use a stale value by mistake, or that one procedure called from another will reset the variable that is being used for different purposes by each of them. It’s good practice to scope variables and other objects as locally as possible, even if it means having the same definitions in more than one place.

Using the SELF handle to identify an object in a trigger

Now you need to check the SCREEN-VALUE of the field to see whether the user typed anything into it. The reason for this check is that the LEAVE trigger could fire under other circumstances, such as if the user clicked on a button while the cursor was positioned in the field. This action would fire the LEAVE event, but you want to disregard such events unless the user actually entered a value in the field.

But which field? You’re defining the same trigger code for all the fields.

Progress defines another built-in handle that gives you what you need: the SELF handle. Within a trigger block, this handle resolves to the object the trigger has fired for.

To identify a field in a trigger:

Check to see whether the user entered a value using this statement and start a block that is executed only if the user did:

IF SELF:SCREEN-VALUE NE "" THEN   DO:

It’s important to make this check because the LEAVE trigger fires in many different ways, including clicking on a button while the cursor is in the field. You need to check that the user really entered a value to filter on.
Inside this block, use the list of fill-in field handles to disable them again, now that the user has had a chance to enter a value into one of them:

DO iField = 1 TO NUM-ENTRIES(cFillIns):        ASSIGN hField = WIDGET-HANDLE(ENTRY(iField, cFillins))               hField:SENSITIVE = NO.    END.

Remember that you defined the cFillIns variable in the Definitions section specifically because you do want its value to be scoped to the whole procedure, so that its value persists between trigger or procedure calls. To remind yourself, and other readers of your code, that a variable like this is scoped to the entire procedure, you can use a naming convention such as putting a g for global on the front of the variable name. Even though such a variable is not actually global to the session, it is global to this procedure, so its scope is worth noting in some way.
Get the handle to the query, along with the current PREPARE-STRING:

ASSIGN hQuery = QUERY CustQuery:HANDLE        cPrepare = hQuery:PREPARE-STRING.

You’re saving off the current PREPARE-STRING before closing and re-preparing the query so that you can restore it later in the block in case the user chooses not to continue with the filtering. There’s a special case you have to deal with here. If the query has not yet been re-prepared dynamically, then the PREPARE-STRING attribute is unable to return the FOR EACH statement used in the original static OPEN QUERY statement. In this case, you just have to set the variable to the default query string:

/* If the query hasn't been prepared dynamically just restore    the original query definition. */ IF cPrepare = ? THEN    cPrepare = "FOR EACH Customer".

Close the query and re-prepare it using its handle and the value the user entered into whichever one of the fill-in fields:

hQuery:QUERY-CLOSE().     hQuery:QUERY-PREPARE("FOR EACH Customer WHERE " + SELF:NAME +                         (IF SELF:DATA-TYPE = "CHARACTER" THEN                          " BEGINS " ELSE " = ")                          + QUOTER(SELF:SCREEN-VALUE) +                          " BY " + SELF:NAME).

The QUERY-CLOSE step is actually optional. The QUERY-PREPARE method closes the query automatically.
Using the DATA-TYPE attribute of the SELF handle, the code identifies the data type of the field the user is filtering on. If the field is CHARACTER, the code builds a query retrieving all Customers where the field value BEGINS with what the user typed in, which is the SELF:SCREEN-VALUE attribute. Otherwise, it retrieves records where the field value is equal to the filter value. You could, of course, set up such a query to filter records in any way that you want. Finally, the FOR EACH statement sorts the record by the filter field, using the SELF:NAME attribute.

Using INDEX-INFORMATION and a MESSAGE with a yes/no answer

Next, you need to check whether the field the user selected has an index. If not, you need to warn the user that the retrieval will not be indexed, and you need to provide the option of canceling the query.

To add this check and warning to your sample procedure:

Use the INDEX-INFORMATION attribute of the query and a MESSAGE statement that asks a question:

IF ENTRY(1, hQuery:INDEX-INFORMATION(1)) = "WHOLE-INDEX" THEN DO:     MESSAGE "This query can't use an index. Continue?"            VIEW-AS ALERT-BOX BUTTONS YES-NO SET lContinue AS LOGICAL.     IF NOT lContinue THEN        hQuery:QUERY-PREPARE(cPrepare). END.

If INDEX-INFORMATION returns "WHOLE-INDEX" for the first (and, in this case, the only) table in the query, then you know that there is no index Progress can use to retrieve the selected records, so it has to search the whole primary index.
To give the user the option of canceling the query, you can use an option on the MESSAGE statement that you haven’t seen before. If you define a MESSAGE as VIEW-AS ALERT-BOX, you can define the choice buttons that appear in the alert box. As you have seen, OK is the default. Other choices are YES-NO, YES-NO-CANCEL, OK-CANCEL, and RETRY-CANCEL. You can then SET a variable of LOGICAL data type to record the answer. You can use a variable that you defined earlier or you can define it right in the MESSAGE statement using the AS LOGICAL phrase. You can then check the value of the variable to see which choice the user picked. The first choice within any set of buttons returns yes, the second no, and if there is a third choice (as in YES-NO-CANCEL), choosing that button sets the variable to the unknown value.
(You can set other kinds of variables and fields in messages that aren’t defined as alert boxes, as well. See the online Help for details.)
In this case, if the user decides not to continue, the query is re-prepared using the PREPARE-STRING you saved off earlier.
Reopen the query and APPLY CHOOSE to the First button to display the first matching record and its Orders:

hQuery:QUERY-OPEN().     APPLY "CHOOSE" TO btnFirst.   END.    /* END DO IF SCREEN-VALUE Not “” */

Run the window. The Customer fields initially come up disabled:



Choose the Filter button. The fields are enabled and blanked out, and you can enter a value into any one of them:



Enter a value into a nonindexed field, such as the City. You get a warning message:



If you answer Yes, the query is prepared and opened:



If you answer No, the procedure reverts to the previous query or to the default query if this is the first time the query has been re-prepared.

Using dynamic buffers and buffer handles

You can create a dynamic buffer at run time in much the same way that you create a dynamic query, using this syntax:

CREATE BUFFER buffer-handle FOR TABLE table-expression.

The table-expression can be either a literal database table or temp-table name (both in quotation marks) or a variable or other expression that evaluates to a table or temp-table at run time.

You can also access the handle of any static buffer at run time:

buffer-handle = BUFFER buffer-name:HANDLE.

Dynamic buffers are useful only when you really need to define the table name for a buffer at run time. Just as with dynamic queries, you will find that in many cases you can simply get the handle to a static buffer and use that handle’s attributes and methods to manipulate the buffer dynamically, without creating a dynamic buffer at all.

Buffer handle attributes

This section describes some of the most useful attributes you can access through a buffer handle, for either a static or dynamic buffer. You can see a complete list and descriptions at the Buffer Object Handle entry in the online help.

NAME, TABLE, and DBNAME attributes

The NAME attribute is the name of the underlying buffer. For a dynamic buffer, it is the table name the buffer was created for.

The TABLE is the name of the database table or temp-table the buffer is defined or created for.

The DBNAME is the database name for a buffer defined or created for a database table. For a temp-table, this equals the string "PROGRESST".

ROWID attribute

The ROWID attribute is the value of the RowID of the record currently in the buffer. You can use this, for example, to reposition a query to the same record, as illustrated in the "Extending the test window to use a buffer handle" .

NEW, LOCKED, and CURRENT-CHANGED attributes

The NEW attribute is a logical value that is true if the record in the buffer is newly created, and false if it is a record that has been read from the database table or temp-table the buffer is for.

The LOCKED attribute is a logical value that is true if the underlying record is locked and false if it is not.

The CURRENT-CHANGED attribute is a logical value that is true if the record in the buffer has any different field values from the underlying database table record, indicating that it has been changed by another user since it was read.

NUM-FIELDS attribute

The NUM-FIELDS attribute is the number of fields in the buffer.

AVAILABLE and AMBIGUOUS attributes

These attributes perform the same functions as their counterpart functions in static buffer references. If there is currently a record in the buffer, the logical AVAILABLE attribute is true, otherwise it is false. If a FIND statement or method executed on the buffer finds more than one matching record, then there is no record in the buffer (AVAILABLE is set to false) and the logical AMBIGUOUS attribute is set to true.

Buffer handle methods

There are many methods you can use to perform operations dynamically on a buffer handle, both for static and for dynamic buffers. These are all dynamic equivalents of static statements you’re already familiar with. The principal value of these methods is that they let you define selection criteria for data retrieval and other values needed by the methods at run time. In many cases, you can use these methods effectively with queries and buffers that are defined as static objects, where the table names are known at compile time. The example that follows the method descriptions shows how to extend the Customers and Orders windows to use these dynamic methods on the query and buffer for the Customer table. These are summary method descriptions to make you aware of what is possible in working with buffer handles. As always, consult the online help or OpenEdge Development: Progress 4GL Reference for complete descriptions.

BUFFER-FIELD method

The BUFFER-FIELD method takes an argument, which can be either the name of a field or its ordinal position within the buffer, and returns the handle of that field object. You can then use the BUFFER-FIELD handle in turn to access various attributes of the field that are defined below. Note that BUFFER-FIELD is considered a method, rather than an attribute, only because it takes an argument to identify which field object you want. Beyond identifying the field, this method doesn’t really do anything except return its handle.

BUFFER-COMPARE and BUFFER-COPY methods

These two methods on a target buffer handle take a source buffer handle (and other optional values) as an argument. BUFFER-COMPARE compares the field values in the two buffers and returns a report of their differences. BUFFER-COPY copies the field values in the source buffer to the target buffer.

BUFFER-CREATE, BUFFER-DELETE, and BUFFER-RELEASE methods

These methods perform the same function as the CREATE, DELETE, and RELEASE statements do.

Buffer FIND methods

There is a whole set of methods you can use to perform a FIND operation on a buffer dynamically. Some of these include:

FIND-BY-ROWID — Takes a record RowID as an argument and reads that record into the record buffer. This method can be useful after a separate operation has recorded the RowID of a record using some other criteria and you wish to re-retrieve that record.
FIND-CURRENT — Re-reads the current record from the database, replacing the contents of the record buffer. This method can be useful if you want to refresh the record in case it has been changed, for example by another user, since it was originally read.
FIND-FIRST — Takes a WHERE clause (including the initial WHERE keyword) as an argument and retrieves the first record from the buffer’s table that satisfies the WHERE clause into the buffer.
FIND-LAST — Takes a WHERE clause as an argument and retrieves the last record from the buffer’s table that satisfies the WHERE clause into the buffer.
FIND-UNIQUE — Takes a WHERE clause as an argument that identifies a single record, and retrieves the one record from the buffer’s table that satisfies the WHERE clause into the buffer. If more than one record satisfies the WHERE clause, then no record is retrieved and the AMBIGUOUS buffer attribute is set to true.

The FIND methods are a very useful and efficient way of identifying a single record without the overhead of preparing and opening a query with selection criteria that identify that one record, and then doing a GET-FIRST() to position the query cursor to that one record.

In addition to the RowID argument for FIND-BY-ROWID and the WHERE clause argument for the FIND-FIRST, FIND-LAST, and FIND-UNIQUE methods, all five of these methods take optional arguments that you can use to specify the lock mode (NO-LOCK, SHARE-LOCK, or EXCLUSIVE-LOCK) and wait mode (if it is NO-WAIT). The default lock mode is SHARE-LOCK. You will generally want to change this to specify either NO-LOCK or EXCLUSIVE-LOCK, depending on whether you need to prepare to allow it to be changed and protect the record against changes by other users.

This example changes the lock mode to NO-LOCK:

hBuffer:FIND-UNIQUE(“WHERE CustNum = 125”, NO-LOCK).

This example changes the lock mode to EXCLUSIVE-LOCK and the wait mode to NO-WAIT:

hBuffer:FIND-UNIQUE(“WHERE CustNum = 125”, EXCLUSIVE-LOCK, NO-WAIT).

Specifying the lock or wait option as a variable

NO-LOCK, EXCLUSIVE-LOCK, SHARE-LOCK, and NO-WAIT are Progress keywords that also represent constants that you can assign to an integer variable and pass in as an integer to the FIND- and GET- methods. For example:

DEFINE VARIABLE iLock AS INTEGER NO-UNDO. iLock = EXCLUSIVE-LOCK. hBuffer:FIND-FIRST(“CustNum = 5”,iLock).

This can be useful when the FIND or GET method is in a different procedure or function from the one that determines what the lock or wait mode should be, or when you want to enumerate these lock and wait options and pick one depending on some other value.

Extending the test window to use a buffer handle

In this section, you’ll extend the h-CustOrderWin8.w procedure beyond the changes you already made in this chapter to illustrate the use and the value of some of the buffer handle attributes and methods. To preserve the first set of changes separately, this variant of the procedure is saved as h-CustOrderWin9.w. You’ll add buttons to the window to reposition within the current query to a particular record, and also to use a dynamic FIND method to locate and display a single record without using the query at all.

To extend the sample window to use a buffer handle:

Define another variable in the Definitions section to record which of several new buttons the user selected:

DEFINE VARIABLE cBtnChoice AS CHARACTER    NO-UNDO.

This variable keeps track of whether the user chose the Filter button or one of the new buttons labeled Reposition and Find that you’ll define next.
Drop two new buttons onto the window beneath the Filter button. Call them btnRepos and btnFind and give them the labels Reposition and Find, respectively.
Extend the existing CHOOSE trigger on btnFilter to fire for the other buttons, just as you did for the Customer field’s LEAVE trigger before:



At the end of the trigger, add a line to save off which button was chosen:

cBtnChoice = SELF:LABEL. /* Was this a Filter, Reposition, or Find? */

Go into the LEAVE trigger for CustNum (which of course applies to all the Customer fields). Define a variable to hold the buffer handle:

DEFINE VARIABLE hBuffer  AS HANDLE      NO-UNDO.

After the statement that assigns the query handle and PREPARE-STRING, start a block based on the value of the new cBtnChoice variable. Execute the existing code only if the button chosen was Filter:

ASSIGN hQuery = QUERY CustQuery:HANDLE        cPrepare = hQuery:PREPARE-STRING.   IF cBtnChoice = "Filter" THEN   DO:

End the DO block for the Filter choice and start an ELSE block for the Reposition choice. Save off the Customer buffer handle and execute a FIND-FIRST method on it that once again uses the BEGINS keyword for CHARACTER fields and the = comparison otherwise. Execute the FIND-FIRST method as NO-ERROR in case there’s no such record:

END.      /* END DO IF "Filter" */ ELSE IF cBtnChoice = "Reposition" THEN DO:     hBuffer = BUFFER Customer:HANDLE.     hBuffer:FIND-FIRST("WHERE " + SELF:NAME +                     (IF SELF:DATA-TYPE = "CHARACTER" THEN " BEGINS "                             ELSE " = ") +                         QUOTER(SELF:SCREEN-VALUE)) NO-ERROR.

Use the AVAILABLE attribute to check whether there was a matching record. If there was, use the REPOSITION-TO-ROWID method on the query handle to position the query to that record using its RowID. Do this operation NO-ERROR. Apply CHOOSE to the Next button to position onto the record itself, display it, and retrieve its Orders:

IF hBuffer:AVAILABLE THEN     DO:       hQuery:REPOSITION-TO-ROWID(hBuffer:ROWID) NO-ERROR.       APPLY "CHOOSE" TO BtnNext.     END.  /* END DO IF AVAILABLE Customer */

There are a few things worth examining here. First, remember that because you are using the FIND-FIRST method, and not FIND-UNIQUE, Progress retrieves a record into the buffer even if there’s more than one match. The AMBIGUOUS attribute is never true in this case.
Second, you might be a little confused about why you have to reposition the query to the record you just retrieved in the buffer. Isn’t it already there for you to see? Yes, it is. If you leave out the REPOSITION method on the query and just display the contents of the buffer, you see the record FIND-FIRST retrieved. But if you then choose the Next button, you don’t see the next record following the one FIND-FIRST retrieved. You just see the next record in the query as it was before you ever did the FIND-FIRST. The reason for this is that the FIND-FIRST method on the buffer handle is effectively reusing the buffer for a purpose completely separate from the query. There is no connection between the FIND method and the records in the query. That’s really the purpose of the FIND methods, that they allow you to fetch a specific record without using a query at all. To use the FIND method to reposition the query, you need code similar to what you just wrote, which uses the RowId to reposition the query to the same record.
Also, remember why the NEXT operation is required. If you use one of the FIND methods on a buffer, the record you want is placed in the buffer, and you can use it immediately. But if you use one of the query handle’s REPOSITION methods, the query cursor is effectively placed immediately before the record you are repositioning to, so you need the GET NEXT statement to actually bring that record into the buffer.
And finally, consider the NO-ERROR qualifier on the FIND-FIRST method. It’s entirely possible that the user might choose the Filter button and filter the query before choosing the Reposition button, and then enter a value to reposition to that’s in the Customer table (and therefore found by the FIND-FIRST method on the buffer) but not in the query’s result set as it has been filtered. For example, the user could filter on Customer Names beginning with A, and then reposition to the first Customer Name beginning with B. Progress successfully retrieves the first Customer Name starting with B from the database, but then the REPOSITION method on the query fails because that record is not in the query’s result list. This is important to keep in mind as you use these objects and their methods.
Check whether there’s a record available and display a message if there is not:

IF NOT hBuffer:AVAILABLE THEN        MESSAGE "No record matches that value. Try again. ". END.        /* END ELSE DO (IF "Reposition" ) */

In such a case, a record might not be available either because there was no matching record in the database or because that record was not in the query.
Define a block of code to support the Find button. Because the Find button is identifying a single record, the code uses the FIND-UNIQUE buffer method:

ELSE IF cBtnChoice = "Find" THEN     DO:         hBuffer = BUFFER Customer:HANDLE.         hBuffer:FIND-UNIQUE("WHERE " + SELF:NAME + " = " +                                 QUOTER(SELF:SCREEN-VALUE)) NO-ERROR.

Because you’re trying to find just one matching record, the comparison operator in the WHERE clause is simply "=".
As before, you need to invoke the method with the NO-ERROR qualifier, in case the selection either doesn’t yield a record or yields more than one matching record. Next, you need to check for those conditions.
Add code to check the AMBIGUOUS and AVAILABLE attributes to make sure you got exactly one match:

IF hBuffer:AMBIGUOUS THEN    MESSAGE "This choice returns more than one row. Try again.".  ELSE IF NOT hBuffer:AVAILABLE THEN     MESSAGE "This choice does not match any row. Try again.".

Write the code to handle the successful case.
Close the Customer query.
Disable the navigation buttons and the Reposition button because they don’t apply if you’ve just got one record.
Display the record that the FIND-UNIQUE method retrieved.
Reopen the Order Browse:

ELSE DO:             CLOSE QUERY CustQuery.             DISABLE BtnFirst BtnNext btnPrev BtnLast BtnRepos                 WITH FRAME CustQuery.             DISPLAY Customer.CustNum Customer.Name Customer.Address                 Customer.City Customer.State             WITH FRAME CustQuery IN WINDOW CustWin.             {&OPEN-BROWSERS-IN-QUERY-CustQuery}         END.    /* END DO IF "Find" */     END.        /* END ELSE DO IF no errors */   END.          /* END DO IF SCREEN-VALUE NOT "" */ END.            /* END DO for the trigger block */

Why close the query? Because you’re not using it. You found a record using the same buffer the query uses, but that record is not in any way related to the query.
Why disable the buttons? Because they expect to be able to navigate through the query or reposition within it. You’d get an error if you allowed the user to choose them.
You can copy the DISPLAY statement and the {&OPEN-BROWSERS-IN-QUERY-CustQuery} preprocessor from any of the navigation triggers. As you did in an earlier exercise, you could clean this up by factoring out the repeated code into an include file or internal procedure.
Don’t forget to end all your blocks properly, with a comment on each END statement to help you verify the block structure as things get more complex.
Because the Find button disables the navigation and Reposition buttons, the block of code in the same trigger for the Filter button needs to re-enable them.
Add this ENABLE statement to that block, after it reopens the query:

hQuery:QUERY-OPEN().   ENABLE BtnFirst BtnNext BtnPrev BtnLast BtnRepos         WITH FRAME CustQuery.   APPLY "CHOOSE" TO btnFirst.

Try out the procedure with various combinations of actions. When you choose the Find button, for instance, it should enable the fields, accept input, and then disable the fields along with the navigation and Reposition buttons and display the one matching record:



Using various other Filter, Find, and Reposition requests, you can test the various error conditions that you wrote code to handle.

Cleaning up dynamic buffers

This example uses a handle to a static buffer to illustrate how to use buffer attributes and methods, because the table name is known. You could also create a dynamic buffer for the table like this:

CREATE BUFFER hBuffer FOR TABLE "Customer".

Given that the table name is known and that it is, therefore, a literal in the CREATE BUFFER statement, there is no particular advantage to doing this. The CREATE BUFFER statement is better used when the buffer name is variable.

When you create dynamic buffers, rather than just using handles to static buffers, you need to delete them when you’re done using them, just as with other objects. You use the same DELETE OBJECT statement to do this.

Often you will want to delete dynamic buffers along with the dynamic query that uses them. You can use the GET-BUFFER-HANDLE method to determine what buffers to delete when you have finished with a dynamic query with dynamic buffers, as in this example, saved as h-cleanup.p:

/* Procedure h-cleanup.p -- shows how to delete     a dynamic query and its dynamic buffers. */ DEFINE VARIABLE hQuery      AS HANDLE     NO-UNDO. DEFINE VARIABLE hBuffer     AS HANDLE     NO-UNDO. /* A list of tables for the dynamic query comes in as a parameter    or is determined at run time . This simulates the list of names. */ DEFINE VARIABLE cBufNames AS CHARACTER NO-UNDO     INIT "Customer,Order,SalesRep". DEFINE VARIABLE iBufNum     AS INTEGER    NO-UNDO. DEFINE VARIABLE cBufHandles AS CHARACTER  NO-UNDO. /* CREATE a dynamic query and dynamic buffers for each table.    Add each buffer to the query. */ CREATE QUERY hQuery. DO iBufNum = 1 TO NUM-ENTRIES(cBufNames):     CREATE BUFFER hBuffer FOR TABLE ENTRY(iBufNum, cBufNames).     hQuery:ADD-BUFFER(hBuffer). END. /* Verify that the query has the buffers we created. */ cBufNames = "". DO iBufNum = 1 TO hQuery:NUM-BUFFERS:    cBufNames = cBufNames + hQuery:GET-BUFFER-HANDLE(iBufNum):NAME + " ". END. MESSAGE "This query uses buffers " cBufNames VIEW-AS ALERT-BOX. /* Application code here uses the dynamic query and its buffers... */ /* When you're done with the query, build up a list of its buffers. */ DO iBufNum = 1 TO hQuery:NUM-BUFFERS:   cBufHandles = cBufHandles +      (IF cBufHandles = "" THEN "" ELSE ",") +      STRING(hQuery:GET-BUFFER-HANDLE(iBufNum)). END. MESSAGE "Buffer handles are: " cBufHandles VIEW-AS ALERT-BOX. /* After building the list, delete the buffers and then the query. */ DO iBufNum = 1 TO hQuery:NUM-BUFFERS:      DELETE OBJECT WIDGET-HANDLE(ENTRY(iBufNum, cBufHandles)). END. DELETE OBJECT hQuery.

Note that you cannot delete the buffers in the loop where the code uses the GET-BUFFER-HANDLE method at the end of the procedure because deleting the buffers would un-prepare the query and cause it to lose the list of buffers before you had retrieved them all. Thus, you need to build a comma-separated list of buffer handles and walk through the list afterwards to delete the buffers, and then delete the query.

Special dynamic buffer considerations

When you use dynamic buffers, there are a few special rules that you need to be aware of:

You can define a delete validation expression for a table in the Data Dictionary when you define your database. Although this is not recommended in new applications, if you have done this, Progress is unable to verify whether the delete expression is true or not when you use a dynamic buffer for the table. It therefore disallows any BUFFER-DELETE operation on the table.
It is important that you use the BUFFER-RELEASE method to release a dynamic buffer when you are done using it, especially if you have written to the buffer. This is true of static buffers as well, where you could use the static RELEASE statement. But because there is no inherent record scoping for dynamic buffers, a record release might be postponed indefinitely if you don’t handle it yourself.
A dynamic buffer has the same scope as the widget pool in which it was created. This means that Progress automatically deletes a dynamic buffer object only when it deletes the widget pool. To delete a dynamic buffer yourself, use the DELETE OBJECT statement, as the preceding example shows.
If you place the phrase BUFFER name anywhere in a procedure file, where name represents the name of a table, not necessarily the name of a buffer you defined using a DEFINE BUFFER statement, Progress scopes the name as it would a free reference to the buffer.

Using BUFFER-FIELD objects

As you saw in the previous chapter, there is a BUFFER-FIELD object for each field in a buffer, whether the buffer is static or dynamic. You can access a field within a buffer through its handle, using one of two types of arguments to the BUFFER-FIELD method, either its ordinal position within the buffer or its fieldname.

Buffer fields have all the same attributes that static field-level objects have. You’re already familiar with these attributes from static syntax, including COLUMN-LABEL, DATA-TYPE, DBNAME, DECIMALS, EXTENT, FORMAT, HANDLE, HELP, INITIAL, LABEL, MANDATORY, NAME, TABLE, and WIDTH-CHARS. Buffer field objects also have several useful attributes distinctive to the object and accessible only through its handle. This section summarizes those objects.

You cannot create or delete a buffer field independent of its buffer. Buffer field objects are created when a buffer is created. They are deleted along with the buffer, if it is dynamic. In the case of static buffers, the buffer field is really just a handle through which you can access useful attributes.

BUFFER-HANDLE attribute

This attribute is the handle of the buffer the field belongs to. So, the BUFFER-FIELD handle points from the buffer to one of its fields, and the BUFFER-HANDLE points from any field back to its buffer.

BUFFER-NAME attribute

This CHARACTER attribute holds the name of the buffer the field belongs to.

BUFFER-VALUE and STRING-VALUE attributes

The BUFFER-VALUE is the value of the field in its native data type. The attribute therefore holds a value in whatever the data type of the field is. The STRING-VALUE, on the other hand, is a CHARACTER attribute that holds the field value as it is formatted for display. It is therefore in the same format as the SCREEN-VALUE of a displayed field. Note that this is not necessarily the same as what the expression STRING(hField:BUFFER-VALUE) would return, because the STRING-VALUE includes any characters that are part of the field format, whereas using the STRING function simply turns the value into a character string without applying any special formatting to it.

POSITION attribute

This INTEGER attribute is the ordinal position of the field within the buffer.

Note that there is a difference between the order of the fields you obtain using the BUFFER-FIELD(n) form to identify the field and the value of the field’s POSITION attribute. The order using BUFFER-FIELD(n) is the display order of the fields, which is determined using the Order # as it is assigned in the Data Dictionary. The POSITION attribute is assigned internally as fields are created for a table, and does not change, even though the display Order # can be changed. In addition, the first field position is reserved so that the numbering of the POSITION begins with 2. For example, this code shows the POSITION attribute value for each Customer field:

DEFINE VARIABLE hCust  AS HANDLE  NO-UNDO. DEFINE VARIABLE hField AS HANDLE  NO-UNDO. DEFINE VARIABLE cLabel AS CHARACTER FORMAT "X(20)" NO-UNDO. DEFINE VARIABLE cValue AS CHARACTER FORMAT "X(40)" NO-UNDO. DEFINE VARIABLE iCount AS INTEGER NO-UNDO. hCust = BUFFER Customer:HANDLE. hCust:FIND-FIRST("WHERE CustNum = 1", NO-LOCK). REPEAT iCount = 1 TO hCust:NUM-FIELDS:      ASSIGN hField = hCust:BUFFER-FIELD(iCount)             cLabel = hField:LABEL             cValue = hField:STRING-VALUE.   DISPLAY cLabel hField:POSITION FORMAT "Z9" LABEL "Position" cValue. END.

Figure 20–10 shows the result.

Figure 20–10: Result of using the POSITION attribute

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