Thanks for this find comes to me from Victor Yelizarov. He reported to me an odd issue with sorting and query of queries. Consider the following code sample:
<cfset q = queryNew("number","Decimal")>
<cfset queryAddRow(q,5)>
<cfset q["number"][1] = 10.1>
<cfset q["number"][2] = 0.01>
<cfset q["number"][3] = 22.75>
<cfset q["number"][4] = 21.05>
<cfset q["number"][5] = 8.33>
<cfquery name="q" dbtype="query">
SELECT number
FROM q
ORDER BY number DESC
</cfquery>
<cfdump var="#q#">
We know that ColdFusion looks at the query metadata to figure out how to do sorting. In this case he explicitly set the column type to decimal. That should have been enough. But from the result you can see otherwise...
So obviously this is a bug - how do we get around it? If you JavaCast when setting the values, that works. If you multiply the values by one, that also works. But an easier solution, and one that may be useful to remember anyway, is to cast in your query of query.
<cfquery name="q3" dbtype="query">
SELECT cast(number as decimal) as n
FROM q
ORDER BY n DESC
</cfquery>
Archived Comments
I filed this bug report: http://cfbugs.adobe.com/cfb...
This is not a bug, and something a lot of people including myself have fallen too. The problem is that unless you add the type of data it is, ColdFusion will assume it is something else.
So what you need to do is something like this.
http://livedocs.adobe.com/c...
And when you add the type of data, the QoQ qill behave in the correct manner.
Eh? Isn't that what the code does? The very first line?
Hmm, you are right how did I miss that :-(
Yeah, I've never done that. Ever. (Um....)
It's not the query itself that's to blame for the wrong sort, it's the way you populate the query rows.
In my experience, using querySetCell() takes perfectly care of the data type, while array notation obviously doesn't. For whatever reason.
<cfset q = queryNew("number","decimal")>
<cfset queryAddRow(q, 5)>
<cfset querySetCell(q, "number", 10.1, 1)>
<!--- <cfset queryAddRow(q)> --->
<cfset querySetCell(q, "number", 0.01, 2)>
<!--- <cfset queryAddRow(q)> --->
<cfset querySetCell(q, "number", 22.75, 3)>
<!--- <cfset queryAddRow(q)> --->
<cfset querySetCell(q, "number", 21.05, 4)>
<!--- <cfset queryAddRow(q)> --->
<cfset querySetCell(q, "number", 8.33, 5)>
<cfquery name="q" dbtype="query">
SELECT number
FROM q
ORDER BY number
</cfquery>
<cfdump var="#q#">
This should work without the need of casting an tweaking in the select clause.
Wow - you are right. I don't normally use the format above - that's how he sent me his test code. That must be why I haven't run into this before. Good find there Andreas!
Hey Andreas, how come you commented out the add row lines? (Or why were they there in the first place?)
You don't have to add the rows since 5 were added initially.
@Doug No special meaning, in there. I realized right after pressing the post button, that I had forgotten to delete them. They were there in the first place, because in my scribble I first added line by line to the query and then did some refactoring and switched to adding 5 rows at once.
aha. Thanks.
Yeah, I always thought QuerySet was kind of a nuisance, but you raise a good reason to make sure we are thorough when we're creating our own queries.
Back in the day Hal Helms (I believe) had a custom tag so you could do stuff like:
<cf_newquery name="q">
id,name,age
1,Raymond,38
2,Jacob,9
</cf_newquery>
Obviously not ideal, but for quick stuff it was simpler.
I agree! Hal's cf_querysim was a great and very useful tool. I even used it to import text data, such as e-mail addresses and the like and mainly for quickly populating dropdowns while developing an application. Some (read: more than I would admit :) of these sims survived the development process, have never been replaced by 'real' queries and are still in use.
I'm doing a presentation at RIAUnleashed - a 3 hour one on extending CF. In my custom tags section I'll mention this.
This trips me up sometimes, as I prefer fewer chars (array notation vs querySetCell). When used within a list of query cell assignments, querySetCell is an _ok_ workaround:
q.stringCol1[1] = "foo";
querySetCell(q, "numberCol", 10.1, 1);
q.stringCol2[1] = "bar";
However, querySetCell is not so helpful when it's just a single dynamic query cell assignment being looped over:
[begin loop]
q[colName][rowNum] = cellValue;
[end loop]
Also, when all columns are needed, "SELECT *" is shorter than "SELECT col1,col2,col3,CAST(col4 AS castType) AS Col4,..,col19,col20".
Array notation should respect query column datatype. (IMNSHO)
This tip also helped a question that just came to the Japanese CFUG a couple of days ago. The person was trying to insert a huge data into a database, but it seemed that the data was somehow truncated when retrieving. So I tried to replicate the issue by using the following code. It's basically creating a text that contains 50,000 letters and inserting it into a database, and then retrieving it.
<cfset letters = "abcde">
<cfset text = ''>
<cfloop index="i" from="1" to="10000">
<cfset text &= letters>
</cfloop>
Text Length: <cfoutput>#len(text)#</cfoutput><br />
<cfquery name="qInsert" datasource="cfjp" result="result">
INSERT INTO test(content)
VALUES(<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#text#">)
</cfquery>
Queryparam Length: <cfoutput>#len(result.sqlparameters[1])#</cfoutput><br />
<cfquery name="qRead" datasource="cfjp">
SELECT content
FROM test
WHERE id = #result.identitycol#
</cfquery>
Content Length: <cfoutput>#len(qRead.content)#</cfoutput>
Then the output was:
Text Length: 50000
Queryparam Length: 50000
Content Lengh: 32000 (This should be 50000, right?)
So first I thought my SQL server (MS SQL 2008 R2) might be the issue, but I used the data type varchar(max) for the field named "content" to store the data, and according to MSDN(http://msdn.microsoft.com/e..., it can hold up to 2^31-1 bytes.
There shouldn't be a problem for this test and sure enough when I checked the data in the database, exact 50,000 letters I inserted were there.
So what's the heck?
After for a while trying to figure this thing out, I remembered this tip and applied it. Bingo. That fixed the problem. All I needed to do was to change the second cfquery to this.
<cfquery name="qRead" datasource="cfjp">
SELECT CAST(content as varchar(max)) as c
FROM test
WHERE id = #result.identitycol#
</cfquery>
Content Length: <cfoutput>#len(qRead.c)#</cfoutput>
Then the output was:
Text Length: 50000
Queryparam Length: 50000
Content Lengh: 50000 (Fixed it)
Thanks for the tip.