Quick and Dirty Dummy Queries in ColdFusion 8.0.1

I needed a dummy query for a page I was working on the other day, which caused me to sigh with grief. Everyone knows what a pain creating dummy queries using queryNew()/queryAddRow()/querySetCell() can be, even for a simple query with one row. Thus I decided not to bother and simply used a "real" query with a real datasource and a "select" statement but no "from" clause. This worked nicely, so I decided to expand on this and utilize the nested implicit structure and array creation that was fixed in ColdFusion 8.0.1 to create a UDF that would allow me to create a query in two lines of code.You may or may not know this, but you can actually send queries that look like the one below and they will work just fine (at least in my tests on MSSQL and MySQL - I don't have much experience on other rdbms):

<cfquery name="fakeQuery" datasource="dsn"> SELECT 'Brian' AS firstName, 'Rinaldi' AS lastName, 34 as age </cfquery>

Doing this returns a full ColdFusion 8 query object that has a resultset, cached flag, execution time and executed SQL statement. It also automatically types the age column in the above query as an integer, which you can see if you use getMetaData() on the query.

I utilized this technique for a UDF I called dummyQuery() which uses a "union" to allow you to create multiple rows of data. All it requires is a structure of arrays with your query data and a dsn (which can be any DSN since we don't actually get real data). The UDF is as follows:

<cffunction name="dummyQuery" access="public" output="false" returntype="query">
<cfargument name="queryData" type="struct" required="true" />
<cfargument name="dsn" type="string" required="true" />
<cfset var i = 0 />
<cfset var columnName = "" />
<cfset var myQuery = "" />
<cfset var queryLength = arrayLen(arguments.queryData[listFirst(structKeyList(arguments.queryData))]) />
<cfquery name="myQuery" datasource="#arguments.dsn#">
<cfloop from="1" to="#queryLength#" index="i">          SELECT <cfloop collection="#arguments.queryData#" item="columnName">
<cfif isNumeric(arguments.queryData[columnName][i])>#arguments.queryData[columnName][i]#<cfelse>'#arguments.queryData[columnName][i]#'</cfif> AS #columnName#<cfif columnName neq listLast(structKeyList(arguments.queryData))>,</cfif>
</cfloop>
<cfif i neq queryLength>UNION</cfif>
</cfloop>
</cfquery>
<cfreturn myQuery />
</cffunction>

Of course, prior to ColdFusion 8.0.1 creating the structure of arrays would have been nearly as much of a pain as using queryNew()/queryAddRow()/querySetCell() - though not quite but still... However, as of ColdFusion 8.0.1 you can nest implicit array and structures. For example, the following code creates a structure of arrays that I can pass to the above UDF to get a query with three columns and three rows:

<cfset queryData={firstName=['Brian','Samuel','Luke'],lastName=['Rinaldi','Rinaldi','Rinaldi'],age=[34,1,5]} />
<cfset dummyQuery(queryData,"myDsn") />

Now if only 8.0.1 had "fixed" the ability to pass an implicit array or structure as an argument I could cut that down to one line, but hopefully we'll see that in 8.0.2 ;)

For the sake of comparison, the above two lines (plus the UDF of course) would replace the below 11 lines of code:

<cfset fakeQuery = queryNew('firstName,lastName,age','varchar,varchar,integer') />
<cfset queryAddRow(fakeQuery,3) />
<cfset querySetCell(fakeQuery,'firstName','Brian',1) />
<cfset querySetCell(fakeQuery,'firstName','Samuel',2) />
<cfset querySetCell(fakeQuery,'firstName','Luke',3) />
<cfset querySetCell(fakeQuery,'lastName','Rinaldi',1) />
<cfset querySetCell(fakeQuery,'lastName','Rinaldi',2) />
<cfset querySetCell(fakeQuery,'lastName','Rinaldi',3) />
<cfset querySetCell(fakeQuery,'age',34,1) />
<cfset querySetCell(fakeQuery,'age',1,2) />
<cfset querySetCell(fakeQuery,'age',5,3) />

Even aside from the UDF, I feel the technique of getting a fake query this way is useful.

Here's one that doesn't require a db at all:
http://querysim.riaforge.org/

i was going to suggest querysim. if you wanted the above to work with oracle, you would have to add "from dual". i haven't validated your code, but to return data from a select statement, you use "from dual".

Nice, I never knew you could something from nothing :-)