OpenEdge Development: Progress 4GL Handbook

Using Queries

The programming syntax you have learned so far uses blocks of 4GL code to define and iterate through a set of records. Progress defines an alternative to this form of data access called a query. You’ve seen queries in action already, in the chapter where you looked at the syntax the AppBuilder generates to define a default result set for a window and to display its records in a browse. This chapter discusses why these different forms exist and how you can use queries in ways that are distinct from how you would use a result set in a FOR EACH block.

This chapter includes the following sections:

Why you use queries in your application
Defining and using queries

Why you use queries in your application

The first question to answer about queries is why Progress has them at all. For many years, the Progress 4GL did not support queries, and developers wrote very powerful and complete applications without them. So, before you go into the details of how to define and use queries, you’ll learn the differences between queries and the block-oriented data access language you’ve used so far.

Queries versus block-oriented data access

The first and most obvious characteristic of queries is precisely that they are not block-oriented. The language statements you’ve used in the last few chapters are all tied to blocks of 4GL code. You define a result set inside a block beginning with DO, FOR, or REPEAT. The result set is generally scoped to the block where it is defined. The term result set denotes the set of rows that satisfy a query.

You learned which of the block types iterate through the result set automatically and which require you to explicitly find the next record.

Even the FIND statement itself, although it does not define a block, is subject to the same rules of record scoping. You’ve learned how the presence of record-oriented blocks like a FOR EACH block and FIND statements together define the scope of a record buffer within a procedure.

These are among the most powerful features in the Progress 4GL. They help give it its unique flexibility and strength for defining complex business logic in a way that ties data access statements closely to the logic that uses the data.

However, there are times when you don’t want your data access tied to the nested blocks of 4GL logic in your procedures. Earlier chapters briefly discussed the notion of event-driven applications. The examples you built in the chapter on the AppBuilder are a small but representative example of this. Think about the procedure h-CustOrderWin1.w, for instance. When you run this, a Customer and its Orders are displayed for you, as shown in Figure 11–1.

Figure 11–1: Customers and Orders sample window

Iterating through the data, for example through all the Customers in New Hampshire, isn’t done inside a block of 4GL logic. It’s done entirely under user control. Defining the Customer result set and its Order result set using queries is essential to this. When the user chooses the Next button, the code retrieves and displays the next record in the result set:

DO:   GET NEXT CustQuery.   IF AVAILABLE Customer THEN     DISPLAY Customer.CustNum Customer.Name Customer.Address Customer.City             Customer.State       WITH FRAME CustQuery IN WINDOW CustWin.   {&OPEN-BROWSERS-IN-QUERY-CustQuery} END.

This happens independently of the block of code where the query is defined, or where it is opened. This gives you great flexibility as a developer to let user interface events, or other programmatic events, determine the flow of the application and the processing of data. By contrast, the examples you built in "Record Buffers and Record Scope,"were chunks of logic that executed independently of the user interface, such as in the procedure h-BinCheck.p, which does some calculations and returns the results to another procedure.

This is the essence of the difference between queries and block-oriented data access. You use queries when you need to keep the data access separate from the structure of the procedure, and instead control it by events. You use block-oriented data access when you need to define business logic for data within a defined scope.

Thus queries give your data access language these important characteristics:

Scope independence — You can refer to the records in the query anywhere in your procedure.
Block independence — You aren’t required to do all your data access within a given block.
Record retrieval independence — You can move through the result set under complete control of either program logic or user events.
Repositioning flexibility — You can position to any record in the result set at any time.

Using queries to share data between procedures

A query is also a true object in Progress. It has a definition and a name, and you can use the name to access it anywhere in your procedure. You have already learned a little about handles, which give you a reference to an object that you can pass from procedure to procedure. Using a query’s handle, you can access the query and its result set from anywhere in your application session. This gives you the ability to modularize your application in ways that can’t be done with block-oriented result sets, which are not named objects and which have no meaning or visibility outside their defined scope. You’ll learn a lot more about how to use a query’s handle to access its data in later chapters that discuss dynamic data retrieval language.

Using queries to populate a browse

Another thing you observed in the chapters on the AppBuilder and the code it generates is that a query is the basis for the data definition for a browse object. You can’t populate a browse with the data from a FOR EACH block, only from a query. The browse gives you a viewport into the query. The repositioning you can do by selecting a record in the browse or scrolling through its contents reflects the repositioning that you can do programmatically through the query. In fact, selecting a row in a browse automatically repositions the query to that row, making it the current row in the buffers the query uses.

The chapters on graphical objects didn’t cover the browse control for this very reason: you need to know more about defining and using queries before you can use the browse. You’ll look at some of the many capabilities of the browse after you know how to define the queries they use.

Defining and using queries

There is a DEFINE statement for a query just as there is for other Progress objects. This is the general syntax:

DEFINE QUERY query-name FOR buffer [ , . . .] [ SCROLLING ].

The statement gives the query a name and, in turn, names the buffer or buffers that the query uses. In the simplest case, the buffers are database table names. But you can also use other buffers you’ve defined as alternatives to table names or, as you’ll learn later in this chapter, buffers for temporary tables.

If you want to reposition within the result set without using the GET FIRST, NEXT, PREV, and LAST statements, you need to define the query as SCROLLING. You’ll learn later in this section how to reposition within the result set of a scrolling query. You must also define a query that is going to be associated with a browse as SCROLLING. There is a slight performance cost to using the SCROLLING option, so you should leave it off if you are not using the capabilities it enables.

You don’t actually specify the exact data selection statement for the buffers and the tables they represent until you open the query. At that time, you can describe the joins between tables and any other parts of a WHERE clause that filter the data from the tables. As with other DEFINE statements, nothing actually happens when Progress encounters the DEFINE QUERY statement. No data is retrieved. Progress simply registers the query name and sets up storage and a handle for the query itself as an object.

OPEN and CLOSE QUERY statements

To get a query to retrieve data, you need to open it. When you open it, you specify the name of the query and a FOR EACH statement that references the buffers you named in the query definition, in the same order. If the query is already open, Progress closes the current open query and then reopens it. This is the general syntax:

OPEN QUERY query-name [ FOR | PRESELECT ] EACH record-phrase [ , . . .]      [ BY phrase ].

The syntax of the record-phrase is generally the same as the syntax for FOR EACH statements. If you use the PRESELECT EACH phrase instead of the FOR EACH phrase, then all the records that satisfy the query are selected and their row identifiers pre-cached, just as for a PRESELECT phrase in an ordinary data retrieval block. However, there are a few special cases for the record phrase in a query:

The first record phrase must specify EACH, and not FIRST, because the query is intended to retrieve a set of records. It is, however, valid to specify a WHERE clause in the record-phrase for the table that resulted in only a single record being selected, so a query can certainly have only one record in its result set. The record-phrase for any other buffers in the query can use the FIRST keyword instead of EACH if that is appropriate.
You cannot use the CAN-FIND keyword in a query definition. Doing so results in a compile-time error.
Queries support the use of an outer join between tables, using the OUTER-JOIN keyword, as explained below. FOR EACH statements outside of a query do not support the use of OUTER-JOIN.

Using an outer join in a query

An outer join between tables is a join that does not discard records in the first table that have no corresponding record in the second table. For example, consider this query definition:

DEFINE QUERY CustOrd FOR Customer, Order. OPEN QUERY CustOrd FOR EACH Customer, EACH Order OF Customer.

As Progress retrieves records to satisfy this query, it first retrieves a Customer record and then the first Order record with the same CustNum field. When you do a NEXT operation on the query, Progress locates the next Order for that Customer (if there is one), and replaces the contents of the Order buffer with the new Order. If there are no more Orders for the Customer, then Progress retrieves the next Customer and its first Order.

The question is: What happens to a Customer that has no Orders at all? The Customer does not appear in the result set for the query. The same is true for a FOR EACH block with the same record phrase. This is simply because the record phrase asks for Customers and the Orders that match them, and if there is no matching Order, then the Customer by itself does not satisfy the record phrase.

In many cases this is not the behavior you want. You want to see the Customer data regardless of whether it has any Orders or not. In this case, you can include the OUTER-JOIN keyword in the OPEN QUERY statement:

DEFINE QUERY CustOrd FOR Customer, Order. OPEN QUERY CustOrd FOR EACH Customer, EACH Order OF Customer OUTER-JOIN.

Now Progress retrieves Customers even if they have no Orders. When the Customer has no Orders, the values for all fields in the Order buffer have the unknown value.

Sorting the query results

You can specify a BY phrase on your OPEN QUERY statement just as you can in a FOR EACH block. In this case, Progress either uses an index to satisfy the sort order if possible or, if no index can allow Progress to retrieve the data in the proper order, preselects and sorts all the query results before any data is made available to the application.

GET statements

You use a form of the GET statement to change the current position within the record set that the OPEN QUERY statement defines. You’ve already seen these statements on the button triggers in the h-CustOrderWin1.w procedure you built in "Introducing the OpenEdge AppBuilder." This is the syntax for the GET statement:

GET [ FIRST | NEXT | PREV | LAST | CURRENT ] query-name.

The query must be open before you can use a GET statement. If the query involves a join, Progress populates all the buffers used in the query on each GET statement. As noted earlier, a record can remain current through multiple GET statements if a second table in the query has multiple records matching the record for the first table. This would be the case for a Customer and its Orders, for example. A series of GET NEXT statements leaves the same Customer record in its buffer as long as there is another matching Order record to read into the Order buffer.

When you first open a query, it is positioned in effect before the first record in the result set. Either a GET FIRST or a GET NEXT statement positions to the first record in the result set.

If you execute a GET NEXT statement when the query is already positioned on the last record, then the query is positioned effectively beyond the end of the result set. A GET PREV statement then repositions to the last record in the result set. Likewise, a GET PREV statement executed when already on the first record results in the query being positioned before the first record, and a GET FIRST or GET NEXT statement repositions to the first record. When the query is repositioned off the beginning or off the end of the result set, no error results. You can use the AVAILABLE function that you’re already familiar with to check whether you have positioned beyond the result set. For example, this code opens a query and cycles through all the records in its result set, simply counting them as it goes:

DEFINE QUERY CustOrd FOR Customer, Order. DEFINE VARIABLE iCount AS INTEGER     NO-UNDO.     OPEN QUERY CustOrd FOR EACH Customer, EACH Order OF Customer.     GET FIRST CustOrd.     DO WHILE AVAILABLE(Customer):         iCount = iCount + 1.         GET NEXT CustOrd.     END.     DISPLAY iCount.

Figure 11–2 shows the result.

Figure 11–2: Result of GET statement example

The GET FIRST statement is needed to make a record available before the IF AVAILABLE statement is first encountered. Otherwise, the AVAILABLE test would fail before the code ever entered the loop.

Note also that the AVAILABLE function must take a buffer name as its argument, not the name of the query. If the query involves an outer join, then you should be careful about which buffer you use in the AVAILABLE function. If you name a buffer that could be empty because of an outer join (such as an empty Order buffer for a Customer with no Orders), then your loop could terminate prematurely. On the other hand, you might want your application logic to test specifically for the presence of one buffer or another in order to take special action when one of the buffers has no record.

Using the QUERY-OFF-END function

There is a built-in Progress function that you can use for the same purpose as the AVAILABLE statement:

QUERY-OFF-END ( query-name ).

QUERY-OFF-END is a logical function that returns true if the query is positioned either before the first result set row or after the last row, and false if it is positioned directly on any row in the result set. The query-name parameter must be either a quoted literal string with the name of the query or a variable name that has been set to the name of the query. In this way, you can use the statement programmatically to test potentially multiple different active queries in your procedure. For example, here is the same procedure used above, this time with the QUERY-OFF-END function in place of AVAILABLE:

DEFINE QUERY CustOrd FOR Customer, Order. DEFINE VARIABLE iCount AS INTEGER     NO-UNDO.     OPEN QUERY CustOrd FOR EACH Customer, EACH Order OF Customer.     GET FIRST CustOrd.     DO WHILE NOT QUERY-OFF-END('CustOrd'):         iCount = iCount + 1.         GET NEXT CustOrd.     END.     DISPLAY iCount.

The difference between QUERY-OFF-END and AVAILABLE is simply that AVAILABLE requires a buffer name as a parameter, whereas QUERY-OFF-END requires a query name. If you use the AVAILABLE function with the name of the first buffer in the query, it is equivalent to using QUERY-OFF-END with the query name. Just for stylistic reasons, it is more appropriate to use the QUERY-OFF-END function in most cases, since it is the position of the query and not the presence of a record in a particular buffer that you’re really interested in. By contrast, if you really want to test for the presence of a record, especially when your query does an outer join that might not always retrieve a record into every buffer, then use the AVAILABLE function.

Closing a query

When you are done with a query, you should close it using this statement:

CLOSE QUERY query-name.

An OPEN QUERY statement automatically closes a query if it was previously open. For this reason, it isn’t essential to execute a CLOSE QUERY statement just before reopening a query. However, you should explicitly close a query when you are done with it and you are not immediately reopening it. This frees the system resources used by the query. After you close the query you cannot reference it again (with a GET statement, for instance). However, if there are records still in the buffer or buffers used by the query, they are still available after the query is closed unless your application has specifically released them.

Determining the current number of rows in a query

You can use the NUM-RESULTS function to determine how many rows there are in the current results list:

NUM-RESULTS ( query-name )

This INTEGER function returns the number of rows currently in the query’s results list. As with the QUERY-OFF-END function, the query-name is an expression, which can be either the quoted name of the query or a variable containing the name.

The phrase “currently in the query’s results list” requires some explanation. The results list is a list of the row identifiers of all rows that satisfy the query, and that have already been retrieved.

Progress normally builds up the results list as you walk through the query using the GET statement. Therefore, when you first open a query with a FOR EACH clause in the OPEN QUERY statement, the results list is empty and the NUM-RESULTS function returns zero.

As you move through the query using the GET NEXT statement, Progress adds each new row’s identifier to the results list and increments the value returned by NUM-RESULTS. For example, this example retrieves all the Customers in the state of Louisiana using a query. For each row, it displays the Customer Number, Name, and the value of NUM-RESULTS:

DEFINE QUERY CustQuery FOR Customer. OPEN QUERY CustQuery FOR EACH Customer WHERE State = "LA". GET FIRST CustQuery. DO WHILE NOT QUERY-OFF-END("CustQuery"):     DISPLAY Customer.CustNum Customer.NAME             NUM-RESULTS("CustQuery") LABEL "Rows"         WITH FRAME CustFrame 15 DOWN.     GET NEXT CustQuery.     DOWN WITH FRAME CustFrame. END.

When you run the procedure, you see the value of NUM-RESULTS change as each new row is retrieved, as shown in Figure 11–3.

Figure 11–3: Result of NUM-RESULTS example

Using a DOWN frame and the DOWN WITH statement

As a small digression, it is necessary to explain a couple of statements in this example that you haven’t seen before. They illustrate one of the key characteristics about queries: there’s no built-in block scoping or iteration in a query. First, here’s the new phrase on the DISPLAY statement:

WITH FRAME CustFrame 15 DOWN.

You learned a little about down frames in "Using Basic 4GL Constructs," and "Examining the Code the AppBuilder Generates." A down frame is a frame that can display more than one row of data, each showing the same fields for a different record in a report-like format. In the examples you wrote in earlier chapters, you didn’t have to specify the DOWN phrase to indicate how many rows the frame should have. Progress gave you a down frame with a default number of rows automatically.

Why doesn’t it do that in this case? Because a query is not associated with a particular block, and doesn’t have any automatic iteration, Progress doesn’t know how the data is going to be displayed. So by default, it just gives you a one down frame that displays a single record.

The second new piece of syntax is this statement at the end of the block:

DOWN WITH FRAME CustFrame.

No, this is not a political protest slogan! Rather, it tells Progress to display a row in the frame CustFrame, and then to position down a row in the frame before displaying the next row. If you don’t use this statement, then even if you define the frame to be 15 DOWN, all the rows are displayed on top of each other on the first row of the frame. Once again, this is because Progress doesn’t know how you’re going to display the data. It does not associate iteration through a result set with your DO block automatically, as it would with a FOR EACH block. Therefore, you have to tell it what to do.

Retrieving query results in advance

The value of NUM-RESULTS does not always increment as you execute GET NEXT statements and operate on each row, however. There are various factors that force Progress to retrieve all the results in advance of presenting you with any data.

One of these is under your direct control: the PRESELECT option on the OPEN QUERY statement. When you use a PRESELECT EACH rather than a FOR EACH statement to define the data selection, you are telling Progress to retrieve all the records that satisfy the query in advance and to save off their record identifiers in temporary storage. Then Progress again retrieves the records using their identifiers as you need them. As discussed in "OPEN and CLOSE QUERY statements" , you typically use the PRESELECT option to make sure that the set of records is not disturbed by changes that you make as you work your way through the list, such as changing a key value in such a way as to change a record’s position in the list.

To see visible evidence of the effect of the PRESELECT keyword in your OPEN QUERY statement:

Change the OPEN QUERY statement in the sample procedure:

OPEN QUERY CustQuery PRESELECT EACH Customer WHERE State = "LA".

Run the procedure again to see the different value of NUM-RESULTS:

All the records are pre-retrieved. Therefore, the value of NUM-RESULTS is the same no matter what record you are positioned to. This means that you could use the PRESELECT option to display, or otherwise make use of, the total number of records in the results list before displaying or processing all the data.

Another factor that can force Progress to pre-retrieve all the data is a sort that cannot be satisfied using an index.

To see an example of this:

Change the OPEN QUERY statement back to use a FOR EACH block and then try sorting the data in the query by the Customer Name:

OPEN QUERY CustQuery FOR EACH Customer WHERE State = "LA" BY Name.

Run the query:



The Name field is indexed, so Progress can satisfy the BY phrase and present the data in the sort order you want by using the index to traverse the database and retrieve the records.
By contrast, try sorting on the City field:

OPEN QUERY CustQuery FOR EACH Customer WHERE State = "LA" BY City.

Add the City field to the DISPLAY list and rerun the procedure to see the result:



There is no index on the City field, so Progress has to retrieve all 13 of the records for Customers in Louisiana in advance to sort them by the City field before presenting them to your procedure. Therefore, NUM-RESULTS is equal to the total number of records from the beginning, as soon as the query is opened.

Identifying the current row in the query

As you move through the results list, Progress keeps track of the current row number, that is, the sequence of the row in the results list. You can retrieve this value using the CURRENT-RESULT-ROW function:

CURRENT-RESULT-ROW ( query-name )

The function returns an INTEGER value with the sequence of the current row. The query-name is an expression, either a quoted query name or a variable reference.

For CURRENT-RESULT-ROW to work properly, you must define the query to be SCROLLING. If you don’t define the query as SCROLLING, the CURRENT-RESULT-ROW function returns a value, but that value is not reliable.

To use CURRENT-RESULT-ROW, make these changes to your sample procedure:

DEFINE QUERY CustQuery FOR Customer SCROLLING. OPEN QUERY CustQuery FOR EACH Customer WHERE State = "LA". GET FIRST CustQuery. DO WHILE NOT QUERY-OFF-END("CustQuery"):     DISPLAY Customer.CustNum Customer.NAME             NUM-RESULTS("CustQuery") LABEL "Rows"             CURRENT-RESULT-ROW("CustQuery") LABEL "Row#"         WITH FRAME CustFrame 15 DOWN.     GET NEXT CustQuery.     DOWN WITH FRAME CustFrame. END.

When you run the procedure, you see that the value of CURRENT-RESULT-ROW keeps pace with NUM-RESULTS, as shown in Figure 11–4.

Figure 11–4: Result of CURRENT-RESULT-ROW example

This is not always the case, of course. If you use the PRESELECT option or a nonindexed sort to retrieve the data, then NUM-RESULTS is always 13, as you have seen. But the value of CURRENT-RESULT-ROW changes from 1 to 13 just as it does above.

You can use the CURRENT-RESULT-ROW function to save off a pointer to reposition to a specific row. See the "Using a RowID to identify a record" for information on how to identify a record.

Here are a few special cases for CURRENT-RESULT-ROW:

If the query is empty, the function returns the unknown value (?).
If the query is explicitly positioned before the first row, for example by executing a GET FIRST followed by a GET PREV, then the function returns the value 1.
If the query is explicitly positioned after the last row, for example by executing a GET LAST followed by a GET NEXT, then the function returns the value one more than the number of rows in the results list.

Using INDEXED-REPOSITION to improve query performance

If you anticipate jumping around in the result set using statements such as GET LAST, you should add another option to the end of your OPEN QUERY statement: the INDEXED-REPOSITION keyword. If you do this, your DEFINE QUERY statement must also specify the SCROLLING keyword.

If you don’t open the query with INDEXED-REPOSITION, then Progress retrieves all records in sequence in order to satisfy a request such as GET LAST. This can be very costly. If you do use INDEXED-REPOSITION, Progress uses indexes, if possible, to jump directly to a requested row, greatly improving performance in some cases. There are side effects to doing this, however, in terms of the integrity of the results list, as discussed next.

Factors that invalidate CURRENT-RESULT-ROW and NUM-RESULTS

Under some circumstances, when you open your query with the INDEXED-REPOSITION keyword, the value of CURRENT-RESULT-ROW or NUM-RESULTS becomes invalid. As explained earlier, the results list holds the row identifiers for those rows that satisfy the query and that have already been retrieved.

Thirteen rows satisfy the query for Customers in Louisiana, so the value of these two functions goes as high as 13 for that query. When you do a PRESELECT or a nonindexed sort, all the rows have already been retrieved before any data is presented to you, so NUM-RESULTS is 13 at the beginning of the DISPLAY loop. Normally, Progress adds the identifiers for all the rows it retrieves to the results list, but there are circumstances where this is not the case. If you execute a GET LAST statement on a query, and your OPEN QUERY statement does not use a PRESELECT or a sort that forces records to be pre-retrieved, Progress jumps directly to the last record using a database index, without cycling through all the records in between. In this case, it has no way of knowing how many records would have been retrieved between the first one and the last one, and it cannot maintain a contiguous results list of all rows that satisfy the query. For this reason, Progress flushes and reinitializes the results list when you jump forward or backward in the query. So after a GET LAST statement, NUM-RESULTS returns 1 (because the GET LAST statement has retrieved one row) and CURRENT-RESULT-ROW is unknown (because there is no way to know where that row would fit into the full results list).

Repositioning a query

Often you need to reposition a query other than to the first, last, next, or previous row. You might need to jump to a row based on data the user entered or return to a row that you previously saved off. Or you might want to jump forward or backward a specific number of rows to simulate paging through the query. You can do all these things with the REPOSITION statement, which has this syntax:

REPOSITION query-name     { |TO ROW row-number        |FORWARDS n        |BACKWARDS n        |TO ROWID buffer-1-rowid [, . . .] [ NO-ERROR ]     }

The query-name in this case is not an expression. It can only be an unquoted query name, not a variable.

If you specify the TO ROW option followed by an integer expression, the query repositions to that sequential position within the results list. If you have previously saved off that position using the CURRENT-RESULT-ROW function, you can use the value that function returned as the value in the TO ROW phrase to reposition to that row.

If you use the FORWARDS or BACKWARDS phrase, you can jump forward or backward any number of rows, specified by the n integer expression. You can use the FORWARD or BACKWARD keywords instead of FORWARDS or BACKWARDS.

The last of the REPOSITION options requires an explanation of a Progress data construct you haven’t seen before.

Using a RowID to identify a record

Every record in every table of a database has a unique row identifier. (Technically, the row identifier is only unique within a single storage area of a database. Since an entire database table must be allocated to a single storage area, this effectively makes the identifier unique at least within that table. A discussion of database constructs such as storage areas is beyond the scope of this book.)

The identifier is called a RowID. There is both a ROWID data type that allows you to store a row identifier in a procedure variable and a ROWID function to return the identifier of a record from its record buffer.

Generally, you should consider a RowID to be a special data type without being concerned about its storage format. The RowID is (among other things) designed to be valid, not just for the OpenEdge database, but for all the different databases you can access from the 4GL using OpenEdge DataServers, which provide access from the 4GL to database types such as Oracle and Microsoft SQLServer.

In fact, you can’t display a RowID directly in a Progress 4GL procedure. If you try to, you get an error. You can see a RowID by converting it to a CHARACTER type using the STRING function. For instance, here is a procedure that shows you the RowIDs of the rows that satisfy the sample query you’ve been working with:

DEFINE QUERY CustQuery FOR Customer SCROLLING. OPEN QUERY CustQuery FOR EACH Customer WHERE State = "LA". GET FIRST CustQuery. DO WHILE NOT QUERY-OFF-END("CustQuery"):     DISPLAY Customer.CustNum Customer.NAME             CURRENT-RESULT-ROW("CustQuery") LABEL "Row#"             STRING(ROWID(customer)) FORMAT "x(12)" LABEL "RowId"         WITH FRAME CustFrame 15 DOWN.     GET NEXT CustQuery.     DOWN WITH FRAME CustFrame. END.

Figure 11–5 shows the result.

Figure 11–5: Result of RowID example

The RowID is displayed as a hexadecimal value. The values you would see in your own copy of the Sports2000 database might be different from these, and certainly they would be different if you modified the data, dumped it, and reloaded it into the database, because the RowID reflects the actual storage location of the data for the record, and this is not in any way predictable or necessarily repeatable. You should never count on a RowID as a permanent identifier for a record. However, you can use a RowID if you need to relocate a record you have previously retrieved within a procedure and whose RowID you saved off. This is what the TO ROWID phrase in the REPOSITION statement is for.

Even in this case, you must be aware that in the event of a record deletion, it is possible that a new record could be created that has the same RowID as the record that was deleted. So, even within a single session a RowID is not an absolutely reliable pointer to a record. In addition, RowIDs are unique only within a single database storage area. Therefore, the same RowID might occur for records in different tables that happen to be in different storage areas.

With these conditions in mind, you can use the TO ROWID phrase to reposition to a record in a query. Note that the RowID is for a particular database record, not an entire query row, so you need to save off the RowID of the record buffer, not of the query name, to reuse it. And in the case of a query with a join between tables, you need to save the RowID of each record buffer in order to reposition to it later and restore each of the records in the join.

The NO-ERROR option in this phrase lets you suppress an error message if the RowID turns out to be invalid for any reason. You could then use the AVAILABLE function or the ERROR-STATUS handle (see "Managing Transactions") to determine whether the query was successfully repositioned.

There is another similar identifier in Progress called a RECID. This identifier was used in earlier versions of Progress to identify records in the same way as RowIDs do now. The RECID is essentially an integer identifier for a record, though it has its own data type. It is still supported but for several reasons (including, but not limited to, portability of code between database types that you can access with DataServers), it is strongly recommended that you use only the RowID form in new application code. The Progress 4GL continues to support RECIDs mainly for backward compatibility with older applications that still use them.

Positioning details with the REPOSITION statement

So if you execute a REPOSITION statement that repositions TO ROW 5 or FORWARDS 10 or TO ROWID rRow, then your procedure is positioned to that row so that you can display it or otherwise use it, right? Well, not exactly. When you use the REPOSITION statement, Progress positions the query in between records, so that you then have to execute a GET NEXT or GET PREV statement to position on a row so that you can actually use it. Here are some of the specifics:

When you execute a REPOSITION statement that uses the TO ROWID or TO ROW phrase, the query is positioned before the record requested. You then need to execute a GET NEXT statement to make the row you want available.
When you execute a REPOSITION query-name BACKWARDS statement, the query is positioned between records, and you need to execute a GET NEXT statement to make the row you intend available. For example, if the query is positioned to row 6 of a query results list and you execute a REPOSITION query-name BACKWARDS 2, then the query is positioned effectively between results list rows 3 and 4, and a GET NEXT statement makes row 4 available. A GET PREV statement makes row 3 available.
When you execute a REPOSITION statement with the FORWARDS phrase, you are actually positioned beyond the record you might expect. For example, if you are on row 6 and issue a statement with FORWARDS 2, then the query is positioned between row 8 and row 9, and you need to then execute a GET PREV statement to make row 8 available or a GET NEXT statement to make row 9 available.

Note as well that there is only one way for Progress to know which row is five rows ahead of the current row, or five rows behind it, or the fifth row of the result set, and that is to walk through the query row by row until it gets to the one you want. If the row in question has already been retrieved and is in the results list, then Progress can reposition to the row you want very quickly using the results list. Therefore, the REPOSITION statement with any of the options TO ROW, FORWARDS, or BACKWARDS maintains the integrity of the results list and the functions that use it. The REPOSITION TO ROWID statement also maintains the integrity of the list if the row you want has already been retrieved and the results list has not been flushed since you retrieved it, because Progress can scan the results list to locate the RowID.

Extending the sample window to use the queries

Now you can try out some of the things you’ve learned about queries.

To extend the sample application window to use some query statements:

Open the procedure h-CustOrderWin1.w.
This gives you the test procedure with Customer fields and the Order browse but without some of the other graphical objects you added later.
Extend the window somewhat to the right, then drop two buttons onto the window.
Name the first button PosButton and give it a label of Save Position.
Name the second button ReposButton and give it a label of Restore Position.
Open the Section Editor and select the Definitions section.
Define a variable to hold a value for the Customer query’s CURRENT-RESULT-ROW:

/* Local Variable Definitions --- */ DEFINE VARIABLE iQueryRow AS INTEGER     NO-UNDO.

Remember that the Definitions section is scoped to the entire procedure file, so anything you define here is available to any trigger or internal procedure inside it.
Go into the Triggers section for the PosButton and give it this CHOOSE trigger:

DO:   iQueryRow = CURRENT-RESULT-ROW('CustQuery'). END.

Define this CHOOSE trigger for the ReposButton:

DO:     REPOSITION CustQuery TO ROW iQueryRow.     APPLY "CHOOSE" TO BtnNext. END.

When you choose the Save Position button, your code saves off the current row number from the results list. You can then move around in the query. When you press the Restore Position button, the Customer query is repositioned to the row you saved, and the Order query is opened for that Customer.
Why did you need the APPLY “CHOOSE” TO BtnNext ? statement. Remember that when you use the REPOSITION statement Progress positions before the record you want, so you need to execute a GET NEXT to make it available. At the same time, in this case, your code needs to reopen the dependent Order query for the Customer as well. All this is done by the trigger code on the Next button.
Run the procedure and test saving off and restoring the current row.
Add a fill-in field to give you a reason to test reopening the query with a different WHERE clause:
Drop a fill-in on the window. Call it cState and give it a label of New State.
Write this LEAVE trigger for the fill-in field in the Section Editor:

DO:     OPEN QUERY CustQuery FOR EACH Customer WHERE State =         cState:SCREEN-VALUE.     APPLY "CHOOSE" TO BtnFirst. END.

Each time you enter a state abbreviation in the field and tab out of it, the trigger fires and the query is reopened with that new state. Remember that it isn’t necessary to close a query explicitly if you are immediately going to reopen it, so a CLOSE QUERY CustQuery statement here is optional. Also remember that unless you specifically assign it using the ASSIGN statement, the value the user types into the fill-in exists only in the frame’s screen buffer, so you can retrieve it using the SCREEN-VALUE attribute of the field.
Similarly for the CHOOSE trigger that repositions to the previously saved row, you need to apply CHOOSE to the First button trigger to get the first row for the new query and reopen the Order query.
Run your procedure. You can enter a state name and tab out of the New State field and see the Customers in that state along with their Orders:



If you enter an invalid state name, the procedure doesn’t give you any feedback to confirm this. In the next section, you make a few more changes to the procedure to check whether there were any results for the state that is entered. This makes use of the NUM-RESULTS function and also introduces you to another new and useful 4GL statement.

Using NUM-RESULTS to check the validity of the query

The query is sorted by the City, which is a nonindexed field. Therefore, the NUM-RESULTS function returns the total number of records that satisfy the query as soon as it is opened, because they all have to be retrieved before they can be sorted.

Next, you add another field to the screen to display that number, and then check the same value in the trigger for the New State field to make sure that the state entered was valid

To add another field to the screen:

Drop another fill-in field onto the window. Name it iMatches and give it a Label of Number of Matches.
To display initial values for the New State and Number of Matches fields, you’ll put some code into the Main Block of the procedure, to be executed right after the query is initially opened.
In the Section Editor, select the Main Block and add this code after the RUN enable_UI statement:

DO ON ERROR    UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK     ON END-KEY UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK:     RUN enable_UI.   ASSIGN cState = Customer.State         iMatches = NUM-RESULTS("CustQuery").   DISPLAY cState iMatches WITH FRAME CustQuery. END.

This displays the initial value of the Customer State (“NH”) along with the number of Customers in New Hampshire. Remember that, in this case, CustQuery is both the name of the query and also the name of the frame the AppBuilder created for you.
Edit the LEAVE trigger for the New State field again. Add a statement before the OPEN QUERY statement to save off the current State in the cState field, and a statement following the OPEN QUERY statement to retrieve the NUM-RESULTS value for the new query:

cState = Customer.State.   OPEN QUERY CustQuery FOR EACH Customer WHERE Customer.State =        cState:SCREEN-VALUE BY Customer.City.        /* Check whether the State was valid or not. */   iMatches = NUM-RESULTS("CustQuery").

Remember that the value you assign to cState is not overwritten by the SCREEN-VALUE that you type into the field on the screen, so you can open the query based on the SCREEN-VALUE and still keep the old value around if you need it later (which you will).

Using the MESSAGE statement

Next you need to display a warning message if there are no results, which means that no Customer records match the state value that was entered. Progress has a MESSAGE statement to display a message to the screen or to a message area at the bottom of the window if there is one. A MESSAGE statement can contain one or more character expressions (quoted literals or CHARACTER variables) that make up the message. If there are multiple expressions in the statement, Progress simply concatenates them all, with a single space between them. You can also insert the SKIP keyword anywhere in the message to skip a line, or SKIP(n) to skip n lines.

If you want the message to appear in its own alert box, you can include the phrase VIEW-AS ALERT-BOX in the statement. This is the default if the message is displayed from a GUI window. If you want to give the message alert box a special format, you can include one of the QUESTION, INFORMATION, ERROR, or WARNING keywords after the VIEW-AS ALERT-BOX phrase. The MESSAGE statement has other features, including the ability to display different sets of buttons, beyond just an OK button to acknowledge the message. It can also capture an answer from the message and store it in a variable. You can see all the syntax details in OpenEdge Development: Progress 4GL Reference or in the online help topic for the MESSAGE statement.

To display a warning message that tells you that there are no matching Customers:

In the New State trigger, add this code:

IF iMatches = 0 THEN   DO:       MESSAGE "There are no Customers that match the State"           cState:SCREEN-VALUE "." SKIP           "Restoring the previous State."           VIEW-AS ALERT-BOX WARNING.

Reopen the query using the value of the State field that you saved off in the cState variable and redisplay the previous valid New State value:

/* Reopen the query with the previous state. */       OPEN QUERY CustQuery FOR EACH Customer WHERE Customer.State =       cState BY Customer.City.       /* Display the previous valid state as well. */       DISPLAY cState WITH FRAME CustQuery.   END.

This ends the DO block that is executed if NUM-RESULTS is zero.
To display the number of matching Customers if NUM-RESULTS is not zero, add the following code:

ELSE DISPLAY iMatches WITH FRAME CustQuery.

Add the following code so that, regardless of whether the New State was valid, the statement executes APPLY “CHOOSE” to the First button to do a GET FIRST and opens the Order query:

APPLY "CHOOSE" TO BtnFirst.

Save the procedure as h-CustOrderWin4.w.
Run the procedure to see the number of matching records for any State you enter:



To see your warning message, enter an invalid State value, such as QQ:

Summary

This ends the discussion of using queries in your application. In this chapter, you’ve learned how to define and use queries and what the differences are between queries and other Progress 4GL data access statements.

The next chapter discusses how to define and use temporary tables. At the end of the next chapter, you’ll be using queries and temp-tables together.