Skip to main content
veloopity
veloopity
Known Participant
December 5, 2019
Question

trailing blank / difference CF10 and CF2016

  • December 5, 2019
  • 1 reply
  • 1590 views

We're having a strange problem since the code was migrated from CF10 to CF2016. I'd appreciate your opinions. There is something strange with the way data are retrieved from an AS400 database. The customer used an ODBC driver but installed an AS400 Java driver and the problem persists.

The screenshot shows a query on an AS400 table and a subsequent QoQ. The left version is CF10, the right version is CF2016.

It appears that alphanumeric values on the AS400 come with a trailing blank (LVSROM="QW" but it is in fact "QW "). LVSROM is just an example, the same happens with other alphanumeric fields. The old version of the subsequent QoQ finds something, surprisingly, even though the where clause says "='QW'" and even though there is a trailing blank in the data - CF seems to ignore this trailing blank - in fact the query doesn't even work if the trailing blank is attached in the where clause (see example). I don't understand this but it worked.

The QoQ in the 2016 CF version (right side) won't retrieve data if the field is specified in the where clause, neither without the blank nor with the blank attached. It only retrieves data if the where clause is omitted. What is going on here?

Hopefully there is some setting somewhere that returns CF2016 to the behaviour it had in CF10. We want to avoid having to rewrite a mountain of code, adding trim everywhere, just because of this effect.
Maybe I'm missing something but I don't know what it could be.

-Michael

(SCREENSHOT added as attachment because the inline image appears too small here)


This topic has been closed for replies.

1 reply

Charlie Arehart
Charlie Arehart
Community Expert
December 8, 2019

I’m not aware of any change in CF in 11 or 2016 that would affect this, nor any feature that could.

 

If I were in your shoes, I'd switch from using cfdump to track things (as it could be hiding important details), and instead output to the screen the value of that col from that query, and then do a "view source" in the browser to see what (if anything) may be there besides just what you
perceive to be a "blank". (And trying to look at that with the CFDUMP would be complicated because of all the extra stuff it adds, and it may also CHANGE such low-level formatting before outputting the value.)

You may want to also try taking out that "concat ')'", just in case somehow it's unexpected contributing to the problem. I understand your app may NEED it, but I just mean for the sake of this diagnostic step, let's make sure it's not having any unexpected impact.

Then as for the qofq where oddness, perhaps it will be explained if you see that what's in the output field is NOT what you think.


Let us know if that shows anything.

 

PS Sorry, I had replied to this on Fri morning, but I see that my reply by email was rejected. And then as I copied it here somehow my first and last paragraphs were didn't get pasted, so I've just edited this. (Am saying that in case you or anyone gets email the first version of my reply here today). Grr. 

/Charlie (troubleshooter, carehart. org)
    veloopity
    veloopityAuthor
    Known Participant
    December 8, 2019
    Thank you for your reply.

    The dump and the concat thing were added for diagnostic purposes, to
    display the field in question, showing that there is a trailing extra
    character. The value of that character was then displayed using an asc
    function. Everything for diagnostic purposes.

    -Michael
    Charlie Arehart
    Charlie Arehart
    Community Expert
    December 8, 2019

    I do realize that you added the cfdump for diagnostic purposes. Please reread my note. I propose you do NOT use it, and instead use a plain CFOUTPUT for such diagnostics, and more specifically that you then do a "view source" in your browser, to be 100% sure of what is displaying. 

     

    As for the "asc", if you mean the CFML function of that name, that would not be helpful, as it only returns the ascii representation of the FIRST character of the string. That won't help with your problem being a seeming character  (or what may be many hidden charaters) at the END of the string.

    /Charlie (troubleshooter, carehart. org)
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices