Interesting little bug with query columns
An interesting bug (not a ColdFusion bug, but a user bug) cropped up in a discussion last week in the ColdFusion IRC channel. Unfortunately I don't remember his IRC name (Edited: It was Baz!), but after we worked together on this for an hour or so, he was kind enough to send me a real nice and simple code sample to replicate the issue.
So let's back up a bit and talk about what he saw. The user had a web service that returned a structure. Before he actually used it as a web service he did his testing locally like so:
<cfset Local=createobject('component','test').invokeFunction() />
<cfdump var="#Local#" label="Local">
This worked perfectly fine for him. Let's take a look at the code he was using in the component (and give yourself a pat on the back if you see the problem):
<cfcomponent output="false">
<cffunction name="invokeFunction" access="remote" returntype="struct">
<cfscript>
var MyQuery=QueryNew('ColumnA,ColumnB');
var MyStruct=structnew();
// Populate 1 row of the query
QueryAddRow(MyQuery, 1);
QuerySetCell(MyQuery, 'ColumnA', 'ValueA');
QuerySetCell(MyQuery, 'ColumnB', 'ValueB');
// Copy the values of that row to a struct
MyStruct.KeyA=MyQuery['ColumnA'];
MyStruct.KeyB=MyQuery['ColumnB'];
</cfscript>
<cfreturn MyStruct />
</cffunction>
</cfcomponent>
As you can see, the function creates a query and then copies values to a structure. His real code was obviously a bit more complex. So he then switched to using a web service:
<cfset Remote=createobject('webservice', 'http://localhost/test.cfc?WSDL').invokeFunction() />
<cfdump var="#Remote#" label="Remote">
And something odd occurred. This is the output from running both his local and remote tests:
As you can see - the remote call returned a structure of arrays - not a structure of simple values. Why? While I can't answer why one method returned different data then the other, I can point out the problem with the code in the method. Notice how he copied the values from the query:
// Copy the values of that row to a struct
MyStruct.KeyA=MyQuery['ColumnA'];
MyStruct.KeyB=MyQuery['ColumnB'];
When working with a query like this, you are supposed to specify which particular row you want. When called locally, the function was able to guess he meant the first row, but when called remotely this no longer worked. Again - I don't know why, but the fix is simple enough:
// Copy the values of that row to a struct
MyStruct.KeyA=MyQuery['ColumnA'][1];
MyStruct.KeyB=MyQuery['ColumnB'][1];
What helped diagnose this issue was inspecting the data he had in MyStruct. When he examined the particular Java class when invoked remotely, he saw that it was a QueryColumn (I don't have the full class name in front of me, but that is the approximate name), not a simple value as he would have expected.
Comments
<cfcomponent output="false">
<cffunction name="invokeFunction" access="remote" returntype="struct">
<cfscript>
var MyQuery=QueryNew('ColumnA,ColumnB');
var MyStruct=structnew();
// Populate 1 row of the query
QueryAddRow(MyQuery);
QuerySetCell(MyQuery, 'ColumnA', 'ValueA1');
QuerySetCell(MyQuery, 'ColumnB', 'ValueB1');
QueryAddRow(MyQuery);
QuerySetCell(MyQuery, 'ColumnA', 'ValueA2');
QuerySetCell(MyQuery, 'ColumnB', 'ValueB2');
// Copy the values of that row to a struct
MyStruct.KeyA=MyQuery['ColumnA'];
MyStruct.KeyB=MyQuery['ColumnB'];
</cfscript>
<cfreturn MyStruct />
</cffunction>
</cfcomponent>
MyStruct.KeyA=MyQuery.ColumnA;
MyStruct.KeyB=MyQuery.ColumnB;
Weird :s
I was working with some code that used the <CFX_CYBERCASH> custom tag, and I noticed that the query it was returning contained duplicates of each column (each column was listed twice in the query). I'm not sure why the query has duplicate columns, but I didn't worry about it.
Later, I looped over the query's columnlist to create a struct holding a key for each query column. This seemed to work fine, giving me what appeared to be a normal struct of simple values; when I dumped out the struct, everything appeared as expected. Below is the code I had used to fill my struct.
<cfloop index="k" list="#my_query.columnlist#">
<cfset my_struct[k] = my_query[k]>
</cfloop>
As I continued to program, I eventually tried to do something with my struct values that required them to be simple values (I can't remember what I had done - used ListAppend function to create a list of certain values, I think). At this point, I started getting an error saying that the value in my struct element was not a simple value. After playing around a while, I started testing the value with various decision functions and realized that the contents of the struct element were seen as an array.
Thinking back to the query, I remembered that there had been duplicate columns. So, it seemed as though I was getting an array of values assigned to my struct element instead of the simple values that appeared to be going in (like I mentioned, the dump didn't show an array).
I changed my code to the following where I specify the query row, and it fixed the issue.
<cfloop index="k" list="#my_query.columnlist#">
<cfset my_struct[k] = my_query[k][1]>
</cfloop>
I'm not sure if this is directly related or if I even understand it correctly, but it seemed to be due to a similar issue.
Surely that's not right.
http://i167.photobucket.com/albums/u157/rhop10/cf_...
Even though the dump of the struct doesn't show it, each struct element is seen as an array. I suspect that has to do with the duplicate query columns.
ArraySum( Query[ "column" ] )
... however, you cannot get the value list of the column:
ValueList( Query[ "column" ] )
... that throws an error - only allows dot-notation. Also, if you try to treat it as a string:
Query.column.ReplaceAll()
... throws an error cause "ReplaceAll" is not a method on the column. You have to do something like:
ToString( Query.column ).ReplaceAll()
Like I said, very inconsistent as to when the data type is cast or something, but once you get a feel for it, you can begin to see where problems might crop up.