Making ColdFusion's QueryNew() More Readable
Posted December 4, 2006 at 2:08 PM by Ben Nadel
The ability to manually create, query, and manipulate ColdFusion record sets is one of the best features of ColdFusion. Maybe, I am crazy, but ColdFusion query of queries are just the cat's pajamas! Some times, as in today, I use the QueryNew() function to create rather large queries. As you know, the QueryNew() method takes two arguments: the query column names and the query column types. As you can imagine, the bigger the query (number of columns) the harder and harder it becomes to see which column types apply to which columns. To remedy this, I have come up with a simple solution to map the column names to column types in a highly readable way:
- <!--- Create the columns definitions for query new. --->
- <cfsavecontent variable="strColumns">
- data :: VARCHAR,
- rfid :: VARCHAR,
- date :: VARCHAR,
- time :: VARCHAR,
- gross :: VARCHAR,
- tare :: VARCHAR,
- net :: VARCHAR,
- level :: VARCHAR,
- rollover_id :: VARCHAR,
- rb_dollars :: DECIMAL,
- is_valid :: INTEGER,
- is_invalid_account :: INTEGER,
- is_invalid_entry :: INTEGER,
- is_invalid_gross :: INTEGER,
- is_invalid_tare :: INTEGER,
- is_invalid_net :: INTEGER,
- is_duplicate_credit :: INTEGER,
- useraccount_id :: VARCHAR,
- rfid_count :: VARCHAR,
- address_id :: VARCHAR,
- street1 :: VARCHAR,
- street2 :: VARCHAR,
- city :: VARCHAR,
- state :: VARCHAR,
- zip :: VARCHAR
- </cfsavecontent>
-
- <!---
- Manually create a query using the column mapping
- defined in the CFSaveContent tag above.
- --->
- <cfset qCredit = QueryNew(
- <!--- Column names. --->
- strColumns.ReplaceAll( "\s*::[^,]+", "" ).ReplaceAll( ",\s*", ", " ).Trim(),
-
- <!--- Column types. --->
- strColumns.ReplaceAll( ",[^:]+::\s*", ", " ).ReplaceAll( "^[^:]+::\s*", "" ).Trim()
- ) />
As you can see, I am creating a mapping between the column names and the column data types that is quite easy to read and maintain. Then, for the actually QueryNew() method call, I am just stripping out the unwanted characters for each QueryNew() argument. Sure, this might not be the fastest method, but I think it will be the best method (for me) for large queries over time. After all, this is the alternative:
- <cfset qCredit = QueryNew(
- "data, rfid, date, time, gross, tare, net, level, rolloverid, rb_dollars, is_valid, is_invalid_account, is_invalid_entry, is_invalid_tare, is_duplicate_credit, useraccount_id, rfid_count, address_id, street1, street2, city, state, zip",
- "VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, DECIMAL, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR"
- ) />
Try telling me what the heck is going on there without counting on your fingers!
The only issue I have is that it requires a bit more code. What would be cool is to be able to make that in to a custom tag; something like:
- <sys:querynew name="qCredit">
- data :: VARCHAR,
- rfid :: VARCHAR,
- date :: VARCHAR,
- time :: VARCHAR,
- gross :: VARCHAR,
- tare :: VARCHAR,
- net :: VARCHAR,
- level :: VARCHAR,
- rollover_id :: VARCHAR,
- rb_dollars :: DECIMAL,
- is_valid :: INTEGER,
- is_invalid_account :: INTEGER,
- is_invalid_entry :: INTEGER,
- is_invalid_gross :: INTEGER,
- is_invalid_tare :: INTEGER,
- is_invalid_net :: INTEGER,
- is_duplicate_credit :: INTEGER,
- useraccount_id :: VARCHAR,
- rfid_count :: VARCHAR,
- address_id :: VARCHAR,
- street1 :: VARCHAR,
- street2 :: VARCHAR,
- city :: VARCHAR,
- state :: VARCHAR,
- zip :: VARCHAR
- </sys:querynew>
That sort of tag would take about 3 seconds to implement and would be quite useful (it would return the qCredit query if that was not clear). I am not a FuseBox guy, but this might be something like what QuerySim tried to do. The difference is that I am not creating records, I am creating record SETS. Anyway, I was quite happy with this solution, thought I would pass it on.
Reader Comments
"The difference is that I am not creating records, I am creating record SETS."
In the context of queries, records == record sets. I think what you mean to say is:
"...I am not creating record sets, I'm defining record sets."
Paul,
Yes, excellent distinction. I am just defining the record set. Thanks for correcting that. I was trying to emphasize that this has nothing to do with adding records (but both methods actually created record sets).
Why include the commas in your custom tag, when newline is a perfectly good delimiter, and means you can't suffer from the annoying "oh crap, I missed the last comma /again/" thing.
Excellent point. There is no real need for the comma. I think I just had it in because I was thinking about stripping away characters. Without the comma in the original data, I would need to add it in during the ReplaceAll()s, which would not be complicated.
I am using querynew() to create a query on the fly..
Getting errors in Production environment that i did not get in DEV..
I am suspecting it has to do with the versions of Coldfusion we have installed on the different environment..
In DEV i get no error..
In PROD...i am getting slammed by:
***************************************************
Parameter validation error for function QUERYNEW. The function takes 1 parameter.
The error occurred on line 86.
***************************************************
My question is, is there a difference in the versions..? 6 vs 7 or 7.0.2?
Thanks.
@Victor,
Yeah, QueryNew() starts taking more parameters in the new versions. Specifically, I think the data type column options.
I know you probably already know this, but you should probably have the same version of ColdFusion in your dev and your production environments :P sorry, that's just the teacher in my coming out.
When my xmlquery gets to be 800 rows or more it takes a long time to load, if it gets to 1600 rows it times out (20 seconds). Much slower then pulling from database. any performance tips for querynew. or other alternatives. I'm probably doing something wrong ?
Its seems to me that it is faster to do a cffile read and only loop throught part of the dataset, using find() or something like to filter. I can read and find stuff in a secound, with basic find() and cffile. But when i use querynew it always takes 20 secs or timeout with larger datasets. It all depends how much you are outputting with cffile on the speed. I only want to display 12 files at a time so this works much better. If I wanted to display all the results on one page it would be same speed.
@James,
What kind of data are you storing? May I ask why you are using a file rather than a database?
@ Ben
I am trying to max out my gdidy account. They only offer up to 200 simultaneous mysql transactions. But unlimited data transfer, and storage.
So i am trying to leverage what really needs database access (which is easier, faster etc) and what I can store in xml for access. And what I can make static. And a bit of normalizing. I liked how the query new object worked but its Slow !. really really really slow. But very convenient, since its so similar. But it takes 20 secounds to pull what the database takes in 2-3 secs. So its cant be an option unless I can make it faster.
But for the most part im storing text. For which to be searched.
My questions might sound nutz , im not formally trained.
@James,
Have you considering serializing the query into XML via WDDX and then saving that file. That way, you can de-serialize the WDDX file back into a query - it might be faster?
No - but I will now, just got to figure it out, never used WDDX method before.
