Brian Rinaldi posted on his blog about dummy queries in CF 8.0.1, and it struck me as a weird solution. So here's a drop-in replacement, that I think works in a more reasonable fashion, and doesn't have any dependency on an existing DSN.
<cffunction name="dummyQuery2" access="public" output="false" returntype="query">
<cfargument name="queryData" type="struct" required="true" />
<cfset var i = 0 />
<cfset var columnName = "" />
<cfset var myQuery = queryNew(structKeyList(queryData)) />
<cfset var queryLength = arrayLen(arguments.queryData[listFirst(structKeyList(arguments.queryData))]) />
<cfloop from="1" to="#queryLength#" index="i">
<cfset queryAddRow(myQuery) />
<cfloop collection="#arguments.queryData#" item="columnName">
<cfset querySetCell(myQuery, columnName, queryData[columnName][i]) />
</cfloop>
</cfloop>
<cfquery dbtype="query" name="myQuery">
select *
from [myQuery]
</cfquery>
<cfreturn myQuery />
</cffunction>
As you can see, the structure is almost identical, but it doesn't use a database, it just builds in memory. The "no-op" QofQ at the end is to ensure there is actual query metadata, not just the raw records, which Brian listed as one of his prerequisites. If you don't care, it can be removed with no ill effects.
One interesting benefit of this approach is that the rows come out in the same order as they go in – with Brian's DB-based one, that's not guaranteed because there is no ORDER BY clause on the query. Running his example on my box (using MSSQL 2005), I got rows sorted by first name. With the in-memory building, the rows are explicitly kept in order throughout.
Heh. Yeah, there is more than one way to skin a cat. Anyway, part of my post was to show that you could do a query without an actual table. I actually did that as a placeholder for a real query in my code where the tables don't exist yet. In that case I just go put a real query statement in place of the fake one when the table is ready and the rest of my code remains as is. The UDF was more an experiment to see if I could expand that concept…
Brian,
I wasn't demeaning your implementation, if I came across like that, my apologies. I think the UDF has general use, however. I have a similar one that behaves as CFSAVECONTENT to grab it's body as JSON, and build "real" object out of it, optionally doing array-of-structs to query conversion. I use it all the time, both for prototyping, and where I have some static data that I want to manage as JSON (easy to read), but process with CF.
And as an interesting aside, Oracle always requires a FROM clause, and provides a dummy DUAL table that you can use for queries that you'd otherwise not need a FROM clause on.
No offense taken whatsoever (I didn't read the post in that way). Like I said, I was just having some fun. Someone did comment that Oracle issue…though I don't think its worth fixing on my UDF for that since it would require my knowing its Oracle which adds a whole new layer of complication. If they *need* Oracle they can use your version :)
[...] Rinaldi and Barney Boisvert debate the best way to create dummy [...]