Version 4.00 in full

This release was developed as 3.25. The new Query Cloud, the reworked code completion, the rebuilt SQL Scanner, the new SQL History and the first central settings dialog add up to more than a minor step, so the version number goes from 3.24 to 4.00. Installing and updating work as before.

If you used the old Query Cloud, read the migration note below before you update.

• CHANGE SQL DATA LENS is published by Data Prudentia GmbH. The setup’s publisher entry and the Windows file properties of sqldl.exe now name Data Prudentia GmbH, and Help > Support Portal opens the Data Prudentia service desk.
• NEW Query Cloud V2. Several databases can now be queried together in one statement — an IRIS table joined against a PostgreSQL table and a CSV folder, for example. Every source is a reference to an existing connection plus namespace or schema, or a CSV/DBF folder; no credentials are stored in the Query Cloud itself. Each source gets an alias that becomes its schema name in SQL. Filters, joins and aggregations run inside each source wherever possible, and Show Federation Plan in the SQL editor shows what was pushed down.
• NEW Query Cloud: saved views over the sources. A federated query is capped at 100,000 rows by default; that cap, the query timeout, the connections per source, the pool wait timeout, identifier casing and SQL logging are all configurable in the Sources tab.
• CHANGE Query Cloud is a paid feature. In the Free Edition the node stays visible but connecting is refused with a message naming the edition you need. Named User and Team federate up to five database sources at a time, Enterprise has no limit. Unticked sources and CSV/DBF folder sources do not count.
• CHANGE Migration — the legacy Query Cloud. The embedded H2 1.4.200 engine has been removed. The old querycloud.mv.db file cannot be read any more and its sources are not carried over: add them again under the Query Cloud node’s properties, Sources tab. The old connection entry is renamed to “Query Cloud (removed)” on the next start, and Legacy Query Cloud… in the Sources tab offers to delete the old file and remove that entry. The old file holds your source passwords in plain text — delete it once you no longer need it, and treat those passwords as exposed. SQL DATA LENS 3.24 and earlier can still open the old entry if you need to look up what it contained.
• CHANGE The “H2 V1.x” driver entry has been removed. H2 1.4.200 is affected by CVE-2021-42392 and CVE-2022-23221, both exploitable through a JDBC URL. Use “H2 V2.x” instead; it cannot open 1.x database files directly, so convert them with H2’s own SCRIPT and RUNSCRIPT tools first. Connection entries still pointing at the removed driver report this instead of failing with a technical error.
• NEW SQL History overhaul. The day-by-day tree is replaced by a flat, sortable, filterable table with real columns: pin marker, time, connection, duration, row count, status and an editable comment. A quick-filter field above the table searches SQL and comments, with an optional regular-expression toggle; separate filters narrow by connection, time range and status, plus a favourites-only toggle. The context menu adds pin and unpin, add or edit comment, copy as SQL, copy error message, delete, and the three paste modes. Entries export as .sql or .csv. Pinned entries survive the size limit, and “Max size” is now a real, persisted setting. The history is stored as sqlhistory.jsonl; an existing sqlhistory.dat2 is migrated once and renamed.
• CHANGE The SQL History now also records statements that failed or were cancelled, not just successful ones, and each entry carries the connection name, duration and row count. Use the status filter if you prefer the old success-only view.
• NEW Tools > Settings… — one dialog for everything SQL DATA LENS can be configured with. Until now there was no options dialog at all. It has pages for General, Results & Formatting, Editor & Completion, Metadata, InterSystems IRIS / Caché, Network and Logging, plus an Advanced page listing every setting of both stores with a filter and an editable value column. Every field says when the change takes effect. Values are written back to the same stores the application always read, and all previous entry points keep working. Proxy passwords and credential-like keys are masked and cannot be edited here.
• ENH SQL Scanner rebuilt. One click on “Start scanning” now runs every check and fills all tabs instead of only the tab in front, findings are sorted by severity across all checks, and each finding carries a reason and — where one can be generated — a suggested statement, offered as “Copy suggested SQL” and “Open suggested SQL in a new editor”. The scanner itself never executes anything.
• NEW SQL Scanner: a “Table Metadata” tab for IRIS and Caché — tables whose optimiser statistics were never gathered, statistics older than 90 days or invalidated by changed rows, statistics from too small a sample, tables with automatic collection switched off, and indexes that were defined but never populated. The suggested remedy is TUNE TABLE … %RECOMPILE_CQ: without the recompile the cached plans keep the old numbers.
• NEW SQL Scanner: index type advice for IRIS. Index types are invisible over JDBC, so they are read from the class dictionary and compared against the column cardinality — standard indexes on low-cardinality columns that would be better as bitmaps, bitmaps on high-cardinality columns, bitmap indexes without a bitmap extent index, and indexes the optimiser cannot select at all.
• NEW SQL Scanner: a “Scanned Objects” tab listing every table that was checked, the schemas excluded as system objects, and any check that could not run, with the reason.
• FIX SQL Scanner, “Foreign Keys”: the check never reported anything on IRIS and Caché. The metadata calls behind it passed no schema, and the InterSystems driver returns zero rows in that case. On the shipped AdventureWorks sample the corrected check finds 40 unindexed foreign keys where the old one found none.
• ENH SQL Scanner: a foreign key counts as indexed only when an index carries its columns as the leading columns, composite keys are evaluated as a whole, and the index type is taken into account. “Redundant Indexes” (now “Indexes”) reports an index only when its column list is a prefix of another index’s, excluding unique indexes, indexes with their own data columns and the IRIS structural indexes.
• FIX SQL Scanner: switching the namespace clears every tab and cancels a running scan; the redundant-index check no longer needs the table browser of the same namespace to have been opened first; and the group nodes of the result trees are labelled properly instead of “null”.
• ENH Code completion substantially reworked. The cursor position and statement context are identified correctly in scripts with several statements, matching is case-insensitive, views are listed next to tables, and keyword and function lists are kept separately per database dialect. Auto-popup waits for two characters by default but still fires immediately after ”.” or after a space following FROM, JOIN, INTO or UPDATE. Right-click and choose “Code Completion Settings…” to tune the minimum characters, the delay, fuzzy matching, the list size, whether views are included and whether the table list is preloaded after connecting.
• FIX Code completion showed nothing in the situation it is used in most — the cursor at the end of what you typed, right behind a space. It no longer offers schema names where a column belongs, offers a schema’s tables and views behind “schema.”, and reads columns and foreign keys straight from the database metadata. It now also says when there is nothing to show, instead of quietly listing only keywords.
• ENH JOIN suggestions are back. Right after JOIN, tables connected through a declared foreign key are listed first; with the cursor in the ON part, a ready-made condition built from the foreign key is offered as the first entry, or press Ctrl+Alt+J to insert it directly. Without a foreign key, a condition guessed from column names is offered instead, always marked as a guess.
• FIX The Execution Plan tab has been rebuilt. It shows the plan as plain, monospaced text — fully selectable and copyable, readable in the dark theme — instead of the old HTML tree view, which relied on a fixed position in the plan’s XML and could silently show an empty pane for a perfectly valid plan. Unparseable plans now fall back visibly to the raw text. DuckDB, SQL Server and PostgreSQL show a text plan as well, and other generic JDBC connections show a message naming the database instead of an empty pane.
• CHANGE Classes on IRIS: Delete, Copy, Export, Import and Compile are implemented for Caché only. On IRIS they were empty stubs — Copy ended in an internal error, Export wrote no file, and the rest did nothing without saying so. They are now disabled on IRIS and carry the reason in the menu text. Reading classes is unaffected: the class list, the Class Infos tree and the Source Code tab keep working. Use VS Code with the InterSystems ObjectScript extension, or the IRIS Management Portal, to move or compile classes on IRIS.
• FIX Licensing: a valid licence could be rejected because of the way its expiry date was written. Only one exact format was accepted, so several common spellings made the check fail and started SQL DATA LENS in the free edition. All of those forms are now read, and a value that still cannot be read no longer takes the licence away — it goes to the log and the licence stays in force. Only an expiry date genuinely in the past ends a licence.
• FIX Security: “Send connection details by email…” no longer includes the stored password. The obfuscation it used is reversible with information that ships with the product, so anyone who received such an email effectively received the password. The recipient now enters it once at the first connect; emails written by older releases still import their password.
• FIX Security: creating or deleting an SQL Gateway entry built its SQL by string concatenation. A quote in the connection name, URL, user or password broke the statement, and the gateway password was written into it in clear text, where it could reach the server’s query log. Both statements now use bound parameters.
• FIX Three settings that had no effect: the auto-commit state is remembered again, so a new SQL editor starts with the setting you last chose; the read-error behaviour set in Tools > Settings… survives a restart; and the documented property sql.scripting.quotealways, which nothing ever read, has been removed.
• FIX Query Cloud stability: opening a SQL editor on the Query Cloud no longer blocks the interface for minutes, sources that could not be reached are reported in the interface rather than only in the log, “Cancel” really ends the query on the source instead of leaving it running, and the query timeout takes effect when it says it does. CLOB, BLOB and the equivalent IRIS and Caché stream fields now come through a federated query correctly.
• FIX Free Edition: connecting to an unsupported database system now names the product — “This edition does not support connections to MariaDB.” — instead of claiming a licence value that was never read.
• FIX Closing the application could take very long with an unreachable server, because connections were closed one after the other with no time limit. They are now closed in parallel with a five-second budget for all of them together. The “background work is still running” prompt also covers running exports and SQL executions, so closing no longer cuts off a file export without asking.
• FIX Management: the process list, the lock list and their detail panes no longer read their results on the UI thread. The Databases view labels its percentage column “Free (%)”. The Data Pumper no longer queries the database on the UI thread while determining source and target columns.
• FIX Views that list server internals — Global Browser, processes, locks, tasks, cached queries — left one statement open on the shared connection per refresh, which could make them fail on InterSystems IRIS Community after a while.
• FIX DROP INDEX generated from the Index Analyzer and from the scanner now names the table, schema-qualified, on the databases that require it, and still omits it where an index is a standalone object.
• FIX HyperSQL connections failed to start because the driver entry pointed at a file name that is not shipped. Open in Excel no longer leaves the Ctrl key stuck in the operating system. A right-click on a result grid column header opens a working menu instead of an empty one.