A common function of ColdFusion applications is the query-by-example interface (QBE) that allows end users to select from a list of properties in order to find matching records. It generally involves creating a simple HTML form. Based on end user input into the form, you construct and execute a SQL query on an action page, displaying the results to the user.

Creating such a mechanism in ColdFusion is fairly perfunctory. During the last three years of developing with CF, I must have created 5,637 such interfaces. Perhaps I'm a slow learner or a glutton for punishment, but finally, at number 5,638 I decided to call it quits. I figured there had to be a better, faster solution and steadfastly refused to code another QBE.

The challenge was laid out before me. How do I create a cross-platform reusable query component with a customizable interface that could be deployed in any application? Well, the result is depicted in Figure 1. The coding methodologies involved integrating nested frames, nested CFML custom tags, CFWDDX and copious amounts of JavaScript. The coding methods involved in this example are complex enough to make one developer exclaim, "That thing is the QBE of Death!" The name stuck.

Key Technology: A Brief Tutorial on Custom Tags
ColdFusion 3.x allows us to create reusable components built from CFML with protected variable scoping. CF custom tags can't overwrite the contents of variables from the calling page unless explicitly ordered to do so. This innovation has spawned, at last count, over 450 reusable tags available for download from the Allaire tag gallery(www.allaire.com/taggallery/). Most are royalty free and unencrypted. Invoking CF 3.x custom tags can be accomplished by using one of the following conventions.

Method 1: Using Simple Syntax
<CF_mytag [optional parameter list]> where mytag is the name of the custom tag file with a .CFM extension and the optional parameter list contains a series of name and value pairs. For example, running the file alert.cfm and passing a parameter-named message involves the following syntax:

<CF_alert message="Red Alert!">

ColdFusion will find and execute the alert.cfm located in either the same directory as the calling page or in a subdirectory of the CustomTags directory of your ColdFusion install directory.

Method 2: Using <CFMODULE> with the Template Attribute
The <CFMODULE> tag allows you to explicitly specify the URL path of your custom tag. If your custom tag was located one directory up from the calling directory, the invocation syntax would be the following:

<CFMODULE template="../alert.cfm" MESSAGE="Red Alert">

Method 3: Using <CFMODULE> with the Name Attribute
Using <CFMODULE> with the name attribute allows you to specify the location of a custom tag underneath the CustomTags subdirectory using dot notation. Calling a file located in the C:\cfusion\customtags\javascript\popups directory resolves to the following:

<CFMODULE name="javascript.popups.alert" MESSAGE="Red Alert">

For the custom tag to "see" the parameters, they must be prefaced with the attributes prefix. The custom tag depicted in Listing 1 receives the parameter and generates a simple JavaScript alert box.

<CFPARAM NAME="attributes.message" DEFAULT="I have nothing to report">

<CFOUTPUT>
<SCRIPT LANGUAGE="JavaScript">
alert("#attributes.message#");
</SCRIPT>
</CFOUTPUT>

While these calling methods allow for the easy passing of a fixed number of parameters and values, they're not well equipped to deal with the variable number of associated parameters we might encounter when deploying a reusable QBE. Fortunately the enhanced tag architecture found in ColdFusion 4.x allows for the creation and invocation of multiple nested subtags. Using this new feature, we can create a series of integrated custom tags providing virtually unlimited flexibility, as described in Listing 2.

Using <CFMODULE> to invoke nested custom tags is largely undocumented, however, it yields more flexibility in determining the location of custom tags. Listing 2 can be rewritten using the following structure:

<CFMODULE TEMPLATE="qbe.cfm" [tagparams] >

<CFMODULE TEMPLATE="querycriteria.cfm" [tag params]>
...
...
...
</CFMODULE>

ColdFusion will find and execute the alert.cfm (see Listing 1) file located in either the same directory as the calling page or in a subdirectory of the Custom Tags directory of your ColdFusion install directory. During this operation, the variable thistag.executionmode is equal to the text string "start." CF then executes each <CF_QUERYCRITERIA> subtag in turn (see Listing 2). When the </CF_QBE> tag is reached, the QBE.CFM file is reexecuted; however, at this point the variable thistag.executionmode is equal to the text string "end." The QBE tag accommodates this dual-execution process using the <CFSWITCH> construct as depicted below.

<CFSWITCH expression="#thistag.executionmode#">
<CFCASE value="Start">
<!--- do processing from <CF_QBE> Call -à
</CFCASE>
<CFCASE value="End">
<!--- do processing for </CF_QBE> Call -à
</CFCASE>
</CFSWITCH>

Key Technology: Nested Framesets
The <CF_QBE> interface as depicted in Figure 1 is split into three sections. The top left frame displays a list of columns that the user may select to form the basis of his/her search. Once s/he makes a selection, the data field, along with the appropriate boolean logic constructor, appears in the top right frame. The nested frameset definition, depicted in Listings 3 and 4, allows the bottom submit/requery button to persist on the screen throughout the process of executing and refining the search criteria. It also allows us to keep field selections persistent by storing them as WDDX JavaScript arrays in the button frame while destroying and re-creating the contents of a data frame, thus decreasing reliance on cookie support and session variables memory overhead. Of course, one drawback to this approach is that if the user should hit the browser RELOAD button, all their selections will be lost.

Key Technology:
JavaScript Document.write()
Both Netscape 2.0+ and IE 3.x+ support what I term "poor man's DHTML." While dynamic HTML allows you to programmatically change the contents and format of a document without initiating a full-page reload, its utility is diminished since only advanced browsers support it. Worse still, the syntax varies significantly between IE and Netscape. You can, however, simulate cross-platform client-side redraws through the JavaScript document.write() method. In many instances, using document.write you may be able to reduce or eliminate calls to the Web server, since a page's contents are coming not from the Web server but from within the browser itself. This results in faster application performance and enhanced overall scalability. Listing 5 details an excerpt from the QBE drawCriteria() function which is responsible for continually updating the search criteria frame whenever the user makes a selection.

The JavaScript open() method clears out a frame to accept data. Next, I programmatically output the ubiquitous <HTML> and <BODY> tags, followed by a form, table and JavaScript loop to output table rows containing the selected fields. The JavaScript close() method closes the frame for writing.

Key Technology:
Passing Tag Attributes to a Base Tag Through Structures
When CF encounters the <CF_QBE> starting tag, it outputs a series of JavaScript validation routines used to parse search criteria entries. It also includes the Allaire supplied wddx.js, a library of JavaScript functions used to manipulate client-side WDDX recordsets.

For every <CF_QUERYCRITERIA> tag called, all the parameters passed to the subtag are aggregated in a CF structure, called thistag.assocattribs. The contents of querycriteria.cfm, depicted in Listing 6, are quite simple - a series of <CFPARAM> statements specifying default values, followed by the <CFASSOCIATE> tag, which makes these parameters visible to the parent tag.

Finally, the second call to the parent tag, </CF_QBE>, actually outputs the JavaScript required to write the field selector list, instantiates the JavaScript object that will hold the user search criteria and redraws the selected fields. Listing 7 demonstrates a CFLOOP whereby all of the attributes aggregated from the multiple nested subtags are rerendered as an array of JavaScript objects during the </CFQBE> sequence.

Key Technology: Web-Distributed Data Exchange (WDDX)
WDDX is a generic format for storing and passing data between servers, or in this case between CF and the browser. The ColdFusion <CFWDDX> tag is used to both serialize data (convert CF to WDDX format or CF to JavaScript objects) and to deserialize data (convert WDDX format to CF or JavaScript objects to CF). Both simple (text strings) or complex (arrays, structures or queries) variables may be translated between the domains.

Notice that in Listing 8, the CFWDDX tag appears between the <SCRIPT></SCRIPT> tags. The ColdFusion server takes the empty CF server-side recordset and converts it to a series of JavaScript arrays that may be accessed through the variable name SEARCHCRITERIA. The columns of SEARCHCRITERIA can be referenced in Javascript as searchcriteria.fieldname, searchcriteria.logic, etc., and the rows as searchcriteria.fieldname[0], searchcriteria.fieldname[1], etc. The ACTION parameter of the CFWDDX tag is used to indicate how to translate the data object. CFML2JS specifies conversion from ColdFusion to JavaScript. Later, we'll see how <CFWDDX> translates end-user selections back into a CFQUERY recordset object.

Each time a search field is chosen from the list of available fields (in the left frame), the field appears in the right frame and a new row is added to the SEARCHCRITERIA object. This is done through a call to the addfield function, depicted in Listing 9. Note that the addRows method is defined in wddx.js and is similar to the ColdFusion QueryAddRow function. Also, be aware that while CFQUERY recordsets begin with a row offset of 1, their JavaScript counterparts begin with a row offset of zero, hence the -1 modifier.

As the user chooses which search fields to use in the query, the list of chosen fields in the right upper frame grows. Columns are set up so the user can choose which relational operator to use with the example data s/he enters. The HTML code that displays these columns, after the DEPARTMENT field is chosen from the search field list, is depicted in Listing 9. Note that it was dynamically generated from within the browser using the document.write() methodology.

The code in Listing 10 sets up the two columns for the first chosen field (department in this case). Since this is the first field (and first row in the SEARCHCRITERIA object), the form elements are named with a 0 following the descriptive text (logic and data); subsequent elements will be named logic1, data1, logic2, data2, etc. There are two event handlers associated with these elements. When the logic element selection is made, the onChange event fires and updates the current row (0) of the logic property of the SEARCHCRITERIA object in the button frame with the value (text) of the selection chosen. If the first option is chosen (REMOVE, value=0), the drawCriteria() function is also called to redraw the field list in the right frame. The second event fires when the element named data0 is visited. This event simply updates the current row (0) of the value property of the SEARCHCRITERIA object in the button frame.

When the Run Query button is pressed, the doRunQuery function is called. The following is a small portion of that function:

wddxSerializer = new WddxSerializer();
wddxPacket = ddxSerializer.serialize(searchcriteria);
document.forms[0].querypacket.value=wddxPacket;
document.forms[0].mybutton.value='Requery';
document.forms[0].submit();

The first line above creates an instance of the WddxSerializer object (defined in wddx.js). The wddxPacket object is then given a value equal to the serialized version of the SEARCHCRITERIA object. The hidden field, named querypacket in the button frame, is then assigned the value of wddxPacket. At this point, the hidden field querypacket's value is (contents have been reformatted for easy viewing):

<wddxPacket version='0.9'>
<header/>
<data>
<recordset rowCount='1'
fieldNames='fieldname,logic,value,datatype,display,deletedyn'>
<field
name='fieldname'><string>EmpDepartments.DepartmentID</string></field>
<field name='logic'><string>=</string></field>
<field name='value'><string>1</string></field>
<field
name='datatype'><string>numeric</string></field>
<field
name='display'><string>Department</string></field>
<<field
name="deletedyn"><string>N</string></field>
</recordset>
</data>
</wddxPacket>

The wddxPacket above simply uses a taglike notation to identify the type of data contained between each set of tags (recordset, field and string). The recordset consists of one row with the specified fields. Each field then has a name and value associated with it delimited by the tags describing the datatype of that field.

The next step is to execute the query. The form in the querystart.cfm template (in the button frame) is submitted and the action page (runquery.cfm) constructs the query by decoding the above wwddxPacket .

The runquery.cfm template calls the qbedecode.cfm template which actually decodes the wddxPacket and constructs most of the where clause in the query.

Robert Segal, a certified Allaire instructor, works at Fig Leaf Software. He has been developing applications for the past 10 years and using CF for the past three. He can be reached at rsegal@figleaf.com

<CFWDDX ACTION="wddx2cfml"
input="#attributes.querypacket#"
output="myquery">

The above tag is called from qbedecode and converts the contents of the querypacket variable back into the original query (with a number of new rows). The newly converted query is called myquery. The rest of the qbedecode template loops through the myquery rows and constructs the where clause based on the fieldname, logic, value and datatype fields in the query.

Putting It All Together
Developing advanced database applications with ColdFusion is deceptively simple. Certainly, there's a lot that can be done using only CFML, however, the real payoff comes from mixing server-side technologies with client-side JavaScript and DHTML. In the coming months, we'll continue to investigate and develop highly reusable and scalable constructs using the techniques described here. In the meantime, have fun with the QBE of Death and (hopefully) never have to code another complex query system again.

• Figure 1
• Source Code