manualshive.com logo in svg

MACROMEDIA COLDFUSION MX 61-GETTING STARTED BUILDING COLDFUSION..., Начало работы, Страница: 117 / 134

Поделиться
Скачать
MACROMEDIA COLDFUSION MX 61-GETTING STARTED BUILDING COLDFUSION... Скачать руководство пользователя страница 117

117

CHAPTER 10

Lesson 6: Adding and Updating SQL Data

In this lesson, you will complete the Compass Travel Trip Maintenance application. The 
exercises will guide you through the steps of adding the database update logic to add new trip 
offerings and update existing trips in the Compass Travel database. 

This lesson explains how to do the following tasks:

•

Modify the Trip Edit page to link the add and update functions to the main application page.

•

Write code to insert new trips using SQL. 

•

Write code to insert new trips without using SQL.

•

Write code to update several records in the Trips table.

ColdFusion tags and functions introduced in this lesson

The following table identifies the ColdFusion tags and functions that you use in this lesson to 
enhance the ColdFusion application:

Element

Type

Description

cfinsert

Tag

Inserts records in a JDBC data source.

cfupdate

Tag

Updates a records in a JDBC data source.

Содержание COLDFUSION MX 61-GETTING STARTED BUILDING COLDFUSION...

Страница 1: ...Getting Started Building ColdFusion MX Applications...

Страница 2: ...Macromedia Inc or other entities and may be registered in certain jurisdictions including internationally This product includes code licensed from RSA Data Security This guide contains links to third...

Страница 3: ...guage 12 The ColdFusion MX Administrator 13 Using ColdFusion MX with Macromedia Flash MX 14 CHAPTER 2 CFML Basics 15 Working with ColdFusion pages 16 Creating a ColdFusion page 16 Understanding CFML e...

Страница 4: ...for ColdFusion development 46 Configuring HomeSite for ColdFusion development 46 PART II Building a ColdFusion Application CHAPTER 5 Lesson 1 Preparing to Build the Sample Application 49 Application...

Страница 5: ...erver side action page 96 Validating data on the client using ColdFusion form tags 99 Using cfselect tag to present valid event types 102 Using other client side script to reduce edits on the server 1...

Страница 6: ...6 Contents...

Страница 7: ...x and HP UX Configuring and Administering ColdFusion MX Part I describes how to manage the ColdFusion environment including connecting to your data sources and configuring security for your applicatio...

Страница 8: ...ne documentation All ColdFusion MX documentation is available online in HTML and Adobe Acrobat Portable Document Format PDF files Go to the documentation home page for ColdFusion MX on the Macromedia...

Страница 9: ...erview of the ColdFusion Markup Language CFML It also provides generic database concepts and information about how to prepare your development environment for using the tutorial in Part II of this boo...

Страница 10: ......

Страница 11: ...technologies that are the foundation for Macromedia ColdFusion MX In addition it introduces the basic concepts about ColdFusion MX how it works and the various components that comprise it Contents Wh...

Страница 12: ...usion page 1 The ColdFusion server looks at the content of the page and searches for the following ColdFusion instructions Tags that begin with cf Variables and functions that are always surrounded by...

Страница 13: ...ds that you build your applications using Macromedia Dreamweaver MX Dreamweaver MX offers features and wizards that enhance ColdFusion development For more information about Dreamweaver MX see Chapter...

Страница 14: ...an Action Message Format protocol over HTTP This protocol provides fast lightweight binary transfer of data between the Flash client and ColdFusion By using the following features of ColdFusion MX an...

Страница 15: ...o create ColdFusion pages and use variables functions conditional processing and form processing Contents Working with ColdFusion pages 16 Understanding CFML elements 18 Working with CFML expressions...

Страница 16: ...Note This book shows how to create ColdFusion applications by writing your code manually It does not address how to create ColdFusion pages by generating code with wizards For information about using...

Страница 17: ...run the Web Server Configuration Tool and save pages under your web root directory Tip ColdFusion MX on Macintosh OS X uses the J2EE configuration To save the code you typed to create a ColdFusion pa...

Страница 18: ...erations such as accessing a database Functions can return data and do other operations like retrieving the system date Almost everything you want to accomplish with ColdFusion will involve using tags...

Страница 19: ...le the server needs to know specifics about the database such as the database name and the table name The code required to write this type of statement might look like this cfupdate datasource mydb ta...

Страница 20: ...an unformatted date and time However you can format the results of this function with other functions such as the DateFormat or TimeFormat functions Nesting functions Functions can generate data as w...

Страница 21: ...atabase table Naming variables You must use the following rules for naming ColdFusion variables Names are case insensitive uppercase lowercase or mixed case Names can contain only letters numbers and...

Страница 22: ...iable is important because ColdFusion supports many types of variables The syntax for referencing a local variable is as follows variables variablename Because ColdFusion lets you use the same name wi...

Страница 23: ...example the following code creates a variable and instructs the ColdFusion server to return the value of the variable cfset my_first_name Kaleigh cfset my_last_name Smith cfset my_full_name variables...

Страница 24: ...ve languages such as JavaScript Specifying pound signs to denote functions or variables In ColdFusion you specify pounds signs to denote functions and variables within a string of text You use pounds...

Страница 25: ...ues of the referenced variables Kaleigh and 5 respectively when assigning the value to the variable the_string To display quotation marks on a page as literal characters you must double the quotation...

Страница 26: ...NOT NEQ NOT EQUAL Tests for nonequality CONTAINS Tests whether a value is contained within a second value DOES NOT CONTAIN Tests whether a value is not contained within a second value Operators Descri...

Страница 27: ...reate a cfif statement The basic syntax is as follows cfif expression HTML and CFML tags executed if expression is True cfif In the previous example ColdFusion only executes the code inside the cfif s...

Страница 28: ...ependent steps for creating a form in ColdFusion 1 Creating the layout for the form itself 2 Writing the code to process the submitted information Form processing Every form that you create in ColdFus...

Страница 29: ...at ColdFusion supports is post All ColdFusion forms must set the method attribute to post In Part II of this book you will use ColdFusion form tags to create forms and write collected values to a data...

Страница 30: ...30 Chapter 2 CFML Basics...

Страница 31: ...of relational database concepts and terms It describes what a database is and how it is organized It also discusses the Structured Query Language SQL that you use to interact with databases Contents U...

Страница 32: ...nd on hand inventory is stored in a database Within the database you organize the data into storage containers called tables Tables are made up of columns and rows Columns represent individual fields...

Страница 33: ...nt information is the same for each employee in a given department however repeating the department information for each employee is redundant Storing redundant data takes up more disk space If the de...

Страница 34: ...C JDBC is a standard application programming interface API for accessing information from different database systems and different storage formats About data sources A data source is a complete databa...

Страница 35: ...the name of the query The second attribute of cfquery defines the name of the data source The SELECT statement defines the fields columns to be retrieved from a tabled named Employee CFML tags that i...

Страница 36: ...36 Chapter 3 Database Fundamentals...

Страница 37: ...structure and how to configure the database connection and debugging options in the ColdFusion MX Administrator Additionally it provides a brief overview of using Macromedia Dreamweaver MX or Macrome...

Страница 38: ...rectory a backup copy of the file is in the new_user_database directory To ensure that you are working with the original database file verify that the file in the db directory has the same date as the...

Страница 39: ...ngs Configuring the connection to the sample database file The following procedures describe how to configure a connection to the sample database file CompassTravel using the ColdFusion MX Administrat...

Страница 40: ...ied for the Compass Travel database file is correct To define the sample PointBase database file 1 In the ColdFusion MX Administrator select Data Services Data Sources The Add New Data Source dialog b...

Страница 41: ...his click the Delete action button associated with the CompassTravel data source name then repeat the steps in this procedure to reconfigure this data source b Verify that the JDBC URL is correct Fiel...

Страница 42: ...u can have these options turned on all the time However if you are working on a production system you most likely will not want to have these options turned on because the debugging information can ap...

Страница 43: ...Administrator select Debugging and Logging Debugging Settings A list of debugging options appears on the Debugging Settings page 2 Select the Enable Debugging check box The debugging service is enabl...

Страница 44: ...ent environment tools Macromedia Dreamweaver MX is the preferred development environment for building ColdFusion MX applications It combines the best code editing features of ColdFusion Studio with th...

Страница 45: ...ery CFML tag Code hints for writing CFML tag attributes Code validator for validating code readiness against other ColdFusion versions Tag chooser with integrated reference material for inserting Cold...

Страница 46: ...HomeSite to create the sample application in Part II of this book you must configure HomeSite to recognize the tutorial files and data sources To use HomeSite to create the sample application 1 Establ...

Страница 47: ...sts of six lessons Lesson 1 Preparing to Build the Sample Application 49 Lesson 2 Writing Your First ColdFusion Application 57 Lesson 3 Creating a Main Application Page 77 Lesson 4 Validating Data to...

Страница 48: ......

Страница 49: ...ign This lesson provides an overview of these application design steps while the remainder of this book guides you through the lessons on constructing the sample application Application development st...

Страница 50: ...nator task Description Produce current trip listing To help Compass Travel agents take trip reservations over the phone and in person the trip coordinator maintains a list of current trip offerings Pr...

Страница 51: ...he trip Price Base cost Assigned trip leader Trip photograph By collecting the preceding information about each trip the Compass Travel website can market its trips online to the general public Custom...

Страница 52: ...ntain text data while the price column can only contain numeric data It is important to know what type of data is valid for each column so that your data collection forms can validate against incorrec...

Страница 53: ...the eventtypes table new mountain climbing trips would be classified differently than ones saved before the change For this reason and to save space in the database the key to the eventtypes row even...

Страница 54: ...lication page is where users will come to view information about trips and to navigate to other ColdFusion pages to add edit or search for new trips The following lessons explain how to do these tasks...

Страница 55: ...ColdFusion web application root For more information see Saving your ColdFusion page on page 17 You can view ColdFusion application pages on your local computer by opening a web browser and entering o...

Страница 56: ...tabase file for MS Windows users and a PointBase database file for UNIX users have been provided For information about how to configure the Compass Travel data source see Configuring Your Development...

Страница 57: ...ble search query Develop a results form to display the result of the search ColdFusion tags and functions introduced in this lesson The following table identifies the ColdFusion tags and functions tha...

Страница 58: ...a trip query facility You will create a search interface that meets both of these requirements in this lesson The following list identifies the components that you will create in this lesson Dynamic...

Страница 59: ...nts are the Compass Travel coordinators and agents not the general public Application development steps You will review or participate in the following application construction steps Steps Description...

Страница 60: ...ng a page that is not likely to change often Converting to a dynamic web page Using the static web page approach the Trip Coordinator needs to modify all the web pages that reference trip lists when t...

Страница 61: ...clause you can use any of the following operators For example to select the columns named Last Name and First Name for Clients whose City is Boston use the following SELECT statement SELECT LastName F...

Страница 62: ...word in the ORDER BY clause the rows are returned in descending order bottom to top The following statement returns a reverse alphabetic list of the Clients table SELECT FROM Clients Order By LastName...

Страница 63: ...ing the column contents from the SQL statement In CFML you surround variables with pound signs to display their contents using the cfoutput tag You also use this approach with column names specified i...

Страница 64: ...s exercise you will improve the Trip List page to make it easier for the Compass Travel agents to locate trips You must make the following improvements Sort the trip names in alphabetic order Display...

Страница 65: ...ng the cfoutput tag from just tripName to include all three selected fields as follows tripName departs departureDate returns returnDate price price BR 3 Create the Budget Trip List report by doing th...

Страница 66: ...eria page When designing the search criteria page it is a good idea to develop a list of possible queries the user might issue when searching for the records Since most Compass Travel customers are pr...

Страница 67: ...a B SQL is well suited for this type of query When designing the Search Criteria page you must decide which operators to support for each of the queryable columns The operators that you use depends o...

Страница 68: ...wever use this page tripsearch cfm later in this lesson to test the search action page The source code for the Trip Search form tripsearch cfm is as follows html head title Trip Maintenance Search For...

Страница 69: ...Name 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 co...

Страница 70: ...er query using the cfoutput block Building the WHERE Clause with the cfif and cfset The WHERE clause in a SQL SELECT is a string You use the CFML cfset and cfif tags to conditionally build the WHERE c...

Страница 71: ...e 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 p...

Страница 72: ...ly builds the tripLocation subclause In the following exercise you will add code for the other two queryable columns departureDate and price Code Explanation cfset WhereClause 0 0 The cfset tag initia...

Страница 73: ...the text box b Click Search The Trip Results page displays several entries as follows c Notice in the Trip Results page that only one trip has a trip location of China d Click the Back button in your...

Страница 74: ...WhereClause and price form priceValue cfelseif Form priceOperator EQ SMALLER cfset WhereClause WhereClause and price form priceValue cfif cfif For Enter this code Windows users using the MS Access da...

Страница 75: ...ode and be a more extensible solution then the one presented here This more extensible approach is beyond the scope of this tutorial however Summary This lesson described how to access a relational da...

Страница 76: ...76 Chapter 6 Lesson 2 Writing Your First ColdFusion Application...

Страница 77: ...mat date and dollar variables Use the MOD function and a query variable to properly alternate the back color of rows in the Trip Search Results table ColdFusion tags and functions introduced in this l...

Страница 78: ...ion to include a main application page that lets Compass Travel employees do these tasks View additional details about a trip Scan records in the Trips database table Modify or search for records in t...

Страница 79: ...essing additional information about trips that meet the search criteria Main Application page In the beginning of this lesson you will develop the Trip Detail page see the following figure Later in th...

Страница 80: ...that you will build After you complete the Trip Maintenance application in this tutorial you will use this Trip Detail page in several ways You can call the TripDetail page directly by typing in the a...

Страница 81: ...ROM TRIPS cfif IsDefined URL ID WHERE tripID URL ID cfif cfquery 3 To output the results from the query append the following cfoutput code after the code you added in step 2 cfoutput query TripQuery i...

Страница 82: ...HTML code before the cfoutput query TripQuery line html head title Trip Maintenance View Record title head body 6 Insert the ending body and html tags at the end of the page body html 7 Save the file...

Страница 83: ...t back from the database In the Trip Detail page we want to only show a single row at a time therefore maxRows is set to 1 cfif IsDefined URL ID WHERE tripID URL ID cfif The URL ID specifies a paramet...

Страница 84: ...ge displays the relevant trip information The following code builds the correct WHERE clause supporting this behavior cfif IsDefined URL ID WHERE tripID URL ID cfif If a user called the Trip Detail pa...

Страница 85: ...LEncodedFormat tripID tripName a Note The URLEncodedFormat is a ColdFusion function that returns a URL encoded string Spaces are replaced with 20 and nonalphanumeric characters with equivalent hexadec...

Страница 86: ...divisible by 2 As described in Chapter 2 CFML Basics on page 15 ColdFusion offers a modulus function MOD that returns the remainder modulus after a number is divided by a divisor for example 10 MOD 3...

Страница 87: ...p Detail page dollar and date fields open the tripdetail cfm in the my_app directory in your editor and make the following changes 2 To format the currency and date fields on the Trips Search Results...

Страница 88: ...apter 7 Lesson 3 Creating a Main Application Page The Trip Search Results page appears 5 In the Trip Search Result page click the link for Riding the Rockies The properly formatted Trip Detail page ap...

Страница 89: ...gation buttons to browse database The drill down search function developed in the last exercise is very useful when the user knows some search criteria to enter Unfortunately however flipping back and...

Страница 90: ...he form code This is desirable because the action page must have the current record ID in order to build the query that navigates to the appropriate record in the trips database table 3 Save the file...

Страница 91: ...ntenance buttons to the Trip Detail page To add maintenance buttons 1 In your editor open tripdetail cfm from my_app subdirectory 2 Enter the following code immediately after the cfoutput query TripQu...

Страница 92: ...ng Your First ColdFusion Application into a drill down facility for trip queries You built a Trip Detail page to show more information about a particular trip You also formatted the Trip Search Result...

Страница 93: ...functions introduced in this lesson The following table identifies the ColdFusion tags and functions that you use in this lesson to enhance the ColdFusion application Element Type Description cfform...

Страница 94: ...ill create in this lesson Trip Edit page You will create the Trip Edit page see the following figure to add new trips and edit existing trips The page will be launched from the Add and Edit buttons on...

Страница 95: ...abase A fundamental concern therefore is ensuring that the captured data is suitable for the column definitions in the Trips table This type of validation on a single field is often referred to as a s...

Страница 96: ...you code the following cfif statement cfif Form tripName EQ cfoutput Trip Name cannot be blank cfoutput cfif In the previous example the cfif statement tests to see if the value of the form variable...

Страница 97: ...iness rules are passed successfully before the database insert is executed Failure of any one of the rules negates the insert One approach to ensuring that the action page considers each business rule...

Страница 98: ...cfif and cfelse conditional processing tags The code inside the cfif tags only executes when the condition evaluates to True To perform other actions when the condition evaluates to False the cfelse t...

Страница 99: ...and the server If the data is validated on the client then only valid data is posted to the server and traffic is reduced Validating data on the client using ColdFusion form tags An alternative appro...

Страница 100: ...tripeditaction cfm page Finally you will test the form to ensure the client side validation is working correctly To exploit the ColdFusion form tags on the Trip Edit page 1 Open the tripedit cfm in t...

Страница 101: ...the single field edits deleted Copy tripeditaction2 cfm in the solutions directory to tripeditaction cfm in the my_app directory 6 When you finish deleting the single field validation rules save the f...

Страница 102: ...ype required Yes message Type of event must be selected option value 1 selected Surfing option option value 2 Mountain Climbing option option value 3 Mountain Biking option cfselect td tr As described...

Страница 103: ...st of event types from the eventtypes table and add validation To display a list of event types from the eventtypes table and add validation 1 View the tripedit cfm page in a browser Select the event...

Страница 104: ...iness rule 6 The trip departure and return dates must be specified for each trip All trip dates must be valid future dates Departure date must precede return date One reason this rule is a good candid...

Страница 105: ...ng in the following format MM DD YYYY This means that months and days require leading zeros Returns True if the date passed is a future date greater than the departure date Returns False if the date p...

Страница 106: ...he date validation by checking that each of the following tasks fail a Enter a date in the past for the departure date field for example 01 01 2000 be sure to remember the leading zeroes b Enter a dep...

Страница 107: ...the FileName passed in cfset fileLocation fileLocation form testFileName cfif FileExists fileLocation cfoutput The test file exists cfoutput cfif cfif cfif Note The trip photo images are stored in the...

Страница 108: ...ed photos or cfset PhotoLocation webserverdocroot cfdocs getting_started photos 3 Save the page and test it by opening the tripedit cfm page in your browser Testing recommendations a In the Trip Edit...

Страница 109: ...the main application page to browse the trips table Use SQL DELETE to delete rows in the trips table Use cflocation to link the search facility built in Lesson 2 Writing Your First ColdFusion Applica...

Страница 110: ...ation pages Notice that the Trip Detail page is at the center of the Trip Maintenance application Depending on the user action the Trip Detail page navigates the records in the database or connects to...

Страница 111: ...ally by ID There can be missing IDs where trips were deleted Therefore if the current trip ID is 1 and the user clicks the next navigation button it will not navigate to 2 In order to ensure that the...

Страница 112: ...able Form RecordID in the navigation action page for building the proper test in the WHERE clause of the SQL SELECT statement The following code from navigationaction cfm processes the navigation butt...

Страница 113: ...er of rows returned cannot exceed 1 maxRows 1 SELECT tripID FROM trips The SQL SELECT statement will always start with SELECT tripID FROM trips cfif IsDefined Form btnPrev X WHERE tripID Form RecordID...

Страница 114: ...only for the button that the user clicked Therefore the IsDefined function is used to test which action to take The following code is an excerpt from the maintenanceaction cfm page that tests which ac...

Страница 115: ...when issuing the SQL DELETE The RecordID field holds the tripID Therefore using the hidden RecordID input tag from the Trip Detail page the following SQL statement deletes a row from the Trips table...

Страница 116: ...k 10 In the Trip Detail page click Delete to remove this record from the Trip database file 11 Verify that the trip record was removed from the Trips database by repeating the search in step 7 Summary...

Страница 117: ...odify the Trip Edit page to link the add and update functions to the main application page Write code to insert new trips using SQL Write code to insert new trips without using SQL Write code to updat...

Страница 118: ...the Browsing and Maintenance Database Functions you built a Trip Edit page to collect the data Now you can modify the Trip Edit action page to insert the data into the database There are two approache...

Страница 119: ...uring Your Development Environment on page 37 a data source stores information about how to connect to an indicated data provider such as a relational database management system The data source you es...

Страница 120: ...urnDate Form price Form tripLeader Form photo Form baseCost Form numberPeople Form depositRequired cfquery UNIX users using PointBase Insert the new trip record into the Compass Travel Database Use lo...

Страница 121: ...e then click Save After the new trip is written to the database the following message appears Trip is added 5 To verify that the save worked open tripsearch cfm in the my_app directory in your browser...

Страница 122: ...pears 8 Click the link to the NH White Mountains to display the details of the trip you just added Verify that all the fields were saved correctly The following page appears 9 Click the Delete button...

Страница 123: ...database You will replace the SQL INSERT statement with the cfinsert tag Code Explanation cfquery name AddTrip datasource CompassTravel Using the datasource attribute cfquery connects to the data sour...

Страница 124: ...ot specified all fields in the form are included in the operation All the fields of the tripedit cfm page have corresponding columns in the Trips table so you can omit the FormFields attribute for bot...

Страница 125: ...et PhotoLocation opt cfusionmx wwwroot cfdocs getting_started photos or cfset PhotoLocation webserverdocroot cfdocs getting_started photos 4 Save the file as tripeditaction cfm in the my_app directory...

Страница 126: ...st numberPeople depositRequired tripID FROM trips cfif IsDefined URL ID WHERE tripID ID cfif cfquery Set the local variables cfset tripName TripQuery tripName cfset eventType TripQuery eventType cfset...

Страница 127: ...query name TripQuery datasource CompassTravel maxrows 1 SELECT tripName eventType tripDescription tripLocation departureDate returnDate price tripLeader photo baseCost numberPeople depositRequired tri...

Страница 128: ...s as follows UPDATE table_name SET column_name new_value WHERE column_name some_value Consider a database table named Clients that contains information about people in the following rows After the fol...

Страница 129: ...and the ColdFusion cfquery tag To update multiple database rows using SQL UPDATE with cfquery 1 In an editor open a new page and save it as priceincrease cfm in the my_app directory 2 Remove any line...

Страница 130: ...ave completed the Getting Started tutorial You should understand how you can combine CFML and SQL to develop powerful applications When compared with traditional development methods ColdFusion helps s...

Страница 131: ...46 described 12 13 introduction 11 14 markup language See CFML using with Macromedia Flash MX 14 ColdFusion MX Administrator accessing 39 debugging options 42 described 13 ColdFusion pages browsing 1...

Страница 132: ...I IsDefined function defined 77 J J2EE configuration 17 M Macintosh OS X data source 39 saving CFM pages 17 Macromedia ColdFusion MX See ColdFusion MX Macromedia Dreamweaver MX configuring 46 defined...

Страница 133: ...plication requirements 50 creating main application page 77 92 data requirements 51 database configuration 39 database design 52 enforcing business rules 93 108 file structure described 38 implementin...

Страница 134: ...134 Index...

Отзывы:

Нет отзывов