Chapter 24. Advanced Database Interaction

In previous chapters, we reviewed some of the basic concepts relating to relational databases as well as the fundamental ways ColdFusion can interact with a database. Our interactions with the database have been based on static structured query language (SQL) statements. Take a look at the code in Listing 24.1. This is a simple select statement that returns all records from the database table named State, where the state name begins with the letter C. The recordset returned would look something like Figure 24.1.

Figure 24.1. Recordset returned by the simple select statement.

Listing 24.1 A Simple Static Select Statement

 <cfquery name="GetStates" datasource="MyTest">       SELECT StateAbbreviation,StateName       FROM State       WHERE StateName LIKE 'C%'  </cfquery>  <TABLE WIDTH=50%>      <TR>            <TD WIDTH=50%><B>Abbreviation</B></TD>            <TD WIDTH=50%><B>Name</B></TD>      </TR>      <cfoutput query="GetStates">      <TR>            <TD>#StateAbbreviation#</TD>            <TD>#StateName#</TD>      </TR>      </cfoutput>  </TABLE> 

With a static SQL statement, the exact same query is executed each time the query is called. Let's assume that we are building an application and we need to be able to return a recordset that contains a listing of state names based on the first letter of the state name. Now we could write 26 queries (one for each letter of the alphabet), but that would be a bit cumbersome, particularly if we had similar scenarios that required complex queries. Instead, we could write our GetStates query so that it enabled the user to specify the letter(s) to be used in the WHERE clause when he or she called the query. Dynamic queries enable the user to specify parameters in the form page. These parameters are then passed to the CFQUERY tag on the action page.