Kbase 15408: Dynamic query or FOR EACH with variable fields or tables
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
8/13/1998 |
|
Dynamic query or FOR EACH with variable fields or tables
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:
o 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.
o 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.
o Write a FORM statement where the fields included
in the definition are determined based on a variable
or user input.
o 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 tablename AS CHAR.
DEF VAR fieldname AS CHAR.
UPDATE tablename fieldname.
RUN p-foreach.p tablename fieldname.
/* p-foreach.p */ /* This code is NOT precompiled */
FOR EACH {1}:
DISPLAY {2}.
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.
Progress Software Technical Support Note # 15408