#SQL

In this article, exceptions are covered.

Working with Exceptions

Instead of returning a %Status response, you can raise and throw an Exception. You are then responsible for catching the exception and validating it. IRIS provides five main classes to handle exceptions effectively. Additionally, you can create custom exception class definition based on your needs.

From the previous article, we identified some issues when working with JSON in SQL.

IRIS offers a dedicated feature for handling JSON documents, called DocDB.

InterSystems IRIS® data platform DocDB is a facility for storing and retrieving database data. It is compatible with, but separate from, traditional SQL table and field (class and property) data storage and retrieval. It is based on JSON (JavaScript Object Notation) which provides support for web-based data exchange. InterSystems IRIS provides support for developing DocDB databases and applications in REST and in ObjectScript, as well as providing SQL support for creating or querying DocDB data.

By its nature, InterSystems IRIS Document Database is a schema-less data structure. That means that each document has its own structure, which may differ from other documents in the same database. This has several benefits when compared with SQL, which requires a pre-defined data structure.

The word “document” is used here as a specific industry-wide technical term, as a dynamic data storage structure. “Document”, as used in DocDB, should not be confused with a text document, or with documentation.

Let's explore how DocDB can help store JSON in the database and integrate it into projects that rely solely on xDBC protocols.

Host Variables are a rather common programming feature in many implementations of SQL. 
A recent question in DC made me aware that in IRIS, Caché, Ensemble, ... 
host variables just exist within embedded SQL

>  You can supply host variables for Embedded SQL queries only. <

Related examples are included in the available Documentation

This is a description  for a workaround if you don't / can't use embedded SQL.

You need to install the application first. If not installed, please refer to the previous article

Application demonstration

After successfully running the iris image vector search application, some data needs to be stored to support image retrieval as it is not initialized in the library.

Image storage

Firstly, drag and drop the image or click the upload icon, select the image, and click the upload button to upload and vectorize it. This process may be a bit slow.

This process involves using embedded Python to call the CLIP model and vectorize the image into 512 dimensional vector data.

While working on getting JSON support for some Python libraries, I discovered some capabilities IRIS provided.

• JSON_OBJECT - A conversion function that returns data as a JSON object.
• JSON_ARRAY - A conversion function that returns data as a JSON array.
• IS JSON - Determines if a data value is in JSON format.
• JSON_TABLE function returns a table that can be used in a SQL query by mapping JSON.
• JSONPath support - is a query language for querying values in JSON.

I am currently experiencing frustration with trying to Authenticate an Active Directory account through JDBC as the Hospital System moves from OnPrem SQL Server to using Azure SQL Server with Microsoft Entra Authentication.

Microsoft cannot give me a straight answer of what is required from a JDBC standpoint to authenticate from a Linux environment.

Not sure there are many that connect to MS SQL to execute queries, stored procedures, etc, but our Healthsystem has many different MS SQL based databases we use within the Interoperability environment for various reasons.

With the push to moving from on-prem to the Cloud we ran into some difficulties with our SQL Gateway connections and knowing how to config them to use Microsoft Entra for Active Directory Authentication.

Hello guys,

I need to tranform columns into rows or rows into columns.
I'm using only SQL Cache.

The code:

SELECT
    codRepresentante
    ,vendasAbril
    ,vendasAgosto
FROM
    Ped.MetasRepresen
WHERE ano = 2024

The columns: 

Can you help me?

Thanks

Hi Everyone!

The Certification Team of InterSystems Learning Services is currently developing an InterSystems ObjectScript Specialist certification exam, and we are reaching out to our community for feedback that will help us evaluate and establish the contents of this exam.   

Please note that this is one of two exams being developed to replace our InterSystems IRIS Core Solutions Developer exam. You can find more details about the other exam, InterSystems IRIS Developer Professional exam, here. 

Hello everyone,

I’m facing issues with replicating data from my Caché 2016 database to a PostgreSQL database. I need to handle around 300 data updates per minute, and whenever certain tables are modified, those changes must be reflected in other databases.

So far, I’ve tried various approaches, including:

• Setting up an intermediary API,
• Using Azure Service Bus,
• Leveraging Caché Jobs,
• All of which rely on table triggers as the entry point.

I need to use a context variable in my code block for the table to query. When I use :content.table, it does not work.  When using the literal, it does.  How do I get the :context to work?

Hi! I've extended my demo repository, andreas5588/demo-dbs-iris, to make it easy to test the FOREIGN SERVER and FOREIGN TABLE features in IRIS.

To achieve this, I created a namespace called FEDERATION. The idea is as follows:

1. Set up JDBC connections for each namespace.
2. Create a FOREIGN SERVER within the FEDERATION namespace for each connection.
3. Define a FOREIGN TABLE a least for one table based on each foreign server.

The Script:  demo-dbs-iris/src/sql/02_create_foreign_server.sql

In tracking down some of our Orphaned message issues, I was wondering if setting our EnsLib.SQL.Snapshot variable equal to "" was the same as calling the .Clean() method on EnsLib.SQL.Snapshot? Are they the same?

should I be possibly using ##class(EnsLib.SQL.Snapshot).%Delete() instead?

Hy Friends,

I have a little problem, I need to diplay the data based on input status (first time and after)

this is the data:

and this what I need to display

the notes become new because it is the first time that the data has been inputed, and it will become old if we have the data before (2nd data, 3rd data  the notes will become old).

I've try to use SubQuery (with LAG Script also) but it doesn't works, because it will give a notes Old even it's a New Data

Does anyone could help me ?

Thank You

Best Regards,

Steven Henry

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:

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?

Here at InterSystems, we often deal with massive datasets of structured data. It’s not uncommon to see customers with tables spanning >100 fields and >1 billion rows, each table totaling hundred of GB of data. Now imagine joining two or three of these tables together, with a schema that wasn’t optimized for this specific use case. Just for fun, let’s say you have 10 years worth of EMR data from 20 different hospitals across your state, and you’ve been tasked with finding….   every clinician within your network      who has administered a specific drug         between the years of 2017-2019

Auditing is a critical capability ensuring the security of your server, and for quite a while ago we have included the ability to audit SQL statements run on the server.

With v2024.3 out already we're providing finer-grained options for defining these events to audit.

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: 

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.)

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.

CREATEGLOBALTEMPORARYTABLE  arcc.txt (line VARCHAR(32000))

Not a thrilling action.

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

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. The error is intermittent - yesterday FHIR Facade didn't work at first, then it worked, this morning when I try to select a patient record from the FHIR facade layer using the %OpenId method it returns NULL again. I also checked the context of the current user using the $USERNAME function, in both cases the current user is _SYSTEM. How can this be explained?

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"}