Dynamic SQL

Queries of this type are known as dynamic queries because the SQL sent to the database is not known until runtime. Dynamic queries contain both static (common SQL) and dynamic SQL. Let's look at our GetStates query, written as dynamic SQL in Listing 24.2.

Listing 24.2 GetStates Query Written as Dynamic SQL

 <cfquery name="GetStates" datasource="MyTest">       SELECT StateAbbreviation,StateName       FROM State       WHERE StateName LIKE '#Form.State#%'  </cfquery> 

If the user entered a value of D in the input form, the SQL statement sent to the database would look like the code in Listing 24.3.

Listing 24.3 Code Executed in the GetStates Query

 SELECT StateAbbreviation,StateName  FROM State  WHERE StateName LIKE 'D%' 

One of the most common uses of dynamic queries is to generate the query based on user inputs. In Listing 24.1, the only portion that is dynamic is the string value used to search for state names. There might be many scenarios in which you would construct dynamic queries that have multiple conditional statements all based on input from the user. In these scenarios, you often would include only the condition if the user provides input. Such a scenario would be a search of a Customer table.

You might want the capability to search for customers based on the customer's ID number, last name, state, or a combination of the three. There are a couple of different methods that can be used to accomplish this task. The first involves using a combination of the CFIF tag and IsDefined() function. In this situation, you would use the IsDefined() function to check for the existence of a parameter value and, if it exists, check the value to ensure that it meets certain conditions. This would be done prior to the submission of the query. The second method, which builds on some of the best practices discussed in previous chapters, employs the CFPARAM tag to ensure that the parameter always exist and then uses the CFIF tag to determine if a value was passed by the user. You likely will encounter both methods, so I want to demonstrate both in Listing 24.4.

Listing 24.4 Dynamic Query Using IsDefined()

 <cfquery name="FindCustomer" datasource="MyTest">        SELECT CustomerID, CustomerFirstName, CustomerLastName,        CustomerEmail        FROM Customer        WHERE 0 = 0        <cfif IsDefined("Form.ID") AND Form.ID IS NOT "">                     AND CustomerID = #Form.ID#        </cfif>        <cfif IsDefined("Form.LastName") AND Form. LastName IS NOT "">                     AND CustomerLastName ='#Form.LastName#'        </cfif>        <cfif IsDefined("Form.StateID") AND Form.StateID IS NOT "">                     AND CustomerStateID = #Form.StateID#        </cfif>  </cfquery> 

If the user input the criteria of LastName = Smith and StateID = 5, the resulting SQL statement would look like Listing 24.5.

Listing 24.5 Code Executed in the FindCustomer Query

 SELECT CustomerID, CustomerFirstName, CustomerLastName, CustomerEmail  FROM Customer  WHERE 0 = 0  AND CustomerLastName = 'Smith'  AND CustomerStateID = 5 

You might encounter similar examples that use a nested CFIF statement rather than the combined CFIF statement that I used. Although it looks a bit different, they both accomplish the same thing:

 <cfifF IsDefined("Form.ID")>              <cfif Form.ID IS NOT "">              AND CustomerID = #Form.ID#         </cfif>  </cfif> 

So, what did our code actually do here? We first checked whether the Form field was defined. This check prevents an error if the page has been changed and the field no longer is part of the Form. We then checked whether there was actually a value in the form field. In most circumstances we want the condition to be part of the SQL statement passed to the database only if a value is passed. Keep in mind that if our form page contains three input fields named ID, LastName and StateID, a value of "" is passed when the form is posted to the action page and the user does not provide a value. To demonstrate the point, let's remove the second part of the CFIF statement from our CFQUERY.

Listing 24.6 FindCustomer Without Value Check

 <cfquery name="FindCustomer" datasource="MyTest">        SELECT CustomerID, CustomerFirstName, CustomerLastName,        CustomerEmail        FROM Customer        WHERE 0 = 0        <cfif IsDefined("Form.ID")>              AND CustomerID = #Form.ID#        </cfif>        <cfif IsDefined("Form.LastName")>              AND CustomerLastName ='#Form.LastName#'        </cfif>        <cfif IsDefined("Form.State")>              AND CustomerStateID = #Form.StateID#        </cfif>  </cfquery> 

If the user once again submits the values of LastName = Smith and StateID = 5, the resulting SQL statement would be much different, as shown in Listing 24.7.

Listing 24.7 SQL Executed by FindCustomer Without Value Check

 SELECT CustomerID, CustomerFirstName, CustomerLastName, CustomerEmail  FROM Customer  WHERE 0 = 0  AND CustomerID =  AND CustomerLastName = 'Smith'  AND CustomerStateID = 5 

Take a look at Figure 24.2. As you can see, not only is the SQL statement that is passed to the database different, so are the results.

Figure 24.2. FindCustomer without value check throws error.

Let's show how we can write our FindCustomer query utilizing the CFPARAM tag instead of the IsDefined() function. Notice in this method that we do not need to check for the existence of the variable as we use the CFPARAM tag to ensure it exists. We still check the variable to determine whether the user passed a value. As with our previous example, we still do not want the search criteria to be part of the SQL WHERE clause unless, of course, the user provides a value.

Listing 24.8 FindCustomer Query Using CFParam

 <cfparam name="form.id" default="0">  <cfparam name ="form.lastname" default="">  <cfparam name ="form.stateid" default="0">  <cfquery name="findcustomer" datasource="MyTest">    SELECT CustomerID,           CustomerFirstName,           CustomerLastName,           CustomerEmail      FROM Customer     WHERE 0 = 0    <cfif NOT Val(Form.ID)>        AND CustomerID = #Val(Form.ID)#    </cfif>    <cfif Len(Trim(Form. LastName))>        AND CustomerLastName ='#Trim(Form.LastName)#'    </cfif >    <cfif Val(Form.StateID)>        AND CustomerStateID = #Val(Form.StateID)#    </cfif >  </cfquery> 

Although one of the most popular uses of dynamic queries is creating user-generated search criteria, there are other ways to use dynamic queries. One of the most common is to specify the datasource dynamically. This enables you to change the datasource, normally set in the Application.cfm file, without having to modify additional code.

Listing 24.9 Dynamic Datasource

 <cfquery name="ListStates" datasource="#Request.DSN#">       SELECT StateID,StateAbbreviation       FROM State  </cfquery>