SQL Console Help
Table of contents
- Console Toolbar
- Result Set Manipulations
RybaFish Charts designed as a single-database client so it is not possible to open connection to two different databases. Single configuration used for charts and consoles. If you really need to open two different console connections - the only way is to start two RybaFish instances.
To open an SQL Console you need to be already connected to the database, then: File → New SQL Console or
SQL Console uses basic syntax highlighting for SQL keywords, comments and literals. Developed under heavy influence of Notepad++ editor there are additional highlighting features like brackets highlighting and words highlighting.
Important note: SQL Console does not have autocommit flag set. That means any data changes will not be visible for other transactions until explicit
commit executed. There is a request to make this configurable.
Each tab in RybaFish has it’s own ‘indicator’ in the status bar: small solid color square indicating the status of the console: is it connected, is it running, etc. We will be paying cttention to the indication during this documentation and you might start paying attention to it too. There is a short reference on this topic.
Frankly no special training required to start using the console, but to get the full power of it you might need to go quickly through this document. So, let’s jump right into it!
Main functions of the console are available in the toolbar:
The toolbar can be enabled/disabled by menu Actions → SQL Console Toolbar, which is equivalent to sqlConsoleToolbar setting.
Let’s go through the buttons one by one and see what they do.
Main Execute Statements Button
The main point having SQL console is executing SQL statements, that is why RybaFish has three different ways of doing this. To execute a statement you need to place cursor somewhere inside and press or
F8. RybaFish will try to parse the statement and, if succesful, - highlight and execute it.
To make this work, statements need to be separated by the semicolon:
; character. This makes it possible to work with several statements in the same console and execute them when required. Let’s say we work with the following SQLs:
select now() from dummy; select time, host, port, cpu, memory_used, memory_allocation_limit from m_load_history_service order by time desc;
When the cursor is on the first line (anywhere before semicolon) you can hit (
F8) and and execute it:
If you move the cursor to the line #5 and press the Execute button again, the second statement will be highlighted and executed:
Once again, you don’t need to select the statement, just put the cursor somewhere inside and execute.
While the statement is running, corresponding indicator will became green: . When finished the indicator becomes idle: .
In case of syntax error during the processing of the statement the reported line/position will be highlighted with red background:
Here the source object does not have the HOSTNAME column and it is reported in console log and highlighted in the SQL text.
Also, if you pay attention to line numbers - numbering adjusted to the statement itself. This makes it way much easier to see what line is reported because the database has no idea what is the statement position inside your console and only able to report position inside the statement.
As soon as you make any change in the console - numbering will reset to the normal mode. This behavior can be desabled by setting blockLineNumbers to False.
Execute several statements
Sometimes it is required to execute several statements at once. For example, you have a list of steps to be executed together. In this case it will be required so select statements manually and hit the same Execute button.
What actually happens in this case - RybaFish still does parsing of the SQLs based on the semicolon character, and execute them in a queue:
Corresponding number of result set tabs will be populated, in this case two of them: Results and Results2
Execute without parsing
Alt+F8 Execute without parsing
As mentioned, the Execute button relies on SQL parsing, which may fail. In some cases, for instance SQLScript CREATE PROCEDURE statements or similar semantically complex constructions parsing may highlight incomplete statement which cannot be executed. This is where Execute without parsing required: you just select the statement manually and hit or
Alt+F8, that’s it: RybaFish will send to the database whatever was selected without any intermediate processing. Currently known parsing issue is nested begin … end blocks in SQLScript procedures, there is an open issue on that.
Execute Creating a New Result Set
Sometimes it might be required to keep the results of the previous execution and repeat the same query. In most cases this is required when you need to compare the results of two executions but lazy saving previous result. This is where
Ctrl+F8 will help save some electricity for you. You can use this function several times, every next execution will populate a new Results tab.
The moment you use regular Execute all the previous Results will be cleared (it is planned to have a warning in this regards).
Beautify (auto format)
RybaFish console has a simple auto formatting function that can be useful to make SQL statement readable. To use this function you need to select the statement first.
Every DBA has it’s own collection of crafted over the years SQL statements. RybaFish gives an option to organize the stuff in a built-in library which is quickly available from the tool itself. The approach is very straight-forward: you keep all the SQLs in flat files on local or network drive and point RybaFish to the root folder of this collection. By default RybaFish uses
scripts folder for that, but this can be changed by setting the scriptsFolder setting in config.yaml.
From this dialog you can insert the statement in the current cursor position or open it in a new SQL console. Both actions will not use clipboard.
If the file starts from a single-line comment (started from
--) this comment will be displayed. As extracting comments from all the files might take some time, to avoid freezing of the dialog - a background thread is used. Comments will appear in the dialog as soon as the background thread finishes. This thread runs only for the first dialog opening and following executions use cached values. If it is required to reload the library without restart of RybaFish - there is a
Reload button available.
This button connects the console to the database when disconnected and reconnects when connected. If you try to execute an SQL in disconnected console - RybaFish will notify you and propose connecting, so this button is not the most often used one.
Connected console has the ‘idle’ indicator: .
This button disconnects the console from the database. Might be useful when you want to make sure you close the session and release all the transaction-related things, for example. Not the most often used button either (because the Execute is).
Connected console has the ‘disconnected’ indicator: .
Generate Cancel Session SQL
RybaFish does not have built in abortion mechanism due to several reasons, but there is a button to help you preparing the ALTER statement for this. When pressed it generates cancel session statement related to the current console:
alter system cancel session '304021'
The statement generated in the log area of the console. It is not executed, you need to copy it and execute in the other console manually. The other console should be used because the current one is most likely busy doing something, otherwise, why do you want to cancel it?
By the way, in some cases “cancel session” is not good enough and you might want to use “disconnect session” instead.
Schedule Automatic Refresh
Sometimes it might be required to have the result set updated by itself. For example you are waiting for some event or monitoring a particular value. This is where the automatic refresh might be useful. To get use of it you need to execute the statement first and then press . RybaFish will request the refresh interval:
This feature shows its real power when combined with the alerting functionality: RybaFish can play a sound when certain conditions are met.
Logging for this console will be suppressed until the auto-refresh stopped. To stop it you can press the button again (un-press it) or execute any sql in the same console: this will also disable the auto-refresh mode.
There is a limitation: this option can only be used for a single result set queries.
When the console is in auto refresh mode it has ‘autorefresh’ indicator: .
This is a toggle-button that enables ABAP-style results copy which might be useful to extract the results to be pasted in text form. The output looks like this:
---------------------------------------------- |TIME |HOST |PORT |CPU| |--------------------------------------------| |2022-06-27 11:20:18.727|atgvmls967|30007| 0| |2022-06-27 11:20:18.727|atgvmls967|30003| 1| |2022-06-27 11:20:08.7 |atgvmls967|30003| 1| |2022-06-27 11:20:08.7 |atgvmls967|30007| 0| |2022-06-27 11:19:58.672|atgvmls967|30007| 0| ----------------------------------------------
Make sure you use monospace font for that, like Courier or Consolas.
Note: with 091 beta V the default format is slightly changed to support markdown formatting. This can be disabled by setting copy-markdown: False.
By the way, there is a context menu which is available on right mouse click in the area of SQL Console:
This menu contains mostly the same functions that are available in the toolbar except the Explain Plan.
Result Set Manipulations
Result set tabs provide some options more related to the results than to sql console itself:
- Copy Column Names
- Take a Screenshot
- Insert Column Names
- Generate Filter Condition
- Highlight Value
- Highlight Changes
- Those two option duplicate toolbar functionality
- Editor Hotkeys
Result set related options are available in the context menu when the mouse is over a Results tab:
You can select one or several columns and use this option. Column names will be copied into clipboard, comma-separated. If you are using this feature to create a column list to be listed in SQL statement, consider using Insert Column Names option instead to avoid clipboard manipulations.
Take a Screenshot
Very stright-forward: it puts a screen-shot of the visible area of the result set into clipboard to be used in presentation or email.
Insert Column Names
This option puts comma-separated list of column names into current position of the cursor in SQL console. Clipboard is not used during this operation. It might be useful while composing SQL statement based on something like select * from …
By default column names are upper-cased, I prefer lower-case when possible, there is a setting lowercase-columns for that. When required (special characters, different case letters, etc), column names will be double-quoted according to SQL standard.
Generate Filter Condition
When you do free-style investigation it is often required to focus on particular subset of the result. Let’s execute the same query:
There is data on two ports 30003 and 30007 available but let’s say we are only interested in port number 30003. That means we need to add
where port = 30003 to this statement but instead typing the full where clause we will only type ‘where ‘ and right-mouse-click on any cell containing the required value (port 30003) and choose Generate Filter Condition from the context menu. The rest of condition
port = 30003 will be insetred at cursor position.
What is nice about this feature - it keeps the clipboart intact and you can combine several columns. If you select time and port cells before using it - the generated condition will be something like
time = '2022-06-29 09:01:26.517' and port = 30003
Literals or timestamp columns will be quoted, сonjunction (“and”) used to combine values.
You still need to type ‘where’ manually.
This feature also helps tracking particular value in the result set but without modification of the query. Let’s take the same example and use right-mouse-click → Highlight Value on the same 30003 port. Rows with this value will be highlighted:
This feature is very similar to the previous one but it highlights changes in certain column. In my practice this is most often useful to highlight timestamp changes. Let’s take the same example but now right-mouse-click on the ‘time’ column and use Highlight Changes from the context menu:
As you can see, rows highlighting changes with time value changes.
Not yet, but we will cover:
- autocommit (disabled by default)
- explain plan