Debugging Dynamic Database Queries

Before we finish this chapter, there is something you should be aware of. Look at the following code:

 <!--- Get a movie from database ---> <cfquery name="movie" datasource="ows"> SELECT FilmID, MovieTitle,        PitchText, Summary,        DateInTheaters, AmountBudgeted FROM Films WHERE FilmID=#URL.FilmID# </cfquery> 

As you now know, this code builds a dynamic SQL statementthe expression #URL.FilmID# is replaced by the contents of that variable to construct a complete SQL SELECT statement at runtime.

This particular example is a simple one, a single expression is used in a simple WHERE clause. But as the complexity of the expressions (or the number of them) increases, so does the chance that you'll introduce problems in your SQL. And to find these problems, you'll need to know exactly what SQL was generated by ColdFusiontaking into account all dynamic processing.

I already showed you one way to obtain the dynamically generated SQL (using the optional <cfquery> result attribute). But here is another option.

In Chapter 3, "Accessing the ColdFusion Administrator," I mentioned the debugging screens (and told you that we'd use them in this chapter). The debugging screens can be used to append debug output to the bottom of generated pages, as seen in Figure 10.19.

Figure 10.19. Dynamic SQL specific are displayed along with the standard ColdFusion debugging output.

As you can see, the appended output contains database query information (including the SQL, number of rows retrieved, and execution time), page execution time, passed parameters, CGI variables, and much more.

To try this for yourself, see Chapter 3 for instructions on turning on debug output. Once enabled, execute any page in your browser and the debug output will be appended automatically.

NOTE

If you are browsing files within Dreamweaver the debug output will be displayed in the results window beneath the editor.

TIP

Most ColdFusion developers find that the tags you have learned thus far, <cfquery>, <cfoutput>, <cfset>, <cfif>, and <cflocation>, account for almost all the CFML code they ever write. As such, it's highly recommended that you try every example in this chapter before proceeding.