Exercise 2: Building a query that uses dynamic SQL

Dynamic SQL is a term that refers to SQL code that your program generates using variables before the SQL is executed. You can use dynamic SQL to accomplish tasks such as adding WHERE clauses to a search based on the fields that the user filled out on a search criteria page.

Based on the columns that you can query in this tutorial, the SQL query to display the search results would look like this:

SELECT tripName,  tripLocation, departureDate, returnDate, price, tripID 
FROM trips

The purpose of the Trip Search form is to supply the data needed to build the WHERE clause to finish this SQL SELECT statement and constrain the query according to the user’s input.

When the user enters the search criteria on the Trip Search form and clicks the Search button, the form fields are posted to the Trip Search Results page. The posted field values compose the WHERE clause in the SQL SELECT statement. The following example lists the WHERE clauses that you can generate depending on the criteria set on the search page:

WHERE tripLocation = 'Aruba'
WHERE tripLocation Like 'C%'
WHERE tripLocation = 'China' 
	AND departureDate > 1/1/2001
	AND price < 1500

In this example, the SQL AND operator joins the search condition clauses. To simplify the trip search example, you will use the SQL AND operator to combine all the search condition clauses. A more sophisticated search criteria page might present the user a choice of using AND or OR to connect one search criterion with the others.

The action page invokes a method that builds the WHERE clause so that the SQL SELECT statement retrieves the information that the user requests. Then, the action page displays an HTML table with the results of the user query using the cfoutput block.

Building the WHERE Clause with the cfif and cfset tags

The WHERE clause in a SQL SELECT statement is a string. You use the CFML cfset and cfif tags to conditionally build the WHERE clause depending on values passed to the search action page. The cfset statement creates a variable or changes the value of an existing variable. For example, to create a variable named color and initialize its value to red, you use the following statement:

<cfset color = "red">

The cfif tag instructs the program to branch to different parts of the code depending on whether a test evaluates to True or False. For example, to have some code execute if the color variable is equal to red, and other code execute if it is not, you use the following pseudocode:

<cfif color EQ "red">
... statements for color red
<cfelse>
... statements for other than red
</cfif>

Building a SQL WHERE clause in code is largely an exercise in string concatenation. The & operator combines two strings in ColdFusion. For example, the following code snippet:

<cfset FirstName = "Wilson">
<cfset LastName = "Gato">
<cfset FullName = FirstName & " " & LastName>
<cfoutput>My name is #FullName#.</cfoutput>

results in the following text:

My name is Wilson Gato.

For each search criterion on the Trip Search form, the code within the Trip Search Results page must do the following:

The following code shows the creation of the WHERE subclause:

<cfif Form.tripLocationOperator EQ "EQUALS">			
	<cfset WhereClause = WhereClause & " AND tripLocation = '" &
		form.tripLocationValue & "'" >
<cfelse>
	<cfset WhereClause = WhereClause & " AND tripLocation like '" &
		form.tripLocationValue & "%'" >
</cfif>

When you test for a string column within the WHERE clause of the SQL SELECT statement, you must enclose the test value in quotation marks.

When you use a variable to construct a WHERE clause, you must preserve the quotation marks so that the database server does not return an error. To preserve the quotation marks, you must use the ColdFusion PreserveSingleQuotes function. The PreserveSingleQuotes function prevents ColdFusion from automatically escaping single-quotation marks contained in the variable string passed to the function.

Note: The cfqueryparam tag also escapes single-quotation marks. For more information, see CFML Reference.