Data Management

The many uses of ColdFusion simulated queries

All Web applications need data access, but working with databases complicates development. See how a custom tag for creating simulated query result sets can make your development easier.


Like any modern Web application server, ColdFusion allows you to query databases and retrieve data. Unlike many Web application servers, CF makes it extremely easy to query databases and retrieve data. You only need to write some SQL and place that code into the <cfquery> tag, which takes your SQL, sends it on to the database, and returns a query result set that you can then use in your application. However, this isn’t the only way to create a query result set.

The ColdFusion Markup Language (CFML) includes a set of functions for creating, populating, and manipulating your own query result sets. You can even create artificial, simulated data sets without a database. In addition to showing you how to create a simulated query, I’ll give you some examples where the unusual strategy can be useful.

Simulating a query with <cf_querysim>
ColdFusion’s query result sets are related to arrays and structures. You can conceptualize them as a grid, with column names across the top row, row numbers going down the first column, and data values populating the remainder of the grid. You can use the same CFML functions that ColdFusion uses to create query result sets to define your own result set, name it, set column names, add rows, and populate it with data.

But these functions are difficult to use, so some clever developers encapsulated the result set creation logic into a free, custom CF tag called <cf_querysim>, which you can download. Listing A contains an example of constructing a simulated query result set (which I’ll hereafter refer to as a querysim) with the custom tag.

As you can see, the syntax is very simple. Within the tag set, the first line sets the name of the query and the second line names the columns as a comma-separated list. Subsequent lines provide data values for each row, where each cell is separated with the pipe symbol. If you only define the name and the columns but give no data values, you end up with an empty result set.

Reusing forms using querysims
One function that’s common to most Web applications is allowing a user to add, edit, and delete database data through HTML forms. You might create such a form using code, like that found in Listing B, that queries the database for a matching user and outputs empty or default values if no matching record was found.

Even in this simple example with only three fields, you can see that there are a lot of conditional checks. It gets worse if you want to define default values for some of the fields if you’re dealing with a new or empty record.

This is a great spot for a querysim, which will let you eliminate all that conditional logic, as you can see from Listing C. This time I need only one conditional check, right before the <cf_querysim> tag. If no matching records are found in the database, I build a result set with some default values. The form itself doesn’t need any conditional logic to detect an empty result set and display default data.

Make testing easier too
All developers worth their salt do at least some testing on their work. I do at least unit testing on each code file to make sure that it runs the way it is expected to run. However, I've experienced problems doing this testing when one file depends on other files in order to execute properly.

A perfect example would be a news item display page that pulls data from a database using a query in a separate file. The display page depends on the query file in order to execute, so in order to test the display page I’d also need to run the file that queries the database for test data. That’s not too onerous a task. But what if the display page depends on several separate queries? Should I really have to run them all just to test my display code? If there is an error during the test, how do I know which file is the culprit?

The solution to this problem is to use querysims in your unit tests. Not only can you avoid pulling any external files into the mix, you can also test your display code several different ways and see how it handles unexpected data with minimal trouble. If you turn up an error, you’ll know it’s in your display code, not in one of several external files

Separate application and database development
Querysims can also be helpful during development. On large projects, dedicated DBAs often handle the task of designing and tuning the database. Typically the database design on the projects must be finalized before the application code can be written, but creating and optimizing a large-scale database system can take significant time. Therefore, architects usually try to define up front what data will be used in the application, so that database design and application development can take place simultaneously.

If you’re ever on a project with a similar division of labor, you can rely on querysims to provide data as you write and test your code. For example, say your application will show the total number of customers in the database. If the architect defines up front that a column called totalCustomerCount must be provided by a specific query, you can use a querysim to test your code. It doesn’t matter if the DBA decides that returning the total customer count requires 14 table joins, along with assorted triggers, stored procedures, and indexes. As long as the query returns a column called totalCustomerCount, everything works correctly because database-level design decisions can be isolated from the application developers.

How have you used simulated queries?
There are a number of uses for a homegrown result set. Download the tag, give it a try, and let me know if you come up with other interesting uses for simulated queries.

Editor's Picks

Free Newsletters, In your Inbox