Writing Queries Using an Editor

As an alternative to writing SQL statements, Dreamweaver MX, ColdFusion Studio, and HomeSite+ provide a GUI for writing and executing queries. The GUI helps you develop and test your queries before inserting them into an application.

Writing Queries Using Dreamweaver MX

This section describes how to define a query using the Dreamweaver MX Recordset dialog box. This dialog box allows you to create a recordset without entering SQL statements manually. Since this method is menu-driven, it's easy to define a recordset using it. You can select a database connection and table from the pop-up menus.

To define a recordset without writing SQL statements, perform the following steps:

1. In the Dreamweaver Document window, open the page that will use the recordset.

2. To open the Data Bindings panel, select Window, Data Bindings.

3. In the Data Bindings panel, click Plus (+) and choose Recordset from the pop-up menu. The Recordset dialog box appears.

4. To fill information in the Recordset dialog box, click Test. This executes the query and ensures the retrieval of the information you need.

5. If you've defined a filter that uses parameters that are input by end users, clicking Test will display the Test Value dialog box. Enter a value in this text box and click OK. If an instance of the recordset has been created successfully, a table will display the data from your recordset.

6. Click OK to add the recordset to the list of available content sources in the Data Bindings panel.

7. To write your own SQL statements or create queries more complex than the ones allowed by the Simple Recordset dialog box, define recordsets using the Advanced Recordset dialog box.

Using ColdFusion Studio's SQL Builder

You can build an SQL query without leaving the Integrated Development Environment (IDE). SQL Query Builder, a part of ColdFusion Studio, allows you to view the database schema and construct and test SQL operations that are to be included in the templates. Although SQL statements are easier to use, SQL Builder provides more advanced SQL techniques.

You can use SQL Builder to join tables, specify sorting orders, and indicate filtering criteria. It creates SQL statements for you dynamically.

The first step is setting up the connection to the data source. ColdFusion Studio uses the Database Connection Manager in ColdFusion Server to connect to remote data sources. Authentication is required before the connection can be established.

SQL Builder tool enables you to create <CFQUERY> tags and SQL statements through a GUI. To use the SQL Builder:

1. Right-click the data source for which the query needs to be built.

2. Select New Query.

3. Select the desired table.

4. The Query Builder screen appears. Double-click columns from the tables to add them to the query.

5. After the new columns have been selected, the SQL statement is updated to reflect the changes.

6. You can add sorting under the SORT TYPE column. Use the exclamation mark (!) to run this query. The results of the query are displayed.

7. Click Copy CFQUERY to Clipboard.

8. Return to the main Studio Editing screen after closing the window. Name and save the query.