#SQL

Hi Developers!

There is a recent update came for developer community images of InterSystems IRIS and IRIS For Health.

This release comes with Environment variables support.

Currently 3 variables are supported:

• IRIS_USERNAME=user to create
• IRIS_PASSWORD=with password
• IRIS_NAMESPACE=create namespace if doesn't exist

Here is what you can do - see below.

Start iris with your username and password created:

docker run --rm --name iris-sql -d -p 9091:1972 -p 9092:52773  -e IRIS_PASSWORD=demo -e IRIS_USERNAME=demo intersystemsdc/iris-community

 i use %SQLGatewayConnection to insert data to mysql from xml，when if the value`s ascii of any field in the sql equal to 57659 ，

Hi Developers!

Here is the score of technical bonuses for participants' applications in the InterSystems Developer Tools Contest 2023!

We have a rule to disable a user account if they have not logged in for a certain number of days. IRIS Audit database logs many events such as login failures for example. It can be configured to log successful logins as well. We have IRIS clusters with many IRIS instances. I like to run queries against audit data from ALL IRIS instances and identify user accounts which have not logged into ANY IRIS instance.

Hi Community!

Please welcome a new video on InterSystems Developers YouTube Channel:

Getting Sharded with InterSystems IRIS

Hi Community,

Watch this video to see how to connect to InterSystems Cloud Services from your Java application using the InterSystems JDBC driver:

⏯ Connecting to InterSystems Cloud Services with Java

Here're the technology bonuses for the InterSystems IRIS Cloud SQL and IntegratedML Contest 2023 that will give you extra points in the voting:

• IntegratedML usage
• Online Demo
• Article on Developer Community
• The second article on Developer Community
• Video on YouTube
• First Time Contribution
• Community Idea Implementation
• IRIS Cloud SQL Survey

See the details below.

Within IRIS I defined a task to export audit data every day.

I provide some sample files in my GitHub repo.

I wrote ObjectScript code to import all files into otw.audit.consolidator class.

I want to use Python SQLAlchemy, pandas and sqlalchemy-iris (created by Dmitry Maslenikov) to copy consolidated audit data from my consolidator class in my IRIS container to my InterSystems Cloud SQL deployment.

I updated my Dockerfile to pip3 install sqlalchemy-iris and pandas (dataframes).

docker-compose build –no-cache in my personal AWS took 700 seconds.

Hi Community,
In this article, I will introduce my application iris-mlm-explainer

This web application connects to InterSystems Cloud SQL to create, train, validate, and predict ML models, make Predictions and display a dashboard of all the trained models with an explanation of the workings of a fitted machine learning model. The dashboard provides interactive plots on model performance, feature importances, feature contributions to individual predictions, partial dependence plots, SHAP (interaction) values, visualization of individual decision trees, etc.

Prerequisites

I have Audit consolidator deployed in AWS where I scheduled Audit Export task to run daily.

Xml files are stored in mgr directory because the task runs in %SYS namespace.

/usr/irissys/mgr

-rwxrw-r--. 1 irisowner irisowner 249080 Apr  9 21:48 8eedba82d0ee_2023-04-09_auditexport.xml*

-rwxrw-r--. 1 irisowner irisowner  19487 Apr 10 00:02 8eedba82d0ee_2023-04-10_auditexport.xml*

-rwxrw-r--. 1 irisowner irisowner  23554 Apr 11 00:02 8eedba82d0ee_2023-04-11_auditexport.xml*

-rwxrw-r--. 1 irisowner irisowner  27624 Apr 12 00:02 8eedba82d0ee_2023-04-12_auditexport.xml*

Data Initialization

1. Create a table with sql statements

create table IF NOT EXISTS MLTEST_MSG.HeightWeightPredictMSG (Id int primary key identity(101,2),number varchar(50), Height float, Weight  float);

create table IF NOT EXISTS MLTEST_MSG.HeightWeightMSG (Id int primary key identity(101,2),number varchar(50), Height float, Weight  float);

create table IF NOT EXISTS MLTEST_MSG.FamilyMSG(id int primary key identity(101,2),Csex int, Family float,Father float,Mother float, num int,Height float);

Hey Developers,

We'd like to invite you to join our next contest dedicated to creating AI/ML solutions that use Cloud SQL to work with data: 

🏆 InterSystems IRIS Cloud SQL and IntegratedML Contest 🏆

Duration: April 3 - April 23, 2023

Prize pool: $13,500

The SQL editor in the contest environment promises these features
InterSystems SQL Reference.  

But this is the reality:

I see no chance to have a different user.
Or did I miss something?
Or is this just fiction from ChatGPT ?

Hi all,

I'm trying to use LOAD DATA to insert 11k (11,377) rows of data. LOAD BULK DATA is not available for the version of IRIS I am using.

After calling LOAD DATA it says only 5,500 rows has been inserted. The LOAD DATA docs says any error rows are skipped and a count of skipped rows can be found in %SQL_Diag.Result however there are no results here. There are no errors in the xDBC error log either.

Why have over half the rows been skipped?

In the vast and varied SQL database market, InterSystems IRIS stands out as a platform that goes way beyond just SQL, offering a seamless multimodel experience and supporting a rich set of development paradigms. Especially the advanced Object-Relational engine has helped organizations use the best-fit development approach for each facet of their data-intensive workloads, for example ingesting data through Objects and simultaneously querying it through SQL. Persistent Classes correspond to SQL tables, their properties to table columns and business logic is easily accessed using User-Defined Functions or Stored Procedures. In this article, we'll zoom in on a little bit of the magic just below the surface, and discuss how it may affect your development and deployment practices. This is an area of the product where we have plans to evolve and improve, so please don't hesitate to share your views and experiences using the comments section below.

Assuming I have an SQL table with data, is it possible to get DML export (INSERT statements for this data)?

InterSystems FAQ rubric

The ID of the last updated record can be obtained using the SQL function LAST_IDENTITY().
* This function can be used with embedded SQL or ODBC but not with Dynamic SQL, SQL Shell, or the Management Portal's SQL interface.

Here's an example of usage with simple Embedded SQL:

looking into tenants  I see this:

But on the main page, I have 2 deployments that I can work with  !! 

What is going on ??

I have  an accepted tenant and want to share my deployment.

how is this possible?

On an IRIS system, we expect the default string collation for SQL columns to be SQLUPPER. This means WHERE conditions will be case-insensitive. However, when I make a WHERE condition on a SQL procedure that returns a string, it's case sensitive.For example:

Class Sample.Person Extends%Persistent
{

Property Name As%String;ClassMethod Test() As%String [ SqlProc ]
{
    return"Abe Lincoln"
}

} 

Hi developers!

What is the way using SQL to list all the tables available in the current database/namespace?

Hi.

I have a query:

SELECT '['||Material->Sifra||'] '||Material->Opis AS Material,
       SUM(MasaBlago) AS MasaBlago
 FROM Tehtanje.Dokument
 WHERE DatumDokumenta BETWEEN '01/01/2023' AND '04/01/2023'
 AND (Material->Sifra %INLIST $LISTFROMSTRING('5,7',','))
 GROUP BY Material
 ORDER BY %EXACT Material

The query returns all rows where Material->Sifra is 5 or 7. That's OK.

If I want to get all rows where Material->Sifra is NOT 5 or 7 I use query:

Most of the databases with SQL access offers the DATE_TRUNC function or analogs. This helps to easily truncate a date by any part while keeping the DateTime in a valid format

In some testing scenarios, I need to clone the database. While I use only SQL access.

I need all data to be cloned, not mapped from the original database, and a separate database to be accessed.

Is there any way to do so?

Something like

CREATEDATABASE [Databasetocreate]
WITHTEMPLATE [Databaseto copy]

In the documentation  Data Types | Caché SQL Reference | Caché & Ensemble 2018.1.4 – 2018.1.8 (intersystems.com)

it says that "by default, Caché establishes a system-wide ODBC VARCHAR maximum length of 4096; this ODBC maximum length is configurable"

Per instructions, I went to SMP -> System -> Configuration -> General SQL Settings, where most of the fields from  $SYSTEM.SQL.CurrentSettings()

appear. That one does not.

How does one change it, and can it be increased?

Thanks!

[Cross-posted from https://groups.google.com/g/intersystems-public-cache/c/eRFeORb_sb0]

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

I am trying to pinpoint and troubleshoot an issue we have been seeing lately. We have several SQL Outbound Adapter Operations that are seeing issues trying to connect to our MS SQL Servers using the JDBC Gateway for calling insert, select, update, and stored procedure calls.

We are migrating to new servers, and I need to copy the information from Business Partner's over to the new server. Is this information in a Global or SQL table somewhere that I can export and import into the new IRIS environment?

Thanks

Scott

Introduction

Say you have a receiving system that accepts HL7 and provides error messages in field ERR:3.9 in the ACK it returns. You require a different reply code action depending on the error message, however the Reply Code Actions settings for the operation do not provide this level of granularity. One option could be to create a process that takes the ACK and then completes the action you were expecting, however things can get a bit messy if the action is to retry the message, especially when trying to view a message trace.

This is the second piece in our series on 2021.2 SQL enhancements delivering an adaptive, high-performance SQL experience. In this article, we'll zoom in on the innovations in gathering Table Statistics, which are of course the primary input for the Run Time Plan Choice capability we described in the previous article.