Troubleshooting ColdFusion Applications

As with any development tool, sooner or later you're going to find yourself debugging or trouble shooting a ColdFusion problem. Many applications and interfaces have to work seamlessly for a ColdFusion application to function correctly. The key to quickly isolating and correcting problems is a thorough understanding of ColdFusion, data sources, SQL syntax, URL syntax, and your Web serverand more importantly, how they all work with each other.

If the prospect of debugging an application sounds daunting, don't panic. ColdFusion has powerful built-in debugging and error-reporting features. These capabilities, coupled with logical and systematic evaluation of trouble spots, will let you diagnose and correct all sorts of problems.

This chapter teaches you how to use the ColdFusion debugging tools and introduces techniques that will help you quickly locate the source of a problem. More importantly, because an ounce of prevention is worth a pound of cure, we introduce guidelines and techniques that help prevent common errors from occurring in the first place.

Understanding What Can Go Wrong

As an application developer, sooner or later you are going to have to diagnose, or debug, a ColdFusion application problem. Because ColdFusion relies on so many other software components to work its magic, there are a lot of places where things can go wrong.

Since you're reading this chapter, the following assumptions are made:

You are familiar with basic ColdFusion concepts.
You understand how ColdFusion uses data sources for all database interaction.
You are familiar with basic SQL syntax and use.
You know how to use the ColdFusion Administrator.
You are comfortable using Macromedia Dreamweaver.

If you aren't familiar with any of these topics, I strongly recommend you read the chapters about them before proceeding.

See Chapter 1, "Introducing ColdFusion," for more information on how ColdFusion works and how all the pieces fit together to create a complete application.

See Chapter 3, "Accessing the ColdFusion Administrator," to learn how to enable debugging options using the ColdFusion Administrator.

See Chapter 5, "Building the Databases," for a detailed explanation of databases, tables, rows, columns, keys, and other database-related terms.

See Chapter 6, "Introducing SQL," for more information about data sources and how ColdFusion uses them for all database interaction.

Almost all ColdFusion problems fall into one of the following categories:

Web server configuration problems
Database driver errors
SQL statement syntax or logic errors
ColdFusion syntax errors
URL and path problems
Logic problems within your code

Let's look at each of these potential problem areas.

Debugging Web Server Configuration Problems

You should almost never encounter problems caused by Web server mis-configuration during routine, day-to-day operations. These types of problems almost always occur either during the initial ColdFusion setup or while testing ColdFusion for the first time. After ColdFusion in installed and configured correctly, it will stay that way.

The only exception to this is the possibility of you receiving an error telling you that ColdFusion isn't running. This error will only occur if using an external HTTP server (not ColdFusion's integrated server), and will be generated when the Web server ColdFusion extensions can't communicate with the ColdFusion Application Server.

Obviously, the Application Server must be running for ColdFusion to process templates. Steps to verifying that the server is running, and starting it if it isn't, differ based on your operating system:

If you're running ColdFusion on a Windows NT-based machine (including Windows 2000 and Windows XP), you should run the Services applet. It will show whether the service is running and will enable you to start it if isn't.
If you're running Windows 9x, you'll see the ColdFusion icon on the taskbar (near the clock) when the Application Server is running. If it isn't running, select ColdFusion from the ColdFusion program groups under your Start button menu.
If you're running ColdFusion on Unix/Linux, use the ps command (or ps ef|grep cfusion) to list running processes to see whether ColdFusion is running.

TIP

Windows services can be started automatically every time the server is restarted. The service Startup option must be set to Automatic for a service to start automatically. Windows 9x users can automatically start ColdFusion by ensuring that the ColdFusion Application Server is in the Programs, Startup group. This setting is turned on by the ColdFusion installation procedure and typically should be left on at all times. However, if the service doesn't automatically start, check these options.

TIP

If your operating system features a mechanism by which to automatically restart services or daemons upon system restart, use it.

One other situation worth noting is when you are prompted to save a file every time you request a ColdFusion page. If this is the case, one of two things is happening:

ColdFusion isn't installed on the server correctly (if not using the integrated HTTP server).
You are accessing URLs locally (using the browser File, Open option) instead of via the Web server.

Debugging Data Driver Errors

ColdFusion relies on database drivers (JDBC or ODBC) for all its database interaction. You will receive data driver error messages when ColdFusion can't communicate with the appropriate driver or when the driver can't communicate with the database.

Database driver error messages are always generated by the driver, not by ColdFusion. ColdFusion merely displays whatever error message it has received from the database driver. Unfortunately these error messages are often cryptic or even misleading.

Database driver error messages always contain an error number, which in and of itself is pretty useless. A text message that describes the problem follows the error number, however. The text of these messages varies from driver to driver, so it would be pointless to list all the possible error messages here. Instead, the more common symptoms and how to fix the problems that cause them are listed.

TIP

You can use the ColdFusion Administrator to verify that a data source is correctly set up and attached to the appropriate data file.

Receiving the Error Message `Data Source Not Found`

ColdFusion communicates with databases via database drivers. These drivers access data sourcesexternal data files. If the database driver reports that the data source could not be found, check the following:

Make sure you have created the data source.
Verify that the data source name is spelled correctly. Data source names are not case sensitive, so you don't have to worry about that.
If you're using ODBC drivers, note that Windows ODBC data sources are user login specific. This means if you create a data source from within the ODBC Control Panel applet while logged in as a user without administrator privileges, only that user will have access to that ODBC data source.

Receiving the Error Message `File Not Found`

You might get the error message File not found when trying to use a data source you have created. This error message applies only to data sources that access data files directly (such as Microsoft Access, Microsoft Excel, and Borland dBASE), and not to client/server database systems (such as Microsoft SQL Server and Oracle).

File not found simply means that the database driver could not locate the data file in the location it was expecting to find it. To diagnose this problem, perform the following steps:

1.	Data files must be created before data sources can use them. If you haven't yet created the data file, you must do so before proceeding.
2.	Check the data source settings, verify that the file name is spelled correctly, and ensure that the file exists.
3.	If you have moved the location of a data file, you must manually update any data sources that reference it.

Receiving Login or Permission Errors When Trying to Access a Data Store

Some database systems, such as Microsoft SQL Server, Sybase, and Oracle, require that you log on to a database before you can access it. When setting up a data source to this type of database, you must specify the login name and password the driver should use to gain access.

The following steps will help you locate the source of this problem:

1.	Verify that the login name and password are spelled correctly. (You won't be able to see the passwordonly asterisks are displayed in the password field.)
2.	On some database systems, passwords are case sensitive. Be sure that you haven't left the Caps Lock key on by mistake.
3.	Verify that the name and password you are using does indeed have access to the database to which you are trying to connect. You can do this using a client application that came with your database system.
4.	Verify that the login being used actually has rights to the specific tables and views you are using and to the specific statements (`SELECT`, `INSERT`, and so on). Many better DBMSs enable administrators to grant or deny rights to specific objects and specific operations on specific objects.

TIP

When you're testing security- and rights-related problems, be sure you test using the same login and password as the ones used in the data source definition.

Receiving the Error Message `Unknown Table`

After verifying that the data source name and table names are correct, you might still get unknown table errors. A very common problem, especially with client/server databases such as Microsoft SQL Server, is forgetting to provide a fully qualified table name. You can do this in two ways:

Explicitly provide the fully qualified table name whenever it is passed to a SQL statement. Fully qualified table names are usually made up of three parts, separated by periods. The first is the name of the database containing the table; the second is the owner name (usually specified as dbo); the third is the actual table name itself.
Some database drivers, such as the Microsoft SQL Server driver, let you specify a default database to be used if none is explicitly provided. If this option is set, its value is used whenever a fully qualified name isn't provided.

TIP

If your database driver lets you specify a default database name, use that feature. You can then write fewer and simpler hard-coded SQL statements.

Debugging SQL Statement or Logic Errors

Debugging SQL statements is one of the two types of troubleshooting you'll spend most of your debugging time doing (the other is debugging ColdFusion syntax errors, which we'll get to next). You will find yourself debugging SQL statements if you run into either of these situations:

ColdFusion reports SQL syntax errors. Figure 17.1, for example, is an error caused by misspelling a table name in a SQL statement. If you see only partial debug output, as shown in Figure 17.2, access the ColdFusion Administrator and turn on the Enable Robust Exception Information option in the Debugging Settings screen.
Figure 17.1. ColdFusion displays SQL error messages as reported by the database driver.

Figure 17.2. During development, be sure that Enable Robust Exception Information is turned on in the ColdFusion Administrator, or you won't see complete debug output.
No syntax errors are reported, but the specified SQL statement didn't achieve the expected results.

Obviously, a prerequisite to debugging SQL statements is a good working knowledge of the SQL language. I'm assuming you are already familiar with the basic SQL statements and are comfortable using them.

See Chapter 7, "SQL Data Manipulation," for information about basic SQL statements and examples of their uses.

TIP

The Debug Options screen in the ColdFusion Administrator contains a checkbox labeled Database Activity. During development, turn this option on so that the full SQL statement and data source name is displayed in any database-related error messages.

The keys to successfully debugging SQL statements are as follows:

Isolate the problem. Debugging SQL statements inside ColdFusion templates can be tricky, especially when creating dynamic SQL statements. Try executing the same statement from within another database client, replacing dynamic parameters with fixed values if appropriate.
The big difference between ColdFusion SQL statements and statements entered into any other database client is the use of ColdFusion fields. If you are using ColdFusion fields within your statement, verify that you are enclosing them within quotation marks when necessary. If the value is a string, it must be enclosed in single quotation marks. If it's a number, it must not be enclosed in quotation marks. (And be sure double quotation marks are never used within SQL statements, because this will terminate the statement prematurely.)
Look at the bigger picture. Dynamic SQL statements are one of ColdFusion's most powerful features, but this power comes at a price. When you create a dynamic SQL statement, you are effectively relinquishing direct control over the statement itself and are allowing it to be changed based on other conditions. This means that the code for a single ColdFusion query can be used to generate an infinite number of queries. Because some of these queries might work and others might not, debugging dynamic SQL requires that you be able to determine exactly what the dynamically created SQL statement looks like. Thankfully, ColdFusion makes this an easy task, as you will see later in the section "Using the ColdFusion Debugging Options."
Break complex SQL statements into smaller, simpler statements. If you are debugging a query that contains subqueries, verify that the subqueries properly work independently of the outer query.

CAUTION

Be careful to not omit number signs (#) from around variable names in your SQL code. Consider the following SQL statement:

 DELETE Actors WHERE ActorID=ActorID

What the code is supposed to do is delete a specific actor, the one whose ID is specified in ActorID. But because the number signs were omitted, instead of passing the actor ID, the name of the actor ID column is passed. The result? Every row in the Actors table is deleted instead of just the oneall because of missing number signs. The correct statement should have looked like this:

 DELETE Actors WHERE ActorID=#ActorID#

Incidentally, this is why you should always test WHERE clauses in a SELECT before using them in a DELETE or UPDATE.

Whenever a SQL syntax error occurs, ColdFusion displays the SQL statement it submitted. The fully constructed statement is displayed if your SQL statement was constructed dynamically. The field names are displayed as submitted if the error occurred during an INSERT or UPDATE operation, but the values are replaced with question marks (except for NULL values, which are displayed as NULL).

NOTE

If you ever encounter strange database driver error messages about mismatched data types or incorrect numbers of parameters, check to see if you have mistyped any table or column names and that you have single quotation marks where necessary. More often than not, that is what causes that error.

TIP

If you're using Dreamweaver MX 2004 (and you should be), you can completely avoid typos in table and column names by using the database drag-and-drop support. To do this, open the Application panel and select the Database tab, select the desired data source, and expand the tables to find the table and column you need. You can then click the table or column name and just drag it to the editor window, where it will be inserted when you release the mouse key.

Debugging ColdFusion Syntax Errors

Debugging ColdFusion syntax errors is the other type of troubleshooting you'll find yourself doing. Thankfully, and largely as a result of the superb ColdFusion error-reporting and debugging capabilities, these are usually the easiest bugs to find.

ColdFusion syntax errors are usually one of the following:

Mismatched number signs or quotation marks
Mismatched begin and end tags; a <cfif> without a matching </cfif>, for example
Incorrectly nested tags
A tag with a missing or incorrectly spelled attribute
Missing quotation marks around tag attributes
Using double quotation marks instead of single to delimit strings when building SQL statements
Illegal use of tags

If any of these errors occur, ColdFusion generates a descriptive error message, as shown in Figure 17.3. The error message lists the problematic code (and a few lines before and after it) and identifies exactly what the problem is.

Figure 17.3. ColdFusion generates descriptive error messages when syntax errors occur.

CAUTION

If your template contains HTML forms, frames, or tables, you might have trouble viewing generated error messages. If an error occurs in the middle of a table, for example, that table will never be terminated, and there's no way to know how the browser will attempt to render the partial table. If the table isn't rendered and displayed properly, you won't see the error message.

TIP

If you think an error has occurred but no error message is displayed, you can view the source in the browser. The generated source will contain any error messages that were included in the Web page but not displayed.

One of the most common ColdFusion errors is missing or mismatched tags. Indenting your code, as shown in the following, is a good way to ensure that all tags are correctly matched:

 <cfif some condition here>  <cfoutput>   Output code here   <cfif another condition>    Some other output code here   </cfif>  </cfoutput> <cfelse>  Some action here </cfif>

Dreamweaver users should take advantage of the available features designed to help avoid common mismatching problems. Color coding is one of these (if the code isn't colored correctly, you've done something wrong), as is right mouse-click support for Tag Editors.

See Chapter 2 for more information about Macromedia Dreamweaver.

Inspecting Variable Contents

Sometimes problems throw no errors at all. This occurs when your code is syntactically valid but a logic problem exists somewhere. Aside from using the interactive debugger (which is discussed shortly), the primary way to locate this type of bug is to inspect variable contents during processing. Two ways to do this are available:

Embed variable display code as necessary in your page, dumping the contents of variables to the screen (or to HTML comments you can view using View Source in your browser).
The <cfdump> tag (introduced in Chapter 8, "Using ColdFusion") can display the contents of any variable, even complex variables, and can be used to aid debugging when necessary.

By displaying variable contents, you usually can determine what various code blocks are doing at any given point during page processing.

TIP

You can use <cfabort> anywhere in the middle of your template to force ColdFusion to halt further processing. You can move the <cfabort> tag farther down the template as you verify that lines of code work.

TIP

During development, when you find yourself alternating between needing debugging information and not needing it, you can enclose debug code in <cfif IsDebugMode()> and </cfif>. This way, your debug output will be processed only if debugging is enabled.

Using the Document Validator

To help you catch syntax errors before your site goes live, Dreamweaver has an integrated validation engine. The validation engine can be used to check for mismatched tags, unknown variables, missing number signs, and other common errorsand not just CFML errors, either.

To use the validator, open the file to be checked in Dreamweaver and then open the Results panel and select the Validation tab. Click the Validate button (the green arrow at the top left of the panel) and select what it is you'd like to validate (the first option is the one you should use to validate the current document). Dreamweaver then validates your code and lists any errors in a results window at the bottom of the screen, as seen in Figure 17.4.

Figure 17.4. The Dreamweaver validation engine lists any errors in the Results panel.

To quickly jump to the problematic code, click on any error message in the Results panel. As seen in Figure 17.5, Dreamweaver goes to the appropriate line of code and even highlights the trouble spot for you. This lets you fix errors before you roll out your application.

Figure 17.5. The Dreamweaver validation engine can flag problem code for you.

TIP

Ctrl-Shift-F7 is a shortcut that takes you directly to the Validation tab.

NOTE

You can customize the behavior of the validation engine, including specifying what gets validated and which tags to validate. To do this, open the Preferences screen, and then select the Validator tab.

Debugging URL and Path Problems

URL- and path-related problems are some of the easiest to diagnose and resolve because they tend to be binary in naturethey either work consistently or they fail consistently.

Images Are Not Displayed

If image files (and other files) aren't always displayed when you reference them from within ColdFusion, the problem might be path related. If you're using relative paths (and you generally should be), be sure that the path being sent to the browser is valid. Having too many or too few periods and slashes in the path is a common problem.

TIP

Most browsers let you check image paths (constructing full URLs from relative paths in your code) by right-clicking the image and viewing the properties.

Passing Parameters That Aren't Processed

Parameters you pass to a URL can't be processed by ColdFusion, even though you see them present in the URL. URLs are finicky little beasts, and you have to abide by the following rules:

URLs can have only one question mark character in them. The question mark separates the URL itself from the query.
Each parameter must be separated by an ampersand (&) to pass multiple parameters in the URL query section.
URLs must not have spaces in them. If you are generating URLs dynamically based on table column data, be sure to trim any spaces from those values. If you must use spaces, replace them with plus signs. ColdFusion correctly converts the plus signs to spaces when used. Use the ColdFusion URLEncodedFormat() function to convert text to URL-safe text.

TIP

ColdFusion debug output, discussed in the following section, lists all passed URL parameters. This is an invaluable debugging tool.

Debugging Form Problems

If a form is submitted without data, it can cause an error. Web browsers submit data to Web servers in two ways. These ways are called GET and POST, and the submission method for use is specified in the <form> method attribute.

As a rule, forms being submitted to ColdFusion should always be submitted using the POST method. The default method is GET, so if you omit or misspell method="POST", ColdFusion may be incapable of processing your forms correctly.

You might occasionally get a variable is undefined error message when referring to form fields in the action template. Radio buttons, check boxes, and list boxes aren't submitted if no option was selected. It's important to remember this when referring to form fields in an action template. If you refer to a check box without first checking for its existence (and then selecting it), you'll generate an error message.

The solution is to always check for the existence of any fields or variables before using them. Alternatively, you can use the <CFPARAM> tag to assign default values to fields, thereby ensuring that they always exist.

See Chapter 12, "ColdFusion Forms," for more information about working with form fields and working with specific form controls.

How can you check which form fields were actually submitted and what their values are? Enable ColdFusion debugging (as explained shortly) any time you submit a form. Its action page contains a debugging section that describes the submitted form. This is shown in Figure 17.6. A field named FORM.FORMFIELDS contains a comma-delimited list of all the submitted fields, as well as a list of the submitted fields and their values.

Figure 17.6. ColdFusion displays form-specific debugging information if debugging is enabled.

Here are some other things to look for:

Be sure all form fields have names.
Be sure related check boxes or radio buttons have the same name (unless using Flash Forms, in which case check boxes must be uniquely named).
Be sure form field names are specified within double quotation marks.
Be sure form field names have no spaces or other special characters in them.
Be sure that all quotation marks around attribute values match.
When all else fails, dump the contents of the FORM scope with <cfdump var="#form#">.

All these are HTML related, not ColdFusion related. But every one of them can complicate working with forms, and HTML itself won't generate errors upon form generation.