This article, the second of two parts, looks at the different database applications available for use with ColdFusion. It is excerpted from chapter two of the book ColdFusion Web Development with Dreamweaver MX 2004, written by Jen and Peter de Haan et al. (Apress; ISBN: 1590592379).
Databases and Dreamweaver MX 2004, concluded - Using Dreamweaver MX 2004ís Advanced Recordset Interface (Page 3 of 5 )
The advanced mode in Dreamweaver allows you to customize SQL syntax and write complex queries that can select data from more than one table. To switch to advanced mode, simply click on the Advanced button in the top right of the Recordset dialog box. The advanced layout of the Recordset dialog box is shown in Figure 2-12.
Figure 2-12. The Advanced Recordset dialog box
At the bottom of the window is the Database Items tree, which shows any existing tables, views, and stored procedures for the selected data source.
We cover views and stored procedures in Chapter 6, but for now just expand the Tables tree by clicking on its plus symbol. You should see three database tables. You can expand these as well, as shown in Figure 2-13, to see the columns for each of these tables.
Figure 2-13. You can expand the Database Items tree to view the structure of the database.
If you followed the previous example and clicked Advanced, you would see the following SQL code, which Dreamweaver generated when you selected your options in the simple Recordset dialog, as shown in Figure 2-14.
Figure 2-14. Dreamweaver automatically generates this SQL code.
The SQL statements shownSELECT the Dept_ID and Dept_Name from the Departmt table for all records where the location equals Cambridge, and then sort any matching records by department name in ascending order.
TIP It is important to note that SQL uses single quotation marks around strings. If you accidentally use double quotation marks, SQL will throw an error. Numbers and Booleans do not need single quotation marks around them.
Notice that in theORDER BYstatement, department name in ascending order is specified. By default, SQL orders all columns in ascending order, so you could shorten this line toORDER BYDept_Name and produce exactly the same results. You can verify this by clicking on Test. To reverse the sort order, add theDESC(descending) keyword after the column name.
Letís create a few queries in advanced mode before moving on to writing SQL from scratch.