Kbase P26689: Progress Oracle DataServer does not find records that have leading or trailing spaces in an indexed
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
25/05/2011 |
|
Status: Verified
SYMPTOM(s):
Progress Oracle DataServer does not find records that have leading or trailing spaces in an indexed column.
Oracle DataServer cannot find records that have leading or trailing spaces
Using a WHERE clause in FIND first statement
The right-hand-side (RHS) value of the WHERE clause is a column name in Oracle
The left-hand-side (LHS) value of the WHERE clause is a buffer defined for the table referencing the same table's same column
The first record in the table is a record with trailing spaces
Example code :
DEFINE BUFFER b_cust FOR customer.
/* First customer has a name of "Bob " */
FIND FIRST customer NO-LOCK NO-ERROR.
IF AVAILABLE customer THEN
DO:
/* without -znotrim, the DataServer compares "Bob " with "Bob" */
FIND FIRST b_cust NO-LOCK WHERE b_cust.NAME = customer.NAME NO-ERROR.
MESSAGE AVAILABLE b_cust
VIEW-AS ALERT-BOX INFO BUTTONS OK.
END.
FACT(s) (Environment):
OpenEdge 10.x
Progress 9.x
Progress 8.x
All Supported Operating Systems
Oracle DataServer
CAUSE:
By default, PROGRESS Oracle DataServer always trims leading or trailing blanks from the right-hand-side (RHS) values of the WHERE clause before comparing it to the value or variable on the left-hand-side (LHS). The right-hand-side's "Bob " is trimmed to "Bob" but the left-hand-side's "Bob " is not trimmed. Thus the FIND FRIST does not find this record.
FIX:
In order to bypass the default behavior for ORACLE DataServer, the startup option -znotrim is available as a runtime client parameter for PROGRESS. This startup parameter only alters the behavior of trailing spaces in the WHERE clause. It will always alter the behavior of the FIND ...WHERE statements if trailing spaces are stored for VARCHAR2 datatype in ORACLE database.