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 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#">
<cfset users = queryNew("id,username,password", "varchar,varchar,varchar")>
<cfset queryAddRow(users, 4)>
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?
Archived Comments
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"}]
etc?
Then if you change the column name, you don't have to do it in loads of places?
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.
YAY! Two _very_ welcome enhancements!
Nice! :)
Very nice.
I agree with Tom K. Column names are already defined. That's just extra typing to make users define them again.
Tom - what if you want to specify some, but not all of the columns?
@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!
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",
[[1,"bob","unicoron"],
[2, "scott","ilikesoccer"] ])>
Ok ok you convinced me. ;)
Sam, you know where you can make this suggestion. Please do so. :)
Wherever that place may be. Its done. :)
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.
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?
Henry - what do you mean?
http://help.adobe.com/en_US...
There's setAttributes() & getAttributes() & clearAttributes()
but there's only addParam() & clearParams()...
Where's getParams()?
Thx
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.
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. :)
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.
Then you know the routine - file an ER. :) Feel free to use my code above... just correct the typo.
File ER to http://cfbugs.adobe.com/cfb... ?
Yep.
This is awesome.
This technique comes in very handy while using Xml data set returned from an http request.
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()
Well @ Atan, I suppose there are UDF's avaliable, so probably i do not thnk that should be needed
Yo Misty... um... do I need to turn on PG Gravatars?
100 to 1 that is NOT misty. :D
Yeah... I can change my URLs to be G rated only, but wanted to see if she could respond first. ;)
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 );
Ah, thank you Julian. I tested that - but I did it as
queryNew()
and got an error. Didn't think to try an empty string. :)
Awesome! Thanks for news, Ray.
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="#arguments.id#">
</cfquery>
<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)>
</cfif>
<cfreturn user>
</cffunction>
Um - did you mean to post this on another blog entry/
will you please tell me which blog entry?
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.
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.
Um, I'd recommend the FW/1 google group.