Using SQL Builder for Database Queries

SQL Builder is a powerful visual tool that you can use to build, test, and save SQL statements for use in application data queries. You can copy a SQL code block directly into your application or insert it into a cfquery tag. You can also use SQL Builder to test your queries.

To open SQL Builder, do one of the following:

In the Database tab, right-click a database name or a table and select New Query.

Select Tools > SQL Builder from the menu. In SQL Builder, select a database from the drop-down list, and click New Query.

Open the cfquery tag editor and click the New Query button.
The New Query button looks like this:

In the Select Table dialog box (shown in the following graphic), select the table that you want to query against, and click Select.

The SQL Builder user interface

The following graphic shows the SQL Builder user interface:

The SQL Builder is divided into these sections:

Toolbar Contains buttons for SQL keywords and commands

Table pane Provides a view of the tables in a query and lets you create joins between tables

Properties pane Lets you set the properties of a query, such as the columns to select and the columns to update

SQL pane Displays the SQL statement as it is being constructed.
The SQL pane does not support reverse editing, so any changes that you make in this pane are not made in the Properties pane or the Table pane.

Writing SQL statements

When SQL Builder opens, it displays a SELECT statement by default, since this is the most common type of query. If you have not yet selected a table for your query, you are prompted to select one.

SQL Builder supports these types of SQL statements:

Select Retrieves rows or columns of data from a table

Insert Adds data to a table

Update Changes data in a table

Delete Deletes data from a table

To change the type of SQL statement:

Open SQL Builder.
For instructions, see the opening SQL Builder procedure earlier in this section.

Select a statement type from the SQL Builder toolbar.

Building a SELECT statement

SQL SELECT statements let you specify data from which to build a recordset.

To create a SELECT statement:

Open SQL Builder.
For instructions, see the opening SQL Builder procedure earlier in this section.

Select a table to query.
Do one of the following:
- To add a table to the query, right-click in the Table pane and select Add Table, or click the Add Table button in the toolbar.
- To create a join between tables, drag a column name between tables.
- To delete a join, or to make it an inner or outer join, right-click the join and select the appropriate command.
Drag the columns to display onto the Column section of the Properties pane.
(Optional) Set additional query attributes in the Properties pane.
Save the query or insert it into a page.

To use a variable in a SQL WHERE clause:

Open SQL Builder.
For instructions, see the opening SQL Builder procedure earlier in this section.

In the Properties pane, click in the row for a column, in the Criteria column.
In the Select box, select a variable syntax.
Double-click on a variable placeholder name and enter a value.

To save a query:

Open SQL Builder.
For instructions, see the opening SQL Builder procedure earlier in this section.

On the Query toolbar, click the Save Query button.
In the dialog box that appears, enter a name for the query.
Click Save.

Saved queries are stored on the database server. They can be edited and used by anyone who has access to the server.

Note

ColdFusion Studio does not update the Properties and Table panes when you make changes in the SQL pane. If you edit a SQL statement in the SQL pane and save it, and then modify the Table pane or change any values in the Properties pane, a new SQL statement is generated which overwrites the changes that you made in the SQL pane.

Inserting SQL into a page

This section describes how to insert new and saved queries into a page in the Editor.

To insert a new query, do one of the following:

If you opened SQL Builder from the CFQUERY tag editor, it prompts you to insert the query when you close the editor.

If you opened SQL Builder from a CFML page, it prompts you to insert the query when you close SQL Builder.

To insert just the SQL statement from SQL Builder, click the Copy SQL to Clipboard button on the toolbar and close SQL Builder. ColdFusion Studio prompts you to save the query. Then you can paste the statement into your page.

To insert the SQL statement into a cfquery tag, click the Copy CFQUERY button. Close SQL Builder and paste the query into your page. ColdFusion Studio prompts you to save the query.

To insert a saved query, do one of the following:

In the Resources window, click the Database tab.

Open a data source.
Open the Queries folder in the data source.
Drag and drop the query onto the page in the Editor.

On the CFML Basic QuickBar, click the CFQUERY button.
You can also select CFQUERY from the Tag Chooser.

Enter a name for the query.
Click the Insert Query button.
In the select dialog box, select a server.
In the data source that you want to use, open the Queries folder.
Select a query and click Insert.
In the CFQUERY tag editor, click Apply.
The query appears in the Editor.

Testing and editing queries

You can refine SQL statements after constructing them.

Testing queries

Before inserting a query into an application, we recommend that you test it in SQL Builder. The following procedure explains how to run a query for testing.

Caution

Only test SELECT statements. When you click the Run Query button, the SQL statement is processed. If you run an INSERT, UPDATE, or DELETE statement, the changes that you coded in the SQL statement are made in the data source.

To run a query in SQL Builder:

Click the Run Query button.
The Run Query button looks like this:

ColdFusion Studio prompts you to enter values for the variables in the query.

Editing queries

If you save a query, you can edit it later. However, pages containing the query are not automatically updated. To make the changes take effect, you must reinsert the query into the pages.

To edit a query:

Open the query folder for the data source that you want to use.

Double-click a query.
Make changes in the Table pane and the Properties pane.
Save the updated query.

Note

ColdFusion Studio does not update the Properties and Table panes when you make changes in the SQL pane. If you write a query in the SQL pane and save it, the Properties and Table panes show no changes. But the SQL pane displays the SQL generated from changes made in the Properties and Table panes.

LiveDocs comments are not longer enabled for ColdFusion 5.0. Please use one of the following resources instead.

Version 5.0

Using ColdFusion Studio
Accessing Data Sources