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. [. . . ] 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, FROM trips tripLocation, departureDate, returnDate, price, tripID 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 WHERE WHERE AND AND tripLocation = 'Aruba' tripLocation Like 'C%' tripLocation = 'China' departureDate > 1/1/2001 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. 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 other than red </cfif> Building a SQL WHERE clause in code is largely an exercise in string concatenation. 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. [. . . ] Do one of the following: If the site for which you want to restrict access is the currently active site in Dreamweaver, select the Secure Your Active Site option. To restrict access to a different site, select the Secure a Specific Directory option, click Browse, and then select the application for which you want to restrict access. 6. Depending on the type of authentication you want to use, continue with the instructions in the appropriate section: Creating a login using simple authentication Creating a login using LDAP authentication Creating a login using Windows NT authentication Exercise 1: About the Login Wizard 145 Creating a login using simple authentication When you create a Login using simple authentication, you specify one username and password that all users enter to gain access to your application. To create a login using simple authentication: 1. 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. [. . . ]