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 single SQL statements. To execute multiple statements in a single batch, use the Script Editor. Dragging a file from the Windows Explorer into Database Workbench will open up a Script Editor with the file.
You can browse result sets, edit data, execute data manipulation or data definition statements, check the query plan, statement statistics etc...
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, the option to turn this OFF is available for InterBase and Firebird only.
Statement Editor Behaviour
You can edit multiple statements in the editor window. If you have multiple statements, highlight the statement you want to execute before executing it.
Depending on the option Empty Line is Statement Separator (available via the toolbar Options button), you can also leave an empty line between statements and put the cursor in the statement you'd like to execute.
When you write a colon followed by text (eg: :customerid), you signal the SQL Editor that you're using a parameter in the SQL statement, a value placeholder. See the Query Parameters topic for more information.
If the DBMS you are using supports a "query plan", the tab Plan Analysis holds a Plan Analyzer.
You can edit, group, sort and filter data in the SQL Editor when you've executed a statement that returns data.
Tip:
| • | You can also use CTRL+E or CTRL+enter to execute statements |
SQL Editor Explained
Take a look at this screen shot and at the information below for a general introduction and take a look at the other topics for detailed information.
The SQL Editor window
SQL Editor Window
1. File New/Open/Save
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.
Mind you, if you use the load & save buttons here, you will only load and save the contents of single SQL page.
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.
2. SQL Pages
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. Each page has it's own data result set, so you can have multiple result sets, but only 1 will be visible (active) at a time.
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.
Dragging a file from the Windows Explorer onto the SQL Editor will open a new page with the file contents.
3. Session Selector & Transaction Control
If you have multiple database sessions, you can use the Session Selector box to change to a different session for the given SQL Editor. This way you can easily run the same SQL statement on multiple database sessions, if you require to do so.
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. |
4. Clipboard, Commenting and Printing
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.
The Comment Selected Block or Current Line button wraps the block or line with SQL comment markers.
You can print the contents of the editor or selection by using the Print button (ctrl+P).
5. SQL Execution
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.
After running a statement that returns data, you can optionally edit, group, sort and filter the data.
Notes:
| • | None of the statements executed via the SQL Editor in Database Workbench is auto-commit - you need to commit or rollback the statements yourself, unless you have checked the autocommit checkbox, which is checked by default on MySQL. |
| • | 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. |
6. Result set
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.
A result set is "server side", that is, data is fetched "on demand" as you request more rows when you scroll down. This goes for all DBMSses that support server side cursors (MySQL, for example, does not). If you choose to enable local sorting, grouping and filtering, all data is pulled to your local machine.
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, unless autocommit is 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.
7. Print & Export
You can also print the result set or export the result set. Both work on the current result set, starting from the first row and ending at the last row.
8. Column headers: local sorting, grouping and filtering
Column headers display a red asterisk if the table field requires values ("not null").
You can enable ad-hoc sorting, grouping and filtering of data locally on the client side by clicking a column or the yellow area above the column headers.
For detailed information, see the topic Data Editing, Sorting, Grouping and Filtering.
9. Info & errors
The bottom part of the SQL Editor holds information on how fast the statement executed, number of affected or fetched rows etc.
If there's an error in your SQL statement, it's listed here and, if possible, the corresponding line in the editor control is highlighted in a red.
Error in SQL statement
10. Other bottom tabs
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. This history is "project" specific and is persistent across sessions.
The Parameters tab has such a history as well, so you can re-use the parameter values you've entered.