#SQL

I tried executing the SQL JSON_TABLE query with large JSON string(more than 200000 characters) and I got the below error. I'm curious about this under the hood workflow and how does it reach reaches MAXSTRING.

 Thanks!

Hello, community.

I have a problem with running a SQL query on a linked MySQL table.

The connection works fine, but the following query throws an error:

SELECT   TOP 10 * FROM   linkedinternal_test.persons

 [SQLCODE: <-400>:<Fatal error occurred>]

  [%msg: <Remote JDBC error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10 T1.PersonID AS C1,T1.LastName AS C2,T1.FirstName AS C3,T1.Address AS C4,T1.Ci' at line 1. >]

Linked table class:

/// Generated by the Link Table wizard on 2025-01-08 16:09:03.

The Lo-Code Challenge

Imagine the scene.  You are working happily at Widgets Direct, the internet's premier retailer of Widgets and Widget Accessories.   Your boss has some devastating news, some customers might not be fully happy with their widgets, and we need a helpdesk application to track these complaints.   To makes things interesting, he wants this with a very small code footprint and challenges you to deliver an application in less than 150 lines of code using InterSystems IRIS.  Is this even possible?

Hello everyone, 

My team is currently developing guidance and best practices for the generation, storage, and deployment of TUNE TABLE statistics across development and production environments. With that in mind, we want to get an idea of what methods teams in the field have developed for handling this data. In particular, we’d like to know the following: 

1. How often do you use TUNE TABLE in your development vs. production environments? 
2. Do you utilize the $SYSTEM.SQL.Stats.Table package to generate and export TUNE TABLE statistics as files? If so: 
   1. Do you store these in source control?

There are three things most important to any SQL performance conversation:  Indices, TuneTable, and Show Plan.  The attached PDFs includes historical presentations on these topics that cover the basics of these 3 things in one place.  Our documentation provides more detail on these and other SQL Performance topics in the links below.  The eLearning options reinforces several of these topics.  In addition, there are several Developer Community articles which touch on SQL performance, and those relevant links are also listed.

There is a fair amount of repetition in the information listed below.  The most important aspects of SQL performance to consider are:

1. The types of indices available
2. Using one index type over another
3. The information TuneTable gathers for a table and what it means to the Optimizer
4. How to read a Show Plan to better understand if a query is good or bad

Hello my Friends,

I have a question, I have a data like this

And I want to create a display like this, which I can put a status column beside the Item Column

The status become new, if there's no previous item, and if there's a same item after the first one, the status become Old

Does anyone could help me ?

I need to display it without subquery if possible

Thank You

Best Regards,

Steven Henry

Hi everyone,

I’m currently experimenting with the SQL Gateway Connection, CREATE FOREIGN SERVER, and the THROUGH command (IRIS Documentation). To speed up my tests, I’ve combined several DemoDataSets in a Docker container and would like to automate the creation of SQL Gateway Connections using code. 

(By the way, if you’re interested, the Docker container is available here: Demo DBs IRIS.)

To achieve this, I’m using SQL to create a connection by running a command like INSERT INTO %Library.sys_SQLConnection

Earlier this year I announced availability of a VS Code extension for coding in ObjectScript, Embedded Python or SQL using the notebook paradigm popularized by Jupyter. Today I published a maintenance release to correct a "getting started" problem.

Here's a video of the installation steps from the extension's README:

(video superseded by an update in a later comment)

Why not try it for yourself?

I recently met an ages old issue related to code maintenance:

• You have access to your IRIS server just over ODBC/JDBC
• no VSCode access, no Studio access
• no (Web-)Terminal access

But you need to take a look on Classes or Routines or Globals !!
Anyhow SQL is your friend.

• First you need a simple table as temporary text store.

CREATE GLOBAL TEMPORARY TABLE  arcc.txt (line VARCHAR(32000))

Not a thrilling action.

• Next you export your code to local file and import it to your table

CREATE PROCEDURE arcc.show (IN code VARCHAR()) 
  RETURNS INTEGER
  LANGUAGE OBJECTSCRIPT 
 {
	set file=

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 

the documentation - Defining Foreign Tables | Using InterSystems SQL | InterSystems IRIS Data Platform 2024.3 mentions "A user that creates a foreign server must have the %MANAGE_FOREIGN_SERVER administrative privilege" but I am not finding that security setting to give to my role.

A Quick Start to InterSystems Cloud SQL Data in Databricks

My IRIS instance is connected to a Postgres database using SQL Gateway and linked tables. One of these tables is projected to the Patient class. I want to select a record from this table by ID and convert it to a FHIR resource using the %ExistsId and %OpenId methods. I noticed that if I call these two methods from the console, the record is always found. But, if I do the same from the FHIR Facade layer, the %OpenId method returns NULL.

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:

Hi everyone,

I’m using EnsLib.SQL.OutboundAdapter in my InterSystems Ensemble operation to execute an insert statement in Sql Server Management Studio 20. While inline queries (e.g., constructing the query string with _ concatenation) work fine, I'm facing issues when trying to use parameterized queries.

Serialized JSON: {"CHI":"2894","Forename":"Janis"}

Include Ensemble

Class TNHS.Operation.RDS.PatientOperation Extends Ens.BusinessOperation{

Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";
Property Adapter As EnsLib.SQL.OutboundAdapter;
Method OnMessage(pRequest As TNHS.Data.RDS.

First time trying to use Foreign Tables/Servers instead of Linked Tables...

Within the SQL Editor inside of the Managment Portal, or connecting through DBeaver JDBC how we can see what Foreign Servers have been defined? Is there a way to query and verify structure of the Foreign Server connection to know that we are building the correct Foreign Tables?

I attempted to create my first Foreign table but it failed when I tried to query the tables because it said the table could not be found. But when I sign into the Database via SQL Management Studio, I can see the table.

Hello My Friends,

I have a question how to use order by %DLIST, this is my code:

SELECT

$ListToString(%DLIST(DISTINCT MRDIA_ICDCode_DR->MRCID_Code),', ' ) ICDX,

$ListToString(%DLIST(DISTINCT (MRDIA_ICDCode_DR->MRCID_Desc || ' (' || MRDIA_DiagnosisType_DR->DTYP_Code || ')')),', ' ) Diagnose

FROM SQLUser.PA_Adm

LEFT JOIN SQLUser.PA_AdmInsurance ON (PAADM_RowID = INS_ParRef AND INS_Rank = 1)

LEFT JOIN SQLUser.PA_AdmPackage ON (PAADM_RowID = PACK_ParRef)

LEFT JOIN SQLUser.MR_Adm on MRADM_ADM_DR = PAADM_RowID

LEFT JOIN SQLUser.MR_Diagnos ON MRADM_RowId = MRDIA_MRADM_ParRef

LEFT JOIN SQLUser.

I have issue with one of my queries. That query work fine for ages, but suddenly stop works and giving me SQLCODE 100 in the routine. 

&SQL(
	  SELECT * FROM (
		SELECT event FROM dhr_log_lasers.production WHERE createDateUTC >= DATEADD(dd, -5, CURRENT_DATE) AND kiosk = :%var("kioskID") GROUP BY machine
		HAVING ID = MAX(ID)
	  ) WHERE event != 2
	 )

If I copy this same query to the management portal and replace variable with real ID I will get back 6 or more rows.

The rise of Big Data projects, real-time self-service analytics, online query services, and social networks, among others, have enabled scenarios for massive and high-performance data queries. In response to this challenge, MPP (massively parallel processing database) technology was created, and it quickly established itself. Among the open-source MPP options, Presto (https://prestodb.io/) is the best-known option. It originated in Facebook and was utilized for data analytics, but later became open-sourced. However, since Teradata has joined the Presto community, it offers support now.

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)

And there is a Embedded Python with numpy, but logic behind STDDEV(MDX) and numpy.

Production Configuration

This demo has an interoperability production with 16 items. 

Production Configuration HL7 + Kafka Producer

The first part of this demonstration consists of sending an HL7 SIU file which will be transmitted to the 2 other HL7 flows (HTTP and TCP), and transformed and transmitted to the Kafka server. HTTP and TCP flows will transform HL7 messages in the same way before sending them to Kafka as well.

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

Hello all,

We have our system with AutoParallel enabled:

USER>w ##class(%SYSTEM.SQL.Util).GetOption("AutoParallel")
1

But whenever I try to run any sql the autoparallel does not work. For example, this simple query:

When I force it with %PARALLEL we can see it will effectively run in parallel:

The total records is bigger than the threshold. So, what can go wrong? Anyone in the same situation?

I'm playing with some anayltic queries against FHIR server tables. The HSFHIR_X0002_S_Patient.addressCity table contains a lot of cities which names contain german charachers such as ä, ö and ü.

The following query works fine:

select value from HSFHIR_X0002_S_Patient.addressCity

But this one converts city names to uppercase, and characters with umlauts are lost, so instead of "Köln" or "München" I see KOLN and MUNCHEN:

select ac.value, count(ac.value) as cnt
  from HSFHIR_X0002_S_Patient.addressCity ac
  group by ac.value
  order by 2 desc

I'm using DBeaver with IRIS official JDBC driver.

I HAVE A NEW SQL CLASS THAT DISPLAYS ENTRIES BASED ON THE FIRST 2 NODES OF A GLOBAL. I FOUND OUT THAT THE CLASS ALLOWS FOR AN ADDITIONAL NODE(s) TO BE INSERTED IN THE "User Specification Node:" along the delimiter and the Piece in the NewStorage Map1 for "ModifyDDDD" shown below. it is not working.

^GBL("AA","SSSSSS")="1:1:1:1:"

^GBL("AA,"MD",1,1)="1:"

^GBL("AA,"MD","A",1)="0:"

I HAVE USED THIS:

<Data name="ModifyDDDD">
<Delimiter>":"</Delimiter>
<Node>"3,4"</Node>
<Piece>1</Piece>
</Data>

WHILE IT COMPILES IT DISPLAY NOTHING. MY QUESTION IS WHAT IS THE RIGHT SYNTAX AND PUNTUATION FOR THIS, PLEASE?

InterSystems FAQ rubric

By default, the order of columns in a table is determined automatically by the system. To change the order, explicitly set the order for each property using the property keyword SqlColumnNumber when defining the class.

Example:

Property Name As %String [SqlColumnNumber = 2];

Please see the documentation below.

SqlColumnNumber

If you want to change the SQL table name, specify SqlTableName. If you want to change the column name (field name), specify SqlFieldName.

Both apply only to persistent classes.

Hi,

A customer today asked whether we could offer anything like the nice lineage charts that dbt offers, based on SQL view metadata. They are very much aware of our dbt-iris adapter, but were asking in the context of their landing area, which combines foreign tables and views, ahead of possibly using dbt for onward transformations.

Hello,

Is there a programmatic way (in ObjectScript) to determine if a string is an SQL reserved word (aside from using embedded SQL to try to name a property with that string and look for the error :-))?

Thank you,

Jean

Has anyone successfully tested the new THROUGH command in IRIS 2024.2 with a FOREIGN SERVER?https://docs.intersystems.com/iris20242/csp/docbook/Doc.View.cls?KEY=RS…

I have connected from a Docker instance to a VM. I was able to successfully set up the JDBC connection through the UI.

I then configured a foreign server with this connection:

But I am unable to send a SQL 'THROUGH' to the DB. I always get a:

I've get the same message if i try it via Management Portal.
I've also tried this:

and this

Always with the same error.

i am trying to embed a dynamic SQL into Objectscript code , but the %NEXT() will return 0 , however when i copy the same query and run it in the SQL bit it will give me the result i want 
the code :

set myquery = "SELECT"
set myquery = myquery_" $PIECE(Substring(M.Rawcontent,Charindex('PD1|',M.Rawcontent),1000),'|',4),"
set myquery = myquery_" H1.TargetConfigName,"
set myquery = myquery_" H.Timecreated,"
set myquery = myquery_" H.MessageBodyId"
set myquery = myquery_" FROM"
set myquery = myquery_" Ens.MessageHeader H"
set myquery = myquery_" left outer join Enslib_HL7.Message M ON (H.MessageBodyId=M.

Description

This is a template for a FastApi 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-fastapi-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/fastapi/.

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.