Daryl's ColdFusion Primer

ColdFusion Slow?  Find out why with SeeFusion

Contents


1. Overview and Scope
2. Introduction
3. ColdFusion Foundations
4. Queries and SQL
5. Template Style and Structure (Paranoia 101)
6. Forms Processing
7. Questions and Answers
8. File Library
9. Other Sources
10. Glossary

1. Overview and Scope

This document is designed to give someone who knows at least one programming language a reasonable idea of what ColdFusion is about and how to go about creating robust Web applications using the language. People without prior experience in other languages may also find it useful as more of a reference text, but this will probably not be the only resource needed to start developing applications.

Although I have put a great deal of time and effort into making this document as accurate as possible, mistakes can and do happen. If you choose to use any of the advice in this document, you agree to do so at your own exclusive risk, and to hold the author(s) of this document harmless for any damages or losses incurred by use of the contents. If you don't agree to this, don't read any further.

I tend to repeat myself when expressing points that I consider very important. In this document, please excuse any redundancy you may find in this document. [sic]

*

I draw attention to points I consider to be especially important (or, in some cases, interesting but possibly irrelevant) by placing them in asterisked boxes like this one.

2. Introduction

What is ColdFusion?

Dynamic Web Pages

ColdFusion is a programming language. Like Java, in the sense that it is nothing by itself; it requires a programmer with vision and skill to make anything useful out of it. Like Visual Basic, in the sense that the language is tailored for a specific purpose, but general enough to accomplish most any task easily within that scope. (Visual Basic is designed to make applications for the Windows environment; ColdFusion is designed to make applications for the Web server environment.)

Databases

ColdFusion was originally designed for allowing database contents to be easily displayed in Web pages for any browser, without plugins, by manipulating the HTML output of a Web browser. Since the browser is fed nothing other than standard HTML, no plugins are needed.

More..!

ColdFusion also makes gathering, manipulating, and presenting data from other data sources easy, including other Web servers, FTP servers, ASCII data files, POP mail servers--not even COM and CORBA objects are safe :-)

Programming for the Web

Procedural and OOP Run-Time Environments

Most programmers are used to working with procedural or object-oriented (oop) languages like Pascal, C++, and Java. The "run-time" environments for these languages are similar: you start an application, the application runs, and then the user quits the application. A continuous "state" is created at startup, maintained throughout the operation of the program, and destroyed (or saved) at program shutdown.

The ColdFusion Run-Time Environment

A ColdFusion template (think "program" or "program module") is processed when a request is made to the Web server. Once the template has completely finished processing, the Web server sends the HTML generated by the template. (Not even one byte of output is sent to the browser until the template has completely finished.) During a template's execution span, it may prepare and send several SQL statements to be processed by ODBC database systems, or prepare and send HTTP requests to other Web servers, or do whatever else is necessary to acquire and format data for the end user.

Uh, What Language Again?

Multiple-Language Environment

ColdFusion's primary strength is its ability to actively "glue" systems together. Sometimes dynamically writing SQL statements, sometimes gathering related data from other systems, and sometimes altering or outright writing JavaScript statements to be sent to the client browser, ColdFusion acts as middleman and traffic cop. Because you can be working with four or more programming languages in the same template (I'm counting SQL and HTML as "programming languages" here, though that's being generous,) it's extremely important to remember which language you're working with at any given time, especially since you'll often find ColdFusion expressions or tags embedded in any of them.

ColdFusion

ColdFusion is designed to be embedded in other languages--most notably HTML. It is designed like HTML; most of its major capabilities are accessed through tags. Just as <TABLE> and </TABLE> mark the beginning and end of an HTML table, <CFQUERY> and </CFQUERY> mark the beginning and end of an SQL query in ColdFusion.

SQL

SQL is designed to easily and conversationally retrieve and manipulate data stored in any form of relational database (or manipulate the databases themselves, but I'm not going into that in this Primer.) SQL statements are executed outside of ColdFusion; once the </CFQUERY> tag is reached, the SQL generated within the <CFQUERY> tag is sent to the SQL database engine (Access, Oracle, NonStop SQL, or whatever.) The ColdFusion server waits for all of the results to be returned, then resumes processing of the template.

Someone once asked me which is faster, a ColdFusion database or an Oracle database. The question was nonsensical. (What coffee tastes better: Maxwell House or Mr. Coffee?) I was reminded of the Dilbert comic where Dilbert's boss asked him to build a database. Dilbert was fairly sure his boss didn't understand what he was talking about, so he asked his boss what color he wanted that database. 'I think Mauve has the most RAM...'

*

At the risk of being redundant, ColdFusion is not a database system. ColdFusion is an applications development language tailored toward presenting data retrieved from SQL data sources in HTML format.

JavaScript

JavaScript is designed to make a browser a complete programming environment; unfortunately, due to severe compatibility problems from browser to browser, its practical use seems (in my humble opinion) limited to basic forms automation. Since some people turn JavaScript off in their browsers, or run browsers that don't support JavaScript at all, I try to restrict my JavaScript to non-critical features. On internal systems with a predictable browser set (such as company intranets) I will use more JavaScript.

Java?

About the only things JavaScript and Java have in common are the name, and some basic syntactic elements. I generally try to avoid Java applets in Internet systems; they often take way to long to load, their window size is static, and most of the features you'd use an applet for can be implemented in a simple, standard, HTML-based manner. When graphing is required, however, you will often find me using ColdFusion to write parameters for the KavaChart applets, available at http://www.ve.com/.

When and Where

It's also critically important to keep in mind when and where these languages are executing:

  1. A browser submits a form, possibly triggering JavaScript event handlers.
  2. The web server reads the requested template and passes it off to the ColdFusion Application Server service for processing.
  3. The template is scanned for ColdFusion tags and expressions, which are executed in the order coded.
  4. If any ColdFusion tag is encountered that operates with external data, such as <CFQUERY>...</CFQUERY> or <CFFTP Action="Get">, the external server is accessed and the data retrieved before the next ColdFusion statement or expression is evaluated.
  5. Once the ColdFusion server is finished processing the template, the resulting HTML (and JavaScript, as applicable) is sent to the browser.
  6. The browser reads the HTML and renders the page for the screen, executing any "inline" JavaScript statements embedded in the file.
  7. As the user, uh, uses the forms on the page presented, any applicable JavaScript event handlers are triggered. These event handlers may validate input, alter form contents, or even change the destination of the form, all without bothering the web server.
* It is very easy to forget which language you're coding in from one line to the next. One of the biggest hurdles novice ColdFusion programmers have to overcome is remembering where, and when, code from various languages is run. For example, you cannot change the processing of the ColdFusion portion of a given page using JavaScript, because the JavaScript is not processed until after all of the ColdFusion code has been processed, and the Web server has sent the resulting HTML to the browser.

3. ColdFusion Foundations

Variables

ColdFusion Variable Types

A variable is a named location in memory that holds a value. The "type" of variable refers to the type of data stored at that memory location. Some languages strongly resist converting numbers to strings (text) values and vice-versa; these languages are referred to as "strongly typed." ColdFusion is a "loosely typed" programming language. This means that variables are not (generally speaking) strongly associated with one data type or another; for example, a variable assigned a numeric value can be used as if it were a string variable. The length of text variables need not be defined before use, and I've created string variables several megabytes in length. The basic data types in ColdFusion (not that it's really all that important) are Numeric, String, Date/Time, and Boolean.

There are others data types: arrays can be created; SQL queries, once executed, become a type of array; and structures were added in version 4.0. A List is almost another data type is ColdFusion, but it is actually just a string with some sort of delimiter, usually a comma. Special variables cannot be automatically converted to other types.

A string can only be converted to a numeric if it looks like a number. "45.3" can be converted; "two" cannot be converted. Any number can be converted to a string. The two basic Boolean values are TRUE and FALSE; however YES and NO work well too. Non-zero numbers are considered TRUE; zero is considered FALSE. String values other than true, false, yes, no, or something easily converted to numeric, will cause an error to be displayed.

Variables names are NOT case-sensitive, must start with a letter, and can include letters, numbers, and underscores ("_"). "Customer_Name" is valid; "2Customer" and "Customer Name" are not valid variable names. The value of a variable is displayed by expressing the variable in pound signs, within a CFOUTPUT block:

<CFOUTPUT>Welcome to our online system,
#Customer_Name#!</CFOUTPUT>

All variable names that contain a single value exist in "variable scopes." (More about that in the next section.)

SQL Data Types

SQL (as implemented by ODBC) has three data types: string, numeric, and date/time (or timestamp.) SQL queries are sent to SQL-based databases via the <CFQUERY> tag.

*

There is no such thing as a SQL database. SQL is a query syntax, and has rules about table structure and system tables, but SQL has no provisions for how data is stored. Therefore, database systems can be said to be SQL compliant databases, and SQL may be the only means by which to manage the database, but there is no such thing as an SQL database.

SQL Strings

SQL strings begin and end with a single quote ('). If you want to embed a single quote in a query, you need to type two single quotes. (Programmers generically called this 'escaping' a reserved character.) Therefore, to express the value:

I don't get this.

as an SQL string, you would type:

'I don''t get this.'

Within a CFQUERY, ColdFusion will replace single quotes in #variables# with double quotes automagically; however, ColdFusion will not do single quote escaping for functions. Therefore, any data that may contain single quotes should be manipulated outside of the CFQUERY statement.

Wrong:

<CFQUERY...>
    SELECT * FROM Users
    WHERE UserName = '#ucase(User_Name)#'
</CFQUERY>
  

Right:

<CFSET UserName = ucase(UserName)>
<CFQUERY...>
    SELECT * FROM Users
    WHERE UserName = '#User_Name#'
</CFQUERY>

SQL Numbers

Numbers are provided to SQL without any special consideration. However, ColdFusion programmers must be sure to handle numeric values passed into queries! Otherwise, the supposedly numeric value could be 'hijacked' by a malicious user and used to run other SQL commands. Say a link is created that passes a numeric row identifier "OrderID." OrderID is the primary key for the table "Orders" and is numeric. Although it's tempting to write a query to get the order from the database like this:

SELECT *
FROM Orders
WHERE OrderID = #OrderID#

Someone could easily replace the numeric OrderID (e.g. "5") an the URL line (or alter the form, etc.) with a value followed by a malicious command, such as "http://myserver/MyPage.cfm?OrderID=5+delete+from+orders". The SQL passed to the database system would then look like this:

SELECT *
FROM Orders
WHERE OrderID = 5 delete from orders

Most high-end database systems allow more than one SQL statement per query. Without very conservative database security settings, the above command would delete all orders from the system. You don't want to get that phone call. All numeric values passed by the user should be wrapped by the ColdFusion val() function, which returns the numeric value of the parameter supplied to it, or zero (0) if the value is not numeric. Therefore, val("5") is 5, but val("5 delete from orders") is 0. The code would be properly rewritten like this:

SELECT *
FROM Orders
WHERE OrderID = #val(OrderID)#

SQL Date and Date/Time values

ODBC was designed to provide one consistent application interface to many different DataBase Management Systems (DBMSes). There is a problem with providing one SQL front end to many DBMS back end systems...

*

There is no such thing as a standard format for date and time values in SQL. Therefore, expressing date and time values has traditionally been DBMS implementation-specific. To provide consistency, the designers of ODBC developed a standard format for SQL dates and times, which is translated to the DBMS native format by its ODBC driver.

Date/Time values, in ODBC, are provided in one of the following three formats:

For time (and timestamp) values, the seconds and fractional seconds portion are optional. Many SQL systems do not support fractional second timestamps; if you attempt to use a fractional second value with these systems, strange errors may occur.

An example SQL Insert showing all three data types:

<CFQUERY Name="InsertCustomer" DataSource="CustomerData">
    INSERT INTO Customers (
        CustomerName
		,CustomerAge
		,DateAdded
    ) VALUES (
	    'John''s Bakery'
		,30
		,{ts '1999-11-01 15:54:00'}
	)
</CFQUERY>

Variable Scopes

Overview

ColdFusion organizes variables into "variable scopes." A variable scope can be thought of as a container for variables. When you assign a value to a name without specifying a scope, you are actually modifying (or creating) a variable in the "Variables." scope. The following statements are synonymous:

<CFSET MyVariable = 1>

<CFSET Variables.MyVariable = 1>

The most commonly used variable scopes are (in order of precedence):

[QueryName].

When you create a query using <CFQUERY>, you create a new variable scope with the name specified with the Name attribute of <CFQUERY>. If you use <CFLOOP> or <CFOUTPUT> to loop around the result set of a query, the "current row" of that query becomes the highest-precedence variable scope.

Variables.

Programmer-defined variables (<CFSET>)

CGI.

The Web server's "environment" variables. I commonly use CGI.REMOTE_ADDR (the IP address of the user), CGI.SCRIPT_NAME (the name of the currently running script, from the browser's perspective), and CGI.HTTP_USER_AGENT (the type of browser requesting the page, according to the browser. Useful for creating Netscape/IE versions of JavaScript)

File.

Variables created with <CFFILE>

URL.

Variables passed by HREF or FORM Method="Get"

Form.

Variables passed by FORM Method="POST"

Cookie.

Cookies you've set with <CFCOOKIE>

Client.

Variables created using Client State Management

If you don't explicitly name the scope for the variable you are using, ColdFusion automatically searches through the above scopes, in the order listed.

*

Be sure you explicitly scope variables used within query loops that have the same name as a column in the query. Otherwise, you may use the value from the query instead of the value from the variable.


Comments in Code

HTML comments are delimited by <!-- and -->. HTML comments are sent to the browser, and can be viewed by anyone who chooses "View Source" in their browser:

<!-- This is an HTML comment -->

ColdFusion comments have three (or more) hyphens on each end, and can be nested:

<!--- This is a ColdFusion Comment
<!--- and so is this --->
this is still part of the first CF comment
end of comment --->

ColdFusion comments are source-only; they are not sent to the browser. Nesting of ColdFusion comments can be very confusing for programmers and is not recommended. Any tags or functions found inside ColdFusion comments are not executed; however, ColdFusion code found inside HTML comments is executed. I will occasionally use HTML comments to produce debugging information that is only viewable when I select "View Source" in my browser.

*

I suppose it's theoretically possible to over-comment code, but I've never seen anyone do it.



CFOUTPUT

Perhaps the core ColdFusion tag, CFOUTPUT lets you actually change the document from static HTML to a page that produces dynamic output:

<HTML>
<BODY>
<CFOUTPUT>Your IP address appears to be #CGI.Remote_Addr#</CFOUTPUT>
</BODY>
</HTML>

Output:

Your IP address appears to be 10.1.5.17

In a nutshell [is using that cliché a copyright infringement?], the <CFOUTPUT> tag tells ColdFusion to start examining the template for pound signs ('#'). ColdFusion variable names or expressions are expected to be found within pound signs. If you actually want to produce a pound sign, you must double it. Doubling the pound signs is known as "escaping" them, a generic term used to indicate that certain special characters are to be ignored. The term originates from early UNIX terminals, which used the ASCII ESC character (yes, just like the one on your keyboard) to indicate the start of commands intended for the terminal (e.g., to change text color or position.) Therefore, HTML hex color commands need to have the pound signs doubled, or ColdFusion will generate an ugly error.

Wrong:
<HTML>
<BODY>
<CFOUTPUT>
<CFSET SalePrice = "19.95">
<CFSET SalesTax = "0.065">
<FONT Color="#ccffCC">
The price with tax is #val(round((SalePrice*(1+SalesTax))/100 )*100)#
</FONT>
</CFOUTPUT>
</BODY>
</HTML>
Right:
<HTML>
<BODY>
<CFOUTPUT>
<CFSET SalePrice = "19.95">
<CFSET SalesTax = "0.065">
<FONT Color="##ccffCC">
The price with tax is #val(round((SalePrice*(1+SalesTax))/100 )*100)#
</FONT>
</CFOUTPUT>
</BODY>
</HTML>

Notice the use of the val() and round() functions. Complex calculations can be performed using ColdFusion functions in <CFOUTPUT> sections.

Form and URL Variables

The greatest power of ColdFusion lies in the ability for ColdFusion to take user input and perform dynamic database queries based on that input. Let's start with the basics:

So, let's create a basic form to ask for a name and birth date, then display their age and the day of their next birthday. The first page template we'll write will be called "Birthday1.cfm" and the second will be called "Birthday2.cfm":

Birthday1.cfm
<HTML>
<BODY>
<FORM Action="Birthday2.cfm">
Please enter your name:
<INPUT Type="Text" Name="ffName" Size=50><br>
Please enter your birth date (m/d/yyyy):
<INPUT Type="Text" Name="ffBirthdate" Size=12><br>
<INPUT Type="Submit" Value="Show Next Birthday">
</FORM>
</BODY>
</HTML>
Birthday2.cfm
<HTML>
<BODY>
<CFPARAM Name="ffName" Default="Hey You">
<CFPARAM Name="ffBirthdate" Default="">
<CFOUTPUT>
<CFIF IsDate(ffBirthdate)>
  You were born on #DateFormat(ffBirthdate,"dddd, m/d/yyyy")#<br>
  <!--- find birthday for this year --->
  <CFSET NextBirthday=CreateDate(year(now()),month(ffBirthdate),day(ffBirthdate))>
  <CFIF DateCompare(now(), NextBirthday) IS 1>
    <!--- this year's birthday already passed; add a year --->
    <CFSET NextBirthday = DateAdd("y",1,NextBirthday)>
  </CFIF>
  Your next birthday is #DateFormat(NextBirthday,"dddd, m/d/yyyy")#<br>
  You are #DateDiff("yyyy",now(),ffBirthDate)# years old.
<CFELSE>
  I'm sorry, I don't recognize '<b>#ffBirthdate#</b>' as a date.
</CFIF>
</CFOUTPUT>
</BODY>
</HTML>

Note the use of the <CFIF> tag to first determine if the date entered by the user is recognizable by ColdFusion; then <CFIF> is used to determine if the user's birthday for this year has already passed. There are also numerous ColdFusion functions used in this block of code; I strongly encourage you to read and understand the manual sections for those functions, since they are some of the most used (and most powerful) functions of ColdFusion. I will not take the time to explain most of the ColdFusion functions used in this document.

*

I strongly recommend reading the reference manual cover to cover! Many of the tags and functions will not "click" the first time through. Or, to a lesser extent, the second time through. However, you will vaguely remember what functions and tags are available. The next time you see a problem easily solved by a certain tag or function, even if you don't remember the name of the tag or function offhand, you will remember that there is an easy way to accomplish the task at hand. No one expects you to memorize the manual; however, general knowledge of what capabilities are available through ColdFusion is a must for every professional programmer.

 


4. Queries and SQL

Relational Databases (brief intro)

Most popular database systems are "relational" database systems. The simplest way to think of relational databases is to imagine a bunch of spreadsheets linked to each other, each containing one type of data, organized into rows and columns. Each spreadsheet is called a "table." Tables are linked to one another by matching certain data columns. Each link is called a "relationship", hence the term "relational database."

Think of a table of "Customers"

CustomerID CustomerName CustomerPhone CustomerEmail
1 Bob Smith 913-555-1212 bob@reallyslow.net
2 Sally Jones 612-555-1212 sjones@somecompany.com
3 John Public 816-555-1212 jqp@aol.com

Notice that there is no redundancy in the data; each row contains unique data. Each customer, actually, has been assigned a unique number. The column (or combination of columns) that always finds exactly one row, in this case CustomerID, is called the Primary Key. Although not absolutely required by many database systems, all tables should have a unique primary key. Unique real-world data, such as SSN or phone number, can be used as the primary key. Primary keys created using real-world data are known as "natural keys." If you create a number to represent each row, certain problems inherent in using natural keys (which will become apparent later) go away. Keys created using arbitrarily assigned data (usually numbers) are known as "artificial keys."

*

I strongly recommend using artificial keys whenever possible! Updating data that's part of a natural key becomes very problematic after the relationship has been propagated to many other tables. But remember: once a customer knows what "customer ID" they are, it starts to become a natural key. If, for some reason, someone needs to change an ID (e.g., someone is not happy being customer 666), then that change becomes difficult. One recommendation is to assign a customer number that's not part of any foreign key relationship, then use something that's impossible to remember, such as the result of CreateUUID(), as the artificial key.

Now, let's say that we have a table called "orders" to hold orders placed by customers:

OrderID CustomerID OrderDate WidgetName Qty
1 1 3/12/2000 FlidgyWidget 2
2 1 4/14/2000 WidgetFoo 4
3 2 4/15/2000 CagyWidget 17000.5

Hmmm... with this table structure, we have an artificial key (OrderID) to keep things unique, but what if the customer wants to order more than one item in an order? This table won't handle more than one widget per order. So, let's break this out into two tables, so Bob can order WidgetBars to go with his WidgetFoos in order 2:

Table Orders

OrderID CustomerID OrderDate
1 1 3/12/2000
2 1 4/14/2000
3 2 4/15/2000

Table OrderLineItems

OrderLineItemID OrderID WidgetName Qty
1 1 FlidgyWidget 2
2 2 WidgetFoo 4
3 2 WidgetBar 4
4 3 CagyWidget 17000.5

Great! Now we can have as many line items as we want on each order. Notice how the CustomerID exists in Orders to "relate" the Orders table to the Customers table. Since any customer can have many orders, this example shows a "one-to-many" relationship. The CustomerID column in Orders is considered to be a "foreign key" to Customers: the data represented by the Orders.CustomerID can be found in the Customers table by matching each Orders.CustomerID to the corresponding Customers.CustomerID.

The process of splitting up data into tables so that the database can accurately represent, or model, the real-world, is called database design. Breaking up tables so that there is as little redundancy in the data as possible is called "normalizing" the database. There are various stages of normalization known as "normal forms." If each table cell contains exactly one piece of data, the table is said to have achieved "first normal form." Third normal form seems to balance data integrity and select speed well.

* Database normalization is not an end; normalization is a means to an end. The more normalized a database is, the easier it is to update data in the database. The less normalized a database is, the easier it is to retrieve the data (uh, generally speaking. And remember, all generalizations are false.) Your normalization choices are driven by the way users use your database; if it's almost completely read-only, even violations of first normal form may be acceptable. When in doubt, err on the side of normalization.

The design of the database is one of the most important parts of a database driven application. A well-designed database enables flexibility in applications, and makes systems more robust. A poorly designed database can cripple a project-- and bad database design can make project goals completely unattainable. Interviewing people familiar with the data you are modeling into a database is a step that must not be overlooked. If possible, explain the database design to the users that know the data, and have them help you decide if your data structures are adequate to the task at hand. I generally don't start coding until I have a database built and ready. I'm not suggesting that the database design doesn't (or shouldn't) change after the project has started; but coding shouldn't start until you have a good database design to work against.

SQL

There are four basic operations performed on databases:

  1. Putting data into a table (INSERT)
  2. Viewing data in a table (SELECT)
  3. Changing data already in a table (UPDATE)
  4. Removing data from a table (DELETE)

The most common type of query (all database commands in SQL are called queries) is a SELECT statement, where you retrieve data from one or more tables. The syntax for a SELECT statement is usually fairly simple, and reads almost like English. Lets say I want all the information about customers named "Bob Smith":

SELECT *
FROM Customers
WHERE CustomerName = 'Bob Smith'

SQL is not case-sensitive, nor does it require each clause to begin on a new line; I capitalize all SQL keywords, use mixed case for column names, and break the query into several lines, as a matter of style. When frequently retrieving data from certain columns, it's often a good idea to place an index on those columns. A database index, which is like a book index (but much better), allows the database engine to quickly find relevant rows without having to search the whole database for them. If you find yourself searching by CustomerName often, it is a very good idea to index that column. The tradeoff: indexes consume disk space (though that's not usually a problem), and since indexes must be updated whenever an insert is performed, having many indexes on a table can significantly slow inserts. I've seen the simple addition of an index turn a 20 second response time into a 0.2 second response time. When doing performance tuning on applications, start by examining the WHERE clause of all of your queries. There should be an index on a piece of the WHERE clause that eliminates at least 90% of all rows from consideration before other parts of the WHERE clause are processed. Be very careful with OR conjunctions in where clauses. Most database systems will only use one index per table; therefore, an OR may cause the DBMS to have to read all rows from the table (this is called a "table scan") which can be very inefficient. It's often much more efficient to run two different queries than it is to run one query that has WHERE conditions on two different columns, or'ed together.

CFQUERY

Probably the second most used ColdFusion tag after <CFOUTPUT>, <CFQUERY> allows you to send any database query to a DBMS query engine. The main attributes are DataSource and Name. The name is used to name the result set to facilitate use of the results later in the template. As a matter of style, I preface all queries with the letter "q".

To run the query from the last section:

<CFQUERY Name="qCustomers" DataSource="#DataSource#">
  SELECT *
  FROM Customers
  WHERE CustomerName = 'Bob Smith'
</CFQUERY>

Notice the fact that the value for DataSource is #DataSource#. One feature of ColdFusion is that it looks for and runs a template named application.cfm before running each request. I use application.cfm to check security (when necessary), and to set global variables such as DataSource. Then, I use #DataSource# in all my CFQUERYs. If the ODBC datasource name changes for any reason, I only have to fix it in one place.

After a query is executed, the results are stored in a variable scope with the name you specified. You may not name a query using the same name as a built-in variable scope. (When ColdFusion 3.0 was released, I had to do quite a bit of cleaning up of old code. I had named my security check query "Session" and suddenly "Session" was a reserved word..!) You can determine how many rows were returned by the query by examining the "RecordCount" variable that is added to the scope when the query is created; e.g.:

<CFOUTPUT>
There were #qCustomers.RecordCount# customer(s) found.
</CFOUTPUT>

Although you might expect the RecordCount variable to contain the number of records affected by an INSERT, UPDATE, or DELETE, this is not the case. There is no good, cross-platform way to get that information other than simply running a SELECT beforehand and seeing how many records would be returned by that WHERE clause.

Indexing and Query Performance

When you want to find information on a topic from a reference manual, you will typically look for the topic in the book's index. An index is logically separate from the book--an attachment to the end to facilitate faster lookups of the data contained in the rest of the book. Database information is a bit more structured than a reference book--more like a phone book. A phone book is stored in order by last name and first name. However, if you wanted to find a name, given an address or a phone number, you would wind up reading (on average) half of the phone book to find the name. A database engine would read the whole book every time, in case there is more than one name associated with a phone number. Now, imagine the phone book having multiple indexes at the end, that simply map addresses to names, and phone numbers to names. Suddenly, your lookups become much more efficient. A database table will typically have several indexes on chosen columns or groups of columns. "Which columns to index" is a decision made by the database administrator (DBA) based on the queries he/she expects to be run at the database. If you are a developer in a smaller shop, you may take on many of the roles of a DBA for projects, including determining table structures and determining which columns to include in and index or indexes.

Since an index must be updated every time any data in the index in changed (by an insert, update, or delete), indexes will slow those operations somewhat. However, a useful index can have a dramatic effect on the speed and efficiency of a select. DBMSes are usually comprised of two primary components: the query optimizer and the execution engine. The role of the query optimizer is to look at queries presented to the database, and decide which index(es) will get the data with the least cost to the execution engine. The most "expensive" operation for a database to perform is a physical I/O, where a chunk of data is physically retrieved from or written to a hard drive. If the query optimizer cannot find a useful index for a select statement, it will tell the execution engine to read the entire table, which can be very time consuming in terms of physical I/O and record locking. (Record locking is used to prevent someone from changing data while you're using it. Or, to prevent you from using data someone else is changing.) Reading an entire table to service a query is known as a table scan and should be avoided in most cases. Most DBMSes will allow you to ask the query optimizer what indexes it will use (if any) based on a given query. This is referred to as "getting a query plan." Indexes are chosen by comparing the indexes available to the columns referenced in the "where" clause of the query. Therefore, care should be taken to make sure queries reference at least one column in an index in their "where" clauses. If no index is available, strongly consider adding one to help the query perform adequately. When in doubt, get a query plan from the DBMS, and decide from there if you can either:

The Query Object: Floor Wax or Dessert Topping?

With apologies to Saturday Night Live

Now, for a limited time, you too can be the proud owner of a query object! What, you ask, is a query object? A query object is the data structure created when you run a query using <CFQUERY>.

Features:

Creating a query obect

A query object is created every time you run a query, and is conveniently named using the NAME attribute of <CFQUERY>. Then, you can simply use the QUERY attribute of <CFOUTPUT> to display the results of the query! For example, let's assume you want to produce a sorted list all of the users in the Users table. With just seven lines of code, it's as good as done!

<CFQUERY Name="qUsers" Datasource="#request.dsn#">
SELECT LastName, FirstName FROM Users
ORDER BY LastName, FirstName
</CFQUERY>

<CFOUTPUT QUERY="qUsers">
#qUsers.FirstName# #qUsers.LastName#<BR>
</CFOUTPUT>

This query might produce output like this:

Jane Doe
John Doe
Fred Flintstone
Jane Jetson
Marge Simpson

You can also group the output by using the Group attribute and nesting <CFOUTPUT> tags!

<CFOUTPUT Query="qUsers" Group="LastName">
The #LastName# Family: <CFOUTPUT>#FirstName# </CFOUTPUT><BR>
</CFOUTPUT>

The Doe Family: Jane John
The Flintstone Family: Fred
The Jetson Family: Jane
The Simpson Family: Marge

* Every time the value in the column indicated by the Group attribute changes, the outer loop is re-run. You can also create more than two levels of nesting by using <CFOUTPUT Group="foo"> mulitple times. The Query attribute is only used on the outermost CFOUTPUT. Unfortunately, <CFLOOP> does not have a Group attribute.

Works like an array!

You can also get at the contents of rows without looping, simply by specifying the column name and row number! In the example above, the value of #qUsers.LastName[3]# is "Flintstone".

* If you don't specify a row number, you always get the first row of the query.

Works like a structure!

You can even use structure syntax to access columns of a query. In the example above, #qUsers["LastName"][4]# will produce "Jetson"

* However, attempts to use functions that expect structures, such as structKeyList(qUsers) or <CFLOOP Collection="qUsers"> will not work.

Additional properties available if you call now!

That's right, if you call now, we will include, absolutely free, these additional properties with every query created!

* If you're not in a loop, the value of MyQuery.CurrentRow is always 1.

Extra, Bonus Property Available for a Limited Time!

Operators are standing by, call now!

Tastes great! And just look at that shine!

* (No operators are standing by.)

5. Template Style and Structure (Paranoia 101)

Overview

Forms processing is one of the most understudied arts of ColdFusion programming. In the HTML world, you create a form with one or more input elements; the user puts random trash into the input elements (best case), and a second ColdFusion template tries to make sense of the user input and do something useful with it. Most production ColdFusion errors are generated by success-mode processing of form variables. [Success-mode: If all inputs are exactly what the programmer expected, the program works. Otherwise, it crashes. Antonyms: Failure-mode, Microsoft. The key to creating secure, reliable, bullet-proof ColdFusion code is to take a little bit of extra time at the top of any page that accepts user parameters and "scrub" all your parameter variables.

Elements of Style

The following are some procedures I use to keep myself and my programs sane. (Well, it works for my programs, anyway.) They may seem like unnecessary work, until you return to a page weeks or months later to do maintenance. These are rules of thumb, proven by their many exceptions. :-) As usual, your mileage may vary.

Scrubbing Variables

"Scrubbing Variables" refers to the process of checking every variable that is passed as a parameter to your form, to make sure the data is what you expect. All parameters should be checked; if the value is not an expected value, cannot be "repaired," or should not be ignored (reset to a default value), then you should throw a useful (or at least semi-useful) error message and <CFABORT> processing. I use a short custom tag I call <CF_UserError>. Custom Tags are like procedures in other programming languages; they have their own local variable scope, and they accept parameters, but they don't directly return a value the way functions do. Tag attributes that are passed to a custom tag are defined in the "attributes." variable scope, and the caller's variables are available on a read/write basis through the "caller." variable scope. <CF_UserError> has one parameter, called Message, and is used in the form:

<CF_UserError Message="Oh no! I've fallen and I can't get up!">

Look for the Attributes.Message parameter in the following code snippet:

<CFSETTING EnableCFOutputOnly = "No">
<CFSETTING EnableCFOutputOnly = "No">
<!-- begin cf_usererror.cfm -->
<!--- 
Purpose: Crash and Burn.
Close up any outstanding tables (so Netscape will show the error), display error, then abort.

Required Parameters:
Message:  The message to display to the user.
--->
<CFPARAM Name="Attributes.Message" Default="An unspecified error has occurred.">
</TD></TR></TABLE>
</TD></TR></TABLE>
</TD></TR></TABLE>
</TD></TR></TABLE>
</TD></TR></TABLE>
</TD></TR></TABLE>
</CENTER>
<BLOCKQUOTE>
<i>There was a problem processing your request:</i><br>
<br>
<CFOUTPUT><b>#Attributes.Message#</b></CFOUTPUT><br>
<br>
Please press the 
<A HREF="javascript: history.back();">Back</A>
Button on your browser and double-check your input.
</BLOCKQUOTE>
<!-- end cf_usererror.cfm -->
<CFABORT>

[Click Here] to download this tag.

This custom tag is placed in my "CFUSION\Custom Tags" directory, where it is available to all applications on my server.

Use CFPARAM for ALL parameters passed to your page. If you use the Type attribute of CFPARAM, it's also a good idea to create a <CFERROR Type="Validation"> template.

There are four main types of data processed by most applications: Text, Numeric, Date, and Boolean (aka Yes/No). Each requires a different method of scrubbing.

Text Data

There is very little, in most cases, that can or should be done to text input. I usually use trim() to remove any whitespace on either side of the string: <CFPARAM Name="ffSomeText" Default="">

<CFSET ffSomeText = trim(ffSomeText)>

Note that there is a bug feature in some versions of the Microsoft Access ODBC driver that allows commands to be embedded inside string literals (i.e., text data) that run in the context of the current user, which, for most ColdFusion installations, is LocalSystem (See Allaire Security Bulletin ASB99-09 [http://www.allaire.com/security/].) This is a Very Bad Thing. If you MUST use ODBC drivers earlier than the drivers included in Microsoft's Data Access Components 2.1, then use this snippet:

<CFPARAM Name="ffSomeText" Default="">
<CFSET ffSomeText = replace(trim(ffSomeText),"|","","ALL")>

This will remove all vertical bars (aka "pipes") from submitted text.

Numeric Data

Numeric data should always, always, always be converted to a number before being used in a query. The ColdFusion val() function converts anything that looks like a number into a number; everything else is converted to 0 (zero.) If you don't val()idate numbers this way, malicious users can insert unwanted SQL into queries, which can have some catastrophically bad results. For example, if your query was:

SELECT * FROM Widgets
WHERE WidgetID = #ffWidgetID#

and someone passed the value "5 delete from widgets" as ffWidgetID, then database systems that allow multiple statements per query would dutifully delete everything from Widgets, since the statement would be expanded to read:

SELECT * FROM Widgets
WHERE WidgetID = 5 delete from widgets

If you pass ffWidgetID though a val() filter (e.g. <CFSET ffWidgetID = val(ffWidgetID)>) then the statement would be sent to the SQL server as:

SELECT * FROM Widgets
WHERE WidgetID = 0

which is harmless. See Allaire Security Bulletin ASB99-04 [http://www.allaire.com/security/] for more information.

So, we begin by using CFPARAM to ensure the variable is defined:

<CFPARAM Name="ffSomeNumber" Default="">

If your database allows nulls for the field, then use this to convert non-numerics to NULL:

<CFIF isNumeric(ffSomeNumber)>
  <CFSET ffSomeNumber=val(ffSomeNumber)>
<CFELSE>
  <CFSET ffSomeNumber="NULL">
</CFIF>

If your database disallows nulls for the field, then just simply use val():

<CFSET ffSomeNumber=val(ffSomeNumber)>
*

A database null is often confused with a null string. A null string is a string with a length of zero-- but it is a known value. For example, the second line of my address is a null string; we know what the value is, and the value is blank. A database null, what I like to call a "null value" (to differentiate it from a "null string") represents an unknown value. Databases work with nulls in interesting ways. For starters, nothing is equal to null-- and nothing is not equal to null, either. Any comparison to the null value is false, so SQL has a special set of keywords for dealing with nulls: IS NULL and IS NOT NULL. However, when a database returns a null value to ColdFusion, ColdFusion represents that value as a null string. So, if you want to find all people listed in a "users" table that have empty or missing middle names, the SQL would look like this:

SELECT * FROM Users
WHERE UserMiddleName = ''
   OR UserMiddleName IS NULL

Some DBMSes will convert the expression "WHERE foo = NULL" into "WHERE foo IS NULL", but don't expect that to work. There are many times that I've spent extended periods of time trying to figure out why a query that does "= NULL" fails, before I remember that I need to use "IS NULL" instead.

Another note of caution: I've noticed instances where some DBMSes will automatically convert null strings to null values on insert or update. I think the difference between a null string and a null value is subtle, but very important, and would personally recommend disabling that behavior, as a rule.

Date/Time Data

Some data is date only, some is date/time. I rarely see time-only fields, but I hear they're out there. In any case, first ensure the value is defined:

<CFPARAM Name="ffSomeDate" Default="">

If your database allows nulls for the field:

<CFIF (ffSomeDate IS "") OR (NOT IsDate(ffSomeDate))>
  <CFSET ffSomeDate="NULL">
<CFELSE>
  <CFSET ffSomeDate=CreateODBCDateTime(ffSomeDate)>
</CFIF>

If your database disallows nulls for the field:

<CFIF (ffSomeDate IS "") OR (NOT IsDate(ffSomeDate))>
  <CF_UserError Message="I'm sorry, but I don't recognize '#ffSomeDate#' as a valid date/time entry." &
    "  Try using a format like '1/31/2000 3:45 pm'.">
<CFELSE>
  <CFSET ffSomeDate=CreateODBCDateTime(ffSomeDate)>
</CFIF>

Now, many date fields are used as "Start Date--End Date" pairs. Users will enter an end date as "1/31/2000"; however, if the database fields you're comparing on is actually a date/time field, you will ignore all rows created after midnight of the end date..! If no time is specified for a date, ODBC databases will consider the date value to be date/time value of 12:00 am on that date. For end dates used as report criteria, consider using this snippet:

<CFIF (ffEndDate IS "") OR (NOT IsDate(ffEndDate))>
  <CFSET ffEndDate="NULL">
<CFELSEIF ffEndDate CONTAINS ":">
  <!--- if there's a colon, then the time was specified; use as is --->
  <CFSET ffEndDate=CreateODBCDateTime(ffEndDate)>
<CFELSE>
  <!--- no time specified, so fix datetime value to last second of day --->
  <CFSET ffEndDate=CreateODBCDateTime(DateFormat(ffEndDate) & " 23:59:59")>
</CFIF>

Yes/No or Boolean fields

"Yes/No" [MS Access] or "Bit" [MS SQL Server] fields are often presented to the user as an HTML checkbox. An interesting behavior of checkboxes is that they will POST the value given in the <INPUT Type="Checkbox"> tag when checked, and will leave the variable undefined if the checkbox is not checked. Therefore, the safest and most reliable simple checkbox handling I've found is this:

<CFPARAM Name="ffSomeCheckbox" Default="0">
<CFIF val(ffSomeCheckbox)>
  <CFSET ffSomeCheckbox = 1>
<CFELSE>
  <CFSET ffSomeCheckbox = 0>
</CFIF>

If you're using a text input and expecting "Yes" to indicate true, this will work:

<CFPARAM Name="ffSomeYesNo" Default="">
<CFIF "Yes" CONTAINS ffSomeYesNo>  <!--- accepts "Y" or "Yes" --->
  <CFSET ffSomeYesNo = 1>
<CFELSE>
  <CFSET ffSomeYesNo = 0>
</CFIF>

Aren't you just a bit paranoid?

No, but who told you so--and why??

Seriously, code resilience is the hallmark of the professional programmer. These techniques will help you build secure Web applications that don't fall down, no matter what is thrown at them--a feature your clients (whoever they are) will appreciate (and come back to appreciate again.) It's been said that a good programmer looks both ways before crossing a one-way street.

Template Structure

I find maintenance easiest when I structure templates in (usually) three sections: Variable Initialization, Query Section, and the Display Section:

  1. Variable Initialization: Include a brief comment describing the page's function and indicating which other pages link to it, and scrub all your incoming variables. Commenting the purpose of non-obvious variables is also good style.
  2. Query Section: Try to run all of your queries, and as much business logic as possible, in this section. This makes finding queries and business logic easy. Also, I include the Variable Initialization and Query Sections in <CFSETTING EnableCFOutputOnly="Yes"> to prevent a bunch of whitespace from appearing at the top of the source. If your business logic includes significant looping, this can be an big savings in generated HTML document size.
  3. If I want certain parameter defaults to be defined by a query, I add a second Variable Initialization section here.
  4. Display Section: This is where you display the results of your queries to the user. It is sometimes necessary to place queries in the Display Section, particularly when you need to run a second query for detail on each row a first query returned.

Example Template:

<CFINCLUDE Template="header.cfm">
<!-- begin UserSearchResults.cfm -->
<CFSETTING EnableCFOutputOnly="YES">
<!----------------------------------->
<!---   Variable Initialization   --->
<!----------------------------------->
<!--- This template will search for any users LIKE ffUserName --->
<!--- and list the results to the user, with links to UserDetail.cfm --->
<!--- Called by UserSearch.cfm --->
  
<CFPARAM Name="ffUserName" Default="">
<CFSET ffUserName = trim(ffUserName)>
<CFIF NOT len(ffUserName)>
<!--- ffUserName is a zero
  len()gth string; abort with message --->
  <CF_UserError Message="You must type some part of the name you're looking for.">
</CFIF>

<!-------------------------->
<!---��  Query Section   --->
<!-------------------------->

<!--- use SQL "LIKE" operator to find any username that contains ffUserName --->
<!--- the percent sign (%) is the LIKE operator's "wildcard" (it matches any value) --->
<!--- note that this query would be quite slow on a large Users table --->
<CFQUERY Name="qUsers" DataSource="#DSN#">
  SELECT Users.*, Departments.DepartmentName
  FROM Users, Departments
  WHERE Users.DepartmentID = Departments.DepartmentID
    AND UserName LIKE '%#ffUsername#%'
  ORDER BY UserName
</CFQUERY>

<CFSETTING EnableCFOutputOnly="NO">
<!--------------------------->
<!---   Display Section   --->
<!--------------------------->

<H3>Results of User Search</H3>
<TABLE>
<TR>
  <TH bgColor="eeeeee" Colspan=2>
    <CFOUTPUT>#qUsers.RecordCount#</CFOUTPUT> Users Found:
  </TH>
</TR>
<TR>
  <TH bgColor="eeeeee">User</TH>
  <TH bgColor="eeeeee">Department</TH>
</TR>
<CFOUTPUT Query="qUsers">
<TR>
  <TD>
    <A HREF="UserDetail.cfm?ffUserID=#UserID#">#UserName#</A>
  </TD>
  <TD>#DepartmentName#</TD>
</TR>
</CFOUTPUT>
</TABLE>
<!-- end UserSearchResults.cfm -->
<CFINCLUDE Template="footer.cfm">

6. Forms Processing

Text Inputs

(Coming Soon?)

Fun with Checkboxes

Overview

Checkboxes are simple user interface elements; the user clicks on a box to "check" it, and clicks again to uncheck. Perfect for those yes/no fields, and also useful for more complex expressions of data, such as the ability to select several of the same type of data, as in "Check the products you are interested in."

The checkbox input element has four attributes: Type must be "Checkbox", a Name must be assigned to the checkbox, and a Value must be assigned to the checkbox. Optionally, the keyword "Checked" can be added to indicate that the checkbox should be pre-checked when the form is initially displayed. Example:

<INPUT Type="Checkbox" Name="ffMyCheckbox" Value=1 Checked>

There are three possible results posted by checkboxes:

  1. If no checkboxes with a given Name are checked, the Name will be undefined in the form handler. (See "Yes/No or Bit fields" under Template Style and Structure.)
  2. If one checkbox is checked for a given Name, the Name will be defined and hold the value specified by the Input's Value attribute.
  3. If multiple checkboxes exist with the same Name attribute, and more than one is checked, and the Form's Method is "Post", then ColdFusion will define a variable with a name matching the checkbox set's Name, and place a comma-delimited list of values in that variable. (If fewer than two checkboxes with the same Name attribute are checked, then rules 1 and 2 apply.)

Example 1: Simple Checkbox

This example asks the user for an e-mail address, and prompts the user to check a box if they don't want new product notifications.

The input form:

<FORM Action="FormHandler.cfm" Method="Post">
Please enter your e-mail address:
<INPUT Type="Text" Name="ffEmail" Size="30"
  Maxlength="100"><br>
<INPUT Type="Checkbox" Name="ffNoMailings" Value=1>I
  don't want to receive updates via e-mail.<br>
<INPUT Type="Submit" Value=" OK ">
</FORM>

The Form handler:

<!----------------------------------->
<!---   Variable Initialization   --->
<!----------------------------------->
<CFPARAM Name="ffEmail" Default="">
<CFIF ffEmail DOES NOT CONTAIN "@">
  <CF_UserError Message="The e-mail address you entered (#ffEmail#) " &
    "does not appear to be a valid e-mail address.">
</CFIF>
<CFPARAM Name="ffNoMailings" Default=0>
<CFIF val(ffNoMailings)>
  <CFSET ffNoMailings = 1>
<CFELSE>
  <CFSET ffNoMailings = 0>
</CFIF>

<!------------------------->
<!---   Query Section   --->
<!------------------------->
<CFQUERY Name="qCheckForEmail" DataSource="#DSN#">
  SELECT *
  FROM MailingList
  WHERE Email = '#ffEmail#'
</CFQUERY>
<CFIF qCheckForEmail.RecordCount>
  <!--- user already exists, update --->
  <CFQUERY Name="qUpdateEmail" DataSource="#DSN#">
    UPDATE MailingList
    SET NoMailings = #ffNoMailings#
    WHERE Email = '#ffEmail#'
  </CFQUERY>
<CFELSE>
  <!--- user not in database, insert --->
  <CFQUERY Name="qInsertEmail" DataSource="#DSN#">
    INSERT INTO MailingList (
       Email
      ,NoMailings
    ) VALUES (
      '#ffEmail#'
      ,#ffNoMailings#
    )
  </CFQUERY>
</CFIF>

Example 2: Checkboxes for Multiple Values

This example asks the user for an e-mail address, and prompts the user to check boxes for each product they want information on. The user's selections will then be mailed to sales@mycompany.com.

The input form:

<!------------------------->
<!---   Query Section   --->
<!------------------------->
<CFQUERY Name="qProducts" DataSource="#DSN#">
  SELECT ProductName
  FROM Products
</CFQUERY>

<!--------------------------->
<!---   Display Section   --->
<!--------------------------->
<FORM Action="FormHandler.cfm" Method="Post">
Please enter your Name:
<INPUT Type="Text" Name="ffName" Size="30" Maxlength="100"><br>
Please enter your e-mail address:
<INPUT Type="Text" Name="ffEmail" Size="30" Maxlength="100"><br>
Please check all products you want more information on:<br>
<CFOUTPUT Query="qProducts">
  <INPUT Type="Checkbox" Name="ffProductName" 
    Value="#qProducts.ProductName#">#qProducts.ProductName#<br>
</CFOUTPUT>
<INPUT Type="Submit" Value=" OK ">
</FORM>

The Form handler:

<!----------------------------------->
<!---   Variable Initialization   --->
<!----------------------------------->
<CFPARAM Name="ffName" Default="">
<CFIF trim(ffName) IS "">
  <CF_UserError Message="Please enter your name. Lie if you must.">
</CFIF>
<CFPARAM Name="ffEmail" Default="">
<CFIF ffEmail DOES NOT CONTAIN "@">
  <CF_UserError Message="The e-mail address you entered (#ffEmail#) does not appear to be a valid e-mail address.">
</CFIF>
<CFPARAM Name="ffProductName" Default="">
<CFIF trim(ffProductName) IS "">
  <CF_UserError Message="Please select at least one product to receive information on.">
</CFIF>

<!------------------------->
<!---   Query Section   --->
<!------------------------->
<CFMAIL From="#ffEmail#" To="sales@mycompany.com"
   Subject="[Web Page] Contact #ffName#">
"#ffName#" visited our website on #DateFormat(now(),"m/d/yyyy")# #TimeFormat(now(),"h:mm:tt")#.
The e-mail address they entered was "#ffEmail#".
They were looking for information on the following products:
#ffProductName#

Their IP address was #CGI.REMOTE_ADDR#.
</CFMAIL>

Example 3: Checkboxes for Multiple Values and a Database

Let's say the sales department is really happy with your work from examples 1 and 2 (the fact that they were just examples can remain our little secret.) Now they want the mailing list to be somehow tied to the products table, so they can send mailings about products to the people who were interested in the products. So, in addition to the MailingList table from example 1, and the Products table in example 2, we'll create a cross reference table called "MailingListProducts" which will have the fields "Email" and "ProductName".

The input form will remain the same, but the form handler will change a bit:

<!----------------------------------->
<!---   Variable Initialization   --->
<!----------------------------------->
<CFPARAM Name="ffName" Default="">
<CFIF trim(ffName) IS "">
  <CF_UserError Message="Please enter your name. Lie if you must.">
</CFIF>
<CFPARAM Name="ffEmail" Default="">
<CFIF ffEmail DOES NOT CONTAIN "@">
  <CF_UserError Message="The e-mail address you entered (#ffEmail#) " & 
    "does not appear to be a valid e-mail address.">
</CFIF>
<CFPARAM Name="ffProductName" Default="">
<CFIF trim(ffProductName) IS "">
  <CF_UserError Message="Please select at least one product to receive information on.">
</CFIF>

<!------------------------->
<!---   Query Section   --->
<!------------------------->
<CFMAIL From="#ffEmail#" To="sales@mycompany.com" 
  Subject="[Web Page] Contact #ffName#">
"#ffName#" visited our website on #DateFormat(now(),"m/d/yyyy")# #TimeFormat(now(),"h:mm:tt")#.
The e-mail address they entered was "#ffEmail#".
They were looking for information on the following products:
#ffProductName#

Their IP address was #CGI.REMOTE_ADDR#.
</CFMAIL>

<CFQUERY Name="qCheckForEmail" DataSource="#DSN#">
  SELECT *
  FROM MailingList
  WHERE Email = '#ffEmail#'
</CFQUERY>
<CFIF qCheckForEmail.RecordCount>
  <!--- user already exists, update --->
  <CFQUERY Name="qUpdateEmail" DataSource="#DSN#">
    UPDATE MailingList
    SET Name = #ffName#
    WHERE Email = '#ffEmail#'
  </CFQUERY>
<CFELSE>
  <!--- user not in database, insert --->
  <CFQUERY Name="qInsertEmail" DataSource="#DSN#">
    INSERT INTO MailingList (
      Email
     ,Name
    ) VALUES (
     '#ffEmail#'
    ,'#ffName#'
    )
  </CFQUERY>
</CFIF>

<!--- now update the user-product xref --->
<CFTRANSACTION>
<CFQUERY Name="DelOldXref" DataSource="#DSN#">
  DELETE FROM MailingListProducts
  WHERE Email = '#ffEmail#'
</CFQUERY>
<!--- we could, at this point, loop through each list item in ffProductName and perform an insert; --->
<!--- however, we'll use insert/select instead.  This has two benefits: --->
<!--- (1) we only run one query --->
<!--- (2) the subquery validates each productname to make sure it's in the --->
<!---     products table, which ensures full data integrity --->

<!--- Change ffProductNames into a quoted, comma-delimited list --->
<CFSET ProductNamesQVL = "'" & replace(replace(ffProductNames,"'","''","ALL"),",","','","ALL") & "'">
<CFQUERY Name="InsNewXref" DataSource="#DSN#">
  INSERT INTO MailingListProducts (Email, ProductName)
  SELECT '#ffEmail#', ProductName
  FROM Products
  WHERE ProductName IN (#PreserveSingleQuotes(ProductNamesQVL)#)
</CFQUERY>
</CFTRANSACTION>

Radio Buttons

Coming Soon?

Select Lists

Coming Soon?


7. Questions and Answers

The following questions were posed to me by some means: usually directly, or to some mailing list I lurk on. Last names and/or Internet addresses have been changed to preserve anonymity of posters.

Q: I have a Pentium III with 256 Meg ram and a brand newly installed Windows NT Server software loaded � operating system and all. I am using Microsoft IIS as well. I can get the web browser to work through localhost. However, when I try to access CF Administrator I have two problems.

a. CF Administrator wants to use the 127.0.0.1 path which apparently is not working properly.

b. The [ColdFusion Administrator] will not start.

These problems are probably interrelated but need suggestions on what to look at under the hood as it were. It seems that 127.0.0.1 is not working and it should.

A: Please understand that this is an IIS problem, not a ColdFusion problem. If IIS is not configured correctly, then CF will not behave well, since it /depends/ on the Web server process (be it IIS, Apache, Netscape Web Server, or any other) to properly forward requests to the ColdFusion server service. Before you ask, that's a "feature", since it allows ColdFusion to coexist on the server with other Web server technologies, such as Java, Perl, or even...ASP.

While you have a physical Web server computer with a physical NIC at a given IP address, for example 192.168.1.1, it's important to understand that there is always another "virtual" NIC, known as the "loopback adapter", at the IP address 127.0.0.1, with a "HOSTS" file alias of "localhost". This is not a Windows NT invention, but rather a standard (and required) property of any TCP/IP protocol stack. Windows, however, does the unusual [dis]service of hiding the virtual NIC when you type "ipconfig" at the command prompt. If you type "/sbin/ifconfig" at a RedHat Linux command prompt (location of ifconfig may vary with other Linuxes/Unixes), you will see the loopback adapter, with the IP address of 127.0.0.1.

Traditionally, multiple Web sites are hosted on a given Web server by assigning several (or many) IP addresses to one physical NIC. Then, the Web server software is configured to point requests arriving at a given IP address to the file system directory that contains the root of the site: by default, IIS associates "(All Unassigned)" IP addresses to the \Inetpub\wwwroot\ directory on the drive. What you do from there is what can "break" ColdFusion.

Web browsers will typically send the name of the server they are trying to connect to-- whatever name the user typed in. This is accessible in ColdFusion as CGI.HTTP_HOST. (All of the CGI variables that begin with "HTTP_" were sent as "request headers" by the user's browser. The Web server software then prepends "HTTP_" before making the values available.) Since virtually all browsers now send the HTTP_HOST name it was connecting to, some ISPs use that information to determine which Web site is presented to a user. This is known as using "host header" information to multiplex Web sites on a single IP address.

If you are using "host headers" under [IIS Web Site Properties] / [Web Site] / [Advanced] / [Add] / [Host Header Name] to run multiple virtual Web servers on a given "real" IP address (in this case 192.168.1.1), you should also include a mapping for the "administrative" web site that contains the /cfide/administrator directory so that it responds to both the "localhost" host header and its corresponding IP address, 127.0.0.1.


Q: I am having some strange things happen with CFMAIL - I setup a system for our HR department to upload and email new vacancy bulletins. The CFMAIL goes through a database, and sends an email, with a file attachment.

The problem is, our email system properly recognizes the From: part of the message.. my main home email server, and hotmail receive it as Sender Unspecified � while another server I have an account on correctly receives the From:

Any Idea what might be causing this? I am using our FireWall as an SMTP server since the GroupWise SMTP agent would not accept connections.

A: One oft-overlooked smart-relay solution is to install the Microsoft SMTP server service that is part of Option Pack 4. Set up the SMTP service to relay mail for 127.0.0.1 (buried somewhere in MMC), then configure ColdFusion to send mail to 127.0.0.1 (in the usual place in CF Administrator). This requires that the ColdFusion server has access to the DNS of the real world, and that the server can make outbound connections through the firewall to port 25 on remote systems (neither of which is usually a problem.) To test this:

DNS test:

nslookup
> set type=mx
> hotmail.com
[a list of servers should appear]

> exit
SMTP test:
telnet  mail.hotmail.com  25
You should see:

220-HotMail (NO UCE) ESMTP server ready at Wed, 31 May 2000 20:45:27 +1700
220 ESMTP spoken here

I have used this solution before with no problems (once I found the right place in MMC to allow SMTP relaying.)


Q: I decided that today I would learn cookies - but they are kicking my butt all over the place!

I have a simple Cookie thingy setup just as an easy example (to see if I could get them to work, mostly)

[URL Removed]

that form asks for a username and password, the action on that form goes to cookie1.cfm which has this as the source:

<cfcookie name="logintest" value="Logged In">
<cfcookie name="login" value="#FORM.uname#">
<cfcookie name="pass" value="#FORM.password#">
<cflocation url="cookie2.cfm">
Cookie2.cfm has this in the body:
<CFOUTPUT>
<cfif IsDefined("Cookie.Login") IS "True">
#Cookie.login#
<cfelse>
No cookie!
</cfif>
</CFOUTPUT>

And all I get is, "No Cookie!" if I take out the <cfif> statements, it tells me cookie.uname does not exist.

What am I doing wrong??

A: Simple:

A page has to load in order for cookies to be added to the browser. <CFLOCATION> preempts the page content, so no cookies are set.

You can't use <CFCOOKIE> and <CFLOCATION> on the same page. However, you can set a cookie then do a redirect by using CFCOOKIE as normal, then using the following code to redirect to (for this example) logonFailed.cfm:

<cfheader name="location"
    value = "logonFailed.cfm?ffMessage=#urlEncodedFormat("Invalid password for #ffUserName#")#">
<cfheader statusCode = "302" statusText = "Document Moved, Dude.">

Q: I'm having problems getting certain documents using CFHTTP. Are there any alternatives?

A: Leverage ColdFusion's excellent Java integration by implementing a Java class for this purpose:

  1. Download the HTTPClient class files (.zip) from http://www.innovation.ch/java/HTTPClient/
  2. Extract to C:\jdk1.3\jre\lib\HTTPClient (your jdk dir may vary, adjust as needed)
  3. Add C:\jdk1.3\jre\lib\ as "Class Path" in CF Admin
  4. Run following code:
<cfobject action = "Create" type = "Java" 
    class = "HTTPClient.HTTPConnection" name = "httpConn"> 
<cfscript> 
httpConn.init("www.cfprimer.com"); 
response=httpConn.Get("/"); 
content=response.getText(); 
</cfscript> 
<cfoutput> 
#content# 
</cfoutput> 
For another example of Java integration (and more depth), see my DevCenter article at http://www.allaire.com/handlers/index.cfm?ID=22250 .

8. File Library

Abandon lawyers, all ye who enter here!

Here is a collection of tags I wrote (or, with cf_superCache, helped write). Some are LGPL (meaning you can use and distribute the tags with your app, so long as you don't encrypt the source of the custom tag if you distribute it), others are simply free for use/abuse to anyone that has a valid ColdFusion license. Before you may use any of this code, however, you must agree to hold the author(s) harmless for any damages incurred through the use of the code. Use the code here at your own exclusive risk!

That being said, these work pretty well for me, or I wouldn't post them. Please notify me with any bug reports, feature suggestions, or improvements.
General-Purpose ColdFusion
<cf_supercache>
[Download]
High-speed in-memory cache written by several (former) members of Allaire Consulting, including myself.
<cf_xmlParser>
[Download]
[Example]
A set of CF5 functions that will parse simple xml files into ColdFusion structures. A pure ColdFusion XML parser!
<cf_userError>
[Download]
Displays an error message to the user and stops template processing. I prefer this over <cfabort showerror="..."> because cfabort will write the error to the application.log, which I prefer to hold "real" errors.
<cf_troubleshoot>
[Download]
Logs entry and exit (as well as timing) of suspect blocks of code. Useful for troubleshooting suspect 3rd-party calls, such as COM objects or CFX tags. Currently only works with SQL Server datasources. Anyone want/need an Oracle version? Let me know!
<cf_captureOutput>
[Download]
One-line tag to capture output into a variable in CF4.5, similar to the <cfsavecontent> tag included with CF5.
_parameterizeQueries.cfm
[Download]
This will read all CF files in the current directory, and optionally all subdirectories, and give you the option to automagically change variables into <cfqueryparam>-based query parameters. Use with caution! And don't leave it lying around in any publically-accessible directories. If you don't understand why you'd do this, see [my DevCenter article.]
pictures.cfm
[Download]
[Example]
Creates and displays thumbnails of all images in the current directory, and [optionally] all subdirectories. Click on thumbnail for full image. Note the extra work put into munging the directory to prevent abuse. Requires Jukka Manner's cfx_Image tag, available at http://www.kolumbus.fi/jukka.manner/. Demo (2002 Ice Storm) at http://www.darylb.net/pictures/.
Spectra
<cf_structSortLCommonSubkeys>
[Download]
A Spectra tag pair that will sort a structure of objects by multiple values. An interesting example of using recursion with CF custom tags.
Recursive (adj.): see Recursive.
typeDump.cfm
[Download]
A Spectra page that will display the properties and methods of all object types.
objectFixer.cfm
[Download]
A Spectra page that will attempt to "repair" all objects of a given type. Useful for adding properties to all objects of a recently-modified object type.

10. Other Sources

Bookmarkable Pages at Allaire Macromedia

Allaire Knowledge Base:
my "home" page.
http://www.allaire.com/support/KnowledgeBase/SearchForm.cfm
Support Forums:
Get help from peers and Product Support.
http://www.allaire.com/support/forums/
Reference Desk:
The best-kept secret source of useful info on a variety of CF-related topics.
http://www.allaire.com/developer/technologyreference/columnsarticlesarchive.cfm
Tag Gallery:
Many custom tags are provided with source. Learn by example!
http://devex.allaire.com/developer/gallery/

On The Web

The Google Directory has every major ColdFusion resource listed. But they update their links more often than me, so I'll just point you there :-) http://directory.google.com/Top/Computers/Programming/Internet/ColdFusion/

In Print

(with links to buy from FatBrain.com)
Inside SQL Server 2000 The first book I reach for when I have any question about MS SQL Server.

11. Glossary

Coming soon...




Top: Daryl's ColdFusion Primer

Copyright ©2000-2017 Daryl Banttari. See [Disclaimer]. [About Daryl]