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 developer advocate for HERE Technologies. He focuses on JavaScript, serverless 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