Kbase P77953: 4GL: User Defined Functions (UDF) in WHERE clauses (FIND , CAN-FIND or FOR EACH)
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
07/04/2010 |
|
Status: Unverified
GOAL:
4GL: User Defined Functions (UDF) in WHERE clauses (FIND , CAN-FIND or FOR EACH)
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
Although the UDF are allowed in the FIND , CAN-FIND and FOR EACH WHERE clauses, the developer needs to be aware that the WHERE clause is evaluated before the (FIND , CAN-FIND or FOR EACH) ever starts, as global data. That is equivalent to putting it outside the WHERE in a prior statement. If it references the same record that is being sought, and expects to be rerun on every iteration, it will not work. Hence, if a user defined function (UDF) is used with a WHERE clause, it might result in record-seeking behavior which differs from a case where no UDF is used.
The example below shows how a standard WHERE clause can be written using a user defined function:
FUNCTION same_string RETURNS LOGICAL
(INPUT Pstring1 AS CHARACTER,
INPUT Pstring2 AS CHARACTER).
IF Pstring1 = Pstring2 THEN RETURN TRUE.
ELSE RETURN FALSE.
END FUNCTION.
/* Instance #1 -- no UDF used */
FIND FIRST customer WHERE customer.name = customer.name
NO-LOCK NO-ERROR.
IF AVAILABLE customer
THEN MESSAGE "Available for customer.name = customer.name".
RELEASE customer.
/* Instance #2 - UDF used in WHERE clause */
FIND FIRST customer WHERE same_string(customer.name, customer.name
NO-LOCK NO-ERROR.
IF AVAILABLE customer THEN MESSAGE
"Available for same_string function".
When this procedure is run, the second FIND does not make a record available.
This is due to the fact that the server must be involved in any selection of records -- a requirement that conflicts with the fact that a user defined function must be evaluated on the client side.
UDFs in WHERE clauses therefore exhibit a great deal of unpredictability. For instance, if a UDF in a WHERE clause also happens to run other functions that include WHERE clauses, a recursive condition could be created that would cause further problems.
A user defined function is not known to the server, so it cannot be sent to the server for evaluation during record selection. It must be first evaluated on the client side and the result sent to the server. In the case given above, the evaluation depends on the record that is being sought, making it impossible to evaluate the UDF on the client side first.
Currently, the compiler does not detect when 4GL code sets up this type of paradox, making the result of the UDF WHERE clause undefined. Development is undecided at present whether to force final selection to the client or to disallow the use of UDFs in WHERE clauses entirely. The latter approach may be taken if it is determined that selection cannot be guaranteed to work properly when the first approach is tried.