Kbase 19818: AS/400: Force Client to resolve FOR EACH queries With a Cas
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/31/2003 |
|
Status: Unverified
FACT(s) (Environment):
Progress 8.0Cxx
FACT(s) (Environment):
Progress 9.0A
FACT(s) (Environment):
Progress 9.1A
FACT(s) (Environment):
Progress/400 DataServer
SYMPTOM(s):
No records are retrieved when using a FOR EACH statment against case-sensitive indexes
CAUSE:
DB2/400 handles case sensitivity. By default, Progress is case insensitive, but DB2/400 is case sensitive. In this example provided below, the DB2/400 case sensitivity is applied at the index level, but on a standard Progress database it is applied at the field level.
If a multi-component index is defined in a Progress/400 DataServer and just one of the components is case sensitive, the entire index becomes case sensitive. For example, the following is a case-sensitive
index:
Index : pmword-1 on table pmword
- cpy FORMAT X(6)
- word FORMAT X(20) CASE-SENSITIVE
If the data physically stored in the cpy field is "daz" (lower case) and there is no index defined in the "cpy" field alone, it would be correct to expect the following query against the DataServer to retrieve some records:
FOR EACH pmword WHERE cpy = "daz":
However, in this situation, no records would be returned because the query is searching purely on a case insensitive component of a case sensitive index.
Before submitting the query to the Dataserver, the compiler converts it to upper case. Subsequently no records are found with a "cpy" field matching "DAZ".
If the data physically stored in the "cpy" field is converted to "DAZ" and the same lower case query run, the expected records will be displayed.
FIX:
Force the client to resolve the query by using functions such as TRIM or a BY clause.