Developing a data-entry interface for managing pesky lookup tables can be a laborious task for even the most seasoned CF developer. These tables usually include a few simple data fields - an identity column for use as a primary key, a text descriptor and perhaps some ancillary information that needs to be managed through a Web-based data-entry interface.

Single template editing (STE) is a methodology for combining insert, update and delete actions in a single .cfm file. While some coding strategies (Fusebox) may increase the number of files required to perform a data-entry task, this method seeks to minimize files by combining functionality.

Once I had optimized the STE algorithm down to the fewest possible lines of code, I created a studio template file to help author these interfaces. Despite using the template, I was still spending an average of 45 minutes developing the customized HTML, CF and stored procedures for a single interface. For applications with many lookup tables to manage, this was a time-consuming and tedious process. After developing my 2,307th lookup-table management file, a thought finally occurred to me.

What if I could build a wizard to generate these data-entry interfaces on the fly?

There are several different strategies for accomplishing this task. Optimally our interface would be "point and shoot" - the developer chooses a data source and table name from an automatically generated picklist resulting in a CFML file and accompanying SQL stored procedures. Coding in WIZML, VTOM (Visual Tools Object Model) and ActiveScripting in CF Studio could potentially accomplish the job through the use of several undocumented features yielding programmatic access to the data sources tab. This approach requires the developer to own a copy of CF Studio with an RDS mapping to their CF Server. See the next issue of CFDJ for more details.

I decided to adopt a more open approach, creating the wizard using CFML itself. The first version of the single template wizard (STW) was developed for SQL Server 6.5. The latest version works for both SQL Server 6.5 and 7.0 as well as giving you the choice of formatting using plain html or a Cascading Style Sheet. It can be adapted fairly easily for other SQL platforms. The ultimate goal of this excercise was to reduce the time to code these interfaces to five minutes or less while describing, for our junior developers, a standard methodology for writing code.

Step 1: Use <CFREGISTRY> to get a listing of available server data sources.

The wizard's first step is to select the data source name to work with. Listing 1 illustrates using the <CFREGISTRY> tag to get a list of MS SQL Server data sources from the server. On selection of the data source, a JavaScript function (readtables) is fired to launch tablelookup.cfm in a hidden frame. Tablelookup.cfm uses another SQL Server system stored procedure, sp_tables, to get the table names for the data source. The table names for the selected data source are loaded into the table-name select list. Then you simply need to select plain html or CSS as your preferred formatting style. Clicking on the Lookup button kicks off the generation of stored procedures (see Figure 1).

Step 2: Generate stored procedures.
Next, the wizard needed to dynamically create stored procedures for insert, update, delete and select actions on the table. This presented a couple of challenges. First, how would the wizard know which column was the primary key and the data type of each column? We could have made some assumptions based on Fig Leaf's standards for database design. We discovered two system stored procedures in SQL Server - sp_pkeys and sp_columns. When given a table name, sp_pkeys returns the name of its primary key column (very handy in this case); sp_columns returns all information about the columns in the table including data type, length and precision. Armed with this information, dynamically creating the insert, update, delete and select stored procedures was straightforward. Note that a preexisting stored procedure may not be modified; it must first be dropped and then re-created. DROP PROCEDURE syntax, when passed through to SQL Server in a <CFQUERY> block, does the trick (see Listing 2). The STW interface displays four text areas containing the stored procedures for insert, update, delete and select. The procedures are named using Fig Leaf's standard naming convention: insTableName for insert, updTableName for update, delTableName for delete and getTableName for select. Note that the delete procedure generated by the wizard is actually an update action. This is another Fig Leaf standard: to logically delete information from lookup tables instead of physically deleting data, thus creating an audit trail and freeing us from the chore of unbinding any foreign key references to other tables. Any of the stored procedure code in the text areas can be modified to suit specific needs. Clicking on the Save button saves the stored procedures to your database (see Figure 2).

Step 3: Generate the .CFM file.
After the stored procedures are created in the database, the .cfm template code is dynamically generated and displayed in a text area.

In this step the only required input is the path and file name for the single template file being generated. The Select Directory button fires a JavaScript (myfilebrowser) to open a new window containing the directory tree for the server. The directory tree is displayed through the use of a custom tag developed using the Java file selector tree (ripped from the CF administrator) called CF_fileselector.

One of the challenges in developing the wizard in CFML involved escaping special character sequences that I didn't want CF to process. While ColdFusion provides various methods for performing this task, such as using pairs of double quotes to represent a single double quotation mark (see Listing 3), I found that using the escape sequences made the wizard source code difficult to read and debug.

The table name is passed forward and sp_pkeys and sp_columns are executed again. Using the result set returned from sp_columns, a comma-delimited list is created with the form, prefix and single quotes where the data type is varchar. A series of CFSET statements are used to create variables to contain CF and HTML code used in a single template. The variable mystring contains the CF code found at the beginning of a single template. This code includes the <CFIF> and <CFQUERY> blocks and JavaScript code used to process the insert, update or delete action. Characters recognized by CF, such as the # and single quotes, are escaped in the CFSET statement. These escaped characters are then unescaped, using the Replace function, before the variables are displayed in the text area. The variables mystartformdeclaration and myendformdeclaration contain the beginning <CFFORM> and ending </CFFORM> tags. The variable selectbox contains the select box that contains records returned from the select query, which was executed at the top of the single template. The variable delbutton contains the delete button and the code to dynamically display the button when appropriate. The last two variables, startoutput and endoutput, contain opening <CFOUTPUT> and ending </CFOUTPUT> tags.

After creating variables to hold all the content for the single template, they're outputted in a text area named "code". The text area contains the CF and HTML code created by the <CFSET> statements, JavaScript code used by the select box to perform the save action, and HTML table code to lay out the interface (see Figure 3).

Depending on your formatting selection in Step 1 of the wizard, plain HTML or CSS syntax is used to format the data entry screen. Finally, the generated code is written out to the Web server's hard disk using a simple call to CFFILE:

<CFFILE action="write"
FILE="#form.filename#" OUTPUT="#variables.mycode#">

The STW fulfilled its objective by reducing the time to develop data-entry interfaces to under five minutes. Consider developing your own wizards using the techniques described in this article. The best way to standardize developers' code is to have a wizard that autogenerates the majority of it. Frankly, I'm tired of hearing about what I can do with ColdFusion. It's time we turned our collective attention to what the product can do for us using automated and wizard-based processes. Innovations like Allaire's Spectra prove the feasibility of using CF on a more ambitious scale to autogenerate code. The resulting productivity boost can be staggering.

• Figure 1
• Figure 2
• Figure 3
• Source Code