Kbase P81403: Dynamic query or FOR EACH with variable fields or tables
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
8/28/2008 |
|
Status: Verified
GOAL:
Dynamic query or FOR EACH with variable fields or tables
GOAL:
How to create a Dynamic Query with variable fields names?
GOAL:
How to create a FOR EACH with variable fields names?
GOAL:
How to create a Dynamic Query with variable tables names?
GOAL:
How to create a FOR EACH with variable tables names?
FACT(s) (Environment):
All Supported Operating Systems
Progress/OpenEdge Versions
FIX:
Many customers ask whether it is possible to write PROGRESS code that would allow the fields in a frame to be determined at runtime, or whether the fields and tables in a query can be determined at runtime.
The term generally used to describe this concept is DYNAMIC QUERY. In Version 6 this question might arise as a wish to write a FOR EACH that can apply to any number of different tables, with the user determining the target table by setting a variable at runtime. In Version 7 it might come up in the form of a selection list that shows the tables and/or fields in a database. Based on whatever table or field the user chooses from the list, the programmer wishes to open a query in a browse.
One solution attempted by programmers is to make use of the metaschema (_file, _field, etc.) to access table names and field names. They then attempt to use variables to plug these values into a query statement or FOR EACH. A few other creative approaches have also been attempted.
Unfortunately, this kind of design is not possible in precompiled PROGRESS code. (It is possible to create this effect if a deployed application consists partly of UNCOMPILED source code, however this requires an end-user to purchase a more costly license configuration that can compile source code.)
While many features of the data structures in PROGRESS can be determined dynamically at runtime, some (such as frame layout) *must* be determined at compile time.
For most applications, compile time and runtime are very separate, since most applications are compiled by a programmer ahead of time, prior to deployment at a user site.
Where does this limitation come from? It comes from the PROGRESS Frame Manager, which requires that the size and content of a frame be determined before the frame's associated code is executed. (However, other aspects of the frame, such as its location on the screen, can be determined at runtime.) This feature is part of the fundamental architecture of PROGRESS and therefore is not expected to change anytime in the future.
As a result, the following is NOT possible using precompiled PROGRESS code:
Write a query where the name of the queried table can change based on a variable or user input, or where the fields displayed can change.
Write a FOR EACH on a table where the name of the table can change based on a variable or user input, or where the fields displayed can change.
Write a FORM statement where the fields included in the definition are determined based on a variable or user input.
Write a DEFINE FRAME statement where the fields included are determined based on a variable or user input.
While the name of a field or table must be determined at compile time, it is possible to write a WHERE clause that fine-tunes a query or FOR EACH based on user input or contents of a variable. Note, however, that it is not the field or table that changes at runtime, but the range over which it is accessed.
DEF VAR x AS CHAR.
UPDATE x WITH FRAME dialog-1 VIEW-AS ALERT-BOX.
OPEN QUERY browse-2 FOR EACH customer WHERE name BEGINS x.
What can be done with code that is NOT precompiled? If a programmer is willing to deploy uncompiled source code to a user site, then the way to implement a "dynamic query" is by using arguments. The example below shows a simple means of doing this. Note that two separate programs are required: one to obtain the table/field names to be queried, the other to execute the actual query. It is the second program that must remain stored as uncompiled source.
/* main.p This code may be precompiled into a .r */
DEF VAR cTableName AS CHARACTER NO-UNDO.
DEFINE VARIABLE cWhereClause AS CHARACTER NO-UNDO.
DEFINE VARIABLE cFieldList AS CHARACTER NO-U.NDO.
ASSIGN
cTableName = "Customer"
cWhereClause = "WHERE Custnum > 10 AND Custnum < 20"
cFieldList = "CustNum FORMAT ~"ZZZZZ~" Name FORMAT ~"X(20)~"".
RUN p-foreach.p cTableName cWhereClause cFieldList.
/* p-foreach.p */ /* This code is NOT precompiled */
FOR EACH {1} {2}:
DISPLAY {3}.
END.
The same approach can be used with variable names as shown:
/* main.p */
DEF VAR x AS CHAR INITIAL "var-name".
RUN p-foreach.p x.
/* p-foreach.p */
DEF VAR {1} AS CHAR.
UPDATE {1} WITH SIDE-LABELS.
FOR EACH customer WHERE name BEGINS {1}:
DISPLAY name.
END.
This approach can also be applied to DEFINE FRAME and FORM statements.
Please note that encrypting source code (xcode) does not in any way get around this architectural limitation..