trailing blank / difference CF10 and CF2016

[veloopity]

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)

[Charlie Arehart]

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. 

[veloopity]

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]

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.

[veloopity]

I'm not a CF newbie 🙂 I did specify the last character. Think:
asc(right(string)). (I don't have the complete code here right now). It
showed a 32.

-m

[Charlie Arehart]

OK, but rather than presume I think you're a newb, just realize that we here can only go on what you do or do not provide, so I'm simply trying to help. 🙂 (FWIW, I don't recognize your username here, and your profile has no info.)

I have still a few more ideas for you to consider.

1) First, even with your clarification, you are seemingly still presuming that there is just the one character there. (You may still want to make sure there's not more there, which may be hidden. And I still think doing that with a cfoutput and a view source would be better than relying on cfdump.)

Still, fair enough that you have confirmed it WAS there and WAS a 32. (That's the first time you've mentioned it here.)  And I see that ascii 32 is indeed a space, so that one char is at least clearly a space. And that certainly is odd that it would be there. 

2) As for further diagnosis, the question of course now is "where is it coming from", and you may presume it's got to be CF that's doing, but it could be either the driver or the db.

First, yes, I realize you have said that it happened with multiple driver types. That STILL doesn't mean it's CF doing it (though it may).

Second, I realize that you are saying also that it DID work on CF10 and does NOT work on CF2016, but again that still does not necessarily mean that it IS caused *by* CF2016.

So let's ask some more diagnostic questions: is teh CF2016 on the same box where CF10 was running? If not, what is different? In a different location? different subnet (than it had been with CF10)? Could it be a different OS version?

And we could assume that the AS400 you are talking to FROM CF2016 is exactly the same as that called from CF10, but can you confirm? And is it exactly the same database?

And are the driver connectionstring values (of any two same driver types) identical between CF10 and 2016? Besides assessing what you see in the CF Admin datasource "advanced settings" (to see what may be in the connection string), look in the "settings summary" page of the CF Admin, which will list all the DSNs and what connection string that CF may be coming up with. Maybe something about THAT has changed (which then you may be able to modify).

I'm just throwing out ideas, being "on the other side of the wall" from you. Please don't take any of these as challenging you. I really am simply trying to help.

[veloopity]

thanks Charlie, your help is much appreciated.  I'll try to find out the details that you asked for, together with my client.

best,

-Michael

[James Moberg]

Charlie,  This question was initially posted in the Facebook ColdFusion Programmers Group and I asked similar questions, but didn't receive any answers.

https://www.facebook.com/groups/CFprogrammers/permalink/10156895592325036/

Hopefully the driver information & connection settings will be posted so this issue can be researched further.

[Charlie Arehart]

Thanks, James (jamo) and Michael (veloopity). Yep, hoping that more info (of various sorts as I've asked) may help get to the bottom of this.

[veloopity]

here are a few answers and screenshots by my client:

CF10 runs on Windows Server 2012

CF2016 runs on Windows Server 2016

AS400 database is the same for both

Drivers: both ODBC. They switched the driver on the new machine from JDBC back to ODBC because the old machine used ODBC, so, for comparison purposes. The problem that we have in CF2016 is the same regardless of the driver.

-Michael

http://www.mpeters.de/temp/adobe/CF10_DSN.jpg 
http://www.mpeters.de/temp/adobe/datasource_cf10.jpg 

http://www.mpeters.de/temp/adobe/CF16_DSN.jpg
http://www.mpeters.de/temp/adobe/datasource_cf2016.jpg 

[James Moberg]

Do you have any additional information regarding the AS400 ODBC driver (other than it's an "ODBC" driver).  Is it officially provided by Adobe?  Is it 32bit?  What's the filename/version?  Is there a webpage that provides more information regarding the driver?  What are the current driver connection parameters and are there any advanced options that may not be currently enabled?

Also, what version of Java are you using on CF2016?  If it's EOL, have you considered updating? (We've noticed some problems get fixed when working with updated JDK.)

https://www.adobe.com/support/coldfusion/downloads.html#additionalThirdPartyInstallers