Kbase 17920: User Defined Functions in WHERE clauses ( FIND or FOR EACH )
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
13/05/1998 |
|
User Defined Functions in WHERE clauses ( FIND or FOR EACH )
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.