Kbase P61230: How to add a where clause to a query using the QUERY-PREPARE
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
1/7/2004 |
|
Status: Unverified
GOAL:
How to add a where clause to a query using the QUERY-PREPARE() method
GOAL:
Things to keep in mind when building the prepare-string for a dynamic query
FIX:
The important thing to keep in mind is that the QUERY-PREPARE() method can not resolve variables and function calls in the same way the OPEN QUERY statement does. Therefore the prepare-string should contain character representations of the actual values to be used for any filtering.
This means that any character type values should appear quoted within the prepare-string, and non-character values should be converted to a character representation before adding them to the prepare-string.
The following examples shows the relation between an OPEN QUERY statement and a QUERY-PREPARE call.
A typical OPEN QUERY statement with variable references looks like:
DEFINE QUERY CustQuery FOR customer.
DEFINE VARIABLE cName AS CHARACTER NO-UNDO INITIAL 'Blue'.
DEFINE VARIABLE iNum AS INTEGER NO-UNDO INITIAL 4.
OPEN QUERY CustQuery FOR EACH customer WHERE customer.name BEGINS cName
AND customer.cust-num > iNum.
If the variable values would have been hardcoded, the actual query predicate would be:
FOR EACH customer WHERE customer.name BEGINS 'Blue' AND customer.cust-num > 4
The prepare-string used in the QUERY-PREPARE() method should look the same, so using the cName and iNum variables it would be build as follows:
"FOR EACH customer WHERE customer.name BEGINS '" + cName + "'" +
"AND customer.cust-num > " + STRING(iNum).
Taking the OPEN QUERY example above and modifying it to use QUERY-PREPARE, the end result would look like:
DEFINE QUERY CustQuery FOR customer.
DEFINE VARIABLE cName AS CHARACTER NO-UNDO INITIAL 'Blue'.
DEFINE VARIABLE iNum AS INTEGER NO-UNDO INITIAL 4.
DEFINE VARIABLE hCustQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE cPrepare AS CHARACTER NO-UNDO.
hCustQuery = QUERY CustQuery:HANDLE.
cPrepare = "FOR EACH customer WHERE customer.name BEGINS '" + cName + "'" +
" AND customer.cust-num > " + STRING(iNum).
hCustQuery:QUERY-PREPARE(cPrepare).
hCustQuery:QUERY-OPEN().