ColdFusion Zeus POTW: queryNew/queryAddRow

This post is more than 2 years old.

Time for another ColdFusion Zeus preview - this one being simply a modification to two functions but one I've wanted for a while. One of the nicest datatypes in ColdFusion is the query. I'd be willing to bet that the cfquery/cfoutput query="..." combo are the most important features of ColdFusion. Query variables are used in many places, not just database calls. The cfdirectory tag, for example, returns a query object. Queries are so handy, you can actually create your own on the fly using a combination of queryNew, queryAddRow, and querySetCell. As a blogger, I tend to make use of this quite a bit. It allows me to quickly create fake queries without having to setup a database and populate it with data. It's also handy for times when you may not have access to a database and need to mock something up. I once worked on a project where the DBA was out for a few days due to illness and couldn't give us access to their system. We made use of queryNew in our components and carried on. When the DBA returned, we replaced the fake queries with real ones and everything was kosher.

As useful as these tags are, they can be a bit of a pain to type in. Consider for example a simple fake set of user data.

<cfset users = queryNew("id,username,password", "varchar,varchar,varchar")> <cfset queryAddRow(users, 4)>

<cfset querySetCell(users, "id", 1, 1)> <cfset querySetCell(users, "username", "bob", 1)> <cfset querySetCell(users, "password", "unicoron", 1)> <cfset querySetCell(users, "id", 2, 2)> <cfset querySetCell(users, "username", "scott", 2)> <cfset querySetCell(users, "password", "ilikesoccer", 2)> <cfset querySetCell(users, "id", 3, 3)> <cfset querySetCell(users, "username", "todd", 3)> <cfset querySetCell(users, "password", "icheatoncod", 3)> <cfset querySetCell(users, "id", 4, 4)> <cfset querySetCell(users, "username", "ray", 4)> <cfset querySetCell(users, "password", "icodesmelly", 4)>

<cfdump var="#users#">

I've got one line to create the initial query, one to add some rows, then a bunch of querySetCells to populate it. That's a lot of typing. There is also queryAddColumn. It lets you add a column and populate it with an array of values. But since queryNew expects at least one column, you're still going to have to do one the "ugly" way.

ColdFusion Zeus expands both queryNew and queryAddRow so you can immediately set data in the query object. First, queryNew can now take an array of structs:

<cfset users = queryNew("id,username,password","varchar,varchar,varchar", [{id:1, username:"bob", password:"unicoron"}, {id:2, username:"scott", password:"ilikesoccer"}, {id:3, username:"todd", password:"icheatoncod"}, {id:4, username:"ray", password:"icodesmelly"}])>

Second - queryAddRow can take either an array of structs or a struct. This allows you to either add a bunch of rows or just one more row.

<cfset queryAddRow(users, {id:5, username:"vader", password:"whiny"})>

Make sense?

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA

Archived Comments

Comment 1 by Tom K posted on 1/11/2012 at 9:55 PM

Love it - but why do we then need to implicitly state the columns within the array if they've already been declared?

Can't we do:

users = queryNew("id,username,password","varchar,varchar,varchar",
[{1, "bob", "unicorn"},
{2, "scott", "ilikesoccer"}]

Then if you change the column name, you don't have to do it in loads of places?

Comment 2 by Steve Durette posted on 1/11/2012 at 9:55 PM

This is awesome. I always thought there should be a better way to add a row of data than the way we were forced to.

Comment 3 by Matt Quackenbush posted on 1/11/2012 at 9:57 PM

YAY! Two _very_ welcome enhancements!

Comment 4 by O?uz Demirkap? posted on 1/11/2012 at 9:57 PM

Nice! :)

Comment 5 by Brian Swartzfager posted on 1/11/2012 at 10:03 PM

Very nice.

Comment 6 by andy matthews posted on 1/11/2012 at 10:14 PM

I agree with Tom K. Column names are already defined. That's just extra typing to make users define them again.

Comment 7 by Raymond Camden posted on 1/11/2012 at 10:17 PM

Tom - what if you want to specify some, but not all of the columns?

Comment 8 by Tom K posted on 1/11/2012 at 10:31 PM

@Ray - then treat it like a CSV - i.e foo,something,,,something ?
I agree with massive multi column versions, it's less readable, but if it's just 2 or 3.. it would be nice to have the option anyway!

Comment 9 by Sam Farmer posted on 1/11/2012 at 10:42 PM

I like Tom K's suggestion and why not have both? For the third argument to queryNew:
- if its an array of structure use the key as the column.
- if its an array of arrays assume the column from the order defined as so:

<cfset users = queryNew("id,username,password","varchar,varchar,varchar",
[2, "scott","ilikesoccer"] ])>

Comment 10 by Raymond Camden posted on 1/11/2012 at 10:45 PM

Ok ok you convinced me. ;)

Sam, you know where you can make this suggestion. Please do so. :)

Comment 11 by Sam Farmer posted on 1/11/2012 at 10:54 PM

Wherever that place may be. Its done. :)

Comment 12 by MikeZ posted on 1/11/2012 at 11:06 PM

Now there's something the majority of us will have a use for.
Also, think about Zeus' support for closures and you'll see how this can be taken to a whole new level.

Comment 13 by Henry posted on 1/11/2012 at 11:51 PM

Love it, I wanna use it now... to store a dynamically created CFHTTP request...

While we're at it, can we get a getParams() function in the Http class?

Comment 14 by Raymond Camden posted on 1/12/2012 at 12:20 AM

Henry - what do you mean?

Comment 15 by Henry posted on 1/12/2012 at 12:25 AM

There's setAttributes() & getAttributes() & clearAttributes()
but there's only addParam() & clearParams()...

Where's getParams()?


Comment 16 by Raymond Camden posted on 1/12/2012 at 12:28 AM

Um, well, since you are the one who adds params, do you need a get params? Maybe for completeness sake I guess - but it seems a bit weird.

Comment 17 by Raymond Camden posted on 1/12/2012 at 12:31 AM

It looks like params are in a Variables scope key in base.cfc. You could add:

public function getParams() { return variables.parems; }

and there ya go. :)

Comment 18 by Henry posted on 1/12/2012 at 12:38 AM

For completeness and for testing / unit testing.

I know I can add it there myself, but it'd be better if it is added officially by Adobe so that the next guy running my code would not need to change the base.cfc.

Comment 19 by Raymond Camden posted on 1/12/2012 at 12:40 AM

Then you know the routine - file an ER. :) Feel free to use my code above... just correct the typo.

Comment 20 by Henry posted on 1/12/2012 at 12:49 AM
Comment 21 by Raymond Camden posted on 1/12/2012 at 12:50 AM


Comment 22 by John Allen posted on 1/12/2012 at 2:02 AM

This is awesome.

Comment 23 by Meenakshi posted on 1/12/2012 at 11:28 AM

This technique comes in very handy while using Xml data set returned from an http request.

Comment 24 by Atan posted on 1/12/2012 at 1:56 PM

That small enhancements are very nice (long awaited)!

Is there also a way to set default values to omit keys in the structs?

I also hope for queryRowToStruct() & queryToArray()

Comment 25 by Misty posted on 1/12/2012 at 5:23 PM

Well @ Atan, I suppose there are UDF's avaliable, so probably i do not thnk that should be needed

Comment 26 by Raymond Camden posted on 1/12/2012 at 5:28 PM

Yo Misty... um... do I need to turn on PG Gravatars?

Comment 27 by Neeko posted on 1/12/2012 at 10:42 PM

100 to 1 that is NOT misty. :D

Comment 28 by Raymond Camden posted on 1/12/2012 at 10:44 PM

Yeah... I can change my URLs to be G rated only, but wanted to see if she could respond first. ;)

Comment 29 by Julian Halliwell posted on 1/13/2012 at 2:29 PM

Great new feature, Raymond. Looking forward to it.

Just a correction on the current version though. You said:

'since queryNew expects at least one column, you're still going to have to do one the "ugly" way'

Actually you can create a query without any columns, and then add them all with arrays:

q=QueryNew( "" );
column1values = [ "a","b","c" ];
column2Values = [ 1,2,3 ];
QueryAddColumn( q,"column1","VarChar",column1values );
QueryAddColumn( q,"column2","Integer",column2Values );
WriteDump( q );

Comment 30 by Raymond Camden posted on 1/13/2012 at 5:08 PM

Ah, thank you Julian. I tested that - but I did it as


and got an error. Didn't think to try an empty string. :)

Comment 31 by Victor posted on 1/17/2012 at 6:16 PM

Awesome! Thanks for news, Ray.

Comment 32 by Mohammed suleman khan posted on 12/21/2012 at 4:46 PM

add user page is not shown. it is the first time i am using query with fw1.

<cffunction name="get" access="public" output="false" returntype="query">
<cfargument name="id" type="numeric" required="true">

<cfquery name="user">
SELECT * FROM users WHERE id = <cfqueryparam cfsqltype="cf_sql_bigint" value="">

<cfif user.recordcount EQ 0>
<!--- create query with single empty row --->
<cfset user = QueryNew("id, UserName, Password, IsAdmin" , "BigInt, VarChar, VarChar, Integer")>
<cfset newRow = QueryAddRow(user,1)>
<cfset setCell = QuerySetCell(user,"id", 0, 1)>
<cfset setCell = QuerySetCell(user,"IsAdmin", 0, 1)>

<cfreturn user>

Comment 33 by Raymond Camden posted on 12/21/2012 at 5:31 PM

Um - did you mean to post this on another blog entry/

Comment 34 by Mohammed suleman khan posted on 12/21/2012 at 9:22 PM

will you please tell me which blog entry?

Comment 35 by Raymond Camden posted on 12/21/2012 at 9:23 PM

You're kidding, me? You posted the comment. It doesn't appear to be on topic for this blog entry. I have no idea what blog post you wanted to comment on.

Comment 36 by Mohammed suleman khan posted on 12/21/2012 at 10:56 PM

I am really sorry, its wrong place i comment on. actually i have problem while creating empty rows using QueryNew() because it is the first time I am using queries with fw1.
I am sorry, hope you don't mind it.

Comment 37 by Raymond Camden posted on 12/21/2012 at 11:36 PM

Um, I'd recommend the FW/1 google group.