Instead of building a BP to execute stored procedures just to insert the data, I figured I would give Linked Stored Procedures a try since all I need to do is insert the data. I have proved in our MS SQL Development environment that it is possible, so I through I could just repoint the Linked Stored Procedure

I am attempting to create a Foreign Server/Table so I can pull some information in from MS SQL server via JDBC connection, but as soon as I create the Server/Table and logout the Foreign Server disappears.

CREATE FOREIGN SERVER Epic.Clarity 
   FOREIGN DATA WRAPPER JDBC CONNECTION 'MS-EpicClarity'

logout, then run

Using SQL Gateway with Python, Vector Search, and Interoperability in InterSystems Iris

Part 1 - SQL Gateway

Hello

In this article we will look at the use of SQL Gateway in Iris. SQL Gateway allows Iris to have access to tables from other (external) database via ODBC or JDBC. We can access Tables or Views from various databases, such as Oracle, PostgreSQL, SQL Server, MySQL and others.

Hi, I am unsure how to remove this restriction; when I am performing dynamic SQL using ##class(%SQL.Statement).%ExecDirectNoPriv(, .query, args...)

It works fine, but the moment I add specific properties from the persistent class I am performing the select on into the WHERE clause, I get: ERROR #5540: SQLCODE: -99 Message: User UnknownUser is not privileged for the operation. Despite using %ExecDirectNoPriv, I've tried with prepared statement as well, exact same situation.

High-Performance Message Searching in Health Connect

The Problem

Have you ever tried to do a search in Message Viewer on a busy interface and had the query time out? This can become quite a problem as the amount of data increases. For context, the instance of Health Connect I am working with does roughly 155 million Message Headers per day with 21 day message retention. To try and help with search performance, we extended the built-in SearchTable with commonly used fields in hopes that indexing these fields would result in faster query times. Despite this, we still couldn't get some of these queries to finish at all.

Hello,

Our software commonly returns a full result set to the client and we use the DataTables plugin to display table data. This has worked well, but at datasets grow larger, we are trying to move some of these requests server-side so the server handles the bulk of the work rather than the client. This has had me scratching my head in so many ways.

I'm hoping I can get a mix of general best practice advice but also maybe some IRIS specific ideas.

Some background

Using SQL Gateway with Python, Vector Search, and Interoperability in InterSystems Iris

Part 3 – REST and Interoperability

Now that we have finished the configuration of the SQL Gateway and we have been able to access the data from the external database via python, and we have set up our vectorized base, we can perform some queries. For this in this part of the article we will use an application developed with CSP, HTML and Javascript that will access an integration in Iris, which then performs the search for data similarity, sends it to LLM and finally returns the generated SQL. The CSP page calls an API in Iris that receives the data to be used in the query, calling the integration. For more information about REST in the Iris see the documentation available at https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...

Like many others probably find themselves, we were stuck doing live data mapping in our Interface Engine that we really didn't want to do, but had no good alternative choice. We want to only keep mappings for as long as possibly needed and then purge expired rows based upon a TTL value. We actually had 4 use cases for it ourselves before we built this. Use cases:

I am experiencing an issue while executing a stored procedure in InterSystems Cache. Here’s the procedure I created

CREATE PROCEDURE Silk.sp_InsertRecord (
IN RecordDate TIMESTAMP,
IN UserName VARCHAR(50),
IN RecordType INT,
IN RecordID VARCHAR(50),
IN CategoryID INT,
IN ApprovalDate TIMESTAMP,
IN FileSize BIGINT,
IN WorkstationName VARCHAR(50)
)
BEGIN
INSERT INTO DummyRecords (
RecordKey,
FilePath,
RecordDate,
UserName,

Hi Community,

🔐 Make sure your data is secure! Learn some strategies for enhancing security in InterSystems IRIS® data platform:

Navigating SQL Privileges and Security in InterSystems IRIS

https://www.youtube.com/embed/_lBHnrm5Ot4?utm_source=youtube&utm_medium=social&utm_campaign=_lBHnrm5Ot4
[This is an embedded link, but you cannot view embedded content directly on the site because you have declined the cookies necessary to access it. To view embedded content, you would need to accept all cookies in your Cookies Settings]

I have a table in the development and want to erase data from certain columns. There is info in the docs but not for human

This piece of code does not work

ALTER TABLE table DROP COLUMN column %DELDATA

I am using a JDBC connection to MS SQL server to execute a stored procedure to select data and bring it into InterSystems as a EnsLib.SQL.Snapshot. I loop through the EnsLib.SQL.Snapshot using a while loop, but I also want to iterate through the Columns within that Row to do logic.

Is there a way to iterate through the Columns of the current Row of the EnsLib.SQL.Snapshot so I can apply logic/rules for further processing?

Thanks

Scott

I am struggling with returning a SQL Query Result that may have multiple rows that can be searched.

Hence the question: is there a way to do that?

The goal is to get data (from half a thousand to 3-4 thousands lines) from DB, calculate standart deviation then use it as logical condition in analyzer.

For example IF std > custom_value = show_the_result ELSE null

There is a STDDEV(MDX) method used in Analyzer but it is a measure and it can not be used as logical condition (correct me if i am wrong)