SQL Editor
Previous  Top  Next

You can reach the Single SQL Editor by clicking its button from the main tool bar or via Editors | SQL Editor.

Use the SQL Editor to execute ad-hoc SQL statements. You can browse result sets, execute INSERT statements, check the query plan. In short, almost anything. To view UNICODE character sets, use the Database Registration Options.

The SQL Editor uses the colon (":") as a "parameter signal" - if your statement includes a colon, the SQL Editor will think it's a parameter and shows you the Parameters tab in the bottom part of the screen, allowing you to enter parameter values. Press Run (F9) to bind the parameters and execute your statement with the bound parameter values.

You can edit multiple statements in the editor window. If you have multiple statements, highlight the statement you want to execute before clicking the Run button (F9). If you leave an empty line between statements, the statement that contains the cursor will be executed (no need to select the statement).

The SQL Editor is multi-threaded - it allows you to execute a statement and continue working with Database Workbench. This works for all database engines that support multiple, thread-safe connections. Every instance of the SQL Editor creates an additional connection to your database - if you are running into problems regarding the amount of connections allowed by your server, you can always turn this behaviour OFF via the database engine specific Preferences pages.

Tip:
·You can also use CTRL+E or CTRL+enter to execute statements  

Take a look at this screen shot and at the information below:

The SQL Editor window

Explanation
1.   Run, Prepare and Visual Query Builder buttons
2.   SQL Pages and SQL Pages related buttons
3.   Transaction buttons
4.   Result set related buttons
5.   Info, errors, query plan and more...

1.Run, Prepare and the Visual Query Builder  
From the SQL Editor, you can execute SQL statements. These statements may be DML (Data Manipulation Language, such as INSERT, UPDATE and DELETE) statements or DDL (Data Definition Language) statements.

Note:
·None of the statements executed via the SQL Editor in InterBase Workbench is auto-commit - you need to commit or rollback the statements yourself, unless you have checked the autocommit checkbox.  
·Please note that if you execute DDL statements, the corresponding editors (Table Editor, Index Editor etc) will not reflect the changes made outside the object editors unless you use the Refresh From Database button in the Database Navigator.  

When you execute a SELECT statement, the result set will appear in the grid (4). The Query Plan (a description of how the server is executing your statement) will be displayed in the messages part of the screen (5). When an error occurs when executing a statement, the tab sheet 'info/errors' will be shown (5). If no error occurs, the 'info/errors' tab sheet will display the amount of time needed to execute the statement.

If you prepare a statement (that is, sending the statement to the server, let the server examine the statement and generate a plan, but, put the statement 'on hold' after that), you can press ctrl + F9 or click the corresponding button. If you have parameters to fill in - prepare the statement first. Database Workbench will flip to tab sheet 'parameters' and will give you room to substitute in the parameters with real values. To execute a prepared statement, press F9 or click the corresponding button.

You can use the Visual Query Builder button to start the Visual Query Builder to construct an SQL statement by using the mouse and drag and drop operations.

2. SQL Pages
Database Workbench lets you create 'SQL Pages' - these are tab sheets with a different (or multiple) SQL statement each. This way, you can easily have multiple statement in one SQL Editor. You can add, delete or rename SQL Pages via the buttons on the toolbar (2). A right mouse click on the SQL Pages them self gives you these options too.

When you rename a page, a %d will correspond to a digit. Eg: 'Page %d' will become 'Page 1' (depending on the page number it is). If you add an '&' to the name, it will correspond to a hotkey (the underlined key you can use in combination with the <alt> button as a shortcut).

When you delete a page, all pages left will re-number.

Besides saving a single statement or SQL Page, you can also save the contents of all SQL Pages - the Load/Save SQL Pages buttons take care of this.

3. Transaction Buttons
The transaction buttons (3) reflect the current transaction status. If you execute an INSERT statement, you will see these buttons highlighted, indicating that a transaction has started. You then can either commit or rollback the transaction. You can execute multiple statements before issuing a commit or rollback.

The Autocommit checkbox signals if the transaction needs to be manually committed or if each statement is executed and automatically committed for you. This can be useful when running DDL scripts. In general, we recommend using manual transaction control, if possible, for all operations. Not every database engine supports transactions for DML and DDL. For example, MySQL only supports transactions for DML and certain table types, while Oracle doesn't support transactions for DDL.

Note:
·Each SQL/Script Editor will have its own transaction.  
·Issueing a DDL statement in Oracle while a (DML) transaction is active will commit the DML.  

4. Result set grid/buttons
After successfully executing a SELECT statement, a Result Set Grid is shown (4). You can edit, add or delete rows via the buttons just above the grid.

Each result set can be saved to an external format (eg: dBase, Microsoft Excel etc...) when you click the Export Resultset button just above the grid. This will open a dialog in which you can set the different options.

For more information on the export formats, see Data Export.

5. Info, errors and the Plan Analyzer
Besides the statement execution time, any errors etc, this editor can also be used to analyse a statement execution plan.

The Plan Analysis tab

At the Plan Analysis tab, you will find a graphical representation of your statement execution plan. Database Workbench will show any parts of the plan that use the "natural" access type in red
to indicate possible optimization.