Accessing Databases
| In the past few chapters, you created and executed ColdFusion templates. You worked with different variable types, conditional processing, code reuse, and more. But this chapter is where it starts to get really interesting. Now it's time to learn how to connect to databases to create complete dynamic and data-driven pages. NOTE The examples in this chapter, and indeed all the chapters that follow, use the data in the ows data sources and database. These must be present before continuing. And I'll remind you just this once, all the files created in this chapter need to go in a directory named 10 under the application root (the ows directory under the Web root). For your first application, you will create a page that lists all movies in the Films table. Static Web PagesBefore you create your first data-driven ColdFusion template, let's look at how not to create this page. Listing 10.1 contains the HTML code for the movie list Web page. The HTML code is relatively simple; it contains header information and then a list of movies, one per line, separated by line breaks (the HTML <br> tag). Listing 10.1. movies.htmHTML Code for Movie List<html> <head> <title>Orange Whip Studios - Movie List</title> </head> <body> <h1>Movie List</h1> Being Unbearably Light<br> Charlie's Devils<br> Closet Encounters of the Odd Kind<br> Folded Laundry, Concealed Ticket<br> Forrest Trump<br> Four Bar-Mitzvahs and a Circumcision<br> Geriatric Park<br> Gladly Ate Her<br> Ground Hog Day<br> Hannah and Her Blisters<br> Harry's Pottery<br> It's a Wonderful Wife<br> Kramer vs. George<br> Mission Improbable<br> Nightmare on Overwhelmed Street<br> Raiders of the Lost Aardvark<br> Silence of the Clams<br> Starlet Wars<br> Strangers on a Stain<br> The Funeral Planner<br> The Sixth Nonsense<br> Use Your ColdFusion II<br> West End Story<br> </body> </html> Figure 10.1 shows the output this code listing generates. Figure 10.1. You can create the movie list page as a static HTML file. Dynamic Web PagesWhy is a static HTML file not the way to create the Web page? What would you have to do when a new movie is created, or when a movie is dropped? What would you do if a movie title or tag line changed? You could directly modify the HTML code to reflect these changes, but you already have all this information in a database. Why would you want to have to enter it all again? You'd run the risk of making mistakesinformation being misspelled, entries out of order, and possibly missing movies altogether. As the number of movies in the list grows, so will the potential for errors. In addition, visitors will be looking at inaccurate information during the period between updating the table and updating the Web page. A much easier and more reliable solution is to have the Web page display the contents of your Films table. This way, any table changes are immediately available to all viewers. The Web page would be dynamically built based on the contents of the Films table. To create your first data-driven ColdFusion template, enter the code as it appears in Listing 10.2 and save it in the 10 directory as movies1.cfm. (Don't worry if the ColdFusion code doesn't make much sense yet; I will explain it in detail in just a moment.) Listing 10.2. movies1.cfmThe Basic Movie List<!--- Name: movies1.cfm Author: Ben Forta (ben@forta.com) Description: First data-driven Web page Created: 12/15/04 ---> <!--- Get movie list from database ---> <cfquery name="movies" datasource="ows"> SELECT MovieTitle FROM Films ORDER BY MovieTitle </cfquery> <!--- Create HTML page ---> <html> <head> <title>Orange Whip Studios - Movie List</title> </head> <body> <h1>Movie List</h1> <!--- Display movie list ---> <cfoutput query="movies"> #MovieTitle#<br> </cfoutput> </body> </html> Now, execute this page in your browser as http://localhost:8500/ows/10/movies1.cfm TIP As a reminder, the port number (8500 in the above URL) is only needed if you are using the integrated HTTP server. If you are ColdFusion with an external HTTP server then don't specify the port. The results are shown in Figure 10.2. Figure 10.2. Ideally, the movie list page should be generated dynamically, based on live data. TIP You could also browse the page right from within Dreamweaver as seen in Figure 10.3. To do this, switch to Design View (click the Show Design View button, or select Design from the View menu) and turn on Live Data View (click the Live Data View button, select Live Data from the View menu, or press Ctrl-Shift-R). Figure 10.3. ColdFusion pages may be browsed directly within Dreamweaver by switching to Design View with Live Data View enabled. Understanding Data-Driven TemplatesNow compare Figure 10.1 to Figure 10.2. Can you see the difference between them? Look carefully. Give up? The truth is that there is no difference at all (well, other than the file extension in the URL, that is). The screen shots are identical, and if you looked at the HTML source that generated Figure 10.2, you'd see that aside from a lot of extra white space, the dynamically generated code is exactly the same as the static code you entered in Listing 10.1 and nothing like the (much shorter) dynamic code you entered in Listing 10.2. How did the code in Listing 10.2 become the HTML source code that generated Figure 10.1? Let's review the code listing carefully. The <cfquery> TagListing 10.2 starts off with a comment block (as should all the code you write). Then comes a ColdFusion tag called <cfquery>, which submits a SQL statement to a specified data source. The SQL statement is usually a SQL SELECT statement, but it could also be an INSERT, an UPDATE, a DELETE, a stored procedure call, or any other SQL statement.
The <cfquery> tag has several attributes, or parameters, that are passed to it when used. The <cfquery> in Listing 10.2 uses only two attributes:
The query name you specified is movies. This name will be used later when you process the results generated by the query. CAUTION Don't use reserved words (words that have special meaning to ColdFusion) as your query name. For example, don't name a query URL,as URL is a reserved prefix. NOTE Query names passed to <cfquery> need not be unique to each query within your page. If you do reuse query names, subsequent <cfquery> calls will overwrite the results retrieved by the earlier query. You specified ows for the datasource attribute, which is the name of the data source created earlier. datasource is required; without it ColdFusion would not know which database to execute the SQL statement against. The SQL statement to be executed is specified between the <cfquery> and </cfquery> tags. The following SQL statement was used, which retrieves all movie titles sorted alphabetically: SELECT MovieTitle FROM Films ORDER BY MovieTitle TIP The SQL statement in Listing 10.2 is broken up over many lines to make the code more readable. Although it's perfectly legal to write a long SQL statement that is wider than the width of your editor, these generally should be broken up over as many lines as needed. ColdFusion pays no attention to the actual text between the <cfquery> and </cfquery> tags (unless you include CFML tags or functions, which we'll get to later in this chapter). Whatever is between those tags gets sent to the data source for processing. When ColdFusion encounters a <cfquery> tag, it creates a query request and submits it to the specified data source. The results, if any, are stored in a temporary buffer and are identified by the name specified in the name attribute. All this happens before ColdFusion processes the next line in the template. NOTE You'll recall that ColdFusion tags (including the <cfquery> tag) are never sent to the Web server for transmission to the browser. Unlike HTML tags, which are browser instructions, CFML tags are ColdFusion instructions. NOTE ColdFusion doesn't validate the SQL code you specify. If syntax errors exist in the SQL code, ColdFusion won't let you know because that's not its job. The data source will return error messages if appropriate, and ColdFusion will display those to you. But it's the data source (and the database or database driver) that returns those error messages, not ColdFusion. It's important to note that, at this point, no data has been displayed. <cfquery> retrieves data from a database table, but it doesn't display that data. Actually, it does nothing at all with the datathat's your job. All it does is execute a specified SQL statement when the </cfquery> tag is reached. <cfquery> has no impact on generated content at all, and retrieved data is never sent to the client (unless you send it). The next lines in the template are standard HTML tags, headers, title, and headings. Because these aren't ColdFusion tags, they are sent to the Web server and then on to the client browser. Using <cfoutput> to Display <cfquery> DataNext, the query results are displayed, one row per line. To loop through the query results, the <cfoutput> tag is used. <cfoutput> is the same ColdFusion output tag you used earlier (in Chapter 8, "Using ColdFusion"). This time, however, you use it to create a code block that is used to output the results of a <cfquery>. For ColdFusion to know which query results to output, the query name is passed to <cfoutput> in the query attribute. The name provided is the same that was assigned to the <cfquery> tag's name attribute. In this case, the name is movies. CAUTION The query name passed to <cfquery> must be a valid (existing) query; otherwise, ColdFusion will generate an error. The code between <cfoutput query="movies"> and </cfoutput> is the output code block. ColdFusion uses this code once for every row retrieved. Because 23 rows are currently in the Films table, the <cfoutput> code is looped through 23 times. And any HTML or CFML tags within that block are repeated as wellonce for each row. NOTE So what is the minimum number of times a <cfoutput> code block will be processed? It depends on whether you are using the query attribute. Without a query, the code block is processed once. With a query block, it's processed once if a single row exists in the query, and not at all if the query returned no results. TIP You'll notice that I put the SQL query at the very top of the page instead of right where it was needed (in the middle of the output). This is the recommended way to write your codequeries should be organized at the top of the page, all together. This will help you write cleaner code and will also simplify any testing and debugging if (or rather, when) the need arises. Using Table ColumnsAs explained in Chapter 8, ColdFusion uses # to delimit expressions and variables. ColdFusion expressions also can be columns retrieved by a <cfquery>. Whatever column name is specified is used; ColdFusion replaces the column name with the column's actual value. When ColdFusion processed the output block, it replaced #MovieTitle# with the contents of the MovieTitle column that was retrieved in the movies query. Each time the output code block is used, that row's MovieTitle value is inserted into the HTML code. ColdFusion-generated content can be treated as any other content in an HTML document; any of the HTML formatting tags can be applied to them. In this example, the query results must be separated by a line break (the <br> tag). Look at the following line of code: #MovieTitle#<br> That first row retrieved is movie Being Unbearably Light, so when processing the first row the above code will generate the following: Being Unbearably Light<br> Figure 10.2 shows the browser display this template creates. It's exactly the same result as Figure 10.1, but without any actual data in the code. The output of Listing 10.2 is dynamically generatedeach time the page is refreshed, the database query is executed and the output is generated. NOTE Want to prove this for yourself? Open the database and make a change to any of the movie titles and then refresh the Web pageyou'll see that the output will reflect the changes as soon as they are made.
The Dynamic AdvantageTo see the real power of data-driven pages, take a look at Listing 10.3. This is the same code as in Listing 10.2, but a column has been added to the SQL statement (retrieving PitchText as well now) and the output has been modified so that it displays both the MovieTitle and PitchText columns. Save this file as movies2.cfm (you can edit movies1.cfm and use the Save As option (in the File menu) to save it as movies2.cfm, if you find that easier). Now, execute this page in your browser as follows: http://localhost:8500/ows/10/movies2.cfm TIP Again, drop the port if not using the internal HTTP server. Listing 10.3. movies2.cfmThe Extended Movie List<!--- Name: movies2.cfm Author: Ben Forta (ben@forta.com) Description: Retrieving multiple database columns Created: 12/15/04 ---> <!--- Get movie list from database ---> <cfquery name="movies" datasource="ows"> SELECT MovieTitle, PitchText FROM Films ORDER BY MovieTitle </cfquery> <!--- Create HTML page ---> <html> <head> <title>Orange Whip Studios - Movie List</title> </head> <body> <h1>Movie List</h1> <!--- Display movie list ---> <cfoutput query="movies"> <strong>#MovieTitle#</strong><br> #PitchText#<p> </cfoutput> </body> </html> As you can see, two table columns are now used, each delimited by number signs. The MovieTitle is displayed in bold (using <strong> and </strong> tags) and is followed by a line break; on the next line PitchText is displayed followed by a paragraph break. So, for the first row displayed, the previous code becomes <strong>#MovieTitle#</strong><br> #PitchText#<p> Compare that to what you'd have had to change in movies.htm to update a static page to look like Figure 10.4, and you'll start to appreciate the dynamic page advantage. Excited? You should be. Welcome to ColdFusion and the wonderful world of dynamic data-driven Web pages! |
See Chapter 6, "Introducing SQL," for an overview of data sources, SQL and SQL statements.
If you are thinking that constantly rereading the database tables seems unnecessary and likely to impact performance, youre right. Chapter 25, "Improving Performance," teaches tips and techniques to optimize the performance of data-driven sites.
EAN: N/A
Pages: 282
- Integration Strategies and Tactics for Information Technology Governance
- Assessing Business-IT Alignment Maturity
- Linking the IT Balanced Scorecard to the Business Objectives at a Major Canadian Financial Group
- A View on Knowledge Management: Utilizing a Balanced Scorecard Methodology for Analyzing Knowledge Metrics
- Governance Structures for IT in the Health Care Industry
- MPLS Control and Data Plane Components
- OSPF PE-CE Routing Protocol Overview, Configuration and Verification
- BGP PE-CE Routing Protocol Overview, Configuration, and Verification
- Case Study 2: Implementing Multi-VRF CE, VRF Selection Using Source IP Address, VRF Selection Using Policy-Based Routing, NAT and HSRP Support in MPLS VPN, and Multicast VPN Support over Multi-VRF CE
- Case Study 5: Implementing Dynamic Layer 3 VPNs Using mGRE Tunnels