New post

#SQL

13

SQL is a standard language for storing, manipulating and retrieving data in relational databases.

If I had:
________________________________________
Class MN.Student Extends %Persistent
{
Property Name As %String;
________________________________________
Class MN.Course Extends %Persistent
{
Property TeacherName As %String;
Relationship StudentName As MN.Student [ Cardinality = one, Inverse = Name ];
________________________________________

For "Cardinality = one" the SQL line below would be sufficient for archiving
INSERT INTO MN.Course (TeacherName, StudentName) VALUES ('Pierre','3')"

0 1
0 146

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.

Open Exchange app
1 1
0 200

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

https://www.youtube.com/embed/yUn5hdUVlXo
[This is an embedded link, but you cannot view embedded content directly on the site because you have declined the cookies necessary to access it. To view embedded content, you would need to accept all cookies in your Cookies Settings]

0 0
0 282
Article
· Apr 19, 2023 2m read
Apache Superset now with IRIS

Apache Superset is a modern data exploration and data visualization platform. Superset can replace or augment proprietary business intelligence tools for many teams. Superset integrates well with a variety of data sources.

And now it is possible to use with InterSystems IRIS as well.

An online demo is available and it uses IRIS Cloud SQL as a data source.

Open Exchange app
6 4
0 904

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

Open Exchange app
6 1
1 744
Article
· Apr 4, 2023 2m read
InterSystems SQL Cheat Sheet

Hi developers!

As you know InterSystems IRIS besides globals, object, document and XML data-models also support relational where SQL is expected as a language to deal with the data.

And as in other relational DBMS InterSystems IRIS has its own dialect.

I start this post to support an SQL cheatsheet and invite you to share your favorites - I'll update the content upon incoming comments.

Here we go!

10 26
7 1.4K

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

0 2
0 427
Question
· Apr 1, 2023
NOT %INLIST

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:

0 2
0 328
Article
· Mar 29, 2023 1m read
Named Parameter In SQL with Python

Quick Tips: Total Productive Maintenance

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

6 0
0 520
Question
· Mar 23, 2023
Saving data into SQL table

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

0 6
0 347

Hi Community,

Enjoy watching the new video on InterSystems Developers YouTube:

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

https://www.youtube.com/embed/66rZCdX7m8U
[This is an embedded link, but you cannot view embedded content directly on the site because you have declined the cookies necessary to access it. To view embedded content, you would need to accept all cookies in your Cookies Settings]

1 0
1 359

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

13 1
2 463