Adobe ColdFusion 8

Writing queries by using an editor

Dreamweaver and HomeSite+ provide a graphical user interface (GUI) for writing and executing queries. A GUI is useful for developing and testing your queries before you insert them into a ColdFusion application. For more information about these GUIs, see the documentation in your specific tool.

Writing queries using Dreamweaver

You define a query by using the Dreamweaver Recordset dialog box, which lets you create a record set without manually entering SQL statements. Defining a record set with this method can be as easy as selecting a database connection and table from the pop-up menus.

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 Document window, open the page that will use the record set.
  2. Select 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 by using HomeSite+

HomeSite+ includes the combined features of HomeSite 5 and ColdFusion Studio 5, with additional support for new ColdFusion tags. HomeSite+ supports 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.

Open SQL Builder

 Do one of the following:

The SQL Builder interface

The following image shows the SQL Builder interface:

A. Toolbar B. Table Pane C. Properties Pane D. SQL Pane

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, because this is the most common type of query. SQL Builder supports the following four types of SQL statements:

  • Select (default)
  • Insert
  • Update
  • Delete