Problem:
The application is using OpenESQL to retrieve data from an Oracle table.
One of the columns is defined as a VARCHAR(5) and contains the value "AN " with trailing spaces left intact.
If the WHERE clause of the SELECT statement is compaing the colun to a literal value of "AN " it will retrieve the the rows containg this value.
But if the WHERE clause is changed to compare against a host variable of PIC X(5) which contains the value "AN " then no rows will be returned.
The user would like to know why.
Resolution:
Here is the explanation:
OpenESQL is working as expected --- the problem here is space padding on varchar fields.
For a standard character data type in a database that has a length of 5, "AN" and "AN " are identical; however, this is not the case when it comes to the variable character data type --- indeed "AN" is not the same as "AN ".
Years ago database vendors recognized this fact, and developed a "standard" , which OpenESQL uses --- simple pic x host variables are stripped of space padding going to the database for any database request, and, when a simple pic x host variable is used to collect information from a database, it is space padded back out.
This provides a consistency for accessing both char and varchar fields in a database --- ie ensure that if COBOL does the insert with a particular host variable value, it will indeed be able to retrieve it! (this is why this problem cannot be reproduced in a COBOL program that does both selects and inserts)
Here is the bottom line:
if the value "AN " is inserted into a varchar field as a literal, the actual value is "AN " in the database, not "AN". the only way to retrieve this value is to use a host variable defined with level 49 items of a length field followed by the data field where you explicitly ask for "AN " when 5 byte length is specified.
Either one of the following suggestions would solve the problem:
1. The program that inserts the rows should be changed to store the value "AN" rather than
"AN " --- then it would be consistent with COBOL.
2. Change the column in question's data type from varchar to char.
