#SQL

Hello, good morning, thank you so much for reading this question. ☺️🙂👍

We are developing a code to get information about our Production's items: services, processes and operations.

We know we can get various configurations of a given item: Category, Port, Enabled...

But we wonder how we could get the date time of the last mesage (most recent) received in an item.

To give a code snippet a small section of the code we have developed (and tested), it looks like:

[...]

For i=1:1:tProduction.Items.Count() {
    #dim item as Ens.Config.Item
    set item = tProduction.Items.GetA

How do I write DDL script for collection properties?

For example I want to create the following class:

Class SQLUser.Person {

Property Name As %String;

Property FavoriteColors As list Of %String;

}

My DDL script looks like this:

CREATE TABLE Person (Name varchar(50), FavoriteColors ???)

Description

This is a template for a Flask application that can be deployed in IRIS as an native Web Application.

Installation

1. Clone the repository
2. Create a virtual environment
3. Install the requirements
4. Run the docker-compose file

git clone
cd iris-flask-template
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
docker-compose up

Usage

The base URL is http://localhost:53795/flask/.

Endpoints

• /iris - Returns a JSON object with the top 10 classes present in the IRISAPP namespace.
• /interop - A ping endpoint to test the interoperability framework of IRIS.
• /posts -

We are glad to announce that DBeaver has supported InterSystems IRIS out-of-the-box since version 7.2.4. You don't need to configure it manually anymore, just find the IRIS icon in the Connections list. 

If you're migrating from Oracle to InterSystems IRIS—like many of my customers—you may run into Oracle-specific SQL patterns that need translation.

Take this example:

SELECT (TO_DATE('2023-05-12','YYYY-MM-DD') - LEVEL + 1) AS gap_date
FROM dual
CONNECT BY LEVEL <= (TO_DATE('2023-05-12','YYYY-MM-DD') - TO_DATE('2023-05-02','YYYY-MM-DD') + 1);

In Oracle:

• LEVEL is a pseudo-column used in hierarchical queries (CONNECT BY). It starts at 1 and increments by 1.
• CONNECT BY LEVEL <= (...) determines how many rows to generate.
• The difference between the two dates plus one gives 11, so the qu

Hi,

     Can anyone please tell me how to append a string using a update query.

For eg :  "Hello" is the string . I need to append the string world and it needs to be "Hello world".

Thanks

Jude

How would you go about creating an SQL Stored Procedure that would result in the same output as "IN LIKE"?
For example...
CustomersTable

select *
from CustomersTable
where inLike('%Mark%', '%John%')

Would return:

Hello,

I have a class with a "Unique" index (pxfactidIndex) on a %Numeric property (pxfactid) (partially-edit code snippet below):

Property pxfactid As%Library.Numeric(MAXVAL = 9223372036854775807, MINVAL = -9223372036854775808, SCALE = 0) [ SqlColumnNumber = 7 ];
Index pxfactidIndex On pxfactid [ Unique ];
Storage Default
{
<Data name="FactDefaultData">
<Value name="1">
<Value>pysubjectid</Value>
</Value>
...
<Value name="6">
<Value>pxfactid</Value>
</Value>
...
</Data>
<DataLocation>^CRMBI.FactD</DataLocation>
<DefaultData>FactDefaultData</DefaultData>
<ExtentLocation>^CRM

Is there a way in a EnsLib.SQL.InboundAdpapter that you can tell when you have reached the end of the Results of the Query?

Does anyone have a query that I could run to show a Vendor the time difference between when a message was sent out a BO, and when we received the HL7 ACK back that is associated with the message sent?

I am trying to prove to this vendor of the delay we are seeing getting the ACK back because of a Timeout

I know how to pull Ens.MessageHeader, and EnsLib.HL7.Message but not sure how to match up the Message with the HL7 Acknowledgement received.

Hi community.

I was wondering if it was possible to use something like EnsLib.SQL.InboundAdapter with tables in IRIS.

This library monitors when a record has been inserted into a table in an external database, so it requires a DSN to connect to that database.

My goal is to make a call to an external API that takes a long time, it could spend nearly an hour (or more) completing its processes, but I don't want to block the main process.

That's why I was thinking about having the original production store the indicators it needs to process in a table, and from another production, monitor when

I am testing vectorsearch, while doing so I am trying to paginate my resultset for a "next page" function to give me the first, second, third 15 entries within a table. 

For this I have two embedding classes. One with a HNSW Index (vectornomicembedtextlatest) , and one without (vectornomicembedtexttest).

Calling SELECT ID,PRIMKEY FROM SQLUser.vectornomicembedtexttest LIMIT 5 OFFSET 1 works fine with the first entry having the rowID of 486448. (I deleted old entries in the beginning and reused the table)

SELECT ID,PRIMKEY FROM SQLUser.vectornomicembedtexttest ORDER BY PRIMKEY DESC LIMIT 5 OF

I'm excited to announce a major update to SQL Data Lens – a powerful database client and metadata explorer – that opens up new, free possibilities for the InterSystems community.

SQL Data Lens is now completely FREE to use with InterSystems IRIS Community Edition!

No more “localhost only” restrictions
No more limits on the number of connections

No license? No problem.
You can now connect to InterSystems IRIS Community Edition—completely license-free—using the fully functional Free Edition of SQL DATA LENS. Explore all the features, no strings attached.
 

Discover hidden insights with SQL DATA L

With the release of InterSystems IRIS Cloud SQL, we're getting more frequent questions about how to establish secure connections over JDBC and other driver technologies. While we have nice summary and detailed documentation on the driver technologies themselves, our documentation does not go as far to describe individual client tools, such as our personal favourite DBeaver. In this article, we'll describe the steps to create a secure connection from DBeaver to your Cloud SQL deployment.

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.

I have read that it is possible to set privileges on individual properties on a persistent class, but I have no clue whe

Hello. Currently, we are developing using Cache 2018 version.
Our team is working on improving an existing legacy program so that it can also be used on the web.

Before asking my question, here is the development environment.

• IDE: IntelliJ
• Framework: Spring Boot, MyBatis
• DB Connection: JDBC (using the library provided by InterSystems)

Currently, we are successfully mapping global data through the %PERSISTENT class and able to query it with SQL. However, the problem is that the retrieved "Korean" data is all broken. It definitely seems like an encoding issue, but I don’t know where to

Hello Community,

I executed the below query in the %ZLANGC00.mac routine. It returns results when using dynamic SQL, but not with embedded SQL(returns 0). 

 &SQL(SELECTcount(*) INTO :Cnt FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'")
 Write "SQLTablecount : "_Cnt,!
 Set tResultSet = ##class(%SQL.Statement).%ExecDirect(,"SELECTcount(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'")
 Do tResultSet.%Display()

Thanks!

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

If you are a customer of the new InterSystems IRIS® Cloud SQL and InterSystems IRIS® Cloud IntegratedML® cloud offerings and want access to the metrics of your deployments and send them to your own Observability platform, here is a quick and dirty way to get it done by sending the metrics to Google Cloud Platform Monitoring (formerly StackDriver).

Would like to know if there is an alternative or better way to paginate through a dataset using dynamic SQL than what I am using below. The problem is that as the potential pool of data gets larger, this code slows down to the point of not being useable. In analyzing each line of code below, it appears the slow down is related to the initial rset.%Next() iteration. Is there anything available which does not require a subquery/%VID such as a simple LIMIT/OFFSET?

My code is similar to :

s sql=##class(%SQL.Statement).%New()

s query="SELECT *,%VID FROM (SELECT prop FROM table WHERE prop=x) WHERE %VID

Hello IRIS community,  

InterSystems Certification is developing a certification exam for InterSystems IRIS Developer professionals, and if you match the exam candidate description given below, we would like you to beta test the exam.

Migrating from Oracle, MSSQL, or other purely relational database systems to a multimodel InterSystems IRIS is a strategic decision that requires careful planning and execution. While this transition offers significant benefits, including enhanced performance, scalability, and support for modern architectures, it also comes with challenges. In this article I will highlight some of the considerations connected to coding to ensure a successful migration. I will leave everything connected to an actual migration of structures and data outside the scope of this article.

First, when you're considering migrating to a different database system, you need to understand your business logic, whether it's on the side of the application (application server) or the database server. Basically, where do you have your SQL statements that you will need to potentially rewrite? 

I have an IRIS persistent class with a %Stream property whose value is a JSON object. I'd like to use a SQL trigger to pull some value out of the JSON and persist it in another property, for easy querying and indexing. See below for a minimal example:

Class PAB.DebugStream Extends%Persistent
{

Property Contents As%Stream.GlobalCharacter;Property msg As%String;ClassMethod InsertRow()
{
    set stream = ##class(%Stream.GlobalCharacter).%New()
    $$$ThrowOnError(stream.Write({"msg":"hello world!"}))
    &sql(insertinto PAB.DebugStream (Contents) values (:stream))
    $$$ThrowSQ

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

set query = "SELECT Facility FROM FROM osuwmc_EnterpriseDirDB.RelationshipMedCtrID WHERE OSUmedcenterID = ?"SET rset = ##class(%SQL.Statement).%New()
 SET qStatus = rset.%Prepare(query)
 SET rset = rset.%Execute($Get(ID))
 do rset.%Display()

I need to take the values that are returned and say search them for a single value. How would I go about returning the EnsLib.SQL.Snapshot into a Array or List for it to be searchable.

Can this be done internally within the DTL, so I don't have to

I have a general question about HealthShare Provider Directory using Code Tables on disk vs Cache SQL Tables. Why is Provider Directory not using the Cache SQL Tables within the IRIS platform?

When working with InterSystems IRIS, database developers and architects often face a critical decision: whether to use Dynamic SQL or Embedded SQL for querying and updating data. Both methods have their unique strengths and use cases, but understanding their performance implications is essential to making the right choice. Response time, a key metric in evaluating application performance, can vary significantly depending on the SQL approach used. Dynamic SQL offers flexibility, as queries can be constructed and executed at runtime, making it ideal for scenarios with unpredictable or highly variable query needs. Conversely, Embedded SQL emphasizes stability and efficiency by integrating SQL code directly into application logic, offering optimized response times for predefined query patterns.

In this article, I will explore the response times when using these two types of SQL and how they depend on different class structures and usage of parameters. So to do this, I'm going to use the following classes from the diagram:

The 2024.1.4 and 2023.1.6 maintenance releases of InterSystems IRIS^® data platform, InterSystems IRIS^® for Health^TM, and HealthShare^® Health Connect are now Generally Available (GA). These releases include the fixes for the following alert recently issued - Alert: SQL Queries Returning Wrong Results | InterSystems. Please share your feedback through the Developer Community so we can build a better product together.

Documentation

You can find the detailed change lists & upgrade checklists on these pages:

• InterSystems IRIS
• InterSystems IRIS for Health
• HealthShare Health Connect

Early A

I was wondering if someone could help me. In the past I have been able to call external Stored Procedures through a SQL Outbound Connection and have them return me the EnsLib.SQL.Snapshot to use within a BPL to extract data.

But this time instead of using a SQL Outbound BO to make the Stored Procedure call, I decided to create a Linked Stored Procedure through the %JDBC_Server to point to the Stored Procedure out on MS SQL.

However, I am struggling to get the code just right to return the Column value from the Linked Stored Procedure.

set result = ##class(EnsLib.SQL.Snapshot).%New()
 set

Hey Community,

We're excited to invite you to the next InterSystems UKI Tech Talk webinar: 

👉SQL Cloud Services

⏱ Date & Time: Thursday, April 24, 2025 14.30-15.30 UK

👨‍🏫 Speaker: @Kinshuk Rakshith, Sales Engineer, InterSystems

An experiment on how to use the LangChain framework, IRIS Vector Search, and LLMs to generate IRIS-compatible SQL from user prompts.

This article was based in this notebook. You can run it with a ready to use environment with this application in OpenExchange.

Setup

First, we need to install the necessary libraries:

!pip install --upgrade --quiet langchain langchain-openai langchain-iris pandas

Next, we import the required modules and set up the environment:

import os
import datetime
import hashlib
from copy import deepcopy
from sqlalchemy import create_engine
import getpass
import pandas as p