Chapter 25 Queries - FWD - Golden Code Redmine

Queries¶

Queries
- Data Access Language Statements

Database access is a core feature of the Progress 4GL. A variety of language statements exist in the language to access data in different ways. For instance, FIND statements are used to access individual records from a single table at a time, FOR statements are used to access records in a loop construct that is a natural part of the language, DEFINE/OPEN QUERY statements are used to access database records in result sets.

Progress database access language statements are converted to various FWD query types. These query types are implemented as a hierarchy of classes, each of which implements the com.goldencode.p2j.persist.P2JQuery interface. Here is the hierarchy for this interface:

These runtime classes generally map to the various 4GL data access statements; however, the correspondence is not necessarily one-to-one. The same language statement may convert to different FWD query types, based upon how tables are accessed and joined, and which 4GL options are used.

The following section describe how different FWD query types are used for backing different 4GL constructions and statements. The Progress 4GL statements - along with any qualifying notes - are listed in the left-hand column of the table. The Java classes to which they are mapped (all found in the com.goldencode.p2j.persist package) are in the right-hand column of the table.

FIND¶

Progress keyword	FWD equivalent
`FIND [ FIRST \| LAST \| NEXT \| PREV ]` Is not used for iteration of preselected results.	`FindQuery`
`FIND { NEXT \| PREV }` Inside a DO (looping) / REPEAT block, is not used for iteration of preselected results..	`AdaptiveFind`
`CAN-FIND`	`FindQuery`
`FIND [ FIRST \| LAST \| NEXT \| PREV ]` Used for iteration of preselected results.	Not converted to a separate query. Converted to an iteration command for the preselect query bound to the target preselect block.

FOR¶

Progress keyword	FWD equivalent
`FOR EACH ... [BY]` BY clause is not specified or it specifies a sort string which matches an index. The term “matches” means that the sort string components match the leading components of the index or the sort string matches the index completely. An index can be matched in forward and inverse directions.	`AdaptiveQuery`
`FOR EACH ... BY` BY clause that does not match any index.	`PreselectQuery`
`FOR [ FIRST \| LAST ] ... [BY]`	`RandomAccessQuery`
`FOR EACH ... [BREAK] BY` BY clause cannot be represented by an equivalent SQL construction or BREAK keyword presents.	`PresortQuery`
`FOR EACH ..., EACH ... BY` BY clause contains fields of non-outermost tables. All tables are within the same database.	`PreselectQuery`
`FOR EACH ..., EACH ... [BREAK] BY` BY clause cannot be represented by an equivalent SQL construction or BREAK keyword presents. All tables are within the same database.	`PresortQuery`
`FOR [ EACH \| FIRST \| LAST ] ..., [ EACH \| FIRST \| LAST ] ... [BY]` Does not contain BY clause or BY clause contains only fields of the outermost table. Tables may be in the same or different databases.	`CompoundQuery` `EACH` non-outermost components are represented by `AdaptiveQuery` sub-queries. Outermost `EACH` component is represented by: • an `AdaptiveQuery` sub-query if BY clause is not specified or it matches an index of this table; • a `PreselectQuery` sub-query if BY clause does not match any index of this table. `FIRST,` `LAST` and unique components (both outermost and non-outermost) are represented by `RandomAccessQuery` sub-queries.
`FOR { EACH \| FIRST \| LAST } ..., { EACH \| FIRST \| LAST } ... [BREAK] BY` BY clause contains fields of non-outermost tables or it cannot be represented by an equivalent SQL construction or BREAK keyword presents. Tables cannot be joined at database level (FIRST / LAST components are used or tables are joined across databases).	`PresortCompoundQuery` `EACH` components are represented by `PreselectQuery` sub-queries. `FIRST/LAST` components are represented by `RandomAccessQuery` sub-queries.

DO/REPEAT PRESELECT¶

Progress keyword	FWD equivalent
`{ DO \| REPEAT } PRESELECT EACH ... [BY]`	`PreselectQuery`
`{ DO \| REPEAT } PRESELECT EACH ... [BREAK] BY` BY clause cannot be represented by an equivalent SQL construction or BREAK keyword presents.	`PresortQuery`
`{ DO \| REPEAT } PRESELECT EACH ..., EACH ... [BY]` All tables are within the same database.	`PreselectQuery`
`{ DO \| REPEAT } PRESELECT EACH ..., EACH ... [BREAK] BY` BY clause cannot be represented by an equivalent SQL construction or BREAK keyword presents All tables are within the same database.	`PresortQuery`
`{ DO \| REPEAT } PRESELECT { EACH \| FIRST \| LAST } ..., { EACH \| FIRST \| LAST } ... [BREAK] [BY]` BY clause, if specified, can either be simple or contain expressions that cannot be represented by equivalent SQL constructions or BREAK keyword can present. Tables cannot be joined at database level (FIRST / LAST components are used or tables are joined across databases).	`PresortCompoundQuery` `EACH` components are represented by `PreselectQuery` sub-queries. `FIRST/LAST` components are represented by `RandomAccessQuery` sub-queries.

DEFINE/OPEN QUERY¶

Progress keyword	FWD equivalent
`DEFINE QUERY`	`QueryWrapper`
`OPEN QUERY FOR EACH ... [BY]` BY clause is not specified or it specifies a sort string which matches an index. The term “matches” means that the sort string components match the leading components of the index or the sort string matches the index completely. An index can be matched in forward and inverse directions.	`AdaptiveQuery`
`OPEN QUERY FOR EACH ... BY` BY clause that does not match any index.	`PreselectQuery`
`OPEN QUERY PRESELECT EACH ... [BY]`	`PreselectQuery`
`OPEN QUERY { FOR \| PRESELECT } EACH ... BY` BY clause cannot be represented by an equivalent SQL construction.	`PresortQuery`
`OPEN QUERY FOR EACH ..., EACH ... BY` BY clause contains fields of non-outermost tables. All tables are within the same database.	`PreselectQuery`
`OPEN QUERY PRESELECT EACH ..., EACH ... [BY]` All tables are within the same database.	`PreselectQuery`
`OPEN QUERY { FOR \| PRESELECT } EACH ..., EACH ... BY` BY clause cannot be represented by an equivalent SQL construction. All tables are within the same database.	`PresortQuery`
`OPEN QUERY FOR EACH ..., { EACH \| FIRST \| LAST } ... [BY]` Does not contain BY clause or BY clause contains only fields of the outermost table. Tables may be in the same or different databases.	`CompoundQuery` `EACH` non-outermost components are represented by `AdaptiveQuery` sub-queries. `FIRST` and `LAST` components (allowed only as non-outermost) are represented by `RandomAccessQuery` sub-queries. The outermost `EACH` component is represented by: • an `AdaptiveQuery` sub-query if BY clause is not specified or it matches an index of this table; • a `PreselectQuery` sub-query if BY clause does not match any index of this table.
`OPEN QUERY FOR EACH ..., { EACH \| FIRST \| LAST } ... BY` BY clause contains fields of non-outermost tables or it cannot be represented by an equivalent SQL construction. Tables cannot be joined at database level (FIRST / LAST components are used or tables are joined across databases).	`PresortCompoundQuery` `EACH` components are represented by `PreselectQuery` sub-queries. `FIRST/LAST` components are represented by `RandomAccessQuery` sub-queries.
`OPEN QUERY PRESELECT EACH ..., { EACH \| FIRST \| LAST } ... [BY]` BY clause, if specified, can either be simple or contain expressions that cannot be represented by equivalent SQL constructions. Tables cannot be joined at database level (FIRST / LAST components are used or tables are joined across databases).	`PresortCompoundQuery` `EACH` components are represented by `PreselectQuery` sub-queries. `FIRST/LAST` components are represented by `RandomAccessQuery` sub-queries.

Data Access Language Statements¶

The conversion of various 4GL language statements which access data from the database are discussed in the immediately following sections. Topics which are common to all query types, such as WHERE clause conversion, index selection, locking, and error processing are discussed separately, later in this chapter.

FIND¶

4GL Form	Description	Supported	Notes
`FIND` `[ FIRST \| NEXT \| PREV \| LAST ] {record}` `[ {constant} ]` `[ OF {table} ]` `[ WHERE {expression} ]` `[ USE-INDEX {index} ]` `[ USING [ FRAME {frame} ] {field}` `[ AND [ FRAME {frame} ] {field} ] ... ]` `[ SHARE-LOCK \| EXCLUSIVE-LOCK \| NO-LOCK ]` `[ NO-WAIT ]` `[ NO-PREFETCH ]` `[ NO-ERROR ]`	Finds a single record, possibly relative to a record already in the target buffer from a previous read. Often used sequentially (e.g., within loops) to find multiple records, one after the other, and ordered relative to one another. Relies upon an index to both find and order retrieved records.	Yes	The NO-PREFETCH option is ignored by conversion. If the `{constant}` option is used, the conversion calculates the appropriate criterion expression, based on the primary index of the target table, and enhances (or creates) the WHERE clause. Also see sections on Index Selection, Where Clause Processing, and Record Locks.
`FIND CURRENT {record}` `[ SHARE-LOCK \| EXCLUSIVE-LOCK \| NO-LOCK ]` `[ NO-WAIT ]` `[ NO-ERROR ]`	Re-fetch the record currently in the target buffer, but with a different lock type.	Yes	Also see section on Record Locks.

Both forms of the FIND statement generally convert to instances of com.goldencode.p2j.persist.FindQuery, though under certain circumstances, com.goldencode.p2j.persist.AdaptiveFind may be used instead, as an optimization.

FindQuery¶

FindQuery has various methods which implement the navigational keywords converted from Progress code (i.e., FIRST, NEXT, PREV, LAST). These are actually implemented in FindQuery's superclass, com.goldencode.p2j.persist.RandomAccessQuery:

Progress Keyword	FindQuery (RandomAccessQuery) Methods
FIRST	`first()` `first(LockType lockType)` `first(Object[] values)` `first(Object[] values, LockType lockType)`
NEXT	`next()` `next(LockType lockType)` `next(Object[] values)` `next(Object[] values, LockType lockType)`
PREV	`previous()` `previous(LockType lockType)` `previous(Object[] values)` `previous(Object[] values, LockType lockType)`
LAST	`last()` `last(LockType lockType)` `last(Object[] values)` `last(Object[] values, LockType lockType)`
CURRENT	`current()` `current(LockType lockType)`

where:

lockType is the type of lock to acquire on the found record (LockType.NONE, LockType.SHARE, LockType.EXCLUSIVE);

values is an array of data values which are to be substituted for HQL query parameters when the query is executed.

The most common conversion outcome for a FIND statement is a FindQuery object. For example, consider the external procedure find/find.p, which contains only a simple FIND FIRST statement:

FIND FIRST book.

In this first example, we will take a look at the entire Java program generated. In subsequent examples, we will focus only on the section(s) relevant to the query statement being discussed. Details regarding the “scaffolding” code (e.g., package statement, import statements, class declaration, execute() method, etc.) are discussed in other chapters, but are reviewed briefly here for convenience.

The find/find.p program converts to Java as follows:

package com.goldencode.testcases.find;

import com.goldencode.p2j.util.*;
import com.goldencode.p2j.persist.*;
import com.goldencode.testcases.dmo.p2j_test.*;

import static com.goldencode.p2j.util.BlockManager.*;

/**
 * Business logic (converted to Java from the 4GL source code
 * in find/find.p).
 */
public class Find
{
   Book book = RecordBuffer.define(Book.class, "p2j_test", "book");

   /**
    * External procedure (converted to Java from the 4GL source code
    * in find/find.p).
    */
   public void execute()
   {
      externalProcedure(new Block()
      {
         public void body()
         {
            RecordBuffer.openScope(book);
            new FindQuery(book, (String) null, null, "book.bookId asc").first();
         }
      });
   }
}

The first line of the Java program designates the Java package in which the converted program resides. In this case, the conversion was configured to emit source code into the base package com.goldencode.testcases. However, since the original program was located in the “find” subdirectory of its project, the fully qualified package name is com.goldencode.testcases.find.

The next four lines are Java import statements generally necessary for the Java compiler to identify and disambiguate unqualified Java class or static method names.

By default, the Progress program find.p converts to a Java class named Find, located in a file named Find.java. Hence, the Java class in this example is declared as Find.

The Find class contains one instance variable: book (a com.goldencode.testcases.dmo.p2j_test.Book). The purpose of variables of this type is discussed at length in the Record Buffers chapter of this reference. For our purposes here, book is an instance of a Data Model Object (DMO) used to access/contain the database record found by the converted FIND FIRST statement. It is an explicit manifestation in Java of the book record buffer implicitly defined in the original Progress code.

The single method of the Find class is the public execute() method, which corresponds with the external procedure defined by the original Progress program. See the chapter TBD for a further discussion of this.

The external procedure block implicitly defined in the original Progress code is expressed explicitly in the converted program as an unqualified call to the static com.goldencode.p2j.util.BlockManager.externalProcedure method. An anonymous instance of the Block interface is defined and passed to this method. See the TBD chapter for a detailed discussion of blocks and the compatibility runtime's BlockManager class.

It is within the body method defined for this Block instance that our converted FIND FIRST code resides. First, a record buffer scope is opened for the book DMO. This scope controls aspects of the record buffer, such as record locking and transaction/subtransaction handling, which are discussed in further detail in the Record Buffers chapter.

Finally, we come to the line which is the essence of the converted FIND FIRST statement:

new FindQuery(book, (String) null, null, "book.bookId asc").first();

This line chains together the construction of a FindQuery object, and the invocation of the first method on that object. Note that the object is not referenced beyond this line. This FindQuery instance is short-lived; its only purpose is to have its first method invoked.

There are numerous variants of the FindQuery constructor. The one used above is the simplest; it accepts:

the DMO associated with the query (book);
the HQL ¹ where clause, if any, used as the restriction criteria of the database search;
an optional, WhereExpression object for client-side filtering (discussed further below); and
an HQL sort string to determine which record is returned as the “first”, if multiple records match the search criteria.

1. HQL is an acronym for Hibernate Query Language, an object-oriented analog of SQL (Structured Query Language), used by the Hibernate ORM framework. The FWD conversion emits snippets of HQL - representing where clauses and sort strings - into query object constructors. These are processed at runtime into full HQL statements, which are then processed by the Hibernate framework. Hibernate in turn generates SQL and executes it against the backing database.

In this example, the original FIND statement contained no WHERE clause, so null is passed to the FindQuery constructor for the HQL where clause. Likewise, no client-side filtering is necessary, so the WhereExpression is also null.

Note, however, that there is an explicit sort string: “book.bookId asc”, even though the original FIND FIRST statement did not specify any explicit ordering (such as a USE-INDEX option). During conversion, FWD determines which database index would be selected by the Progress environment at runtime for record sorting. A corresponding HQL sort string is generated and emitted into the FindQuery constructor accordingly. In this case, the primary index for the book table - which defines an ascending order on the book-id field - was selected. For details on the index selection process, please refer to the TBD section of this chapter.

FOR¶

In this section we will consider only query-related parameters and conversion ways. Other parameters and conversion aspects are covered in other chapters.

4GL Form	Description	Supported	Notes
`[ label: ]` `FOR` `[ EACH \| FIRST \| LAST ]` `record-phrase` `[, [ EACH \| FIRST \| LAST ]` `record-phrase ] ...` `[ query-tuning-phrase ]` `[ BREAK ]` `[ BY expression [ DESCENDING ]` `\| COLLATE ( string , strength` `[ , collation ]) [ DESCENDING ]` `] ...` `[ variable = expression1 TO` `expression2 [ BY k ]]` `[ WHILE expression ] [ TRANSACTION ]` `[ on-error-phrase ]` `[ on-endkey-phrase ]` `[ on-quit-phrase ]` `[ on-stop-phrase ]` `[ frame-phrase ]`	Starts an iterating block that reads a record from each of one or more tables at the start of each block iteration.	Yes	The `query-tuning-phrase` and `COLLATE` options are not supported and may cause conversion errors.

Each FOR cycle has a query bound to it. This query is iterated with each iteration of the cycle, and thus appropriate records are loaded in the backing buffers of the query at each iteration, emulating 4GL behavior in this way. Work cycle of the query can be divided into three main steps: query declaration, query initialization and query iteration.

Conversion¶

4GL example:

for each person:
   message "cycle body".
end.

Converted example:

externalProcedure(new Block()
{
   public void body()
   {
      forEach("loopLabel0", new Block()
      {
         AdaptiveQuery query0 = null;                                           // declaration

         public void init()
         {
            RecordBuffer.openScope(person);
            query0 = new AdaptiveQuery(person, (String) null, null,             // initialization
                                       "person.siteId asc, person.empNum asc");
         }

         public void body()
         {
            query0.next();                                                      // iteration
            message("cycle body");
         }
      });
   }
});

1. Query declaration. Query of the appropriate type is declared at the cycle scope.

2. Query initialization. It is performed in the init method of the Block instance which represents the loop. A new instance of the query of the appropriate type is created and assigned to the query declared on the step №1. You can see detailed mapping between different 4GL query types and FWD query classes in the Conversion cases section below. Initialization step may include other sub-steps like initialization of query components, preparation of substitution parameters, enabling break groups functionality, adding sort criteria etc.

3. Query iteration. Iteration command is inserted as the first method in the cycle body (in the body method of the Block instance which represents the loop). Usually FWDQuery.next is used for this purpose, however it may be different for some cases (see the Conversion cases section below for more information). The query is implicitly opened on the first iteration.

Iteration command plays an additional role in controlling cycle: when there are no more records available, it throws the QueryOffEndException thus interrupting the cycle. This exception is caught by BlockManager function which runs the cycle (BlockManager.forEach in the example above) and it handled as the signal that the cycle should be finished.

Parameters¶

This table describes how parameters of the 4GL OPEN QUERY statement are represented in FWD.

Progress Keyword	FWD equivalent
`[ EACH \| FIRST \| LAST ]`	Defines iteration type for a component. This type affects conversion in several ways: • Different types may be converted to different query types (e.g. non-`EACH` components are ofter converted to `RandomAccessQuery`, while `EACH` components are converted to `PreselectQuery` or `AdaptiveQuery`). • For compound queries, query iteration type is specified in the `CompoundQuery.addComponent(Joinable, int)` method. An appropriate constant passed to it: `1. EACH` - `QueryConstants.NEXT` (or you can use `CompoundQuery.addComponent(Joinable)` method instead) `2. FIRST` - `QueryConstants.FIRST` `3. LAST` - `QueryConstants.LAST` `4. Not specified (finds an unique record)` - `QueryConstants.UNIQUE` • Non-`EACH` components are pure index-driven, therefore sort string may be different for `EACH` and non-`EACH` components (assuming that `BY` and `WHERE` clauses are the same). • For the `FOR [ FIRST \| LAST ] ... [BY]` conversion case the iteration command differs for different component types. See the Conversion cases section for more details.
`BY expression [ DESCENDING ]`	Specifies record sorting. Converted sort string does not necessarily match the `BY` clause (more often it does not match). See the Conversion cases section for more details.
`BREAK`	Enables break groups functionality. Makes the cycle to be backed by a presort query, and `enableBreakGroups` to be called on that query.

Conversion cases¶

FOR statement can be converted to different Java constructions which use different FWD query classes. This section will give you this mapping in details.

FOR EACH ... [BY] , where BY clause is not specified or it specifies a sort string which matches an index. The term “matches” means that the sort string components match the leading components of the index or the sort string matches the index completely. An index can be matched in forward and inverse directions. You can read more about index matching in the TODO section.

AdaptiveQuery is used. That happens because in this case we need to emulate 4GL dynamic record retrieval using an index. Even if the sort string is not specified there are rules that allow to determine what index will be used (see TODO section for that rules). If table has no indexes the implicit index by record identifier will be used.

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     field a4 as integer
     index idxa a1 a2
     index idxap is primary a4.

for each a by a1:
  ...
end.

Converted example:

forEach("loopLabel0", new Block()
{
   AdaptiveQuery query0 = null;

   public void init()
   {
      query0 = new AdaptiveQuery(a, (String) null, null, "a.a1 asc, a.a2 asc");
   }

   public void body()
   {
      query0.next();
      ...
   }
});

In this case BY clause by a1 matches index idxa (a1 a2), and the converted sort string corresponds this index: a.a1 asc, a.a2 asc.

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     field a4 as integer
     index idxa a1 a2
     index idxap is primary a4.

for each a:
  ...
end.

Converted example:

forEach("loopLabel0", new Block()
{
   AdaptiveQuery query0 = null;

   public void init()
   {
      query0 = new AdaptiveQuery(a, (String) null, null, "a.a4 asc");
   }

   public void body()
   {
      query0.next();
      ...
   }
});

In this case BY clause is not specified. During conversion FWD determines what index should be used in order to reproduce 4GL behavior in this case. It chooses the primary index idxap(a4) as the most appropriate, and the converted sort string will correspond this index: a.a4 asc.

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer.

for each a:
   ...
end.

Converted example:

forEach("loopLabel0", new Block()
{
   AdaptiveQuery query0 = null;

   public void init()
   {
      query0 = new AdaptiveQuery(a, (String) null, null, "a.id asc");
   }

   public void body()
   {
      query0.next();
      ...
   }
});

In this case BY clause is not specified and the table has no indexes. The implicit sorting by record identifier will be used: a.id asc.

FOR EACH ... BY , where BY clause specifies a sort string which does not match any index.

PreselectQuery is used. That happens because in this case 4GL do not have any suitable index to use for results iteration and has to preselect records by forming the result set up front.

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     field a4 as integer
     index idxa a1 a2
     index idxap is primary a4 a3 a1.

for each a by a2 by a3:
   ...
end.

Converted example:

forEach("loopLabel0", new Block()
{
   PreselectQuery query0 = null;

   public void init()
   {
      query0 = new PreselectQuery(a, (String) null, null, "a.a2 asc, a.a3 asc, a.a4 asc, a.a1 asc");
   }

   public void body()
   {
      query0.next();
      ...
   }
});

BY clause by a2 by a3 does not match any index. The sort string naturally starts with the specified fields: a.a2 asc, a.a3 asc .... For more granular sorting 4GL uses an index (you can see index selection rules in TODO section about that rules). In our case it the primary index idxap will be used, so the sort string should be continued with fields of the index: ... a.a4 asc, a.a3 asc, a.a1 asc, however the field a3 already presents in the sort string, so it is dropped in the second half and the compete sort string is a.a2 asc, a.a3 asc, a.a4 asc, a.a1 asc.

FOR [ FIRST | LAST ] ... [BY]

RandomAccessQuery is used because it is the fastest query when you need to retrieve a single record. Sort string formation rules for FIRST, LAST and unique components are different from the rules for EACH components because these components use only index-driven sorting and ignore sort components if they do not match an index. I.e.:

no sort fields for this component - the fields which correspond the most appropriate index for this component are used;
sort fields match an index - the fields which correspond this index are used;
sort fields do not match any index - sort fields are ignored and the most appropriate index is picked using other criteria (e.g. the primary index can be used).

Note that for iteration of the query bound to the FOR block the following functions are used: FWDQuery.first (for FIRST components), FWDQuery.last (for LAST components) or FWDQuery.unique (for unique components).

4GL example where BY clause match an index:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2
     index idxap is primary a3 a1.

for first a by a1:
   ...
end.

Converted example:

forBlock("blockLabel0", new Block()
{
   RandomAccessQuery query0 = null;

   public void init()
   {
      query0 = new RandomAccessQuery(a, (String) null, null, "a.a1 asc, a.a2 asc");
   }

   public void body()
   {
      query0.first();
      ...
   }
});

4GL example where BY clause does not match any index and therefore ignored, the primary index is used:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2
     index idxap is primary a3 a1.

for first a by a2:
   ...
end.

Converted example:

forBlock("blockLabel0", new Block()
{
   RandomAccessQuery query0 = null;

   public void init()
   {
      query0 = new RandomAccessQuery(a, (String) null, null, "a.a3 asc, a.a1 asc");
   }

   public void body()
   {
      query0.first();
      ...
   }
});

FOR EACH ... [ BREAK ] BY , where BY clause cannot be represented by an equivalent SQL construction or BREAK keyword presents.

PresortQuery is used. This type of query is a specialized preselect query which allows to perform client-side (relative to database) sorting. It uses one or more instances of Resolvable criteria provided by business application code to sort the result set returned by the database. It is used in two cases:

When the expression cannot be represented by an equivalent construction suitable for SQL ORDER BY clause (e.g. arithmetic or character expression).
When BREAK BY keyword is used in order to specify groups used for aggregation.

4GL example with complex sort criteria (the name of the file is test.p):

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     field a4 as integer
     index idxa a1 a2
     index idxa2 a1 a3 a4.

for each a by a1 by a2 + a4 by a3:
   ...
end.

Converted example:

public class Test
{
   ...
   IntegerExpression byExpr1 = new IntegerExpression()
   {
      public integer execute()
      {
         return plus(a.getA2(), a.getA4());
      }
   };

   public void execute()
   {
      externalProcedure(new Block()
      {
         public void body()
         {
            ...

            forEach("loopLabel0", new Block()
            {
               PresortQuery query0 = null;

               FieldReference byExpr0 = new FieldReference(a, "a1");

               FieldReference byExpr2 = new FieldReference(a, "a3");

               public void init()
               {
                  query0 = new PresortQuery(a, (String) null, null, "a.a1 asc, a.a3 asc, a.a4 asc");
                  query0.setNonScrolling();
                  query0.addSortCriterion(byExpr0);
                  query0.addSortCriterion(byExpr1);
                  query0.addSortCriterion(byExpr2);
               }

               public void body()
               {
                  query0.next();
                  ...
               }
            });
         }
      });
   }
}

In the example above the arithmetic expression a2 + a4 cannot be represented in Java code using a sort string. It is represented by an anonymous class which extends IntegerExpression class and implements the Resolvable interface. This class implements the execute() function which returns the result of the specified expression. In our case the expression is plus(a.getA2(), a.getA4()). This class is declared as an instance variable of the class which represents the converted 4GL procedure and it can be reused by other queries.

If the query contains at least one complex sort criterion then simple criteria (i.e. by some_field) in this sort clause should be represented by Resolvable objects too. This is done by using FieldReference objects. In our case it is FieldReference(a, "a1") and FieldReference(a, "a3") which point to the fields a1 and a3. FieldReference objects has the same scope as the parent query has and can be reused by the queries of the same scope.

Sorting criteria are specified using the addSortCriterion method which takes a Resolvable object. The order in which criteria are added is significant; the first criterion added dictates the coarsest level of sorting. Each criterion added subsequently only sorts rows within that criterion added immediately previous to it.

Sorting clause is formed basing on the simple criteria contained in the BY clause. In our case sorting criteria will be based on the following sub-expression: by a1 by a3. Formation rules of the sort string is the same as described in the previous sections. Subexpression by a1 by a3 matches the index idxa2 (a1 a3 a4), so the sort string will match this index: a.a1 asc, a.a3 asc, a.a4 asc.

An example that uses a character expression:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     field a4 as char
     index idxa a1 a2.

for each a by substring(a4, 1, 3) by a3:
  ...
end.

Converted example:

CharacterExpression byExpr0 = new CharacterExpression()
{
   public character execute()
   {
      return substring(a.getA4(), 1, 3);
   }
};
...
forEach("loopLabel0", new Block()
{
   PresortQuery query0 = null;

   FieldReference byExpr1 = new FieldReference(a, "a3");

   public void init()
   {
      query0 = new PresortQuery(a, (String) null, null, "a.a3 asc, a.a1 asc, a.a2 asc");
      query0.setNonScrolling();
      query0.addSortCriterion(byExpr0);
      query0.addSortCriterion(byExpr1);
   }

   public void body()
   {
      query0.next();
      ...
   }
});

If BREAK keyword was used then the enableBreakGroups function is called on the presort query which is bound to the FOR block in order to enable break groups functionality. 4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     field a4 as integer
     index idxa a1 a2
     index idxa2 a1 a3 a4.

for each a break by a1 by a3:
   ...
end.

Converted example:

forEach("loopLabel0", new Block()
{
   PresortQuery query0 = null;

   FieldReference byExpr0 = new FieldReference(a, "a1");

   FieldReference byExpr1 = new FieldReference(a, "a3");

   public void init()
   {
      query0 = new PresortQuery(a, (String) null, null, "a.a1 asc, a.a3 asc, a.a4 asc");
      query0.enableBreakGroups();
      query0.setNonScrolling();
      query0.addSortCriterion(byExpr0);
      query0.addSortCriterion(byExpr1);
   }

   public void body()
   {
      query0.next();
      ...
   }
});

FOR EACH ..., EACH ... BY, where BY clause contains fields of non-outermost tables. All tables are within the same database.

PreselectQuery is used. Presence of fields of non-outermost tables in BY clause means that the order of tables does not necessarily define the order of sorting (say, fields of the innermost table can define the coarsest sorting) and therefore the result set cannot be iterated using indexes, and 4GL has to preselect all the matching records of all tables participating in the query and sort them up front.

Components are added to PreselectQuery using addComponent method. Sort string remains as is after conversion.

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb b1 b2.

for each a, each b where b3 = 1 by b1:
   ...
end.

Converted example:

forEach("loopLabel0", new Block()
{
   PreselectQuery query0 = null;

   public void init()
   {
      query0 = new PreselectQuery("b.b1 asc");
      query0.addComponent(a, ((String) null));
      query0.addComponent(b, "b.b3 = 1");
   }

   public void body()
   {
      query0.next();
      ...
   }
});

FOR EACH ..., EACH ... [ BREAK ] BY , where BY clause cannot be represented by an equivalent SQL construction or BREAK keyword presents. All tables are within the same database.

PresortQuery is used. Individual components are added to it using addComponent method.

4GL example with complex BY clause:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa is primary a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb is primary b1 b2.

for each a, each b by a1 by b1 + b2:
  ...
end.

Converted example:

IntegerExpression byExpr1 = new IntegerExpression()
{
   public integer execute()
   {
      return plus(b.getB1(), b.getB2());
   }
};
...
forEach("loopLabel0", new Block()
{
   PresortQuery query0 = null;

   FieldReference byExpr0 = new FieldReference(a, "a1");

   public void init()
   {
      query0 = new PresortQuery();
      query0.setNonScrolling();
      query0.addComponent(a, ((String) null));
      query0.addComponent(b, ((String) null));
      query0.addSortCriterion(byExpr0);
      query0.addSortCriterion(byExpr1);
   }

   public void body()
   {
      query0.next();
      ...
    }
 });

4GL example with BREAK keyword:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa is primary a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb is primary b1 b2.

for each a, each b break by a1:
  ...
end.

Converted code:

forEach("loopLabel0", new Block()
{
   PresortQuery query0 = null;

   FieldReference byExpr0 = new FieldReference(a, "a1");

   public void init()
   {
      query0 = new PresortQuery();
      query0.enableBreakGroups();
      query0.setNonScrolling();
      query0.addComponent(a, ((String) null));
      query0.addComponent(b, ((String) null));
      query0.addSortCriterion(byExpr0);
   }

   public void body()
   {
      query0.next();
      ...
   }
});

FOR [ EACH | FIRST | LAST ] ..., [ EACH | FIRST | LAST ] ... [BY], where BY clause is not present or it contains only fields of the outermost table. Tables may be in the same or different databases.

CompoundQuery is used for joining components because it allows to join dynamic queries without preselecting results and, additionally, can join tables which cannot be joined at database level.

In this conversion case the order of tables defines the order of sorting, i.e. fields of the outermost table define the coarsest sorting and fields of the innermost - the most granular, so records can be iterated using indexes.

EACH components of the non-outermost tables do not have any sort fields specified and they are represented by AdaptiveQuery sub-queries. EACH component which represents the outermost table can have some sort fields specified (that will not break “the order of tables defines the order of sorting” criterion used to distinguish this conversion case), so it can be represented by:

an AdaptiveQuery sub-query if BY clause is not specified or it matches an index of the outermost table;
a PreselectQuery sub-query if BY clause does not match any index of the outermost table.

FIRST, LAST and unique components are always dynamic and therefore represented by RandomAccessQuery sub-queries both for outermost and non-outermost tables.

Note that CompoundQuery should not preselect all results in this case and therefore the preselect parameter of its constructor is set to false.

Sort strings in the converted code are provided to individual components (rather than having a single sort string for the compound query). Rules of formation of these sort strings are equivalent to single-table cases, i.e.:

no sort fields for this component - the fields which correspond the most appropriate index for this component are used;
sort fields match an index (applicable to the outermost component only) - the fields which correspond this index are used;
sort fields do not match any index (applicable to the outermost component only) - for EACH components: the sort fields define primary sorting and the fields which correspond the most appropriate index define more granular sorting; for FIRST, LAST and unique components: sort fields are ignored and the most appropriate index is picked using other criteria (e.g. the primary index can be used).

Note that for iteration of the compound query bound to the FOR block the CompoundQuery.iterate function is used.

4GL example where the outermost component is represented by an AdaptiveQuery:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb b1 b2.

for each a, each b by a.a1:
   ...
end.

Converted code:

forEach("loopLabel0", new Block()
{
   CompoundQuery query0 = null;

   public void init()
   {
      query0 = new CompoundQuery(false, false);
      query0.addComponent(new AdaptiveQuery(a, (String) null, null, "a.a1 asc, a.a2 asc"));
      query0.addComponent(new AdaptiveQuery(b, (String) null, null, "b.b1 asc, b.b2 asc"));
   }

   public void body()
   {
      query0.iterate();
      ...
   }
});

4GL example where the outermost component is represented by a PreselectQuery:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb b1 b2.

for each a, each b by a.a2:
   ...
end.

Converted code:

forEach("loopLabel0", new Block()
{
   CompoundQuery query0 = null;

   public void init()
   {
      query0 = new CompoundQuery(false, false);
      query0.addComponent(new PreselectQuery(a, (String) null, null, "a.a2 asc, a.a1 asc"));
      query0.addComponent(new AdaptiveQuery(b, (String) null, null, "b.b1 asc, b.b2 asc"));
   }

   public void body()
   {
      query0.iterate();
      ...
    }
});

4GL example with FIRST and unique component and tables joined across databases:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

for a where a1 = 1 and a2 = 1, first address:
   ...
end.

Converted code:

forBlock("blockLabel0", new Block()
{
   CompoundQuery query0 = null;

   public void init()
   {
      RecordBuffer.openScope(address);
      query0 = new CompoundQuery(false, false);
      query0.addComponent(new RandomAccessQuery(a, "a.a1 = 1 and a.a2 = 1", null,
                                                "a.a1 asc, a.a2 asc"), QueryConstants.UNIQUE);
      query0.addComponent(new RandomAccessQuery(address, (String) null, null,
                                                "address.addrId asc"), QueryConstants.FIRST);
   }

   public void body()
   {
      query0.iterate();
      ...
    }
});

FOR { EACH | FIRST | LAST } ..., { EACH | FIRST | LAST } ... [ BREAK ] BY, where BY clause contains fields of non-outermost tables or it cannot be represented by an equivalent SQL construction or BREAK keyword presents. Tables cannot be joined at database level (FIRST / LAST components are used or tables are joined across databases).

PresortCompoundQuery is used because:

It is compound and can perform join for tables that cannot be joined at database level.
It has preselect nature, so it allows to preserve sorting defined by fields of non-outermost tables.
It has presort capabilities and allows to specify a sort criterion that cannot represented by an equivalent SQL construction.
It supports break groups.
Sort string is parsed and each sort criterion (either expression or a field) is added using addSortCriterion method, in the same way as for the PresortQuery. Additionally, sort strings for individual components are specified and define more granular sorting. Rules of formation of these sort strings are equivalent to single-table cases (you can see these rules are listed in the previous conversion case).

EACH components are represented by PreselectQuery sub-queries.

FIRST / LAST components are represented by RandomAccessQuery sub-queries because they are faster that PreselectQuery when you need to retrieve a single record. Although RandomAccessQuery is a dynamic query, it is used for preselecting records up front.

Note that for iteration of the presort compound query bound to the FOR block the PresortCompoundQuery.next function with the parameter iterating set to true is used.

4GL example with BY clause that contains fields of non-outermost tables:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb b1 b2.

for each a, first b by b.b1:
   ...
end.

Converted code:

forEach("loopLabel0", new Block()
{
   PresortCompoundQuery query0 = null;

   FieldReference byExpr0 = new FieldReference(b, "b1");

   public void init()
   {
      query0 = new PresortCompoundQuery();
      query0.addComponent(new PreselectQuery(a, (String) null, null, "a.a1 asc, a.a2 asc"));
      query0.addComponent(new RandomAccessQuery(b, (String) null, null, "b.b1 asc, b.b2 asc"),
                          QueryConstants.FIRST);
      query0.addSortCriterion(byExpr0);
   }

   public void body()
   {
      query0.next(true);
      ...
   }
});

4GL example with BY clause that cannot be represented by an equivalent SQL construction:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb b1 b2.

for each a, first b by a1 + a2 by a3:
   ...
end.

Converted example:

IntegerExpression byExpr0 = new IntegerExpression()
{
   public integer execute()
   {
      return plus(a.getA1(), a.getA2());
   }
};
...
forEach("loopLabel0", new Block()
{
   PresortCompoundQuery query0 = null;

   FieldReference byExpr1 = new FieldReference(a, "a3");

   public void init()
   {
      query0 = new PresortCompoundQuery();
      query0.addComponent(new PreselectQuery(a, (String) null, null,
                                             "a.a3 asc, a.a1 asc, a.a2 asc"));
      query0.addComponent(new RandomAccessQuery(b, (String) null, null, "b.b1 asc, b.b2 asc"),
                          QueryConstants.FIRST);
      query0.addSortCriterion(byExpr0);
      query0.addSortCriterion(byExpr1);
   }

   public void body()
   {
      query0.next(true);
      ...
   }
});

4GL example with FIRST component which ignores fields specified in the BY clause:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb is primary b1 b2
     index idxb2 b2 b3 b1.

for each a, first b where b2 > 0 by b.b3:
   ...
end.

Converted example:

forEach("loopLabel0", new Block()
{
   PresortCompoundQuery query0 = null;

   FieldReference byExpr0 = new FieldReference(b, "b3");

   public void init()
   {
      query0 = new PresortCompoundQuery();
      query0.addComponent(new PreselectQuery(a, (String) null, null, "a.a1 asc, a.a2 asc"));
      query0.addComponent(new RandomAccessQuery(b, "b.b2 > 0", null,
                                     "b.b2 asc, b.b3 asc, b.b1 asc"), QueryConstants.FIRST);
      query0.addSortCriterion(byExpr0);
   }

   public void body()
   {
      query0.next(true);
      ...
   }
});

For the second component BY clause by b.b3 does not match any index and can be ignored for this type of components, idxb2 (b2 b3 b1) index was chosen because we have b2 field participating in comparison in WHERE clause.

4GL example with BREAK keyword and tables joined across databases:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

for each a, each address break by a.a1:
   ...
end.

Converted example:

forEach("loopLabel0", new Block()
{
   PresortCompoundQuery query0 = null;

   FieldReference byExpr0 = new FieldReference(a, "a1");

   public void init()
   {
      RecordBuffer.openScope(address);
      query0 = new PresortCompoundQuery();
      query0.enableBreakGroups();
      query0.addComponent(new PreselectQuery(a, (String) null, null, "a.a1 asc, a.a2 asc"));
      query0.addComponent(new PreselectQuery(address, (String) null, null, "address.addrId asc"));
      query0.addSortCriterion(byExpr0);
   }

   public void body()
   {
      query0.next(true);
      ...
   }
});

DO/REPEAT PRESELECT¶

In this section we will consider only query-related parameters and conversion ways. Other parameters and conversion aspects are covered in other chapters.

4GL Form	Description	Supported	Notes
`[ label: ]` `{ DO \| REPEAT }` `[ FOR record [, record] ... ]` `[ PRESELECT` `[ EACH \| FIRST \| LAST ]` `record-phrase` `[,[ EACH \| FIRST \| LAST ]` `record-phrase ] ...` `[` `[ BREAK ]` `{ BY expression [ DESCENDING ] }` `... ]]` `[ query-tuning-phrase ]` `[ variable = expression1 TO` `expression2 [ BY k ]]` `[ WHILE expression ]` `[ TRANSACTION ]` `[ on-error-phrase ]` `[ on-endkey-phrase ]` `[ on-quit-phrase ]` `[ on-stop-phrase ]` `[ frame-phrase ]`	Starts an iterating block that reads a record from each of one or more tables at the start of each block iteration.	Yes	The `query-tuning-phrase` option is not supported and may cause conversion errors.

Each DO/REPEAT PRESELECT block has a query bound to it. This query is iterated using the FIND statement within the block. If we have single-table preselect then FIND statement should point to the record buffer specified by the PRESELECT record-phrase parameter. E.g.:

repeat preselect each a:
   find next a.
end.

If we have multi-table preselect then according to 4GL specifications FIND statement should point to the innermost record buffer specified by the PRESELECT record-phrase, record-phrase [, ...] parameter (on the other hand, FWD converter correctly converts to an iteration command a FIND statement that points to any of the preselected buffers). E.g.:

repeat preselect each a, each b:
   find next b. /* correct both in 4GL and FWD */
   find next a. /* incorrect for 4GL, but works for FWD */
end.

With each iteration driven by FIND statement appropriate records are loaded in the buffers specified in the PRESELECT record-phrase [, ...] parameter.

Work cycle of the query which backs the preselect block can be divided into three main steps: query declaration, query initialization and query iteration.

Conversion¶

4GL example:

repeat preselect each person:
   find next person.
   message "cycle body".
end.

Converted example:

externalProcedure(new Block()
{
   public void body()
   {
      repeat("loopLabel0", new Block()
      {
         PreselectQuery query0 = null;                                           // declaration

         public void init()
         {
            RecordBuffer.openScope(person);                                      // initialization
            query0 = new PreselectQuery(person, (String) null, null,
                                        "person.siteId asc, person.empNum asc");
         }

         public void body()
         {
            query0.next();                                                        // iteration
            message("cycle body");
         }
      });
   }
});

1. Query declaration. Query of the appropriate type is declared at the block scope.

2. Query initialization. It is performed in the init method of the Block instance which represents the loop. A new instance of the query of the appropriate type is created and assigned to the query declared on the step №1. You can see detailed mapping between different 4GL query types and FWD query classes in the [[Chapter_25_Queries#conversion-cases|Conversion cases]] section below. Initialization step may include other sub-steps like initialization of query components, preparation of substitution parameters, enabling break groups functionality, adding sort criteria etc.

3. Query iteration. As it was noted earlier, query iteration is performed using the FIND statement for the preselected buffer(s) inside the DO/REPEAT PRESELECT block. It can be called arbitrary number of times per block iteration anywhere inside the block (i.e. in converted code - inside the body method of the Block instance which represents the block). The query is implicitly opened on the first iteration.

Iteration command plays an additional role in block controlling: when there are no more records available in the direction specified in the FIND statement, it throws the QueryOffEndException thus interrupting the block. This exception is caught by BlockManager function which runs the block (BlockManager.repeat in the example above) and it handled as the signal that the block should be finished.

The following iteration commands are allowed for DO/REPEAT PRESELECT block:

Progress statement	FWD equivalent
`FIND NEXT`	`FWDQuery.next`
`FIND PREV`	`FWDQuery.previous`
`FIND FIRST`	`FWDQuery.first`
`FIND LAST`	`FWDQuery.last`

Note that not all iteration commands may be available for specific conversion cases.

Parameters¶

This table describes how parameters of the 4GL DO/REPEAT PRESELECT statement are represented in FWD.

Progress Keyword	FWD equivalent
`{ EACH \| FIRST \| LAST }`	Defines iteration type for a component. This type affects conversion in several ways: • Different types may be converted to different query types (e.g. non-`EACH` components are ofter converted to `RandomAccessQuery`, while `EACH` components are converted to `PreselectQuery` or `AdaptiveQuery`). • For compound queries, query iteration type is specified in the `CompoundQuery.addComponent(Joinable, int)` method. An appropriate constant passed to it: `1. EACH` - `QueryConstants.NEXT` (or you can use `CompoundQuery.addComponent(Joinable)` method instead) `2. FIRST` - `QueryConstants.FIRST` `3. LAST` - `QueryConstants.LAST` • Non-`EACH` components are pure index-driven, therefore sort string may be different for `EACH` and non-`EACH` components (assuming that `BY` and `WHERE` clauses are the same).
`BY expression [ DESCENDING ]`	Specifies record sorting. Converted sort string does not necessarily match the `BY` clause (more often it does not match). See the Conversion cases section for more details.
`BREAK`	Enables break groups functionality. Makes the block to be backed by a presort query, and `enableBreakGroups` to be called on that query.

Conversion cases¶

DO PRESELECT and REPEAT PRESELECT statement can be converted to different Java constructions which use different FWD query classes. This section will give you this mapping in details. Note that only the REPEAT PRESELECT cases will be considered. The examples for DO PRESELECT are very similar, you can look in the TODO section in order to understand the differences.

{ DO | REPEAT } PRESELECT EACH ... [BY]

PreselectQuery is used because of the PRESELECT keyword. Sort string formation rules are the following:

No BY clause specified - the fields which correspond the most appropriate index for this component are used (you can see index selection rules in the TODO section); even if table has no indexes, the implicit index by record identifier will be used.
BY clause matches an index - the fields which correspond this index are used. The term “matches” means that the sort string components match the leading components of the index or the sort string matches the index completely. An index can be matched in forward and inverse directions. You can read more about index matching in the TODO section.
BY clause does not match any index - the sort fields define primary sorting and the fields which correspond the most appropriate index define more granular sorting.

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     field a4 as integer
     index idxa a1 a2
     index idxap is primary a4.

repeat preselect each a by a1:
   find next a.
end.

Converted example:

repeat("loopLabel0", new Block()
{
   PreselectQuery query0 = null;

   public void init()
   {
      query0 = new PreselectQuery(a, (String) null, null, "a.a1 asc, a.a2 asc");
   }

   public void body()
   {
      query0.next();
   }
});

In this case BY clause by a1 matches index idxa (a1 a2), and the converted sort string corresponds this index: a.a1 asc, a.a2 asc.

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     field a4 as integer
     index idxa a1 a2
     index idxap is primary a4.

repeat preselect each a:
   find next a.
end.

Converted example:

repeat("loopLabel0", new Block()
{
   PreselectQuery query0 = null;

   public void init()
   {
      query0 = new PreselectQuery(a, (String) null, null, "a.a4 asc");
   }

   public void body()
   {
      query0.next();
   }
});

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer.

repeat preselect each a:
   find next a.
end.

Converted example:

repeat("loopLabel0", new Block()
{
   PreselectQuery query0 = null;

   public void init()
   {
      query0 = new PreselectQuery(a, (String) null, null, "a.id asc");
   }

   public void body()
   {
      query0.next();
   }
});

In this case BY clause is not specified and the table has no indexes. The implicit sorting by record identifier will be used: a.id asc.

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     field a4 as integer
     index idxa a1 a2
     index idxap is primary a4 a3 a1.

repeat preselect each a by a2 by a3:
   find next a.
end.

Converted example:

repeat("loopLabel0", new Block()
{
   PreselectQuery query0 = null;

   public void init()
   {
      query0 = new PreselectQuery(a, (String) null, null, "a.a2 asc, a.a3 asc, a.a4 asc, a.a1 asc");
   }

   public void body()
   {
      query0.next();
   }
});

{ DO | REPEAT } PRESELECT EACH ... [ BREAK ] BY , where BY clause cannot be represented by an equivalent SQL construction or BREAK keyword presents.

When the expression cannot be represented by an equivalent construction suitable for SQL ORDER BY clause (e.g. arithmetic or character expression).
When BREAK BY keyword is used in order to specify groups used for aggregation.

4GL example (the name of the file is test.p):

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     field a4 as integer
     index idxa a1 a2
     index idxa2 a1 a3 a4.

repeat preselect each a by a1 by a2 + a4 by a3:
   find next a.
end.

Converted example:

public class Test
{
   ...

   IntegerExpression byExpr1 = new IntegerExpression()
   {
      public integer execute()
      {
         return plus(a.getA2(), a.getA4());
      }
   };

   public void execute()
   {
      externalProcedure(new Block()
      {
         public void body()
         {
            ...

            repeat("loopLabel0", new Block()
            {
               PresortQuery query0 = null;

               FieldReference byExpr0 = new FieldReference(a, "a1");

               FieldReference byExpr2 = new FieldReference(a, "a3");

               public void init()
               {
                  query0 = new PresortQuery(a, (String) null, null, "a.a1 asc, a.a3 asc, a.a4 asc");
                  query0.addSortCriterion(byExpr0);
                  query0.addSortCriterion(byExpr1);
                  query0.addSortCriterion(byExpr2);
               }

               public void body()
               {
                  query0.next();
               }
            });
         }
      });
   }
}

An example that uses a character expression:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     field a4 as char
     index idxa a1 a2.

repeat preselect each a by substring(a4, 1, 3) by a3:
   find next a.
end.

Converted example:

CharacterExpression byExpr0 = new CharacterExpression()
{
   public character execute()
   {
      return substring(a.getA4(), 1, 3);
   }
};

...
repeat("loopLabel0", new Block()
{
   PresortQuery query0 = null;

   FieldReference byExpr1 = new FieldReference(a, "a3");

   public void init()
   {
      query0 = new PresortQuery(a, (String) null, null, "a.a3 asc, a.a1 asc, a.a2 asc");
      query0.addSortCriterion(byExpr0);
      query0.addSortCriterion(byExpr1);
   }

   public void body()
   {
      query0.next();
   }
});

If BREAK keyword was used then the enableBreakGroups function is called on the presort query which is bound to the DO /@ REPEAT block in order to enable break groups functionality. 4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     field a4 as integer
     index idxa a1 a2
     index idxa2 a1 a3 a4.

repeat preselect each a break by a1 by a3:
   find next a.
end.

Converted example:

repeat("loopLabel0", new Block()
{
   PresortQuery query0 = null;

   FieldReference byExpr0 = new FieldReference(a, "a1");

   FieldReference byExpr1 = new FieldReference(a, "a3");

   public void init()
   {
      query0 = new PresortQuery(a, (String) null, null, "a.a1 asc, a.a3 asc, a.a4 asc");
      query0.enableBreakGroups();
      query0.addSortCriterion(byExpr0);
      query0.addSortCriterion(byExpr1);
   }

   public void body()
   {
      query0.next();
   }
});

{ DO | REPEAT } PRESELECT EACH ..., EACH ... [BY], where all joined tables are within the same database.

PreselectQuery is used because of the PRESELECT keyword. If BY clause is specified, it is left as is. If BY clause is not specified then the sort string is combined by concatenating appropriate indexes for each component.

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa is primary a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb is primary b1 b2.

repeat preselect each a, each b by a1:
   find next b.
end.

Converted example:

repeat("loopLabel0", new Block()
{
   PreselectQuery query0 = null;

   public void init()
   {
      query0 = new PreselectQuery("a.a1 asc");
      query0.addComponent(a, ((String) null));
      query0.addComponent(b, ((String) null));
   }

   public void body()
   {
      query0.next();
   }
});

As you can see, the sort string was left as is.

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa is primary a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb is primary b1 b2.

repeat preselect each a, each b:
   find next b.
end.

Converted example:

repeat("loopLabel0", new Block()
{
   PreselectQuery query0 = null;

   public void init()
   {
      query0 = new PreselectQuery("a.a1 asc, a.a2 asc, b.b1 asc, b.b2 asc");
      query0.addComponent(a, ((String) null));
      query0.addComponent(b, ((String) null));
   }

   public void body()
   {
      query0.next();
   }
});

In this example the sort string was combined from the primary indexes of both components.

{ DO | REPEAT } PRESELECT EACH ..., EACH ... [ BREAK ] BY , where BY clause cannot be represented by an equivalent SQL construction or BREAK keyword presents. All tables are within the same database.

PresortQuery is used. Individual components are added to it using addComponent method.

4GL example with complex BY clause:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa is primary a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb is primary b1 b2.

repeat preselect each a, each b by a1 by b1 + b2:
   find next b.
end.

Converted example:

IntegerExpression byExpr1 = new IntegerExpression()
{
   public integer execute()
   {
      return plus(b.getB1(), b.getB2());
   }
};
...
repeat("loopLabel0", new Block()
{
   PresortQuery query0 = null;

   FieldReference byExpr0 = new FieldReference(a, "a1");

   public void init()
   {
      query0 = new PresortQuery();
      query0.addComponent(a, ((String) null));
      query0.addComponent(b, ((String) null));
      query0.addSortCriterion(byExpr0);
      query0.addSortCriterion(byExpr1);
   }

   public void body()
   {
      query0.next();
   }
});

4GL example with BREAK keyword:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa is primary a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb is primary b1 b2.

repeat preselect each a, each b break by a1:
   find next b.
end.

Converted code:

repeat("loopLabel0", new Block()
{
   PresortQuery query0 = null;

   FieldReference byExpr0 = new FieldReference(a, "a1");

   public void init()
   {
      query0 = new PresortQuery();
      query0.enableBreakGroups();
      query0.addComponent(a, ((String) null));
      query0.addComponent(b, ((String) null));
      query0.addSortCriterion(byExpr0);
   }

   public void body()
   {
      query0.next();
   }
});

{ DO | REPEAT } PRESELECT { EACH | FIRST | LAST } ..., { EACH | FIRST | LAST } ... [BREAK] [BY]

BY clause, if specified, can either be simple or contain expressions that cannot be represented by equivalent SQL constructions or BREAK keyword can present. Tables cannot be joined at database level (FIRST / LAST components are used or tables are joined across databases).

PresortCompoundQuery is used because:

It is compound and can perform join for tables that cannot be joined at database level.
It is a preselect query.
Additionally, it has presort capabilities which allows to specify break groups or sort criteria that cannot represented by an equivalent SQL construction.
Sort string is parsed and each sort criterion (either expression or a field) is added using addSortCriterion method, in the same way as for the PresortQuery. Additionally, sort strings for individual components are specified and define more granular sorting. Rules of formation of these sort strings for EACH components are equivalent to single-table cases (you can see these rules are listed in the first DO / REPEAT conversion case). Rules for FIRST / LAST components are slightly different because these components use only index-driven sorting and ignore sort components if they does not match an index. I.e.:

no sort fields for this component - the fields which correspond the most appropriate index for this component are used;
sort fields match an index - the fields which correspond this index are used;
sort fields does not match any index - sort fields are ignored and the most appropriate index is picked using other criteria (e.g. the primary index can be used).

EACH components are represented by PreselectQuery sub-queries.

Note that for iteration of the presort compound query bound to the DO / REPEAT block the PresortCompoundQuery.next function with the parameter iterating set to true is used.

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb b1 b2.

repeat preselect each a, first b by a.a1:
   find next b.
end.

Converted code:

repeat("loopLabel0", new Block()
{
   PresortCompoundQuery query0 = null;

   FieldReference byExpr0 = new FieldReference(a, "a1");

   public void init()
   {
      query0 = new PresortCompoundQuery();
      query0.addComponent(new PreselectQuery(a, (String) null, null, "a.a1 asc, a.a2 asc"));
      query0.addComponent(new RandomAccessQuery(b, (String) null, null, "b.b1 asc, b.b2 asc"),
                          QueryConstants.FIRST);
      query0.addSortCriterion(byExpr0);
   }

   public void body()
   {
      query0.next(true);
   }
});

4GL example with BY clause that cannot be represented by an equivalent SQL construction:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb b1 b2.

repeat preselect each a, first b by a1 + a2 by a3:
   find next b.
end.

Converted example:

IntegerExpression byExpr0 = new IntegerExpression()
{
   public integer execute()
   {
      return plus(a.getA1(), a.getA2());
   }
};
...
repeat("loopLabel0", new Block()
{
   PresortCompoundQuery query0 = null;

   FieldReference byExpr1 = new FieldReference(a, "a3");

   public void init()
   {
      query0 = new PresortCompoundQuery();
      query0.addComponent(new PreselectQuery(a, (String) null, null,
                                                              "a.a3 asc, a.a1 asc, a.a2 asc"));
      query0.addComponent(new RandomAccessQuery(b, (String) null, null, "b.b1 asc, b.b2 asc"),
                          QueryConstants.FIRST);
      query0.addSortCriterion(byExpr0);
      query0.addSortCriterion(byExpr1);
   }

   public void body()
   {
      query0.next(true);
   }
});

4GL example with BREAK keyword:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb b1 b2.

repeat preselect each a, first b break by a1 by a3:
   find next b.
end.

Converted example:

repeat("loopLabel0", new Block()
{
   PresortCompoundQuery query0 = null;

   FieldReference byExpr0 = new FieldReference(a, "a1");

   FieldReference byExpr1 = new FieldReference(a, "a3");

   public void init()
   {
      query0 = new PresortCompoundQuery();
      query0.enableBreakGroups();
      query0.addComponent(new PreselectQuery(a, (String) null, null,
                                                              "a.a1 asc, a.a3 asc, a.a2 asc"));
      query0.addComponent(new RandomAccessQuery(b, (String) null, null, "b.b1 asc, b.b2 asc"),
                          QueryConstants.FIRST);
      query0.addSortCriterion(byExpr0);
      query0.addSortCriterion(byExpr1);
   }

   public void body()
   {
      query0.next(true);
   }
});

4GL example with FIRST component which ignores fields specified in the BY clause:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb is primary b1 b2
     index idxb2 b2 b3 b1.

repeat preselect each a, first b where b2 > 0 by b.b3:
   find next b.
end.

Converted example:

repeat("loopLabel0", new Block()
{
   PresortCompoundQuery query0 = null;

   FieldReference byExpr0 = new FieldReference(b, "b3");

   public void init()
   {
      query0 = new PresortCompoundQuery();
      query0.addComponent(new PreselectQuery(a, (String) null, null, "a.a1 asc, a.a2 asc"));
      query0.addComponent(new RandomAccessQuery(b, "b.b2 > 0", null,
                          "b.b2 asc, b.b3 asc, b.b1 asc"), QueryConstants.FIRST);
      query0.addSortCriterion(byExpr0);
   }

   public void body()
   {
      query0.next(true);
   }
});

DEFINE/OPEN/CLOSE QUERY, GET¶

DEFINE QUERY¶

4GL Form	Description	Supported	Notes
`DEFINE` `[ [ NEW ] SHARED ]` `QUERY query` `FOR` `bufname [ field-list ]` `[ , bufname [ field-list ] ] ...` `CACHE n ]` `SCROLLING ]` `[ RCODE-INFORMATION ]`	Defines a reusable query that can be opened with an `OPEN QUERY` statement and from which records can be retrieved with a `GET` statement or browse widget.	Yes	The `[ NEW ] SHARED` option is ignored, the scope of the query is limited to the external procedure in which it is declared. It is required for at least one buffer name (`bufname`) to be specified, however buffer names and field lists are ignored during conversion, because in FWD you can assign a query with any combination of backing buffers to a reusable query previously defined using `DEFINE QUERY`. But for more readability it's worth to point correct buffers in 4GL code. The `RCODE-INFORMATION` option is ignored by conversion. The `CACHE` option is not supported and may cause conversion errors.

This statements is converted to declaration and initialization of the com.goldencode.p2j.persist.QueryWrapper instance. It is declared as an instance field of the class which represents the external procedure which contains the given statement. 4GL code (the procedure name is test.p):

define query q for person.
...

Converted example:

public class Test
{
   final QueryWrapper query0 = new QueryWrapper(false);
   ...
}

QueryWrapper is a delegating container which delegates all query navigation to internal query components. This class can wrap any type of queries represented in FWD. New queries can be assigned to QueryWrapper multiple times.

Progress Keyword	FWD equivalent
`SCROLLING`	`scrolling` parameter of the `QueryWrapper` constructor. Defines whether you can jump to a location within the list of records that satisfy the query by using the `REPOSITION` statement and move backwards using `FIRST` or `PREV`.

OPEN QUERY¶

4GL Form	Description	Supported	Notes
`OPEN QUERY query-name` `{ FOR \| PRESELECT }` `EACH record-phrase` `[ , { EACH \| FIRST \| LAST }` `record-phrase` `] ...` `[ query-tuning-phrase ]` `[ BY expression` `[ DESCENDING ]` `\| COLLATE` `( string ,` `strength` `[ , collation ] )` `[ DESCENDING ]` `] ...` `[ INDEXED-REPOSITION ]` `[ MAX-ROWS num-results ]`	Opens a query, which might have been previously defined in a `DEFINE QUERY` statement. Opening a query makes it available for use within a `GET` statement, or in a browse widget.	Yes	The `INDEXED-REPOSITION` option is ignored by conversion. The `query-tuning-phrase`, `COLLATE` and `MAX-ROWS` options are not supported and may cause conversion errors. `query-name` is not preserved, names of the variables which represent corresponding FWD queries will be `query0`, `query1`, etc.

Call of OPEN QUERY is converted to a set of Java statements which can be divided into three main steps: query declaration, query initialization and query opening. How these steps look depends on whether the query was declared using the DEFINE QUERY statement.

Conversion (without DEFINE QUERY)¶

Consider that the query wasn't defined by DEFINE QUERY. 4GL example:

open query q for each person.

Converted example:

externalProcedure(new Block()
{
   // Query declaration.
   AdaptiveQuery query0 = null;

   public void body()
   {
      ...
      // Query initialization.
      query0 = new AdaptiveQuery(person, (String) null, null,
                                 "person.siteId asc, person.empNum asc");
      // Query opening.
      query0.open();
   }
});

1. Query declaration. Query of the appropriate type is declared at the proper scope (TODO see the name of the query scoping section). In most cases it will be scoped to an external procedure, like in the example above. But it may be scoped to a specific block either. 4GL example:

repeat:
  open query q for each person.
  ...
end.

Converted example:

repeat("loopLabel0", new Block()
{
   AdaptiveQuery query0 = null;
   ...
});

2. Query initialization. A new instance of the query of the appropriate type is created and assigned to the query declared on the step №1. You can see detailed mapping between different 4GL query types and FWD query classes in the Conversion cases section below. Initialization step may include other sub-steps like initialization of query components and preparation of substitution parameters.

3. Query opening. FWDQuery.open method is called for the the target query.

Conversion (with DEFINE QUERY)¶

Consider that the query was defined by DEFINE QUERY. 4GL example (code is in the file test.p):

define query q for person.
open query q for each person.

Converted example:

public class Test
{
   ...
   // Query declaration.
   final QueryWrapper query0 = new QueryWrapper(false);

   public void execute()
   {
      externalProcedure(new Block()
      {
         public void body()
         {
            ...
            // Query initialization.
            query0.assign(new AdaptiveQuery(person, (String) null, null,
                                            "person.siteId asc, person.empNum asc"));
            // Query opening.
            query0.open();
         }
      });
   }
}

1. Query declaration. QueryWrapper is defined and instantiated as an instance variable of the class which corresponds the converted 4GL .p file.

2. Query initialization. A new instance of the query of the appropriate type is created and assigned to the QueryWrapper declared on the step №1 using the QueryWrapper.assign method. You can see detailed mapping between different 4GL query types and FWD query classes in the Conversion cases section below. Initialization step may include other steps like initialization of query components and preparation of substitution parameters.

3. Query opening. QueryWrapper.open method is called for the the target query.

Parameters¶

This table describes how parameters of the 4GL OPEN QUERY statement are represented in FWD.

Progress Keyword	FWD equivalent
`FOR \| PRESELECT`	Affects the type of corresponding FWD query. `PRESELECT` makes preselect queries to be used. `FOR` makes dynamic queries to be used. However there are other factors that influence on the decision about concrete FWD query class to be used. See the Conversion cases section below for more information.
`[ EACH \| FIRST \| LAST ]`	Defines iteration type for a component. This type affects conversion in several ways: • Different types may be converted to different query types (e.g. non-`EACH` components are ofter converted to `RandomAccessQuery`, while `EACH` components are converted to `PreselectQuery` or `AdaptiveQuery`). • For compound queries, query iteration type is specified in the `CompoundQuery.addComponent(Joinable, int)` or `QueryWrapper.addComponent(Joinable, int)` method. An appropriate constant passed to it: `1. EACH` - `QueryConstants.NEXT` (or you can use `CompoundQuery.addComponent(Joinable)` or `QueryWrapper.addComponent(Joinable)` method instead) `2. FIRST` - `QueryConstants.FIRST` `3. LAST` - `QueryConstants.LAST` • Non-`EACH` components are pure index-driven, therefore sort string may be different for `EACH` and non-`EACH` components (assuming that `BY` and `WHERE` clauses are the same).
`BY expression [ DESCENDING ]`	Specifies record sorting. Converted sort string does not necessarily match the `BY` clause (more often it does not match). See the Conversion cases section for more details.

Conversion cases¶

OPEN QUERY statement can be converted to different Java constructions which use different FWD query classes. This section will give you this mapping in details. Note that only the cases where the query was not previously defined by DEFINE QUERY will be considered. The examples for the case where DEFINE QUERY has been used are very similar, you can look in the Conversion basics section above in order to understand the differences.

OPEN QUERY FOR EACH ... [BY] , where BY clause is not specified or it specifies a sort string which matches an index. The term “matches” means that the sort string components match the leading components of the index or the sort string matches the index completely. An index can be matched in forward and inverse directions. You can read more about index matching in the TODO section.

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     field a4 as integer
     index idxa a1 a2
     index idxap is primary a4.

open query q for each a by a1.

Converted example:

query0 = new AdaptiveQuery(a, (String) null, null, "a.a1 asc, a.a2 asc");
query0.open();

In this case BY clause by a1 matches index idxa (a1 a2), and the converted sort string corresponds this index: a.a1 asc, a.a2 asc.

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     field a4 as integer
     index idxa a1 a2
     index idxap is primary a4.

open query q for each a.

Converted example:

query0 = new AdaptiveQuery(a, (String) null, null, "a.a4 asc");
query0.open();

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer.

open query q for each a.

Converted example:

query0 = new AdaptiveQuery(a, (String) null, null, "a.id asc");
query0.open();

In this case BY clause is not specified and the table has no indexes. The implicit sorting by record identifier will be used: a.id asc.

OPEN QUERY FOR EACH ... BY , where BY clause specifies a sort string which does not match any index.

PreselectQuery is used. That happens because in this case 4GL do not have any suitable index to use for results iteration and has to preselect records by forming the result set up front.

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     field a4 as integer
     index idxa a1 a2
     index idxap is primary a4 a3 a1.

open query q for each a by a2 by a3.

Converted example:

query0 = new PreselectQuery(a, (String) null, null, "a.a2 asc, a.a3 asc, a.a4 asc, a.a1 asc");
query0.open();

OPEN QUERY PRESELECT EACH ... [BY]

PreselectQuery is used because of the PRESELECT keyword. Sort string formation rules are the same as for the OPEN QUERY FOR conversion cases above.

4GL example where BY clause is not specified:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idx1 a1 a2
     index idx2 is primary a3.

open query q preselect each a.

Converted example:

query0 = new PreselectQuery(a, (String) null, null, "a.a3 asc");
query0.open();

The primary index was used.

4GL example where BY clause match an index:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idx1 a1 a2
     index idx2 is primary a3.

open query q preselect each a by a1.

Converted example:

query0 = new PreselectQuery(a, (String) null, null, "a.a1 asc, a.a2 asc");
query0.open();

4GL example where BY clause does not match any index:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idx1 a1 a2.

open query q preselect each a by a2.

Converted example:

query0 = new PreselectQuery(a, (String) null, null, "a.a2 asc, a.a1 asc");
query0.open();

OPEN QUERY { FOR | PRESELECT } EACH ... BY , where BY clause cannot be represented by an equivalent SQL construction.

When the expression cannot be represented by an equivalent construction suitable for SQL ORDER BY clause (e.g. arithmetic or character expression).
When BREAK BY keyword is used in order to specify groups used for aggregation (not applicable to the OPEN QUERY statement).

4GL example (the name of the file is test.p):

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     field a4 as integer
     index idxa a1 a2
     index idxa2 a1 a3 a4.

open query q for each a by a1 by a2 + a4 by a3.

Converted example:

public class Test
{
   ...

   IntegerExpression byExpr1 = new IntegerExpression()
   {
      public integer execute()
      {
         return plus(a.getA2(), a.getA4());
      }
   };

   public void execute()
   {
      externalProcedure(new Block()
      {
         PresortQuery query0 = null;

         FieldReference byExpr0 = new FieldReference(a, "a1");

         FieldReference byExpr2 = new FieldReference(a, "a3");

         public void body()
         {
            ...
            query0 = new PresortQuery(a, (String) null, null, "a.a1 asc, a.a3 asc, a.a4 asc");
            query0.addSortCriterion(byExpr0);
            query0.addSortCriterion(byExpr1);
            query0.addSortCriterion(byExpr2);
            query0.open();
         }
      });
   }
}

An example that uses a character expression:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     field a4 as char
     index idxa a1 a2.

open query q preselect each a by substring(a4, 1, 3) by a3.

Converted example:

public class Test
{
   ...
   CharacterExpression byExpr0 = new CharacterExpression()
   {
      public character execute()
      {
         return substring(a.getA4(), 1, 3);
      }
   };

   public void execute()
   {
      externalProcedure(new Block()
      {
         PresortQuery query0 = null;

         FieldReference byExpr1 = new FieldReference(a, "a3");

         public void body()
         {
            ...
            query0 = new PresortQuery(a, (String) null, null, "a.a3 asc, a.a1 asc, a.a2 asc");
            query0.addSortCriterion(byExpr0);
            query0.addSortCriterion(byExpr1);
            query0.open();
         }
      });
   }
}

OPEN QUERY FOR EACH ..., EACH ... BY, where BY clause contains fields of non-outermost tables. All tables are within the same database.

Components are added to PreselectQuery using addComponent method. Sort string remains as is after conversion.

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb b1 b2.

open query q for each a, each b where b3 = 1 by b1.

Converted example:

query0 = new PreselectQuery("b.b1 asc");
query0.addComponent(a, ((String) null));
query0.addComponent(b, "b.b3 = 1");
query0.open();

OPEN QUERY PRESELECT EACH ..., EACH ... [BY], where all joined tables are within the same database.

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa is primary a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb is primary b1 b2.

open query q preselect each a, each b by a1.

Converted example:

query0 = new PreselectQuery("a.a1 asc");
query0.addComponent(a, ((String) null));
query0.addComponent(b, ((String) null));
query0.open();

As you can see, the sort string was left as is.

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa is primary a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb is primary b1 b2.

open query q preselect each a, each b.

Converted example:

query0 = new PreselectQuery("a.a1 asc, a.a2 asc, b.b1 asc, b.b2 asc");
query0.addComponent(a, ((String) null));
query0.addComponent(b, ((String) null));
query0.open();

In this example the sort string was combined from the primary indexes of both components.

OPEN QUERY { FOR | PRESELECT } EACH ..., EACH ... BY , where BY clause cannot be represented by an equivalent SQL construction. All tables are within the same database.

PresortQuery is used. Individual components are added to it using addComponent method.

4GL example (file name is test.p):

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa is primary a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb is primary b1 b2.

open query q for each a, each b by a1 by b1 + b2.

Converted example:

public class Test
{
   ...
   IntegerExpression byExpr1 = new IntegerExpression()
   {
      public integer execute()
      {
         return plus(b.getB1(), b.getB2());
      }
   };

   public void execute()
   {
      externalProcedure(new Block()
      {
         PresortQuery query0 = null;

         FieldReference byExpr0 = new FieldReference(a, "a1");

         public void body()
         {
            ...
            query0 = new PresortQuery();
            query0.addComponent(a, ((String) null));
            query0.addComponent(b, ((String) null));
            query0.addSortCriterion(byExpr0);
            query0.addSortCriterion(byExpr1);
            query0.open();
         }
      });
   }
}

OPEN QUERY FOR EACH ..., { EACH | FIRST | LAST } ... [BY], where BY clause is not present or it contains only fields of the outermost table. Tables may be in the same or different databases.

CompoundQuery is used for joining components because it allows to join dynamic queries without preselecting results and, additionally, can join tables which cannot be joined at database level.

Components of the non-outermost tables do not have any sort fields specified and they are represented by AdaptiveQuery sub-queries for EACH components and by RandomAccessQuery sub-queries for FIRST and LAST components.

EACH component which represents the outermost table can have some sort fields specified (that will not break “the order of tables defines the order of sorting” criterion used to distinguish this conversion case), so it can be represented by:

an AdaptiveQuery sub-query if BY clause is not specified or it matches an index of the outermost table;
a PreselectQuery sub-query if BY clause does not match any index of the outermost table.

Note that CompoundQuery should not preselect all results in this case and therefore the preselect parameter of its constructor is set to false.

no sort fields for this component - the fields which correspond the most appropriate index for this component are used;
sort fields match an index (applicable to the outermost EACH component only) - the fields which correspond this index are used;
sort fields does not match any index (applicable to the outermost EACH component only) - the sort fields define primary sorting and the fields which correspond the most appropriate index define more granular sorting.

4GL example where the outermost component is represented by an AdaptiveQuery:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb b1 b2.

open query q for each a, each b by a.a1.

Converted code:

query0 = new CompoundQuery(true, false);
query0.addComponent(new AdaptiveQuery(a, (String) null, null, "a.a1 asc, a.a2 asc"));
query0.addComponent(new AdaptiveQuery(b, (String) null, null, "b.b1 asc, b.b2 asc"));
query0.open();

4GL example where the outermost component is represented by a PreselectQuery:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb b1 b2.

open query q for each a, each b by a.a2.

Converted code:

query0 = new CompoundQuery(true, false);
query0.addComponent(new PreselectQuery(a, (String) null, null, "a.a2 asc, a.a1 asc"));
query0.addComponent(new AdaptiveQuery(b, (String) null, null, "b.b1 asc, b.b2 asc"));
query0.open();

4GL example with FIRST component and tables joined across databases:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

open query q for each a, first address.

Converted code:

query0 = new CompoundQuery(true, false);
query0.addComponent(new AdaptiveQuery(a, (String) null, null, "a.a1 asc, a.a2 asc"));
query0.addComponent(new RandomAccessQuery(address, (String) null, null, "address.addrId asc",
                    QueryConstants.FIRST);
query0.open();

OPEN QUERY FOR EACH ..., { EACH | FIRST | LAST } ... BY, where BY clause contains fields of non-outermost tables or it cannot be represented by an equivalent SQL construction. Tables cannot be joined at database level (FIRST / LAST components are used or tables are joined across databases).

PresortCompoundQuery is used because:

It is compound and can perform join for tables that cannot be joined at database level.
It has preselect nature, so it allows to preserve sorting defined by fields of non-outermost tables.
It has presort capabilities and allows to specify a sort criterion that cannot represented by an equivalent SQL construction.
Sort string is parsed and each sort criterion (either expression or a field) is added using addSortCriterion method, in the same way as for the PresortQuery. Additionally, sort strings for individual components are specified and define more granular sorting. Rules of formation of these sort strings for EACH components are equivalent to single-table cases (you can see these rules are listed in the previous conversion case). Rules for FIRST / LAST components are slightly different because these components use only index-driven sorting and ignore sort components if they does not match an index. I.e.:

no sort fields for this component - the fields which correspond the most appropriate index for this component are used;
sort fields match an index - the fields which correspond this index are used;
sort fields does not match any index - sort fields are ignored and the most appropriate index is picked using other criteria (e.g. the primary index can be used).

EACH components are represented by PreselectQuery sub-queries.

4GL example with BY clause that contains fields of non-outermost tables:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb b1 b2.

open query q for each a, first b by b.b1.

Converted code:

FieldReference byExpr0 = new FieldReference(b, "b1");
...
query0 = new PresortCompoundQuery();
query0.addComponent(new PreselectQuery(a, (String) null, null, "a.a1 asc, a.a2 asc"));
query0.addComponent(new RandomAccessQuery(b, (String) null, null, "b.b1 asc, b.b2 asc"),
                    QueryConstants.FIRST);
query0.addSortCriterion(byExpr0);
query0.open();

4GL example with BY clause that cannot be represented by an equivalent SQL construction:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb b1 b2.

open query q for each a, first b by a1 + a2 by a3.

Converted example:

IntegerExpression byExpr0 = new IntegerExpression()
{
   public integer execute()
   {
      return plus(a.getA1(), a.getA2());
   }
};
...
FieldReference byExpr1 = new FieldReference(a, "a3");
...
query0 = new PresortCompoundQuery();
query0.addComponent(new PreselectQuery(a, (String) null, null, "a.a3 asc, a.a1 asc, a.a2 asc"));
query0.addComponent(new RandomAccessQuery(b, (String) null, null, "b.b1 asc, b.b2 asc"),
                    QueryConstants.FIRST);
query0.addSortCriterion(byExpr0);
query0.addSortCriterion(byExpr1);
query0.open();

4GL example with FIRST component which ignores fields specified in the BY clause:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb is primary b1 b2
     index idxb2 b2 b3 b1.

open query q for each a, first b where b2 > 0 by b.b3.

Converted example:

FieldReference byExpr0 = new FieldReference(b, "b3");
…
query0 = new PresortCompoundQuery();
query0.addComponent(new PreselectQuery(a, (String) null, null, "a.a1 asc, a.a2 asc"));
query0.addComponent(new RandomAccessQuery(b, "b.b2 > 0", null, "b.b2 asc, b.b3 asc, b.b1 asc"),
                    QueryConstants.FIRST);
query0.addSortCriterion(byExpr0);
query0.open();

OPEN QUERY PRESELECT EACH ..., { EACH | FIRST | LAST } ... [BY], where BY clause, if specified, can either be simple or contain expressions that cannot be represented by equivalent SQL constructions. Tables cannot be joined at database level (FIRST / LAST components are used or tables are joined across databases).

PresortCompoundQuery is used because a compound and preselect query is required for this case. EACH components are represented by PreselectQuery sub-queries. FIRST / LAST components are represented by RandomAccessQuery sub-queries.

4GL example:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb b1 b2.

open query q preselect each a, first b.

Converted example:

query0 = new PresortCompoundQuery();
query0.addComponent(new PreselectQuery(a, (String) null, null, "a.a1 asc, a.a2 asc"));
query0.addComponent(new RandomAccessQuery(b, (String) null, null, "b.b1 asc, b.b2 asc"),
                    QueryConstants.FIRST);
query0.open();

4GL example with complex BY clause:

def temp-table a
     field a1 as integer
     field a2 as integer
     field a3 as integer
     index idxa a1 a2.

def temp-table b
     field b1 as integer
     field b2 as integer
     field b3 as integer
     index idxb b1 b2
     index idxb2 b3 b1.

open query q preselect each a, first b by a1 + a2 by b3.

Converted example:

IntegerExpression byExpr0 = new IntegerExpression()
{
   public integer execute()
   {
      return plus(a.getA1(), a.getA2());
   }
};
...
FieldReference byExpr1 = new FieldReference(b, "b3");
...
query0 = new PresortCompoundQuery();
query0.addComponent(new PreselectQuery(a, (String) null, null, "a.a1 asc, a.a2 asc"));
query0.addComponent(new RandomAccessQuery(b, (String) null, null, "b.b3 asc, b.b1 asc"),
                    QueryConstants.FIRST);
query0.addSortCriterion(byExpr0);
query0.addSortCriterion(byExpr1);
query0.open();

CLOSE QUERY¶

4GL Form	Description	Supported	Notes
`CLOSE QUERY query`	Closes a query that was opened by a previous `OPEN QUERY` statement.	Yes

Converted into a call of the QueryWrapper.close() function (if the query was previously defined by DEFINE QUERY) or FWDQuery.close() (if the query wasn't previously defined by DEFINE QUERY).

4GL example:

open query q for each person.
close query q. // closing the query

Converted example:

AdaptiveQuery query0 = null;
...
query0 = new AdaptiveQuery(person, (String) null, null, "person.siteId asc, person.empNum asc");
query0.open();
query0.close(); // closing the query

GET¶

4GL Form	Description	Supported	Notes
`GET` `{ FIRST \| NEXT \| PREV \|` `LAST \| CURRENT query` `[ SHARE-LOCK \| EXCLUSIVE-LOCK \|` `NO-LOCK ]` `[ NO-WAIT ]`	Performs iteration of the specified type on a previously opened query and fetches the corresponding record(s).	Yes

Converted into a call of the appropriate method of QueryWrapper which corresponds the specified query:

Progress Keyword	QueryWrapper Methods
`FIRST`	`first()` `first(LockType lockType)`
`NEXT`	`next()` `next(LockType lockType)`
`PREV`	`previous()` `previous(LockType lockType)`
`LAST`	`last()` `last(LockType lockType)`
`CURRENT`	`current()` `current(LockType lockType)`

where:

lockType is the type of lock to acquire on the found record (LockType.NONE, LockType.SHARE, LockType.EXCLUSIVE, LockType.SHARE_NO_WAIT, LockType.EXCLUSIVE_NO_WAIT).

4GL example:

open query q for each person.
get first q exclusive-lock no-wait. // get the first record with the specific lock

Converted example:

query0 = new AdaptiveQuery(...);
query0.open();
query0.first(LockType.EXCLUSIVE_NO_WAIT); // get the first record with the specific lock

REPOSITION QUERY¶

4GL Form	Description	Supported	Notes
`REPOSITION query` `{ TO ROWID rowid1 [ , rowid2 ] ...` `[ NO-ERROR ]` `\| TO RECID recid [ NO-ERROR ]` `\| TO ROW n` `\| FORWARDS n` `\| BACKWARDS n` `}`	Repositions the cursor associated with a specific query. The query must be associated with a browse widget or defined with the `SCROLLING` option. The next record to be retrieved is the record following the cursor position.	Yes	If reposition `TO ROWID` or `TO RECID` fails (because of incorrect row / record identifier) then an error is raised. If reposition `TO ROW`, `FORWARDS` or `BACKWARDS` fails (because of incorrect target row number / number of rows to move) then error is NOT raised.

Reposition functions are defined by com.goldencode.p2j.persist.P2JQuery interface which is implemented by all query types presented in FWD.

Progress Keyword	FWDQuery Methods
`TO ROWID rowid1 [ , rowid2 ] ...`	`repositionByID(rowid rowid1, rowid...rowidN)` Reposition the cursor such that a request to retrieve the next result will retrieve the result which matches the specified array of `rowid` record identifiers.
`TO RECID recid`	`repositionByID(recid recid)` Reposition the cursor such that a request to retrieve the next result will retrieve the result which matches the specified `recid` record identifier (in the case of a join, this identifier is associated with the left-most record in the join).
`TO ROW n`	`reposition(NumberType n)` `reposition(int n)` Reposition the cursor before the specified row `n` in the result set, such that a call to retrieve the next record will get the result at the specified row, and a call to retrieve the previous result will get the result before the specified position.
`FORWARDS n`	`public void forward(NumberType n)` `public void forward(int n)` Advance the current cursor position forward by the specified number of rows. This will always leave the cursor between two results. For example, given a request to move 1 row forward: • if the cursor currently is positioned directly on a result, this would move it ahead one and a half positions, to just beyond the following result; • if the cursor currently is positioned between two results, this would move it ahead one position, to just beyond the following result.
`BACKWARDS n`	`public void backward(NumberType n)` `public void backward(int n)` Move the current cursor position backward by the specified number of rows. This will always leave the cursor between two results. For example, given a request to move 1 row backward: • if the cursor currently is positioned directly on a result, this would move it back one half position, to just before the current result; • if the cursor currently is positioned between two results, this would move it back one position, to just before the current result.

4GL example:

open query q for each person.
reposition q forwards 3.   // reposition forwards
reposition q to recid rid. // reposition to specific record id

Converted example:

query0 = new AdaptiveQuery(...);
query0.open();
query0.forward(3);          // reposition forwards
query0.repositionByID(rid); // reposition to specific record id

In this section only integration of the browse widget with its backing query will be covered. You can read how this widget is represented in UI-related converted code in the DEFINE BROWSE Statement section of the Frames chapter.

4GL Form	Description	Supported	Notes
`DEFINE [ [ NEW ] SHARED ]` `BROWSE name [ QUERY query-name ]` `[ SHARE-LOCK \| EXCLUSIVE-LOCK \|` `NO-LOCK ]` `[ NO-WAIT ]` `[ DISPLAY { column-list \|` `record [ EXCEPT field ... ] } ]` `[ browse-enable-phrase ]` `{ browse-options-phrase }` `[ CONTEXT-HELP-ID expression ]` `[ DROP-TARGET ]` `[ TOOLTIP tooltip ]`	Performs iteration of the specified type on a previously opened query and fetches the corresponding record(s).	Yes	`SHARE-LOCK`, `EXCLUSIVE-LOCK`, `NO-LOCK` and `NO-WAIT` options are ignored, for the records retrieved by the browse widget the lock mode which is specified in the `OPEN QUERY` statement (for the corresponding backing query) is used.

DEFINE BROWSE statement implies binding the backing query to the browse. That is performed in the converted code using the registerQuery(FWDQuery query, FrameElement[] list) method of the com.goldencode.p2j.ui.BrowseWidget class. This method takes two parameters:

query is the target backing query. Can be null, as the QUERY clause is optional.
list is the list of the fields to be displayed in the browse represented by the com.goldencode.p2j.ui.Element elements which link record fields with corresponding browse columns. Can be null, as the DISPLAY columns clause is optional.

An example:

FrameElement[] elementList0 = new FrameElement[]
{
   new Element(new FieldReference(tt, "f1"), fFrameFrame.widgetBrwsF1Column()),
   new Element(new FieldReference(tt2, "f2"), fFrameFrame.widgetBrwsF2Column())
};
fFrameFrame.widgetBrws().registerQuery(query0, elementList0);

Note that even if the backing query of a browse widget is defined as non-scrolling, it is converted to a scrolling query. In FWD that means that means that QueryWrapper constructor with scrolling parameter set to true will be used (if the backing query was declared using DEFINE QUERY) or setScrolling method will be called on the backing query (if the DEFINE QUERY declaration is missing).

You can access the query which backs a browse widget directly and perform operations like GET or REPOSITION on it. Then the following rules will take place:

Modification of the current row in the browse widget through UI (e.g. using up and down keys) causes the current result row number to be updated (you can get it using the CURRENT-RESULT-ROW function) and record(s) contained in the current row are fetched into corresponding record buffer(s).
Use of the GET { FIRST | NEXT | PREV | LAST } statement updates the current result row and fetches the records, but it does NOT reflected in the UI. Consider the selected row in the browse widget is the row №3. You've called GET FIRST. The current row will be №1, however in the browse the row №3 still will be focused. If you will press the down key the row №4 will be focused and it will also be the current result row.
Use of the REPOSITION { FORWARDS | BACKWARDS | TO ROW | TO RECID | TO ROWID } statement causes the browse widget to be updated, the current result row becomes the selected browse row. If after reposition the navigation cursor is placed between rows, then the position is advanced to the next row and record(s) at this row are fetched (this does not happen if a query is not binded to a browse widget).
Use of the CLOSE QUERY statement causes the browse widget to be updated - no records are displayed in the browse.
Use of the OPEN QUERY statement causes the browse widget to be refreshed and position is advanced to the first row.

Project

General

Profile

FWD

Wiki

Queries¶

FIND¶

FOR¶

DO/REPEAT PRESELECT¶

DEFINE/OPEN QUERY¶

Data Access Language Statements¶

FIND¶

FindQuery¶

FOR¶

Conversion¶

Parameters¶

Conversion cases¶

DO/REPEAT PRESELECT¶

Conversion¶

Parameters¶

Conversion cases¶

DEFINE/OPEN/CLOSE QUERY, GET¶

DEFINE QUERY¶

OPEN QUERY¶

Conversion (without DEFINE QUERY)¶

Conversion (with DEFINE QUERY)¶

Parameters¶

Conversion cases¶

CLOSE QUERY¶

GET¶

REPOSITION QUERY¶

BROWSE widget (integration with queries)¶