The SQL Query Editor is a tool in SQL DATA LENS for executing SQL queries against your database. The Query Editor is used to edit simple queries as well as to write complex SQL scripts. SQL DATA LENS can work with several editors open at the same time, executing scripts and processing the results. Writing and executing queries in the advanced SQL editor speeds up your coding and reduces errors with features such as auto-completion, syntax highlighting, parenthesis matching, go to line commands and 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.

Code completion

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

code completion table\view list

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.

See how easy it is to tell the difference between the zero and the letter “O”. The zero has a dot inside. The letter “O” does not

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

Session snapshot

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 do 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