Developing ColdFusion MX Applications with CFML
Introduction to Databases and SQL

Writing queries using an editor

Dreamweaver MX, ColdFusion Studio, and HomeSite+ provide a GUI for writing and executing queries. A GUI is useful for developing and testing your queries before you insert them into a ColdFusion application.

This section contains a brief description of these GUIs. For more information, see the documentation on your specific tool.

Writing queries using Dreamweaver MX

This section describes how to define a query using the Dreamweaver MX Recordset dialog box, which allows you to create a record set without having to manually enter SQL statements. Defining a record set using this method can be as easy as selecting a database connection and table from the pop-up menus.

To define a record set without writing SQL:

1. In the Dreamweaver Document window, open the page that will use the record set.
2. To open the Data Bindings panel, select Window > Data Bindings.
3. In the Data Bindings panel, click the Plus (+) button and choose Recordset (Query) from the pop-up menu.

   The Simple Recordset dialog box appears:

   

4. Complete the dialog box.
5. Click the Test button to execute the query and ensure that it retrieves the information you intended.

   If you defined a filter that uses parameters input by users, the Test button displays the Test Value dialog box. Enter a value in the Test Value text box and click OK. If an instance of the record set is successfully created, a table displaying the data from your record set appears.

6. Click OK to add the record set to the list of available content sources in the Data bindings panel.

If you prefer to write your own SQL statements, or need to create more complex queries then the Simple Recordset dialog box allows, you can define record sets using the Advanced Recordset dialog box

Creating an advanced record set by writing SQL :

1. In the Dreamweaver MX Document window, open the page that will use the record set.
2. Choose Windows > Data Bindings to display the Data Bindings panel.
3. In the Data Bindings panel, click the Plus (+) button and select Recordset (Query) from the pop-up menu.

   If the Simple Recordset dialog box appears, switch to the Advanced Recordset dialog box by clicking the Advanced button.

   The Advanced Recordset dialog box appears:

   

4. Complete the dialog box.
5. Click the Test button to execute the query and ensure that it retrieves the information you intended.

   If you defined a filter that uses parameters input by users, the Test button displays the Test Value dialog box. Enter a value in the Test Value text field and click OK. If an instance of the record set is successfully created, a table displaying the data from your record set appears.

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

Writing queries using ColdFusion Studio and Macromedia HomeSite+

Macromedia HomeSite+ includes the combined features of HomeSite 5 and ColdFusion Studio 5, with additional support for new ColdFusion MX tags. Both HomeSite+ and ColdFusion Studio support SQL Builder for writing queries.

SQL Builder is a powerful visual tool for building, testing, and saving SQL statements for use in application data queries. You can copy completed SQL code blocks directly into your ColdFusion applications.

To open SQL Builder:

Do one of the following:

• Select Tools > SQL Builder from the HomeSite+ or ColdFusion Studio menu, select an RDS server, select a database from the drop-down list, and click New Query.
• In the Database tab, select an RDS server, right-click a database name or a table, and select New Query.
• Open the cfquery tag editor, select an RDS server, and click New Query.

The SQL Builder interface

The following figure shows the SQL Builder interface:

The SQL Builder is divided into the following four sections:

Section	Use
Toolbar	Contains buttons for SQL keywords and commands.
Table pane	Provides a view of the tables in your query and allows you to create joins between tables.
Properties pane	Allows you to set the properties of the query, such as the columns that are being selected or the columns that are being updated.
SQL pane	Shows you the SQL statement as it is built. The SQL pane does not support reverse editing, so any changes you make in this pane will not be made to the query in the Properties pane or the Table pane.

Writing SQL statements

SQL Builder opens a SELECT statement by default, since this is the most common type of query. SQL Builder supports the following four types of SQL statements:

• Select (default)
• Insert
• Update
• Delete

Copyright © 2002, Macromedia Inc. All rights reserved.

Comments