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.
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
In addition to these groups of buttons, you can also see a Session Selector, here you can select the current database session (connection) for the SQL Editor.
With the New, Open and Save buttons, you can clear the current SQL text, load SQL text from a file and save it to a file. The Open button has a drop down menu for recently opened files and the Save button has a drop down so you can choose Save As to enter a new filename.
Additionally, you can use the drop down of the New button to create a new SQL page, via SQL pages, you can load and save multiple SQL-pages as well.
Besides normal Windows Clipboard functionality, the SQL Editor has buttons for Special Copy and Special Paste, just like the main window. This functionality allows you to quickly copy/paste SQL text between Database Workbench and an external development environment.
When you open a SQL Editor, it starts with 1 SQL page, the pages are just below the editor window. You can use SQL pages to enter multiple SQL statements and load/save these statements and the pages to a file. You can add, delete or rename SQL Pages via the buttons on the toolbar (3). 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.
With the transaction buttons you can start, commit and rollback transactions. All actions in the SQL Editor will use the current transaction state, even if you change a value in the result set grid. By default, Database Workbench does not commit anything, you have to do that manually.
If you tick the Autocommit checkbox, all actions (data editing, SQL statements that modify data) will be committed immediately.
Depending on the database engine, the Commit and Rollback button will automatically come enabled as soon as you change data.
Notes:
| • | MySQL doesn't always support transactions, the "autocommit" checkbox is ON by default. |
| • | Each SQL Editor has it's own transaction. |
| • | On some database systems, executing DDL statement automatically commits the current transaction. |
Executing SQL statements is the main objective for the SQL Editor, you can do this by entering a statement (DML or DDL) and using the buttons in this part of the toolbar or the keyboard shortcuts.
When the SQL statement has parameters, use the Prepare button first so you can enter parameter values to be bound to the parameters. If you use Run Query again, the parameter values will be bound and the statement executed.
If the statement executed is a SELECT statement or otherwise returns a result to the client machine, the results will appear in the result set grid. By using Run and Fetch All, the SQL Editor will execute the statement and fetch all rows into memory and display them in the grid.
When an error occurs during execution of the statement or while preparing the statement, it is displayed in the area marked (8). If possible, the SQL Editor will color the line with the error red.
Some database systems support cancelling a running statement, you can use the red Stop button for that.
There's also a Visual Query Builder button that lets you create an SQL statement visually, it also parses the current statement and displays it.
By using the Create SQL Snippet button, you can quickly add a statement to the SQL Catalog.
Notes:
| • | 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. |
If the SQL statement is a SELECT statement or otherwise returns a result set (eg: Microsoft SQL Server CALL to a Stored Procedure), the result set is shown in the grid in the bottom half of the SQL Editor. In general, the result set is "live", meaning that you can edit the data in the result set. However, if the result set comes from multiple tables, it could be the case that some columns can be edited while others cannot. This is a limitation of what can be detected as to which columns can be updated via an SQL statement.
Result set buttons
From left-to-right, the result set buttons have the following meaning:
| • | go to first row |
| • | go to previous row |
| • | go to next row |
| • | go to last row (fetches all rows into memory) |
| • | insert new row |
| • | delete current row |
| • | refresh result set from database |
| • | close result set |
Changes to the data of the result set will not be made permanent unless you commit the transaction, if autocommit isn't turned ON. This goes for inserting new rows and deleting rows as well.
Example result set
As you can see in the above example, SQL NULLs will be displayed not just as empty (which could also mean "zero-length character-value"), but rather as "<null>" and in a slightly different color. You can customize the NULL appearance in the Preferences, "Editors - General" section.
If you use the context menu, you can also easily adjust the column width by selecting an item from the Resize Columns sub-menu.
You can also print the result set, see the PrintDAT! Help or export the result set. Both work on the current result set, starting from the first row and ending at the last row.
8. Info, errors, plan analysis and more...
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.
At the History tab, the SQL statements executed in this SQL Editor will be displayed so you can easily retrieve a previously entered SQL statement. The Parameters tab has such a history as well, so you can re-use the parameter values you've entered.