WebReference.com - Chapter 4 from The Joy of Dreamweaver MX, from Osborne/McGraw-Hill (7/8)

[previous] [next]

The Joy of Dreamweaver MX: Chapter 4

Using SQL Builder

Although Dreamweaver's advanced Recordset dialog box provides some options for building SQL SELECT statements, it's not much use when it comes to creating queries that use joins. To simplify the creation of an advanced query, we'll use the SQL Builder tool included with HomeSite+.

In HomeSite+, select the Edit tab and choose Tools | SQL Builder. This opens the Select Database Query dialog box. Choose your RDS server from the drop-down menu and expand the connGlobal data source. Select the LOCATIONS table and click New Query.

The interface of SQL Builder is similar to Query Design view in Microsoft Access, and Query Designer in SQL Server. The three panes, from top to bottom, are the Table pane, the Properties pane, and the SQL pane.

Since we selected the LOCATIONS table in the first dialog box, it already appears in the Table pane. To add the REGION table, click the Add Tables icon on the SQL Builder toolbar. Select REGION and click Add. Click Done to close the Add Tables or Views dialog box. (Alternatively, you can right-click inside the Table pane and choose Add Table | REGION.)

As you may recall from Chapter 3, the REGION and LOCATIONS tables are related by matching columns (REGION.ID is the primary key and LOCATIONS.REGION_ID is the foreign key). To create the join--in this case, an equijoin--select the ID column in the REGION table and drag and drop it onto the REGION_ID column in the LOCATIONS table. A join line appears between the two tables. If you mouse over the join line, you'll see a tooltip indicating that REGION.ID = LOCATIONS.REGION_ID.

To select the data returned by the query, double-click the following columns in the Table pane, or use the drop-down menus in the Properties pane: LOCATIONS.LOCATION_NAME, LOCATIONS.ADDRESS, LOCATIONS.CITY, LOCATIONS.STATE_COUNTRY, REGION.NAME. The SQL pane updates to reflect each selection.

Click the Copy SQL To Clipboard icon on the SQL Builder toolbar. To exit SQL Builder, click the red "X" and click No when prompted to save and insert the new query. Click Cancel to dismiss the Select Database Query dialog box.

This returns you to Chapter4.cfm in HomeSite+. Highlight the SQL SELECT statement on line 2--SELECT * FROM LOCATIONS--and press CTRL-V to paste the query we just copied to the clipboard.

SELECT   LOCATIONS.LOCATION_NAME,LOCATIONS.ADDRESS,
         LOCATIONS.CITY,LOCATIONS.STATE_COUNTRY,REGION.NAME
FROM     LOCATIONS,REGION
WHERE    REGION.ID=LOCATIONS.REGION_ID

Press CTRL-S to save Chapter4.cfm and exit HomeSite+. In Dreamweaver MX, click Yes when prompted to reload the file. Open the Bindings panel and double-click the rsLocations recordset. The advanced Recordset dialog box opens because the query cannot be represented in Simple mode.

Click Test. Instead of numbers, the query returns geographical locations. Click OK to close the Test SQL Statement dialog box. Click OK again to close the Recordset dialog box.

Congratulations! You did it. Now you can take advantage of SQL Builder in HomeSite+ to create advanced queries.

[previous] [next]

The Joy of Dreamweaver MX, By Paul Newman
Copyright (c) 2002 by The McGraw-Hill Companies

Created: September 16, 2002
Revised: September 16, 2002

URL: http://webreference.com/authoring/languages/html/joydreamweaver/chap4/7.html