MACROMEDIA COLFUSION MX 7-GETTING STARTED BUILDING COLDFUSION..., Начало работы

Страница: 77 / 152

Exercise 2: Building a query that uses dynamic SQL 77
<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 .
Creating the CFC query
The following code shows how to construct the tripLocation SQL WHERE subclause.
Specifically, it uses a dynamic SQL SELECT statement built from parameters from the Trip
Search page to display the search results. To continue the good coding practice of separating
business logic and presentation, you put the code to build the query using dynamic SQL in a
function in the CFC that you have been working with.
To add the new query to the CFC:
1.
Open the file gettrips.cfc file and position the pointer before the closing
cfcomponent
tag.
2.
Enter the following code, or do the steps in the “Let Dreamweaver do it” section:
<cffunction name="getTripsFromForm" access="public" returntype="query">
<cfquery name="TripResult" datasource="CompassTravel">
SELECT tripID, tripName, tripLocation, departureDate,
returnDate, price FROM trips
</cfquery>
<cfreturn TripResult>
</cffunction>
3.
Add the logic for creating the WHERE clause dynamically by entering the highlighted code.
<cffunction name="getTripsFromForm" access="public" returntype="query">
<!--- Create WHERE clause from data entered via search form --->
<cfset WhereClause = " 0=0 ">
<!--- Build subclause for trip location --->
<cfif Form.tripLocationValue GT "">
<cfif Form.tripLocationOperator EQ "EQUALS">
<cfset WhereClause = WhereClause & " and tripLocation = '" &
form.tripLocationValue & "'" >
<cfelse>
<cfset WhereClause = WhereClause & " and tripLocation like '" &
form.tripLocationValue & "%'" >
</cfif>
</cfif>
<cfquery name="TripResult" datasource="CompassTravel">
SELECT tripID, tripName, tripLocation, departureDate,
returnDate, price FROM trips