SQL Execution
After you’ve set up a connection, click “+” tool button (or press Ctrl+N) to open new SQL tab. If you previously set up your default database, new SQL tab will try to connect to this database. Otherwise, select the DB you’d like to run your SQL on from the databases list:
If connection was successful, you’ll see green circle on the left of the database drop-down list. If not, the circle will be in red color. Hover the mouse cursor to see the error, or click on it to try to reconnect (or disconnect, if the tab is online). Yellow circle means that the connection is in progress:
When connected, type or paste your SQL code and click Execute button. If the tab is disconnected, SQL Commander will try to establish connection before query running. Connection and execution timeouts you can set at the Options dialog.
During the execution, you can click the Stop button to abort the process. Please note: such interruption may take time and may not be supported by particular DBMS.
SQL Execution Modes
The application provides several execution options that are available by clicking on three-dots tool button:
Separate queries (default)
The SQL text is being split by delimiter specified (semicolon by default) into separate parts and then executed one by one. This mode is appropriate for simple ad-hoc queries. Data editing is allowed.
Batch mode
All the SQL statements sent at once to a server for execution. DBMS compiles the statements of a batch into a single execution plan. In some cases, this may reduce server workload. After the execution, DBMS returns datasets produced by the commands. Data editing is not allowed.
Please note: most of DBMS require SQL commands to be ended with semicolon (besides of SQL Anywhere and MS SQL Server, where semicolon is optional).
Script mode
Similar to batch, no splitting by delimiter, result sets are returned only in text view (as a server message). Useful for the back-end maintenance tasks, such as SQL objects creation, dropping, upgrading, data loading jobs, and so on. Script may contain separate SQL queries, execution control, logging commands, as well as using variables and other procedural SQL commands (PL/SQL for Oracle, T-SQL for MS SQL Server, etc.) Cross-DBMS queries are not supported for now.
Differences between script and command batch:
| Script | Batch |
|---|---|
| Allows using all possible SQL commands in a single script. | May have limitation, depending on a DBMS. For example, Oracle anonymous PL/SQL block cannot contain a DDL command. |
| Can be split into several transactions. | Must be performed in a single transaction. |
| Allows using non-SQL and custom commands. Support for Oracle SQL*Plus, Microsoft ISQL/OSQL, MySQL mysql.exe/mysqldump.exe, Firebird ISQL. | Only commands understood by a DBMS. |
| Can be split into subscripts. | Can call stored procedures as separated code blocks. |
| Execution is fully controlled by the client. | Execution is controlled only by the DBMS. |
Cross-DBMS SQL Queries
This unique feature of SQL Commander allows to JOIN multiple tables or views from databases of different DBMS. It is very helpful in migration process, as well as in other cases when you need to compare or transfer data in two or even more different DBMS.
How this works:
- Connect the SQL tab to an initial database. Tables of this DB will be used in SQL statements in the usual way.
- In the DB Tree, right-click the table or view from a database of another DBMS you’d like to use in the query, and click Copy Full Path menu item.
- Paste copied data into your query, adding ‘@@@‘ prefix. If the cross-DBMS reference syntax is correct – it will be highlighted with magenta color. Use aliases, if needed.
In the following example query we’ll JOIN 3 tables by LAST_NAME column from PostgreSQL (initial DB), Oracle, and MS SQL Server:
You can use such cross-DBMS references the similar way in UPDATE or DELETE statements. Next example shows using of CREATE TABLE and INSERT INTO statements:
Search In SQL Code
To find something in your SQL, click magnifier tool button (at the top-right corner), or press Ctrl+F. Also, you can double-click on a word inside the SQL code editor to highlight all of those at once:
As you can see on the picture above, various search options are supported (case sensitive, whole words, regular expressions, wrap around), as well as opportunity to replace found occurrences. On the vertical scroll bar, found items lines are highlighted as well.
SQL Editor Context Menu
More SQL code editing capabilities are accessible in the right-click menu:
- Cut, Copy, Paste, Select All, and Undo – common text editing commands.
- Toggle Comment – comment or uncomment selected text (or current line if nothing selected).
- Format SQL Text – inserts line breaks and indents to make code pretty and easy to read. More options to be implemented.
- UpperCase, LowerCase – change case of selected text.
- Quote – adds leading and trailing single quotes, as well as doubles every single quote in the selected text. DeQuote makes reverse.
- Go To Next / Previous Change – quick jump by lines where changes have been made.
- Word Wrap – enable or disable SQL text wrapping in the editor.