8. Query editor

Query Editor is a customisable tool for viewing and executing SQL statements. Any number of editors can be open at the same time.

Query Editor supports the following functions:

  • Customisable SQL syntax highlighting;

  • Tooltips for keywords and database object names;

  • Execution of multiple queries;

  • Executing and displaying multiple queries with multiple results (Result Set);

  • Output contains information about how table counter values have changed during query execution;

  • Support for parameterised queries;

  • Full printing support;

  • Transaction management;

  • IDE style text editor functions - search, replace, paste, etc.;

  • Export results;

  • Support for multiple open connections;

  • Searchable executable history of SQL queries;

  • Fast transition from editor to database object view by pressing CTRL + Left mouse button on the object name;

  • Transaction isolation level selection.

_images/sql_editor.png

Fig. 8.1 Query editor

8.1. Editing result set

For SQL queries that involve exactly one table and the output contains the primary key, you can edit the data in the result set. When you click on the "Commit" button, the data changes will be applied to the table.

_images/edit_result_set.png

Fig. 8.2 Редактирование результирующего набора

8.2. Parameterised queries

In some cases, you need to create a query that can be used multiple times, but with different input values each time. For example, you can write several queries to find data about an employee with a certain last name. Or you can write a single query, changing only the employee's last name.

To create a query that may have different input data at different times, query parameters are used. Parameters can be named or unnamed. An unnamed parameter is a question mark (?) that can be specified anywhere in the query, instead of a literal value. For example:

SELECT * FROM employee WHERE (last_name = ?)

After running such query, a dialogue window will open for entering the parameter value (employee's last name):

_images/unnamed_parameter.png

Fig. 8.3 Unnamed parameter

Named parameters are a combination of a colon and the parameter name (:<paramname>), which can also be substituted for a literal value. Named parameters are especially useful if there are multiple parameters in a query. For example:

SELECT * FROM employee WHERE (last_name = :surname AND first_name =:name)

After running such query, a dialogue box will open for entering the values of the parameters (last name and first name of the employee):

_images/named_parameter.png

Fig. 8.4 Named parameters

8.3. Query history

After successful execution, the query is stored in the editor's log cache. The number of queries stored in the history is specified in the editor settings. Saved queries are not lost after restarting the application or the query editor.

8.4. Transaction Control

To manage transactions, there is a Enable autocommit button on the toolbar. It includes an automatic commit mode in which DDL and DML transactions are committed after completion.

In the query editor, it is possible to enable the autoddl mode, which enables automatic confirmation of DDL operations after their execution. DML operations will be recorded if they are performed in the same transaction before DML. The autoddl mode is enabled by executing the following command in the query editor:

set autoddl on;