SQL Editor of SQLDetective

Ben Brumm described SQL Editor of SQLDetective and compared it with other products (https://www.databasestar.com/oracle-sql-ide-comparison/?platform=hootsuite). But not all features were understood properly. So, there are my tips about work in SQLDetective.



Ben Brumm: Setting up a connection in SQL Detective was pretty easy.

Tjupka: Object Navigator and SQL Editor windows open on first SQLDetective start and their status&positions are saved/restored on next application start. Note: a standard statement (DML, DDL) may be generated on drag&drop an object from Object Navigator tree to SQL Editor.

Ben Brumm: The user interface is then loaded, and the first thing I noticed was that the SQL editor was at the bottom and the results pane was at the top. This is different to most editors I have used, but it’s something I can get used to.

Tjupka: GUI of SQL Editor may be changed by Preferences (main menu "View / Preferences", "Code Editors" and "SQL Editor" pages with their sub-items): one window per application, display the result pane above the editor, navigations between results and code, and many others.

Ben Brumm: One issue I immediately noticed was the width of columns. When I ran a query, the result columns were not spaced very well. It seems they were set to the maximum width of the VARCHAR2 column (e.g. 100 characters), instead of auto fitting to the data and title. This meant I had to scroll to the side to see all of the data.

Tjupka: Before executing a select statement in SQL Editor or browsing data in Smart Dataset the settings of Dataset may be changed in "Dataset Editors" page and its sub-pages of Preferences (main menu "View / Preferences"). According to Oracle DB, SQLDetective fetches 50 records with default auto-fitting of columns, because other settings will take more time for statement execution. As alternative way the "Smart Output" option in SQL Editor window redirects the execution result into more proper output tab. For example, the click on "green arrow" button of result tab executes the script into the clicked output tab.

Ben Brumm: However, there is a button you can click to auto fit the results, but this does not save between queries. There is also a setting that can be changed to set auto fit as the default, so this is not a big issue in the end – just something I noticed on the first run.

Tjupka: By default the "Keep column settings" option (it's available in Dataset Manager area of SQL Output tab in SQL Editor) is turned on. This setting should help to view results in user's mode.

Ben Brumm: It includes a Query By Example editor, which lets you enter parameters to filter and sort by.

Tjupka: QBE and Dataset Manager are a part of any Smart Dataset. QBE works in two modes:

1) wizard opens by click on "red question sign" at the top-left corner of grid;

2) conditions may be typed in grid after enabling the QBE mode by press the "F7" key and execute them by "F8".

QBE mode allows to work with standard condition clauses and aggregate functions. All QBE settings may be saved in Dataset Manager for further using, except SQL Editor as it stores all executed statements in own History.

Smart Dataset in SQLDetective is a tool for full work with data. The select statement may be generated by Query Builder, filter and sort may be saved in the Filter tab of Dataset Manager, renamed and reordered columns can be saved as item of In-Build Repository Project. Data may be browsed in grid or single-record mode. List of values linked by foreign key is available by double-click in cell. Each type of fields has own editor: string, digit, date, picture, nested table or ref-cursor. 


Ben Brumm: There’s a code assistant which helps generate syntax for all kinds of SQL commands.

Tjupka: Code Assistant (main menu "View / Code Assistant", hot-key "Ctrl+Alt+H") is a standalone tool of SQLDetective, it's not a part of SQL Editor. The list of commands may be changed by SQLDetective user and saved to file. Commands are grouped into SQL, PL/SQL and  User's parts. Paste to SQL Editor is available by hot-key or drag&drop.

SQL Editor has the list of SQL commands that is available from popup menu "Show Command List. This list can't be changed. Each command has comments and can be paste to editor by drag&drop.


Ben Brumm: It also includes a workspace save and restore functionality, which is something I have seen in several IDES and is useful.

Tjupka: Workspace is a feature of the whole SQLDetective. Any opened window can be restored on next application start. The settings of workspace are available in "General / Workspace" page of Preferences (main menu "View / Preferences", hot-key "Ctrl+F2").

Ben Brumm: A lot of extra tools are included in SQL Detective such as a profiler, code audit, data comparison, debugger, and a macro recorder which could be a real time saver.

Tjupka: SQL Editor has abilities to results of statement execution in special output tabs: data grid, any sql, dbms outputs, html. It's possible to compute statistics or explain plan in one click by "green arrow" button in corresponding tab. List of errors after statement executions is interactive: navigation to code works or you may read the full description of ora-error in Oracle Documentation (if "Help / Oracle Documentation Browser" is set in SQLDetective). Work with bind&subst variables is very easy and it has useful settings in "Code Editors / Bind and Subst. Variables" page of Preferences. PL/SQL Profiler, Data Comparison, Debugger and Macro Recorder are standalone tools that are not part of SQL Editor. Debugger works in Stored Program Editor only. Code Audit feature is available in any editor of SQLDetective where code can be viewed or modified, analyzer results are linked with editor window.

Ben Brumm: There is a Page List feature, which is like a window list in other IDEs, and it lets you see all of the pages or tabs currently open, and a short version of their SQL code. This makes it easy to navigate to the tab you want.

Tjupka: Navigation between Data Output tabs and editors is more fast because of hints in tabs and tabs synchronization (set options in "Code Editors / SQL Editor / Editor and Tab Handling" page of Preferences). 

Ben Brumm: SQL Detective includes basically every feature you would want from an SQL IDE, plus more. I can’t find any fault with this tool, except that you’ll have to get used to the editor being on the bottom of the screen (which wouldn’t take long).

If I was to pick one thing, I would say that the UI could use some cleaning up? There seems to be a combination of gradient buttons with arrows, depressed cells, toolbars, and icons. I’m not sure if there’s a better way to do this though.


Tjupka: Ben didn't describe the DB connection and background execution features. On executing a statement in SQL Editor the background session may be selected and it speeds up work. SQL Editor supports two types of DB connection: application connection that is available by main menu "Session / New Session" and editor connection (execute a CONNECT statement in SQL Editor) that is added to SQL Editor Connections List (combobox) and may be fast changed. Any action can start by hot-key that is set in Icon Dictionary, it's more helpful than toolbar buttons as the standard hot-key schema may be selected (https://www.youtube.com/watch?v=RFgnUsijudI).




Комментариев нет:

Отправить комментарий