Solution: Query of Queries Whitespace Problem
Posted by
Brad Wood
Oct 09, 2008 14:28:00 UTC
I came up with a ColdFusion-only hackish solution for the whitespace problem caused by query of queries. Brad suggested I hardcode a temp table into a random SQL database on our server, which is also a good hackish solution, but required database access. Because I was doing all of this work without accessing the database, I wanted to come up with a solution I could implement just in ColdFusion.The solution was to add a character to the beginning and ending of the string which prevented the whitespace from being trimmed. Then whenever I actually used the string, I removed the first and last character.
[code]<cfset crlf = Chr(13) & Chr(10)> <cfset tab = chr(09)> <cfset testingwithwhitespace = crlf & tab & tab & "Value Without Tabs and Spaces" & crlf & tab & tab & crlf & tab & tab & crlf> <cfset testingwithoutwhitespace = "Value Without Tabs and Spaces"> <cfsavecontent variable="testingHTML"> <html> <head> <title>White Space Test</title> </head> <body> <div>White Space Test</div> </body> </html> </cfsavecontent> <cfset qryTesting = QueryNew("Description,testvalue")> <cfset rowid = queryAddRow(qryTesting)> <cfset querySetCell(qryTesting,"Description","Testing String with whitespace",rowid)> <cfset querySetCell(qryTesting,"testvalue","~" & testingwithwhitespace & "~",rowid)> <cfset rowid = queryAddRow(qryTesting)> <cfset querySetCell(qryTesting,"Description","Testing String without whitespace",rowid)> <cfset querySetCell(qryTesting,"testvalue","~" & testingwithoutwhitespace & "~",rowid)> <cfset rowid = queryAddRow(qryTesting)> <cfset querySetCell(qryTesting,"Description","Testing HTML",rowid)> <cfset querySetCell(qryTesting,"testvalue","~" & testingHTML & "~",rowid)> <h1 align="center">Before Query of Query</h1> <cfoutput query="qryTesting"> #qryTesting.description#<hr /> <span style="background:yellow">#HTMLCodeFormat(MID(qryTesting.testvalue,2,len(qryTesting.testvalue)-2))#</span><br /><br /> </cfoutput> <cfquery dbtype="query" name="qofqAutoTrim"> SELECT Description,testvalue FROM qryTesting </cfquery> <h1 align="center">After Query of Query</h1> <cfoutput query="qofqAutoTrim"> #qofqAutoTrim.description#<hr /> <span style="background:yellow">#HTMLCodeFormat(MID(qofqAutoTrim.testvalue,2,len(qofqAutoTrim.testvalue)-2))#</span><br /><br /> </cfoutput> [/code]I'm not going to post the output of the code again, suffice it to say it's now exactly the same. Yay!! Problem Solved!
Tags: ColdFusion