Using Dreamweaver MX to Build Query Statements
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
In ColdFusion, you use SQL inside a CFQUERY statement. The CFQUERY tag supplies a name for the recordset created by the SQL statement as well as the ColdFusion data source name for the query. As the following example shows, the CFQUERY tag encapsulates your SQL:
<cfquery name="lastNameQuery" datasource="exampleapps"> SELECT LastName FROM tblEmployees </cfquery>
As you can see, the CFQUERY tag creates a name for the recordset created by the SQL, lastNameQuery, and specifies the database to use, exampleapps.
In Dreamweaver MX, you use the Recordset dialog box, accessible from the Bindings or Server Behaviors panel in the Application panel group to create SQL queries. To open the Recordset dialog box, click the plus (+) button in the Bindings or Database panel. In the submenu that appears, select Recordset (Query).
In the Recordset dialog box, shown in Figure 1, you build the query using a set of menus and text boxes. In the Name text box, you specify the name for the recordset, which translates to the name attribute of the CFQUERY tag. In the Data Source menu, you select the ColdFusion data source to use, which translates to the datasource attribute of the CFQUERY tag. If required, the User Name and Password text boxes let you enter security credentials to the database.
)
Figure 1 The Recordset dialog box lets you create SQL queries using menus
and text boxes. You'll notice that what you select in one control
dynamically changes the values in other controls.
In the Table menu, you select the table to query, which translates to the FROM SQL statement. In the Columns section, you can select some or all of the columns in a table, which translates to the SELECT SQL statement. The Filter menus and text boxes let you construct simple WHERE statements, and the Sort menu lets you specify a column to sort by.
To test the SQL before you create the recordset, click the Test button. In the Test SQL Statement dialog box, shown in Figure 2, Dreamweaver runs the query and displays the results. This feature is especially helpful when developing more complex SQL statements.
)
Figure 2 The Test SQL Statement dialog box displays the results of your
SQL, letting you quickly edit and test SQL statements during development.
To create queries that use multiple tables, you must use the Advanced Recordset dialog box. To switch to the Advanced layout, click the Advanced button in the Recordset dialog box.
In the Advanced Recordset dialog box, shown in Figure 3, you can write your SQL by hand in the SQL text box, or you can use the Database Items section and the SELECT, WHERE, and ORDER BY buttons to build the SQL visually.
You can also specify page parameters in your SQL. Parameters are variables passed from external sources, such as other ColdFusion pages, Flash movies, application and session scope variables, and so on.
)
Figure 3 The Recordset dialog in Advanced mode provides controls for
writing SQL by hand or building SQL visually using the Database Items tree
control and the WHERE, SELECT, and ORDER BY buttons.