How do I create a query by hand?

Most (but not all) ColdFusion queries come straight from the database, but there are times when you need to work with a query without using a database. Whether for debugging or other purposes, it is sometimes useful to be able to create a query manually. To create a query, you use the queryNew() function. This takes two arguments. The first argument is required and is a list of column names. The second argument is option and is a corresponding list of data types for the columns. If you use this second argument, it helps ColdFusion work with the query better. If you do not specify the "Column Type", ColdFusion needs to figure what datatype it is, and that might impede it's performance.

Here is a simple of a query with four columns:

<cfset newQuery = queryNew("Column_1, Column_2, Column_3, Column_4","Integer, VarChar, Decimal, Date")>
<cfloop index="i" from="1" to="10">
	<cfset queryAddRow(newQuery)>
	<cfset querySetCell(newQuery, "Column_1", i)>
    <cfset querySetCell(newQuery, "Column_2", "Value_#i#")>
    <cfset querySetCell(newQuery, "Column_3", #i#*10000.05555555)>
    <cfset querySetCell(newQuery, "Column_4", "#dateadd("d", i, now())#")>   
<cfdump var="#newQuery#">

This question was written by Stefan le Roux
It was last updated on August 14, 2008.


Database / SQL


comments powered by Disqus