Macromedia Studio 8 [Electronic resources] : Training from the Source

Introducing SQL Queries

Now that you've configured your database on the server and enabled ColdFusion to see it, you're ready to start using this data in your site. In the last two tasks of this lesson, you'll retrieve data from the database and then output it into a table. To access the data from the database, the ColdFusion document needs to communicate with the database. But there's a catch: databases don't understand ColdFusion Markup Language (CFML).

The language that modern databases understand is SQL, which is pronounced "sequel" and stands for Structured Query Language. ColdFusionlike ASP, PHP, and JSPuses SQL to obtain information from a database, just like a database programmer would. After that data is obtained, it can be manipulated and/or displayed on the page. Serious application developers need to learn quite a bit of SQL to build powerful applications. But thanks to Dreamweaver's dialog-based ways to construct SQL statements (the scripts used to access databases and the information stored in them), nonprogrammers and SQL newbies can construct SQL queries fairly easily. In this task, you'll create a bona fide SQL statement using a Dreamweaver dialog that will mask what you're doing.

You'll eventually output database data into this page. Before you can do that, you'll have to configure Dreamweaver to see ColdFusion DSNs. You'll need to configure Dreamweaver only once per site, and you can have any page within the site open when you do it.

If you're working on a machine that's running ColdFusion locally, you should enter the RDS password you entered when you installed ColdFusion.

Note

You can reset the RDS password in the Security section of the ColdFusion Administrator page.

If you're accessing files over a network (if you had your network administrator create a DSN), you should enter the RDS password you requested from your administrator earlier in this lesson.

After you enter the password, you might have to wait a few moments before you return to the Application panel. When you do, go to the Database tab and choose the dante database.

Dreamweaver's Databases panel clearly provides convenient access to your data. Not only can you see the database schema (the structure of the tables and their fields), but you can even view its data right in Dreamweaver.

[View full size image]

Now that Dreamweaver is seeing your data, you can start using the data. One of the most common tasks in a dynamic Web application is to retrieve a collection of data. This data might be all the data in a table, a particular subset of data from a table, or even a collection of data from more than one table. When you retrieve a collection of data from a database, it's stored in a recordset, which is a structured collection of data returned from a database and stored in the computer's memory. Once that data is in memory, you can output it into your ColdFusion pages.

Displaying data is a two-step process. In the first step, you retrieve it in the form of a recordset. In the second step, you output the data into a Web page. The first step is accomplished using Dreamweaver's Bindings panel.

You use the Bindings panel as a resource for adding dynamic data to a page. In Lesson 13, you used the Bindings panel to specify a form variable (Form.firstName). This time, you're going to create a recordset.

The recordset you create will contain all the data in the users table, except each user's password and the primary key (unique ID).

Remember, the recordset is the collection of data that is returned. The query is the SQL script that requests the information that makes up the recordset.

Because database tables can get quite large, with thousands of records (or rows), you don't want to pull all the information out of the database, especially if you intend to use only a small part of it. Overly large recordsets place an additional burden on your server and can slow overall performance. Fortunately, SQL enables you to be very specific about the data you want.

After you have made the selection, the Recordset dialog appears.

The name can be anything you want. Many developers preface their recordset names with

rs_ so they're easily recognized in code.

To build a recordset, you have to specify the data. After you tell ColdFusion which DSN to use, you'll once again have access to all the tables and fields in the dante.mdb database, just as you did in the Insert Record dialog.

In this step, you're telling ColdFusion to build the recordset from only data in the username, firstName, and lastName fields. All the data in the other fields is ignored.

These settings tell ColdFusion how to sort the information in the recordset. In this case, you're sorting by last name.

The final dialog should appear as in the following figure.

[View full size image]

Clicking the Test button causes Dreamweaver to show the results of the recordset you're building. This is a great way to test your SQL query without having to save the page, upload it, and test it.

You should see 13 records, listed alphabetically by last name.

[View full size image]

The Bindings tab now lists Recordset (rs_GetUsers). If you expand the recordset, you'll see username, firstName, and lastName listed with lightning-bolt icons.

One important concept to understand about this new recordset is that just because you made it available

to the page doesn't mean it's visible

on the page. If you were to test the page right now, the query would run and the data would be in memory, but the resulting page would still be blank.

You'll see the following code:

<cfquery name="rs_GetUsers" datasource="dante">
SELECT username, firstName, lastName
FROM users
ORDER BY lastName ASC</cfquery>

Sandwiched between the <cfquery> tags is the SQL statement you built in the Recordset dialog. The <cfquery> tags, as you've probably guessed, tell ColdFusion to send a query to the database. The query is the SQL statement. The recordset's name and the DSN are both passed as parameters of the <cfquery> statement.

As we mentioned at the beginning of this lesson, the beauty of DSNs is that you can change their particulars all you want (for example, if you migrate to a different database or server) without breaking any pages, because the pages reference only the DSN name itself.

[View full size image]