SQL Designer

Applies to TestComplete 14.70, last modified on April 22, 2021

The SQL Designer is used to explore and create SQL queries for database checkpoints and DB Table variables. Using the SQL Designer, you can also view the names of tables and fields with which a checkpoint or variable will work, and create queries visually.

Design a Query for a Database Checkpoint

To specify connection parameters and properties of a database checkpoint, you use the Checkpoint wizard. The SQL Designer is called when you click Design on the Enter SQL Query page of one of this wizard.

Design a Query for a DB Table Variable

To specify connection parameters and properties of a DB Table variable, you use the Add DB Table Variable wizard. The SQL Designer is called when you click Design Query on the Specify Query page of one of this wizard.

Below is a sample image of the Designer’s window:

SQL Designer

Panels

The Designer contains the following panels:

  • Tables - Displays the tables and views that exist in the database. Also, it shows the name of a schema they belong to.

  • SQL - Contains the SQL text of the created query. This text is automatically updated when you modify the query in the Designer. You can also type the text into this panel.

  • Results - Displays the results of the query execution.

  • Working area - This panel is not labeled. It displays the tables and views that were included into the query.

The panel layout is not strictly defined. You can dock or undock the panels within the Designer window, change their width or height and perform other operations in the same manner as you would with panels within the TestComplete main window (see Docking). If you hide a panel, you can display it by selecting the appropriate item from the Designer’s toolbar or from the Docking menu.

Creating a Query

You create a query in the following way:

  1. Add the desired tables or views from the Tables panel to the working area.

  2. Select the fields to be used in the query.

  3. Create joins between the tables.

  4. Execute the query and check the returned data.

The following sections provide detailed descriptions of how you can perform these operations:

To add or remove a table or view

To specify an alias for a table or view

To specify the fields for the query

To specify an alias for a field

To join tables

To set the join properties

To execute the query

To specify the selecting, sorting and grouping conditions

To save the changes made to the query in the Designer, choose File > Save and close from SQL Designer’s main menu or press Save and close from the toolbar.

To close the designer without saving the changes, select File > Exit.

Specifics

When you open a query in the Designer, the Designer parses the query code and builds a visual scheme in the work area. If the Designer cannot build a visual scheme, TestComplete displays an error message and the designed is not shown.

SQL Designer also parses the query code when you change it in the SQL panel. If the code was parsed successfully, the Designer updates the visual scheme. If the parsing fails, Designer displays an error message notifying you about the problem.

The Designer only supports specific types of queries:

  • The Designer supports creation of SELECT queries only. It does not support the queries of the UPDATE, DELETE or INSERT types.

  • The Designer does not support visual creation of complex SELECT queries that include nested queries. However, you can create these queries by typing their code in the SQL panel.

  • Tables in the query must be joined. The Designer’s parser does not support the queries that broke this condition.

  • The Designer’s parser does not support SQL statements and clauses that are specific to database providers. For instance, the parser does not support clauses specific to Microsoft SQL Server or Oracle database engines.

  • The query cannot contain the fields, tables or views, whose names coincide with clauses of SQL SELECT command. For instance, the query cannot contain the fields From or Select or a table name Where. A possible workaround is to create an alias for these fields, tables and views.

See Also

Select Database Table Wizard
Select Database Table Wizard - Enter SQL Query Page
Add DB Table Variable Wizard
Specify Query Page (DB Table Variable Wizards)
About DBTables Collection
About Database Checkpoints

Highlight search results