User manual MACROMEDIA COLFUSION MX 7-GETTING STARTED BUILDING COLDFUSION MX APPLICATIONS

[. . . ] COLDFUSION MX 7 ® Getting Started Building ColdFusion MX Applications Trademarks 1 Step RoboPDF, ActiveEdit, ActiveTest, Authorware, Blue Sky Software, Blue Sky, Breeze, Breezo, Captivate, Central, ColdFusion, Contribute, Database Explorer, Director, Dreamweaver, Fireworks, Flash, FlashCast, FlashHelp, Flash Lite, FlashPaper, Flex, Flex Builder, Fontographer, FreeHand, Generator, HomeSite, JRun, MacRecorder, Macromedia, MXML, RoboEngine, RoboHelp, RoboInfo, RoboPDF, Roundtrip, Roundtrip HTML, Shockwave, SoundEdit, Studio MX, UltraDev, and WebHelp are either registered trademarks or trademarks of Macromedia, Inc. and may be registered in the United States or in other jurisdictions including internationally. Other product names, logos, designs, titles, words, or phrases mentioned within this publication may be trademarks, service marks, or trade names of Macromedia, Inc. or other entities and may be registered in certain jurisdictions including internationally. [. . . ] 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. Exercise 2: Building a query that uses dynamic SQL 75 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: · Verify that the user entered data in the search criterion's value field. To do so, you use the cfif tag; for example, <cfif Form. tripLocationValue GT "">. · If data was entered, construct a WHERE subclause by concatenating the following: The SQL keyword AND The corresponding SQL column name (in the Trip Search example, tripLocation) for the search criterion The SQL operator equivalent of the search query operator The test value entered by the user The following code shows the creation of the WHERE subclause: <cfif Form. tripLocationOperator EQ "EQUALS"> <cfset WhereClause = WhereClause & " AND tripLocation = '" & form. tripLocationValue & "'" > <cfelse> 76 Chapter 7: Lesson 4: Building Dynamic Queries <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. 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 Exercise 2: Building a query that uses dynamic SQL 77 </cfquery> <cfreturn TripResult> </cffunction> 4. Add the highlighted code to the cfquery block to use the dynamically built WHERE clause in the query: <cfquery name="TripResult" datasource="CompassTravel"> SELECT tripID, tripName, tripLocation, departureDate, returnDate, price FROM trips WHERE #PreserveSingleQuotes(WhereClause)# </cfquery> 5. [. . . ] (See "Starting the Login Wizard" on page 145. ) 2. Select NT as the type of authentication. Select one of the following: Basic Authentication to use the browser dialog box to prompt the user for credentials to prompt the user in a web page ColdFusion Login page 4. Enter the name of the domain for whose members you want to grant access to your application. Note: The Login Wizard does not perform validation of the domain name and works only on Windows. 5. [. . . ]