Solution: Query of Queries Whitespace Problem
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.
<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>
<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>
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!
There are no comments for this entry.
[Add Comment]