Browse DevX
Sign up for e-mail newsletters from DevX


No Datastructure? ColdFusion's Query Object to the Rescue : Page 2

ColdFusion's Query object enables you to manipulate CF data using standard SQL syntax—even if that data doesn't come from a database.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Filters and Other Goodies
Similarly, you can use SQL on a Query in order to filter data or select a subset of that data. Imagine extracting all books whose Title starts with the letters A-F. The following query would provide that data:

<cfquery dbtype="query" name="filteredBooks"> SELECT * FROM bookQuery WHERE Title >= 'A%' AND Title <= 'F%' </cfquery>

You might also just return a subset of the columns, or append an ORDER BY onto those results, which would also be legal. It is also possible to use some standard SQL aggregate functions on datasets held in Query objects; these commands include: Min(), Max(), Avg(), Count(), and Sum().

Watch Out for Some "Gotchas"
The main gotcha to using Queries is that you need to watch your SQL syntax. Remember that you are not writing SQL in the context of your database server anymore, so you need to avoid any database-specific syntax or extensions provided by database vendors. For example, say you are accustomed to using SQL Server's TOP clause (SELECT TOP 5 * FROM books) or MySQL's LIMIT (SELECT * FROM books LIMIT 5) to limit the number of rows returned from a database query. These keywords are specific to those database servers, and CF's SQL processor does not recognized them. Using them will produce errors.

Another thing you can't do with queries of Queries is use SQL commands other than SELECT—no DELETEs, UPDATEs, or INSERTs allowed. Which syntax is allowed? The "Backus Naur Form (BNF) for CF's Query of Queries" describes the allowable syntax for this feature.

Performance is another area to watch out for, as the SQL execution in CF will in most cases not be as fast as that in a database. However, my personal benchmarking suggests that this probably won't be an issue unless you are handling very large datasets. If the difference between a 10ms and a 100ms execution time is a big deal for your particular application, or if you are handling very large datasets, benchmark your performance.

More CF Query Advantages
This article has focused on CF queries as flexible datastructures that can be manually assembled in code, but other aspects of this functionality also are worth mentioning. CF queries are not only for databases or for data assembled manually: <cfldap>, <cfsearch>, and <cfpop> return Query objects as well. Given this functionality, you can further sort or filter these results in the ways described previously.

Finally, you can also combine data from disparate datasources using the Query object. For example, you could combine the results of a <cfsearch> with <cfquery> results by doing something like this:

SELECT LastName,FirstName FROM databaseQuery UNION SELECT LastName,FirstName FROM textSearchQuery

As such, queries provide a lingua franca for passing data around CF applications, no matter where that data was stored.

Eric Jansson is Assistant Director of Systems and Development for the ACS Technology Center, a regional center supporting the development and use of technology in higher education. His work has focused on commercial and open-source application development in the field of secondary and post-secondary education.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date