#SQL

I'm participating in the Developing with InterSystems Objects and SQL with Joel Solon. The course is very nice and I will share with you some tips I got during the training. Tips presented in the day 4:

This is a translation of the following article. Thanks [@Evgeny Shvarov] for the help in translation.

Someone posted a question on DC asking whether it was possible to determine access rights for a particular table row always at runtime, and if it was, how could one do that?
Answer: it is possible and it’s not hard at all.

On the Latest GlobalSummit 2022, InterSystems Introduced Cloud SQL. So, you may have lightweight InterSystems IRIS with access to SQL only. Well, what if you would still need some Interoperability features in the cloud as well? There are various solutions on the market nowadays, which offer a bunch of integration adapters out of the box and can be extended with support from the community. Some time ago, I've implemented an adapter for the Node-RED project, which can be deployed manually everywhere you want. Now I would like to introduce a new integration with my recent discovery, n8n.io

n8n.io is a workflow automation platform, that supports over 200 different integrations out of the box and from a community, and now including InterSystems IRIS.

This is the third article in our short series around innovations in IRIS SQL that deliver a more adaptive, high-performance experience for analysts and applications querying relational data on IRIS. It may be the last article in this series for 2021.2, but we have several more enhancements lined up in this area. In this article, we'll dig a little deeper into additional table statistics we're starting to gather in this release: Histograms

What do you do if you want to have the ID field have a meaningful name for your application?

Sometimes it comes to pass that when you're making a new table that you want to have the unique row identifier (a.k.a. IDKEY) to be a field that has a name that is meaningful for your data. Moreover, sometimes you want to set this value directly. Caché fully supports this functionality and it works Suppose you have a class Test.Kyle. The data will be stored like so:

^Test.Kyle(IDKEY)=$LB("",Field1,Field2,...,Fieldn)

So I know it's been a while, and I hate to let my adoring fans down... just not enough to actually start writing again. But the wait is over and I'm back! Now bask in my beautiful ginger words!

For this series, I am going to look at some common problems we see in the WRC and discuss some common solutions. Of course, even if you find a solution here, you are always welcome to call in and expression you gratitude, or just hear my voice!

This week's common problem: "My query returns no data."

Let's say we have two serial classes, one as a property of another:

Class test.Serial Extends %SerialObject
{
Property Serial2 As test.Serial2;
}

Class test.Serial2 Extends %SerialObject
{
Property Property As %String;
}

And a persistent class, that has a property of test.Serial type:

Class test.Persistent Extends %Persistent
{

Property Datatype As %String;

Property Serial As test.Serial;

}

So it's a serial, inside a serial, inside a persistent object.

InterSystems FAQ rubric

Countermeasures against SQL injection have been published on various websites, but we believe that it is possible to prevent SQL injection in applications using InterSystems SQL as well as other RDBMS by implementing these countermeasures appropriately. In addition, InterSystems Data Platform (hereinafter referred to as IRIS) incorporates several measures that make SQL injection more difficult than general RDBMS.

Named parameters can be achieved with SQLAlchemy :

from sqlalchemy import create_engine, text,types,engine

_engine = create_engine('iris+emb:///')

with _engine.connect() as conn:
    rs = conn.execute(text("select :some_private_name"), {"some_private_name": 1})
    print(rs.all())

or with native api

from sqlalchemy import create_engine, text,types,engine

# set URL for SQLAlchemy
url = engine.url.URL.create('iris', username='SuperUser', password='SYS', host='localhost', port=33782, database='FHIRSERVER')

_engine = create_engine(url)

with _engine.connect() as conn:
    rs = conn.execute(text("select :some_private_name"), {"some_private_name": 1})
    print(rs.all())

One of the reasons why I love Cache and Iris is that not only you can do anything you can imagine, also you can do it in a lot of different ways!!.

Imagine that you have an integration running with IRIS connected by ODBC you probably only run SQL queries but you can also create stored procedures and inside write the code to do everything you can imagine.

I'm going to give you some examples but the limit is your imagination!!

Presenter: Anton Umnikov
Task: Identify your slowest SQL queries and tune them for better performance
Approach: Use InterSystems’ query profiling and analysis tools. Discuss how system configuration can affect performance

This session will show you how you identify the weakest link in your application SQL and introduce you to the fine art of tuning those queries. To do this we will take a look at InterSystems query profiling and analysis tools, as well as how system configuration can impact SQL performance.

Problem: Obscurity on how our SQL engine works

Content related to this session, including slides, video and additional learning content can be found here.

There is an interesting new feature in the recently announced 2024.1 preview, JSON_TABLE. JSON_TABLE is one of a family of functions introduced by the 2016 version of the SQL Standard (ISO Standard, published in early 2017). It allows for JSON values to be mapped to columns and queried using SQL. JSON_TABLE is valid in the FROM clause of some SQL statements.

The syntax of JSON_TABLE is quite large, allowing for exceptional conditions where the provided JSON values don't match expectations, nested structures and so on.

We recently encountered an Embedded SQL issue while upgrading to IRIS 2021.1, and thought the issue and workaround might be interesting to share.

Key takeaway: Host variables in an ORDER BY clause of an embedded SQL query that is inside of a method don't work as expected. IRIS versions starting with 2020.1 are affected. As a workaround, add the host variable to the Method's PublicList list and "new" them so the embedded query has access to them.

InterSystems FAQ rubric

By specifying the start and end values ​​of the IDs for which you want to rebuild indexes in the arguments of the %BuildIndices() method provided in the persistent class (=table) definition, you can rebuild only the indexes within that range.