#SQL

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

} 

If I query select * from Sample.Person where Name = 'abe lincoln', it will return results where name = "Abe Lincoln". If I query select * from Sample.Person where Sample.

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:

SELECT '['||Material->Sifra||'] '||Material->Opis AS Material,
       SUM(MasaBlago) AS MasaBlago
 FROM Tehtanje.

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

CREATE DATABASE [Database to create]
WITH TEMPLATE [Database to 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.

ERROR <Ens>ErrFailureTimeout: FailureTimeout of 20 seconds exceeded in osuwmc.Visit.

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.

Hi Community,

I am trying to save data into SQL table but each entry  getting saved twice in the SQL table. Is there any reason data is saving Twice in the SQL table 

I have created a %Persistent class for the fields

Please find the business operation below

Class Patient.DBOperation Extends Ens.BusinessOperation

{

Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";

Property Adapter As EnsLib.SQL.OutboundAdapter;

Parameter INVOCATION = "Queue";

Method OnMessage(pInput As Patient.PatientDBReq, pOutput As Ens.Response) As %Status
{
    Set Entry=##class(Patient.PatientDBReq).%New()
    Set Entry.

Hi Community,

Enjoy watching the new video on InterSystems Developers YouTube:

⏯ Querying 6 Terabytes of Protected Health Information at Northwell @ Global Summit 2022

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!!

You might wonder, wait! I don't know how to create a store procedure in IRIS.

Introduction

This article is intended to be a simple tutorial on how to create ODBC connections and working with them, since I found starting with them a little bit confused, but I had amazing people to take my hand and walk me through it, and I think everyone deserves that kind of help too.
I'm going to divide each little part in sections, so feel free to jump to the one you feel the need to, although I recommend reading everything.
I'm going to use the sample data created in a previous article, Quick sample database tutorial: Samples.PersistentData, with the properties Name and Age.

I need a stored procedure where I can execute multiple delete from statements. 

This will work to create a stored procedure, but when I add in other delete from, get error invalid sql statements when try to create

CREATE PROCEDURE DeleteSpecimenTwo
  (IN Specno VARCHAR(50))
BEGIN
DELETE FROM PCONT WHERE PSPECNO = :Specno;
END;

This doesn't work

CREATE PROCEDURE DeleteSpecimenTwo
  (IN Specno VARCHAR(50))
BEGIN
DELETE FROM PCONT WHERE PSPECNO = :Specno;

DELETE FROM PSPEC WHERE PSPECNO = :Specno;
END;

Any suggestions

Thanks

Louie

Introduction

This is a simple tutorial on the quickest way I found to create a sample database for any purposes such as testing, making samples for tutorials, etc.

Creating a namespace

1. Open the terminal
2. Write the command "D $SYSTEM.SQL.Shell()"
3. Write "CREATE DATABASE " and the name you want for your namespace.

Now you have a new namespace in a faster way than creating it from the Management Portal - which of course offers way more configuration options.

To select it, quit the SQL Shell writing "q" and then typing 'zn "namespaceName"'.

The Art of Mapping Globals to Classes 1 of 3

Looking to breathe new life into an old MUMPS application?  Follow these steps to map your existing globals to classes and expose all that beautiful data to Objects and SQL.

By following the simple steps in this article and the next two you will be able to map all but the craziest globals to Caché classes.  For the crazy ones I will put up a zip file of different mappings I have collected over the years.  This is NOT for new data; if you don’t already have existing global please just use the default storage.

Newbie here...

I am looking to use fhir to gather patients in our end point that have a certain diagnosis and or certain insurance.

Is this what fhir can be used for?

In the past I have done the normal SQL query/extract to CSV file.  Looking to use fhir for this for future.

Thanks in advance for your time

Dean-O

With InterSystems IRIS 2022.2, we introduced Columnar Storage as a new option for persisting your IRIS SQL tables that can boost your analytical queries by an order of magnitude. The capability is marked as experimental in 2022.2 and 2022.3, but will "graduate" to a fully supported production capability in the upcoming 2023.1 release. 

The product documentation and this introductory video, already describe the differences between row storage, still the default on IRIS and used throughout our customer base, and columnar table storage and provide high-level guidance on choosing the appropriate storage layout for your use case. In this article, we'll elaborate on this subject and share some recommendations based on industry-practice modelling principles, internal testing, and feedback from Early Access Program participants. 

Is it possible to retrieve the SQL-Executing process ID (PID) from a process spawned from a UNION %PARALLEL? I have a value set in the SQL-Executing process that I need to replicate in the UNION %PARALLEL spawned process(es). The SQL-Executing process writes the value to a global subscripted by its PID so determining the SQL-Executing PID from the spawned process is what I need. I expected $ZPARENT from the spawned process would hold the SQL-Executing PID but the PID returned is different.

With thanks,

Michael Hill

Hi guys,

I'm working on a project with my client.
They have a visit table which has about 7,000,000 records. The table is used in a random search page witch holds 20+ conditions to be combined.
The table is defined as below:

CREATE TABLE SQLUser.

Hi,

      Can anyone please help me how to use regular expression/wild character on a sql query  for a pattern like C00.0 to C00.9, C01.0 to C01.9  and so on until C77.0 to C77.9  .

Thanks for your help

Thanks

Jude

The last days I've work with the great new feature: LOAD DATA With this post I would like to share my first experiences with you. The following points do not contain any order or other evaluation. These are only things that I noticed when using the LOAD DATA command. It should also be noted that these points are based on the IRIS Version 2021.2.0.617 which is a preview release. So it may be that my observations do not apply to newer IRIS versions. But maybe they are helpful for others.

1) The file path is on server side

I have done my first tests via JDBC.

Hi All,

I want to run an SQL like below

Select ID,Rollno,Marks,Name,Section,Teacher from Marks left outer join Student on Marks.StudentID=Student.ID

Now, the Student.ID is having the encrypted value which is not matching with Marks.StudentID (as this is clear value).

I can get the clear value using encrypted value as follow

^StoreValue("EncryptStudentID",Student.ID)=$LB("123"). That 123 is the clear Student.ID which I need to match in SQL query (ON clause).

Now I Need to fetch the data from sql query matching both clear values.