An editor that speaks IRIS SQL

A working SQL session is rarely one statement. It is a scratch file with thirty statements in it, four of which you actually want to run right now, one of which returns two million rows you did not mean to fetch, and one of which calls a stored procedure that returns three result sets. An editor that can only execute the whole buffer, or only the selection, makes that session slower than it needs to be. The SQL DATA LENS editor is built around the way that file is really used.

Work with many statements in one file: GO

GO is not sent to the database. It is a batch separator that the editor recognises: the current batch is every statement entered since the last GO, or since the top of the script if this is the first one. That single convention makes a long working script navigable, and it is the same convention a SQL Server developer already has in their fingers.

Three execution modes cover everything you need from there:

• F5 — execute the statement at the cursor. Put the caret anywhere inside a statement and run it; no selecting required.
• F7 — execute the next statement and move on.
• F9 — execute the whole script, every batch in order.

A selection is also honoured, so highlighting two statements and running just those still works. Any statement the database and the driver will accept can be executed, including DDL and stored procedure calls, and the results pane shows the database's own messages and error codes rather than a generic wrapper — the SQLCODE and the IRIS message text, as returned.

Code completion that reads your data model

The point of completion is to keep you from leaving the editor to go and look something up. The drop-down appears after a delimiter such as a full stop or a comma, or whenever you press Ctrl+Space, and it offers tables, views, functions, procedures and — once a table is in scope — its columns with their data types.

What it shows comes from the data model rather than from a static keyword list. If you keep documentation in the remarks property of a class or a property, it appears in the completion window, and hyperlinks inside that remarks text stay clickable — so a table comment that points at a ticket or a wiki page is one click away while you are writing the query. Completion also covers IRIS-specific constructs that are easy to mistype, including SET OPTION and the optimisation hints, each with its help text attached. Non-InterSystems connections get a generic completion provider so the behaviour is consistent when you switch to SQL Server for an hour.

Highlighting, folding and formatting for IRIS SQL

Syntax highlighting is maintained against the IRIS keyword set, not a generic ANSI one — window functions, LOAD DATA and the newer IRIS keywords are highlighted as keywords rather than as identifiers. Alongside that the editor gives you parenthesis matching, code folding for long statements and blocks, go-to-line, and a built-in SQL formatter that takes a statement someone pasted in as one 400-character line and turns it into something reviewable.

Parameterised scripts are supported, so a script you run weekly against different accounts does not need editing each time:

SELECT ID, Name, LastModified
FROM   Sample.Person
WHERE  Company = ?
AND    LastModified >= ?
ORDER  BY LastModified DESC

The editor prompts for the parameter values on execution and keeps them for the next run, which is both faster and safer than pasting literals into the WHERE clause.

A font chosen for reading code

The editor and the rest of the application use JetBrains Mono. Character widths stay standard, so lines break where a developer expects them to, but the height of lowercase letters is maximised for legibility at small sizes. The practical benefit shows up in the characters that cost you the most debugging time: the zero has a dot inside it and the capital letter O does not, so a mistyped identifier is visible instead of plausible.

Results: grid, text, several at once

Results display as a grid or as plain text, switchable from the toolbar — text output is what you want when you are pasting an answer into a ticket. Stored procedures that produce multiple result sets are handled properly, with each result set on its own tab, and SQL DATA LENS can generate the CALL script for a procedure from its metadata. For diagnosing type problems it can also generate SELECT variants that wrap every result column in %ODBCIN, in %INTERNAL, or in CAST(col AS VARCHAR(255)) — three different views of the same procedure output, which is usually enough to identify which column is lying about its type.

Working with the grid itself:

• Calculate selected cells — select a range in a numeric column and get count, min, max, avg and sum without writing an aggregate query.
• Copy pretty formatted — copy a result block as aligned text that survives being pasted into an email.
• Sorting in the grid, and a refresh that runs while data is still being fetched.
• Double-click any cell to open it in the Data Inspector as text, HTML, XML, JSON, an image or raw binary.

Execution plans and cached queries

The execution plan is available directly in the editor, including the plain plan for IRIS, so you do not need to detour through the Management Portal to find out why a statement chose the index it chose. Because IRIS caches statements as classes, the plan you are reading and the cached query the server is actually running can drift apart; the cached query list and the tooling for clearing it live on the SQL performance page.

Results too big for a grid

Two commands handle results that are not meant to be looked at row by row.

Execute to file writes rows from the database straight to the export file without buffering them in memory, so a 40-million-row extract runs in a flat, small memory footprint instead of running the application out of heap. Read errors do not abort the run: a value that cannot be read is reported and the export continues with the next one, which matters on legacy data where a handful of rows were written past the object layer.

Execute to table writes the result of a statement into a newly created table on the server. Nothing travels to the client at all — for IRIS this uses CREATE TABLE <table> AS SELECT … — which makes it by far the fastest way to materialise a working set you intend to query several more times.

Grid results export to CSV, HTML, XLS, XLSX, XML, SQL, TEXT and JSON, and Open in Excel sends the current grid to Excel natively with one click. Two limits guard the round trip in the first place: max rows caps how many rows are fetched from the server and max chars caps how much of a character or stream column is fetched. Both are remembered across restarts; clear the field to remove the limit. Where a driver ignores the JDBC setMaxRows hint — DuckDB, for instance — the limit is applied as TOP or LIMIT instead, so it holds regardless of the backend.

Sessions, history and connections

Session snapshot makes a restart cheap: closing SQL DATA LENS saves every editor tab with its content and its connection, and reopens all of it on the next start. SQL history keeps what you executed, so the statement you got right on Thursday is still findable on Monday, and anything worth keeping permanently belongs in the Script Manager.

Several connections can be open at once and database communication is asynchronous, so one slow query does not lock the interface. A connect button on the toolbar switches an editor to another server or database without reopening it, and the connection name in the status bar is a hyperlink back to that connection in the Server Navigator.

Error handling is configurable, which sounds dull until you meet a table where one row in ten thousand holds a value its metadata says is impossible. By default reading a result set stops at the first bad cell and reports its row and column, together with a best-effort string rendering of the offending value. A toolbar toggle switches that behaviour globally so a survey query can run to completion and show you every affected row at once.

The editor is where most of a working day goes, so the details in it compound: a completion list that knows your remarks, a plan you can read without leaving the window, and an export path that does not care how large the result is. Download SQL DATA LENS and run your own worst query through it.