Consultor Eletrônico

Using a dynamic query with Smart Objects

This knowledgebase entry discusses three possible
approaches to providing some level of dynamic query capability
using Smart Objects. Approaches 1 and 2 can be used in version
8.0A and higher. Approach 3 relies on use of a temp-table as
a parameter in a run statement, and so can only be used in
version 8.1A and higher. Version 8.1A also provides some
native dynamic query capability using the XFTRS filter option,
which is discussed in Knowledgebase entry 16326, entitled
Example Using the XFTR Advanced Query Filter (Version 8.1A).

There are advantages and disadvantages to each approach which are
discussed also.

Approach 1:
Use a freeform query, and an include file with a conditional
IF statement.

The major disadvantage of this approach is that it is likely
to be slow.

An advantage is that it can be deployed with only a run-time
license.

Below are the steps to create an example using this approach:

1. Create a .i file like the following. Note that this approach
can be used with ranges or inequalities or BEGINS, but
cannot be used with CONTAINS clauses.

/************************ myfld.i *************************/
(IF LOOKUP ("{1}", fld-names) > 0
THEN
{1} = {2}(entry(LOOKUP("{1}",fld-names), fld-values))
ELSE TRUE)
/************** end of myfld.i ****************************/

2. Create a Smart Browser with a freeform query. Note that
the fld-names and fld-values variables can be changed
at run-time, and are only initialized in this example
for demo purposes. In the example code below, for example,
the variable fld-names could be set to "name,state", and
the query would find any customers with the name of "Boston"
and a state of "MA".

- The following definitions are put in the definitions section.

DEFINE VARIABLE fld-names AS CHAR INIT "city,state".
DEFINE VARIABLE fld-value AS CHAR INIT "Boston,MA".

- The following is the freeform query. Note that the tilde
character is required to use an include file.

/* OPEN QUERY trigger */
OPEN QUERY {&SELF-NAME} FOR EACH Customer WHERE
~{myfld.i name~}
AND
~{myfld.i cust-num int ~}
AND
~{myfld.i city~}
AND
~{myfld.i state~}

/* DISPLAY trigger */
cust-num name city state


Approach 2:

In local-open-query, comment out the standard behavior, and
dynamically create a program that opens the query based on the
requested fields, and run it.

An advantage of this approach is that it is likely to be
faster than approach 1 because only the fields of interest
are mentioned in the query.

Two disadvantages are that it requires a development license
to do the session compile, and it requires that the query be
modified to a NEW SHARED query using the procedure editor,
making the code unusable by the UIB. It is likely that
it will possible to make queries NEW SHARED in version 8.2
without using the procedure editor.

Approach 3:

This approach is only available in 8.1A and higher. This is
similar to Approach 2, in that code is generated dynamically
based on user input. The generated code populates a temp-table,
which is a parameter, and the temp-table is then used as the
table for the query. Knowledgebase entry 15831, entitled
"How to build a browse with a TEMP-TABLE with version 7 UIB"
can be used for information on how to use a temp-table in
the UIB.

An advantage of this approach is that it is likely to be
faster than approach 1, and it does not require any
procedure editor modifications that make the .w code unusable
by the UIB.

A disadvantage is that it is not available until 8.1A, and
requires a development license to do the session compile.


Progress Software Technical Support Note # 16545