Posted in ColdFusion | Posted on 01-11-2012 | 2,888 views
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.
2<cfset queryAddRow(users, 4)>
3
4<cfset querySetCell(users, "id", 1, 1)>
5<cfset querySetCell(users, "username", "bob", 1)>
6<cfset querySetCell(users, "password", "unicoron", 1)>
7<cfset querySetCell(users, "id", 2, 2)>
8<cfset querySetCell(users, "username", "scott", 2)>
9<cfset querySetCell(users, "password", "ilikesoccer", 2)>
10<cfset querySetCell(users, "id", 3, 3)>
11<cfset querySetCell(users, "username", "todd", 3)>
12<cfset querySetCell(users, "password", "icheatoncod", 3)>
13<cfset querySetCell(users, "id", 4, 4)>
14<cfset querySetCell(users, "username", "ray", 4)>
15<cfset querySetCell(users, "password", "icodesmelly", 4)>
16
17<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:
2 [{id:1, username:"bob", password:"unicoron"},
3 {id:2, username:"scott", password:"ilikesoccer"},
4 {id:3, username:"todd", password:"icheatoncod"},
5 {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.
Make sense?
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?
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!
- 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"] ])>
Sam, you know where you can make this suggestion. Please do so. :)
Also, think about Zeus' support for closures and you'll see how this can be taken to a whole new level.
While we're at it, can we get a getParams() function in the Http class?
There's setAttributes() & getAttributes() & clearAttributes()
but there's only addParam() & clearParams()...
Where's getParams()?
Thx
public function getParams() { return variables.parems; }
and there ya go. :)
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.
Is there also a way to set default values to omit keys in the structs?
I also hope for queryRowToStruct() & queryToArray()
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 );
queryNew()
and got an error. Didn't think to try an empty string. :)
[Add Comment] [Subscribe to Comments]