The query editor is a tool in SQL DATA LENS to run SQL queries against your database. The query editor is used to edit simple queries but also to write complex SQL scripts. SQL DATA LENS can work with multiple open editors at the same time, execute scripts and process the results. Write and run queries in the advanced SQL editor speeds up your coding and reduce errors with features such as auto-completion, syntax highlighting, Bracket Matching, Goto Line commands and many more.
Work with multiple sql statements – use GO!
SQL DATA LENS provides commands that are not send to the database, but are recognized by SQL DATA LENS query editor. SQL DATA LENS interpret GO as a signal that they should send the current batch of SQL statements to InterSystems Caché \ IRIS.
The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.
With that feature it is very easy to work with a large number of statements within one editor window. You can jump back and forth and execute the current statement simply by pressing F5
- Execute Script with multiple SQL statements
- Execute single SQL statement at cursor position
- Execute selected SQL statement(s)
- place the cursor at any position and execute the current statement with F5
- or execute the next statement with F7
- or just execute all statements with F9
The query editor allows executing all SQL statements accepted by the database and the driver. The results of a executed statement is displayed in the results pane including database specific messages and error codes.
The SQL DATA LENS query editor will help to find the necessary information while writing the SQL script without leaving the query editor for such purposes. A drop down list might appear after you type a delimiter like point or comma or just press CTRL+Space
On the code completion help window SQL DATA LENS shows details that comes from the data model. If you have information in the remarks property saved, you see that here. The window handle hyperlinks in your remarks text also.
The code complition shows Table,Views, Functions, Procedures but also details about columns
Special optimize-options with help information are also available from code completion
Using a font improved for developers
The editor and SQL DATA LENS use a font that is adapted for reading code. So the height is increased for a better reading experience. Characters remain standard in width, but the height of the lowercase is maximized. This approach keeps code lines to the length that developers expect.
Execute to file
Execute statements and save result to file: This is helpful for huge results that can’t be displayed in a grid. This feature works with limited memory because it write the rows from the database direct to the export file without buffering in memory.
Execute to table
Execute to table: Saves the result of a statement direct to an new created table. This is done on Server side! So this is very very fast, cause no data is send to the Client \ SQL DATA LENS
Open in Excel
All grid results can be opened within Excel with just one click
Wtih the feature Session snapshot SQL DATA LENS allows a quick restart. Stopping SQL DATA LENS saves all SQL Editor tabs with content and connection infos. All this will be automatically restored on next start.
Calculate selected cells
The Calculate selected cells feature enables you to apply functions (count/min/max/avg/sum) to the selected items in a number column.
But the Editor of SQL DATA LENS can more…
- Handles multiple open connections
- Asynchronous database communication
- Support for parameterized SQL scripts
- Code completion (Intelli Sense)
- SQL formatter
- SQL Code folding
- Support for procedures producing multiple result sets
- View results as grid or text
- Execute Stored Procedures
- Execution plan visualizer
- SQL History
- Data Inspector shows results as text, html, xml binary and image
- Scripts Manager
- Export grid data CSV, HTML, XLS, XLSX, XML, SQL, TEXT, JSON
- limit data by max rows\max chars