Kbase P86785: Sample code to achieve a pseudo Dynamic query with Progress earlier than version 9
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
15/11/2005 |
|
Status: Verified
GOAL:
Sample code to achieve a pseudo Dynamic query with Progress earlier than version 9
GOAL:
How to achieve a variable query with compile time argument?
GOAL:
Sample code to achieve a variable query without using a dynamic query object
FACT(s) (Environment):
Progress 8
FIX:
The ability to modify dynamically a static or dynamic query was introduced in Progress Version 9. With earlier version, you can take advantage of compile time arguments to achieve something similar, but this technique requires a development license because it involves compilation on the fly.
The following piece of code achieves a kind of pseudo dynamic query with version 8. It uses a shared query and a shared buffer and compiles on the fly the program that opens the query with a where-clause passed as an argument.
With this technique, you can even get rid off sophisticated query-where clauses that might lead to a bad index usage.
This sample code works against the sports database. It actually passes the all query-prepare phrase (and not only the where clause). To test it, just run main.p and type 'a' for name.
/*main.p*/
DEF NEW SHARED BUFFER bcust FOR customer.
DEF NEW SHARED QUERY q FOR bcust.
DEF VAR name AS CHAR NO-UNDO.
DEF VAR s AS CHAR NO-UNDO.
UPDATE name WITH THREE-D SIDE-LABELS.
s = "FOR EACH bcust NO-LOCK WHERE".
IF name <> "" THEN s = s + " AND bcust.name BEGINS '" + name + "'".
/*put other conditions here*/
s = REPLACE(s,"WHERE AND","WHERE").
IF s MATCHES "* WHERE" THEN s = REPLACE(s," WHERE",""). /*No need of where?*/
RUN OpenQ.p s.
REPEAT:
GET NEXT q.
IF QUERY-OFF-END("q") THEN LEAVE.
DISPLAY bcust.
END.
/*OpenQ.p*/
DEF SHARED BUFFER bcust FOR customer.
DEF SHARED QUERY q FOR bcust.
OPEN QUERY q {1}.