ColdFusion in Context - Insert, Update, and Delete

You've heard the standard advice regarding table structure. If you have a one-to-many data relationship, make a separate table for it. If you have a many-to-many data relationship, make a table that relates the other tables involved so that each side of the relationship is one-to-many. However, doing these things does not guarantee that your ColdFusion interface with these tables will be easy to write.

There are some things beyond the usual database advice that you can do to make your life easier when arranging tables for a ColdFusion application. If you use these ideas and templates to create portable, robust applications, you can code less while enjoying it more. Along the way, you'll use the same form for insert and update by creating a shadow table (or using a relatively new ColdFusion function: QueryAddRow), build your own table ID values instead of depending on non-portable autonumbering functions, wrap changes with a try-catch combination that tries again in the face of failure, and write simpler inserts and updates by avoiding numeric fields where possible. To put it all together, you'll add input types not supported by the cfinput tag and create an Exit button to leave the add/edit function when you're ready.

Except for Table IDs, Avoid Numeric Fields Where Possible

If you examine the structure of an SQL insert, you'll see that text fields have single quote delimiters but numeric fields have none. When you insert a string that has no content, the delimiters (single quotes) are still part of the command. So, the database engine knows you meant to provide a value because of these placeholders and doesn't complain. However, when you fail to provide a default value for a numeric field, there is no placeholder. The database returns an error; because, you gave it the wrong number of parameters. The more numeric fields you have, the greater the number of potential bugs you have to guard against.

More importantly, leaving a field empty may be a more accurate representation of reality than putting a zero into it. Setting a field to zero implies you know what it is; leaving it empty admits you don't. Also, it's easier to work with an empty string than it is to work with an empty number.

Finally, just because something is a number doesn't mean you want to handle it that way most of the time. For example, you'll seldom do math on a zip code; so, why store it as numeric? Even if you might do limited math on the number - perhaps it's a checksum or contains encoded permissions in your application - ColdFusion will treat a string as a number when appropriate; so, build in more flexibility by treating it as a string from the start. Don't define a field as numeric unless it really counts or gives the measure of something you would want the database to handle for you as a number.

Create Tables

You've seen forms with a cfif statement for every field so that they can be used to initialize an empty field for inserting a new record or can be used to display a value from an existing record. The "insert" version of the field is fed by an empty variable defined on the page; the update version is fed by a query against the table. The result is a complicated document that defines the value for every field twice: once using an empty variable defined on the page and once with a variable queried from the database. The application would be easier to understand if two separate forms were used (one for insert and one for update), but this would increase the chance that you would forget to modify both documents when the customer needs a change in the database structure. However, there's another way to handle this problem.

Suppose you had an empty table with the same structure as your "real" table. Then you could query the empty table so the fields would be already defined for an insert, query the real table for an update, and use a single form for both purposes. If you're using a version of ColdFusion older than 4.5, that's exactly what I suggest you do. (If you have ColdFusion 4.5, there's another alternative discussed later.)

Create your table as usual. When you're satisfied with it, make a copy of just its structure and give that copy a similar name. For example, if the "real" table is "User", the empty one could be called "UserAdd". It only takes a few seconds for this one-time extra step.

For this demonstration, create two tables: Item and ItemAdd. The Item table needs a numeric ItemID, a text ItemName and a textItemNo (to be used as you would use the part number in a catalog). Copy the structure of this table to ItemAdd, a table you'll leave empty.

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. It's fed by a straightforward select query. This particular form has many submit buttons: an edit button for each row and a delete button for each row. If the user wants to edit, this form puts the ItemID in form.editme. If the user wants to delete, it puts the ItemID in form.delme. Call this code pick.cfm. 

<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

The pick list calls a dual-action form that needs to respond to the pick list (from a previous pass), accept data for insert and update, and display the pick list. If the pick list passed an ItemID in form.editme, it fills the form from the real table; the user can then make changes and "Submit" to post the change.

Otherwise, it fills the form from the empty table USING A QUERY OF THE SAME NAME and offers to add a row. If the pick list passed an ItemID in form.delme, it deletes the row and continues to offer to add a row. If the user presses "Submit" while "ADD" is displayed, it adds a row.

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. It knows the URL of this page from the "Asker" variable. The "Problem", if any, will be displayed on this page.

The action for this form varies with the function to be performed: update.cfm or insert.cfm. (If deleting, the form isn't executed.) Put this code in form.cfm. 

<!--- Respond to pick.cfm --->
<cfparam name="form.editme" default="">
<cfparam name="form.delme" default="">
<cfparam name="form.addme" default="do this">
<cfset Asker="form.cfm">
<cfparam name="url.Problem" default="">
<cfoutput>#Problem#</cfoutput><br>
<cfif len(form.editme)>
  <cfset myaction="update.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 ItemAdd
  <!--- An empty copy of Item --->
  </cfquery>
  ADD<br>
  <cfif len(form.delme)>
    <cfinclude template="delete.cfm">
    <cfset form.delme="">
  <cfelseif len(form.addme)>
    <cfset myaction="insert.cfm">
  </cfif>
</cfif>

The form itself is straightforward and is followed by including pick.cfm; note that it defines each field only once, even though the form can be used for both insert and update. Values to be supplied by ColdFusion are wrapped in cfoutput tags. If the user wishes to exit, javascript provides the brains behind an HTML button to divert the browser to a neutral page. The javascript function "document.location" moves the user to a new page when the user clicks on the button. The "return false" construct is needed to keep some browsers happy. (Remember for your use that the page will fail if this location does not exist.) Note that the ItemId from the query and Action from this page are passed through a hidden field in the form. A horizontal rule, the HTML hr tag, separates the form from the pick list. Add the following code to form.cfm. 

<!--- 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="Asker" value=<cfoutput>"#Asker#"</cfoutput>>
</form>

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

Consider a Newer Alternative

If you have ColdFusion 4.5 or newer, you have an even more attractive option than querying an empty table to populate a form for insert: you can create rows out of nothing. Simply query your original table with an impossible condition (such as "where 1 = 0") so no rows are returned. Then use the QueryAddRow function to add an empty row. The simple syntax looks like this: 

<cfset dummy=QueryAddRow(myQuery)>

That's all it takes. Now you have an empty row to feed to your form. You won't use the variable the function appears to be assigning a value to (e.g., "dummy"); it's just there to satisfy the syntax. Just use the query variables as you usually would. The rest of this demonstration will use shadow tables, but users of recent versions of ColdFusion have this alternative to consider as well. To use it in the context of this demonstration, you would replace the select query for the empty ItemAdd table in the code above with the code below: 

<cfquery name="getItem" datasource="context">
select * from Item
where 1 = 0
</cfquery>
<cfset dummy=QueryAddRow(getItem)>

Don't Autonumber; Create Your Own Table ID

If you rely on the database autonumber function to create a key when you enter rows, you get two problems. Not only are you using a data type that isn't portable between database engines (for example, Access, MS SQL...), but you have to ask the database the key of the record you just entered before you can redisplay it for review and edit. Instead of doing this, use a numeric field for the key instead of an autonumber field and assign the value yourself.

To do this easily, specify the key name and table name, read the maximum value currently used, and use a value one greater than that when you insert the record as part of the same transaction as the "read". The following code snippet assumes that Key contains the NAME of the key field (ItemID) and Table contains the NAME of the table (Myitem). Look at this, but don't put it into a file just yet. 

<!--- Get the next ID --->
<cfquery name="getMax1" datasource="#request.PassDB#">
select max(#Key#) as Max1
from #Table#
</cfquery>
<cfif len(getMax1.Max1)>
  <cfset ID1=getMax1.Max1+1>
<cfelse>
  <cfset ID1=1>
</cfif>

<!--- Try to add the row --->
<!--- (This code is specific to your table structure) --->
<cfquery name="addItem" datasource="context">
insert into Myitem (ItemID, ItemName, ItemNo)
values (<cfoutput>#ID1#, '#form.ItemName#',
'#form.ItemNo#'</cfoutput>)
</cfquery>

Wrap Inserts with a Try-Catch Combination that Tries Again

Unhappy with the thought that the database might fail to honor this as a single transaction? Enclose the whole thing in a loop and try-catch envelope and repeat a few times if needed. The only caveat: comment out the try-catch tags until you've thoroughly debugged; because, they'll hide the messages you would need for debugging. Call this code insert.cfm.

The overall structure will get used again and again. Define a query string that will contain the problem if one is encountered, set up a loop for the desired number of tries, open the try-catch pair, and open the transaction tag (because you have two queries you want to treat as one). The first query and its associated logic yields the next key. If you've defined the Key and Table in the calling page, this code remains the same for all inserts. The second query carries the detail of the insert and therefore always varies for different tables. Close the transaction tag. 

<!-- Specify the key name and table name --->
<cfset Key="ItemID">
<cfset Table="Item">

<!--- 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 --->
<!--- (This code can be re-used "as is" in many places) --->
<cfquery name="getMax1" datasource="context">
select max(#Key#) as Max1
from #Table#
</cfquery>
<cfif len(getMax1.Max1)>
  <cfset ID1=getMax1.Max1+1>
<cfelse>
  <cfset ID1=1>
</cfif>

<!--- Try to add the row --->
<!--- (This code is specific to your table structure) --->
<cfquery name="addItem" datasource="context">
insert into Item (ItemID, ItemName, ItemNo)
values (<cfoutput>#ID1#, '#form.ItemName#',
'#form.ItemNo#'</cfoutput>)
</cfquery>

</cftransaction>

The code will exit in two different places depending on whether the transaction was successful. If the transaction was successful, the logic will continue right after the cftransaction tag, where a cfbreak tag causes control to pass to the statement following the closing cftry tag. If the transaction was not successful, then the cfcatch tag will trap the database error, cause the query string to contain the problem, and break out of the loop. Once outside of the loop, the URL consisting of the calling page and a potential query string is built in two steps for clarity and to avoid confusing the cflocation tag. (The calling page is specified in a variable to permit this structure to be easily reused.) Add this code to insert.cfm. 

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

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

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

Handle Updates

Update code is similar except that there is only one transaction, hence no need for the cftransaction tag. Put this code in update.cfm 

<!-- Specify the table name --->
<cfset Table="Item">

<!--- 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; this small block is table-specific --->
<cfquery name="chgItem" datasource="context">
update Item set
<!--- (leave ItemID unchanged) --->
ItemName = '#trim(form.ItemName)#',
ItemNo = '#trim(form.ItemNo)#'
where ItemID = #form.ItemID#
</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 #Table#; try again later or notify maintenance.">
  <cfbreak>
</cfif>
</cfcatch>
</cftry>
</cfloop>

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

Handle Deletes

Delete code is even simpler. Put this code in delete.cfm. 

<!-- Specify the table name --->
<cfset Table="Item">

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

<cftry>

<!--- Try to delete the row; this small block is table-specific --->
<cfquery name="delItem" datasource="context">
delete * from Item
where ItemID = #form.delme#
</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 #Table#; try again later or notify maintenance.">
  <cfbreak>
</cfif>
</cfcatch>
</cftry>
</cfloop>

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

Summary

This has been a long article, but you now have a starting point for inserting, updating, and deleting database records using ColdFusion in context. Try it out by browsing form.cfm. It uses a shadow table (or the QueryAddRow function) to simplify forms. It does its own record ID creation for portability and simple re-editing of the same record. (By the way, you could even code to re-use "retired" IDs if you choose.) It wraps database changes in a try-catch combination for strength. To simplify inserts and updates, it avoids numeric fields where it can. Finally, it ties these techniques together by extending the power of familiar tags. =Marty=

[You may have seen an earlier version of this tip at this site. After original submission but before publication elsewhere, I revised and sent in the version you see here: the version eventually published. The original only did insert and update. This one does deletion and provides useful controls as well.]