ColdFusion in Context - Core Queries

Suppose you have standard code you use to support every insert, update, and delete. Perhaps you use a try-catch combination for every database change. You've noticed that your support code is larger than the actual query. What can you do to avoid having to manually add this code to every query you write? This tip shows one way.

Set the Stage

To demonstrate changing a database, you'll need infrastructure. Because this technique lends itself well to re-use, you'll want an appropriate directory structure. You'll also need a table to modify. A pick list is appropriate to select records for update and deletion. An entry form supporting insert and update is also necessary.
Create Directories and a Table for the Demo
Directories organized by function (not by application) would be nice, and you'll need a table for the demo. Make the following directories under a common parent: work and querybin. Create a table named Item. The Item table needs a numeric ItemID, a text ItemName and a text ItemNo (to be used as you would use the part number in a catalog).
Make a Pick List
You'll need a page that displays existing rows and lets you pick one of them to modify or lets you prepare to insert a new row; call this code pick.cfm. It's fed by a straightforward select query. This particular form has an edit button for each row and a delete button for each row. 

<cfquery name="getList" datasource="context">
select * from Item
</cfquery>
<form name="pick" action="form.cfm" method="post">
<table>
<tr><td>EDIT</td><td>NAME</td><td>NUMBER</td><td>DELETE</td></tr>
<cfoutput query="getList">
<tr>
<td><input type="submit" name="editme" value="#ItemID#">
</td><td>#ItemName#</td><td>#ItemNo#</td>
<td><input type="submit" name="delme" value="#ItemID#"></tr>
</cfoutput>
</table>
</form>
Make a Dual-Action Form
This form supports the pick list when update or delete is requested; call it form.cfm and put it in the work directory. If delete, the form deletes the record. If update, the entry fields are pre-filled from the selected record.

This form also implements an insert function. If the mode is not update, the entry fields are pre-filled from an empty query to which an empty row has been appended using the QueryAddRow function.

Finally, the page returns to the URL in the "Return" variable, form.cfm in this case. If there was a problem with an insert, update, or delete attempt, the page performing that function will return the problem in a query string attached to the URL of this page. The "Problem", if any, will be displayed on this page. The exit button takes the user to a page you have defined. (It will fail if you don't define it.) 

<!--- Respond to pick.cfm --->
<cfparam name="form.editme" default="">
<cfparam name="form.delme" default="">
<cfparam name="form.addme" default="do this">
<cfset Return="../work/form.cfm">
<cfparam name="url.Problem" default="">
<cfoutput>#Problem#</cfoutput><br>
<cfif len(form.editme)>
  <cfset myaction="../querybin/chgitem.cfm">
  <cfquery name="getItem" datasource="context">
  select * from Item
  where ItemID = <cfoutput>#form.editme#</cfoutput>
  </cfquery>
  EDIT <cfoutput>#form.editme#</cfoutput><br>
<cfelse>
  <cfquery name="getItem" datasource="context">
  select * from Item
  where 1 = 0
  </cfquery>
  <cfset dummy=QueryAddRow(getItem)>
  ADD<br>
  <cfif len(form.delme)>
    <cfinclude template="../querybin/delitem.cfm">
    <cfset form.delme="">
  <cfelseif len(form.addme)>
    <cfset myaction="../querybin/additem.cfm">
  </cfif>
</cfif>
<!--- Accept data for insert or update --->
<form name="myForm"
action=<cfoutput>"#myaction#"</cfoutput> method="post">
ItemName: <input type="text" name="ItemName"
value=<cfoutput>"#getItem.ItemName#"</cfoutput>
size="22" maxlength="20"><br>
ItemNo: <input type="text" name="ItemNo"
value=<cfoutput>"#getItem.ItemNo#"</cfoutput>
size="6" maxlength="5"><br>
<input type="button" name="dothis" value="Exit"
onClick="document.location='../yoursite.cfm'; return false;">
<input type="reset" name="dummy" value="Reset">
<input type="submit" name="dothis" value="Submit">
<input type="hidden" name="ItemID"
value=<cfoutput>"#getItem.ItemID#"</cfoutput>>
<input type="hidden" name="Return" value=<cfoutput>"#Return#"</cfoutput>>
</form>

<!--- Pick for update or delete --->
<hr>
<cfinclude template="pick.cfm">

Consider Core Queries

If you can wedge a core query into your support code without changing that code, you can use a single copy of that support code for all your inserts, another for all your updates, and another for all of your deletes. That's what having a core query does for you.
Craft a Core Insert Query
Call this core query document additem.cfm, and put it in the querybin directory. Because this is an insert, your support code might do a query on its own to get the next ID number and will therefore need the table name and the name of the table's ID element (key). Any query will need the core query itself. The name of the page to return to can be set by the page that calls this one and can be ignored here.

The Table and Key names are readily passed in variables. The query can be stored in a string (named Core). Notice that this is straight SQL except for the ColdFusion variables and the question marks around ID1.

ID1 surrounded by question marks represents the value that WILL be used for the desired key but which is not yet defined and therefore should be treated as text now but as a variable later. Your support code will turn this into a ColdFusion variable and then fill in the key value it has obtained before it actually performs the insert.

Finally, this query document includes the page that contains the support code for the kind of action being performed: insert, update, or delete. In this case, it's insert, to be performed by add.cfm. 

<cfset TableName="Item">
<cfset KeyName="ItemID">
<cfset Core="insert into #TableName#
(#KeyName#, ItemName, ItemNo)
values (?ID1?, '#form.ItemName#', '#form.ItemNo#')">
<cfinclude template="../querybin/add.cfm">
Make a Core Update Query
The update is simpler; call it chgitem.cfm and put it in the querybin directory. You don't have to specify the name of the key field. Because only one query is involved, you don't have to specify the table name separately if you don't need to repeat it in a problem alert. You only need the core query itself, the name you want it to have, and the name of the page that will execute it: chg.cfm. 

<cfset Core="update Item set
ItemName = '#trim(form.ItemName)#',
ItemNo = '#trim(form.ItemNo)#'
where ItemID = #form.ItemID#">
<cfset QueryName="chgItem">
<cfinclude template="../querybin/chg.cfm">
Make a Core Delete Query
The delete query is similar to the update query; call it delitem.cfm and put it in the querybin directory. It will be executed by chg.cfm. 

<cfset Core="delete from Item
where ItemID = #form.delme#">
<cfinclude template="../querybin/del.cfm">

Modify Your Insert Support Code

You may do inserts by wrapping a cftransaction tag around a check for the last-used ID number and the insert query that uses the next ID number. You may in turn wrap that inside a try-catch combination. You may wrap that inside a loop to try a few times before reporting failure. Code like this can nevertheless interface easily with a core query.

The general idea in this demo is that a query in the support code goes after the highest key value currently used and stores the next available value in the variable ID1, which is then used as the key in the insert query.

There are two important differences between hard-coding the query within this structure and using a query stored in a string. For insertion, after you determine the key value to be used, you need to convert the text representing that value (?ID1? in this case) to a ColdFusion variable that will carry the key value into the query. The string "?ID1?" must be changed to "#ID1#" where it appears in the insert query. The conversion must take place after ID1 is defined; that's why ID1 can't be specified as a variable when the core query is first written. The second difference holds true for all queries stored in a string: the preserveSingleQuotes function must be used to avoid dropping the single quotes from the query.

Here's what the result looks like. Notice that it contains no table-specific code and can be used to support any single-key insert query. Call it add.cfm and put it in the querybin directory. 

<!--- Begin the loop --->
<cfset try=0>
<cfloop condition="try lt 4">
<cfset try=try+1>
<cfset Qstring="">

<cftry>
<cftransaction>
<cfset Qerr=0>
<!--- (no error yet) --->

<!--- Get next ID for single-element key --->
<cfquery name="getMax1" datasource="context">
select max(#KeyName#) as Max1
from #TableName#
</cfquery>
<cfif len(getMax1.Max1)>
  <cfset ID1=getMax1.Max1+1>
<cfelse>
  <cfset ID1=1>
</cfif>

<!--- Try to add the row --->
<cfset Core1=replaceNoCase(Core,"?ID1?","#ID1#")>
<cfquery name="add" datasource="context">
<cfoutput>#preserveSingleQuotes(Core1)#</cfoutput>
</cfquery>

</cftransaction>

<!--- Exit here when OK --->
<cfbreak>

<!--- Retry up to max times --->
<cfcatch type="database">
<cfif try gt 3>
  <cfset Qstring="?Problem=Repeatedly failed to add to database; try again later or notify maintenance.">
  <cfbreak>
</cfif>
</cfcatch>
</cftry>
</cfloop>

<cfset myURL="#Return##Qstring#">
<cflocation url="#myURL#">

Handle Updates

Update code is similar except that there is only one query, and it has to be given the name passed in a variable from the calling page. Call this code update.cfm, and put in the querybin directory. 

<!--- Begin the loop --->
<cfset try=0>
<cfloop condition="try lt 4">
<cfset try=try+1>
<cfset Qstring="">

<cftry>
<cftransaction>
<cfset Qerr=0>
<!--- (no error yet) --->

<!--- Try to change the row --->
<cfquery name="#QueryName#" datasource="context">
<cfoutput>#preserveSingleQuotes(Core)#</cfoutput>
</cfquery>

</cftransaction>
<!--- Exit here when OK --->
<cfbreak>

<!--- Retry up to max times --->
<cfcatch type="database">
<cfif try gt 3>
  <cfset Qstring="?Problem=Repeatedly failed to change database; try again later or notify maintenance.">
  <cfbreak>
</cfif>
</cfcatch>
</cftry>
</cfloop>

<cfset myURL="#Return##Qstring#">
<cflocation url="#myURL#">

Handle Deletes

Delete code is even simpler. Call this code in del.cfm, and put it in the querybin directory. 

<!--- Begin the loop --->
<cfset try=0>
<cfloop condition="try lt 4">
<cfset try=try+1>
<cfset Qstring="">

<cftry>

<!--- Try to delete the row --->
<cfquery name="del" datasource="context">
<cfoutput>#preserveSingleQuotes(Core)#</cfoutput>
</cfquery>

<!--- Exit here when OK --->
<cfbreak>

<!--- Retry up to max times --->
<cfcatch type="database">
<cfif try gt 3>
  <cfset Qstring="?Problem=Repeatedly failed to delete from database; try again later or notify maintenance.">
  <cfbreak>
</cfif>
</cfcatch>
</cftry>
</cfloop>

<cfset myURL="#Return##Qstring#">
<cflocation url="#myURL#">

Summary

To try this out, browse form.cfm in the work directory. Coding for additional tables doesn't have to take a long time. You can create core queries simply that share this common infrastructure. You can build forms using the lessons contained in this demo. Extend these techniques and share the wealth. =Marty=