"We're Off to See the Wizard" Vol. 1, Issue 5, p. 34
Developing Code Wizards in CFML

Listing 1: Using <CFREGISTRY> to get a list of SQL Server DSNs

<cfregistry action="GETALL"

branch="HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\ODBC Data Sources"
             name="RegQuery"
             type="Any">

<SELECT NAME="datasource" onChange="readtables(this)">
 <OPTION VALUE="">Please Select
 <CFOUTPUT QUERY="regquery">
 <CFIF regquery.VALUE contains "SQL Server" and entry is not "master">
  <OPTION VALUE="#entry#" <CFIF entry is datasource>SELECTED</ cfif>>#entry#
 </CFIF>
 </CFOUTPUT>
</SELECT>

Listing 2: Writing stored procedures to the database

<!--- check for existence of sp and drop sp if it already exists --->
<CFQUERY NAME="checkforsp" DATASOURCE="#datasource#">
 if exists(select * from sysobjects where id =
object_id('dbo.ins#tablename#') and sysstat = 4)
 drop procedure dbo.ins#tablename#

 if exists(select * from sysobjects where id =
object_id('dbo.upd#tablename#') and sysstat = 4)
 drop procedure dbo.upd#tablename#

 if exists(select * from sysobjects where id =
object_id('dbo.del#tablename#') and sysstat = 4)
 drop procedure dbo.del#tablename#

 if exists(select * from sysobjects where id =
object_id('dbo.get#tablename#') and sysstat = 4)
 drop procedure dbo.get#tablename#
</CFQUERY>

<!--- create sp's in database --->
<CFQUERY NAME="createinsertsp" DATASOURCE="#datasource#">
 #preservesinglequotes(form.createinsertsp)#
</CFQUERY>

<CFQUERY NAME="createupdatesp" DATASOURCE="#datasource#">
 #preservesinglequotes(form.createupdatesp)#
</CFQUERY>

<CFQUERY NAME="createdeletesp" DATASOURCE="#datasource#">
 #preservesinglequotes(form.createdeletesp)#
</CFQUERY>

<CFQUERY NAME="createselectsp" DATASOURCE="#datasource#">
 #preservesinglequotes(form.createselectsp)#
</CFQUERY>

Listing 3: Escaping double quotes with carats yielded easier to read code.

<CFIF myaction contains ^Delete^>
 <CFQUERY NAME=^ delete^ DATASOURCE=^@application.data-source@^>
  {call del#tablename# (##my#pkey###,'##session.up-dateuser## ') }
 </CFQUERY>

 <CFSET my#pkey# = 0>

 <SCRIPT LANGUAGE=^ JavaScript^>
  alert(^ Record Deleted^)
 </SCRIPT>
</CFIF>