Re: XMLSERVICE and losing Wildcard from SQL LIKE

On 16-Dec-2015 13:24 -0600, Richard Schoen wrote:

On Wed, 16 Dec 2015 13:12:18 -0600 CRPence wrote:

On 16-Dec-2015 11:06 -0600, Richard Schoen wrote:

On 16-Dec-2015 08:06 -0600, Richard Schoen wrote:

<<SNIP>>

The following SQL query works:

select * from qiws/qcustcdt where lstnam like 'Henning'

Only if the result of "No rows" defines "works"; LSTNAM being
CHAR(8) vs VARCHAR(8), means the last blank has significance.

<<SNIP>> the pct sign was dropped <<SNIP>>

<<SNIP>>

The EBCDIC Percent Sign [GCID SM020000 at code point 0x6C of
CP00037] would be correctly translated into ASCII as code point
0x25. And as John alludes, the 0x25 in EBCDIC encoding is a control
character Line Feed (LF), but that should not be relevant, unless
something had gone horribly wrong; a flawed processor that blindly
converted all 0x25 to 0xA0, but issued against the ASCII data,
would qualify as something horribly wrong.

Oops. put a TRIM(LSTNAM) and it works.

Not sure if that's a DB2 feature or bug when using padded char
fields.

Fixed-length are treated as such; i.e. trailing blanks are part of the data. The TRIM scalar of course trims the leading and trailing blanks [often the better choice, and typically also the intention, is to use the RTRIM scalar instead], and thus the query "works" to select the row with the /equivalent/ value using the LIKE predicate. The reason an equal predicate functions and a LIKE predicate does not, with the fixed-length CHAR data, is because the literal specified on the former predicate is padded to match the fixed-length specification of the comparison operand. The LIKE predicate requires that the user must specify exactly the desired size, because conspicuously if the operand for the LIKE [as literal or expression] were to be padded with blanks, then most selections would be vitiated, such that many would yield no results; to pad instead with the percent-sign as zero-to-many might resolve the issue for a longer comparison operand, but would be similarly vitiated, such that many would yield all manner of results that are not simply blank-padded.

So basically, the effect is not a bug, and just the way things need to function to operate consistently and predictably, even if sometimes seemingly contradictory to user perception; effectively, best to stick with the rule, that if the operand for a LIKE does not include a percent as zero-to-many or an underscore as one-to-one for character replacement mask, then probably the equal predicate was intended.

The XMLIN data buffer in the XMLCGI RPG app does contain the EBCDIC
%6C coming in, but there's a routine in XMLCGI appropriately called
apaHack that is messing it up and converting it to EBCDIC %25

There's no docs on what specifically it's doing and it uses a bunch
of pointer stuff.

Looks like there was some change made in XMLSERVICE V1.8.0 that does
a check on 6C that might be the culprit.

If there is code that does a pre-conversion of just specific values in the EBCDIC data [such as just changing all 0x6C to 0x25], then if a later properly implemented EBCDIC->ASCII was issued against that /presumed-to-be/ EBCDIC data, then the ill-effect of corrupting the code point 0x25 to 0x0A would be the /proper/, but undesirable, effect. In that case, precluding such a pre-processor from performing that initial character translation would be important to prevent the later character translations betwixt character encodings from compounding the earlier mistake. Note that the Exclamation point is variant across EBCDIC, so any code that blindly changes the code point 0x6C probably is already mistaken\coded-errantly.