Clear filter
Announcement
Evgeny Shvarov · Dec 7, 2016
Hi, Community!Today, the 7th of December is the very good day - today is InterSystems Developer Community birthday!One year ago this day we sent tons of email invitations to you, dear members, to join DC.And you joined and hope you like the site!Please feel free to provide your feedback! Please provide only positive below in the comments, for the negative ones we have this page.So, what do we have after a year?How large are we?Here is the picture:We have 2,200 registered members from all over the world, a set of really helpful articles and the working Q&A forum.We have the site which gives us knowledge, experience and sometimes fun!Thank you for your contribution and participation!We can make the place better, please share your ideas too.We have great plans for the next year in Developer Community, stay tuned! Congratulations! Congratz Gratz! Congrats!!the articles and discussions are very useful for all levels of Caché developers. I find it interesting, when I think of Dec 7, I don't think of the Developers Community, I think of the sneak attach on Pearl Harbor, but, maybe I am just getting old. You asked for ideas, one thing I asked for a long time ago and still think it is needed is a Help function on how all of this works. It is not obvious to be sure. I must be getting old also... Actually that is an event that should always be remembered. Good point, Mike! Thank you! We'll do soon. A day that shall live in infamy. You're not old, just an American. I actually baked a cake to commemorate this occasion, and totally not because I'm fat and wanted cake. Hard to believe it's already been a year
Question
Sébastien Demoustiez · Dec 15, 2016
Hello, Is there some way to reproduce the SQL GROUP_CONCAT(http://sql.sh/fonctions/group_concat) with the Caché DB ?Thanks Sébastien Exactly that :) Thanks a lot. Strange but with this function the perfermance are really bad :(0.04s without and 13s with it ... If you want to investigate the performance of the query you can always open a WRC Case (wrc.intersystems.com :-D).Otherwise, send out your class definition and query plan and I can take a cursory look at it. I tried with an other table with just 2 properties ( but more than 1million records too) and that's the same problem.see:http://imgbox.com/Df2WlAPohttp://imgbox.com/tEFMR5V8 Looks like, it works exactly as LIST function in Caché Looking at that documentation, one difference between LIST and GROUP_CONCAT is that GROUP_CONCAT lets you specify the separator, while LIST always uses a comma.
If you wanted to use a different separator, and your data will never contain commas, then it's as easy as (for example):
select home_city as "City", count(*) as "Count", REPLACE(LIST(Name),',',' ') as "Names"
from sample.person
group by home_city
If "your data will never contain commas" is a bad assumption (as it is in the case of Name in Sample.Person), the solution is to use %DLIST and $ListToString.
select home_city as "City", count(*) as "Count", $ListToString(%DLIST(Name),' ') as "Names"
from sample.person
group by home_city
%DLIST builds a $ListBuild list, and $ListToString joins the list elements with the specified separator.
%DLIST is useful in other cases too - for example, if your data might contain commas and you want to iterate over the aggregated data after running dynamic SQL.
Question
Joaquin Montero · Mar 2, 2020
Hi Everyone,
I've been working on deploying an IRIS for Health environment in EKS. There is a video session in the InterSystems learning portal about this feature but I have not succeeded in finding the proper documentation and resources to use this in my Kubernetes cluster.
Has this been deprecated/discontinued? Any idea where can I find the resources? Should I stick to StatefulSets instead of using the IrisCluster resource type provided by this operator? Tagging @Luca.Ravazzolo and @Steven.LeBlanc I cannot find any real information about the Kubernetes operator either. They said in the video that it's ready and working, but it does not seem that way. Hi Guys
Thank you for your interest on the InterSystems Kubernetes Operator.
We are working hard at preparing it to be available for 2020.2 timeframe.
Stay tuned.
Discussion
Eduard Lebedyuk · Mar 6, 2020
Temporary tables are tables available for a current process only (and destroyed when process ends).
What are you approaches to creating temporary tables?
Here's the two I know:
Process-private Globals storage can be used as a data global in storage definition. That way, each process can have its own objects for the class with ppg storage. Here's how. Here's how 2.
InterSystems TSQL supports #tablename temporary tables. A #tablename temporary table is visible to the current procedure of the current process. It is also visible to any procedure called from the current procedure. #tablename syntax is only supported in TSQL procedures (class methods projected as procedures with language tsql).
Any other ways to achieve similar functionality? CREATE GLOBAL TEMPORARY TABLE <mytable> {} has similar effects as process-private globals... Indeed, that's the recommended SQL way of achieving what Eduard described about PPGs. Drawbacks are that queries on these tables cannot be parallelized (as that implies multiple processes, of course).
Our TSQL support is meant for Sybase customers wishing to redeploy their TSQL applications on IRIS (especially now that SAP/Sybase is terminating support for those platforms). Just temporary table support by itself wouldn't be a reason to start building TSQL applications and abandon IRIS SQL/ObjectScript, of course :-). However, for a recent TSQL migration we did some work on our TSQL temp table support and were considering to roll that out to regular IRIS SQL, so this thread is a good place to share your experiences and requirements so we can make sure to do that properly, as needed. Hi,
I recently needed a temporary class (not just a table) to store data while it was manipulated (imported, queried, modified, etc. and eventually exported) . I eventually set up all the storage locations with PPG refs as noted above, which works, but did wonder if there was a class parameter I had missed that just indicated the extent was temporary. Or maybe an alternative to extending %Persistent?
Mike I'm not really sure what do you mean by
if there was a class parameter I had missed that just indicated the extent was temporary
what do you want to achieve with this? Hi,
I don't want to achieve anything else, it's just that "cache being cache" there's often another way to do the same thing and it might be easier. :-)
Mike
Announcement
Derek Robinson · Mar 26, 2020
In Episode 6 of Data Points, certification manager Jamie Kantor joins the podcast to tell us all about the InterSystems Certification program, what exams are currently being offered (and what ones are in development), and why it matters for developers and enterprises. Check it out!
For more information and to subscribe on your favorite podcast app, head over to https://datapoints.intersystems.com. There, you can also find links to the previous episodes. Make sure to rate the podcast if you're using Apple Podcasts, as well!
Announcement
RB Omo · Mar 12, 2020
In light of the COVID-19 situation worldwide, InterSystems is working to ensure continuous support for our customers, promote the health and well-being of our employees, and protect the communities where we live and work. Please view our statement and frequently asked questions.
In addition, we have also established a special email address, COVID-19@InterSystems.com, should you have any questions or concerns.
As we have for 41 years, we stand by our commitment to customers and are prepared to support you during this challenging time.
Sincerely,
John Paladino
Vice President, Client Services
Announcement
Anastasia Dyubaylo · Jan 13, 2020
Dear Community,
We're pleased to invite you to the InterSystems Benelux Symposium 2020, which will take place from February 11th to 12th in Antwerp, Belgium!
At the Symposium, both InterSystems experts and external thought leaders will discuss what it takes to make your IT innovation work. You're more than welcome to join us in the Radisson Blu Astrid Hotel in Antwerp.
Fastest Path to Possible - With Digital Innovation
"The future lies in the hands of those who use data the best", said InterSystems CEO & Founder Terry Ragon at the InterSystems Global Summit 2019. "However, this data is spread across an enormous set of databases and hardware devices, meaning we need to find ways to connect these systems together."
There is, of course, a way: interoperability. But how do you approach interoperability? And most importantly: how can you use it to create better applications and be a better partner to your customers?
Be part of the discussions! Don’t miss out and register for the event by clicking right here.
Can’t wait to see you in Antwerp!
So, remember:
⏱ Time: February 11-12, 2020
📍Venue: Radisson Blu Astrid Hotel, Antwerp, Belgium
✅ Registration: SAVE YOUR SEAT TODAY I'm finally going to Antwerp this year.
So, you have a chance to meet me there, to see VSCode-ObjectScript in action, get quick help with the migration process, and give your feedback.
See you there. Will have two sessions on Feb 12th on during InterSystems Benelux Summit on behalf of CUG meetup.
See you there! So, today, I'm presenting a demo of VSCode-ObjectScript at CUG meetup here in Antwerp ObjectScript Package Manager presentation is attached Please welcome a brief video overview of the InterSystems Benelux Symposium 2020:
Enjoy! 👍🏼
Announcement
Vadim Aniskin · Oct 2, 2023
Hey Community!
Welcome to the 8th edition of the InterSystems Ideas news bulletin! Here's what you can expect from it:
✓ New video describing all pages of the Ideas Portal
✓ Ideas implemented since the previous news bulletin
⏯Watch the video: Join the Hall of Fame
Ideas Portal contains, in addition to the "Ideas" page, 4 new pages:
Portal Guide
News
Polls
Hall of Fame
Check them out to always be up to date about what's going on on Ideas Portal!
Since the launch of the Ideas Portal, 24 of the ideas have already been implemented by the Community members. And 6 of them were realized during the last 3 months:
Idea
Implementation of the idea (project)
Developer
Include support for GraphQL by @Jose-Tomas.Salvador
GraphQL
@Gevorg.Arutiunian9096
REST API for Security Package by @Evgeny.Shvarov
Config-API
@Lorenzo.Scalese
Jupyter Notebook by @Alex.MacLeod
Jupyter Server Proxy for VS Code
@John.Murray
Java Hibernate support for IRIS by @Dmitry.Maslennikov
IRIS Hibernate Dialect
@Yuri.Gomes
Example of Flask application with SQLAlchemy IRIS by @Dmitry.Maslennikov
IRIS-FlaskBlog
@Muhammad.Waseem
Support for Liquibase by @Yuri.Gomes
liquibase-iris
@Dmitry.Maslennikov
👏 Thank you for implementing and posting these ideas 👏
Stay tuned to read our next announcements!
In the meantime post your brilliant ideas, vote for existing ideas, and comment on them on our InterSystems Ideas Portal! Hi Developers!
Based on the portal user's requests we added a new page to the Ideas Portal recently: Idea-A-Thon Winners
Idea-a-Thon winners:
@Andre.LarsenBarbosa , @Yuri.Gomes , @Heloisa.Paiva , @Scott.Roth , @Sharafat.Hussain , @Danny.Wijnschenk
I hope you like this page, and thanks again for your ideas!
Vote for ideas to make them real!
Announcement
Brenna Quirk · Nov 14, 2023
New to coding in InterSystems IRIS® data platform? Try this brand-new learning program to get the basics, and see how to develop an application with InterSystems ObjectScript alongside your language of choice: Java, .NET, Python, or Node.js.
Announcement
Vadim Aniskin · Dec 27, 2023
Hi Developers!
Welcome to Issue 10 of the InterSystems Ideas newsletter! Here's what you can expect from it:
✓ Month of ideas about "FHIR and Digital Health Interoperability"
✓ Reached 300 ideas on the Ideas Portal 🎉
✓ Recently implemented ideas
Since the new programming contest is dedicated to FHIR and Digital Health Interoperability, we've decided to gather all the related ideas in one place (see the table below). To show your interest and support for your fellow members, we invite everyone to vote for existing ideas or post new ones before the start of the Contest.
Create new ideas devoted to "FHIR and Digital Health Interoperability" and promote your ideas on the Community by creating discussions. The more votes an idea receives, the greater the chances are of its implementation. Vote and comment on existing ideas related to FHIR and Digital Health Interoperability:
Idea
Author
RPMShare - Database solution for remote patient | InterSystems Ideas
@Dmitry.Alexeev
HL7 test message generator
@Vadim.Aniskin
Introduce an Interoperability adapter to import apple health data to IRIS
@Evgeny.Shvarov
Intellisense for FHIR resources
@Jose-Tomas.Salvador
Interoperability Production: "Add a 'Type-to-Filter' ability in dropdown selections"
@Victoria.Castillo2990
Data Analyzer
@Stella.Tickler
HealthShare Patient Unmerge Ooopses
@Sawyer.Butterfield
Editable HL7 Editor in the Message Viewer
@Evgeny.Shvarov
Iterative build of TrakCare configuration/code tables utilising FHIR and HL7 Messaging.
Linda McKay
FHIR step by step setup
@Scott.Roth
Introduce an Interoperability Adapter for POCT1A and POCT1A-2 standards
@Evgeny.Shvarov
Speech Recognition to Prepopulate Incoming Patient Data
Jayesh Gupta
When we launched the portal last year, we had very few ideas. A year and a half later, the number of ideas on the portal exceeded 300. 🌟
Thank you so much @Armin.Gayl5931 for posting the 300th idea Optimization of the HL7 Schema documentation to the Ideas Portal. 👏
Thanks to all Community members who publish, comment, vote, and implement ideas. Looking forward to your new bright ideas in 2024.👏
To round up this newsletter, here are 4 ideas implemented during the last 2 months.
Idea
Implementation of the idea (project)
Developer
Credly badge for VIP into GM by @Yuri.Gomes
Global Masters Credly Badges - new way to share your accomplishment!
InterSystems
Run Jasperreports from ObjectScript and IRIS Adapter by @Yuri.Gomes
JIRISReport
@Yuri.Gomes
Journal file analysis/visualization by @Timothy.Leavitt
journal-file-indexer
@Lorenzo.Scalese
Implement samples of $system.external interface (aka "InterSystems External Servers") by @Enrico.Parisi
samples-dynamicgateway-dotnet, samples-dynamicgateway-java
@Daniel.Pasco, @Summer.Gerry
👏 Thank you for implementing and posting these ideas 👏
Happy New Year! 🎄In 2024 post your brilliant ideas, vote for existing ideas, and comment on them on our InterSystems Ideas Portal!💡 Thank you for all of the ideas which make all of our products and online offerings better :-) Hi Developers!
@Muhammad.Waseem with the idea Web Interface to convert HL7 V2 to FHIR@Maksym.Shcherban with the idea Implement support for FHIRPath Patch resources on InterSystems FHIR Serverthank you for creating your ideas related to the "FHIR and Digital Health Interoperability" topic. 300 ideas! Wow! Congratulations @Vadim.Aniskin and the community itself! 300 ideas! Another milestone! 🎊🎊🎊
Community! Thank you all for sharing your ideas, suggestions and feedback on the Ideas Portal 👏
Announcement
Raj Singh · Nov 13, 2023
As first announced in May, and after many presentations and robust discussions at Global Summit 2023, we are continuing with the plans to deprecate InterSystems Studio (deprecated designates a feature or technology that InterSystems no longer actively develops, and for which better options exist).
InterSystems Studio was deprecated in 2023.2 but will continue to ship with versions 2023.3 and 2024.1. The latter will be the last release that includes Studio as part of the Windows kit, and it will be the only version that allows connections to future versions of InterSystems IRIS. This version that ships with 2024.1 will continue to be available as a standalone tool on the WRC component download page as it is today.
As always, VS Code with the InterSystems ObjectScript Extension Pack is recommended for all InterSystems' development. Check out the Studio migration guide for help getting started. And if you haven't tried VS Code recently, note a new feature, XML import/export, makes adoption even easier.
Thank you deeply for your in-person feedback this past June. I'm always more than happy to hear your experiences and challenges, and look forward to you joining us in enhancing our new flagship IDE.
Article
Yuri Marx · Nov 20, 2023
In the world of Big Data, selecting the right file format is crucial for efficient data storage, processing, and analysis. With the massive amount of data generated every day, choosing the appropriate format can greatly impact the speed, cost, and accuracy of data processing tasks. There are several file formats available, each with its own set of advantages and disadvantages, making the decision of which one to use complex. Some of the popular Big Data file formats include CSV, JSON, Avro, ORC, and Parquet. The last one, Parquet, is a columnar storage format that is highly compressed and separated, making it ideal for Big Data problems. It is optimized for the paradigm Write Once Read Many (WORM) and is good for heavy workloads when reading portions of data. The format provides predicate pushdown and projection pushdown, reducing disk I/O and overall performance time. Parquet is self-describing, has built-in support in Spark, and can be read and written using the Avro API and Avro Schema. (source: https://www.linkedin.com/pulse/big-data-file-formats-which-one-right-fit-amandeep-modgil). Have you ever used pd.read_csv() in pandas? Well, that command could have run ~50x faster if you had used parquet instead of CSV. (source: https://towardsdatascience.com/demystifying-the-parquet-file-format-13adb0206705). The reasons and benefits of use Parquet are (source: https://www.linkedin.com/pulse/big-data-file-formats-which-one-right-fit-amandeep-modgil):
Optimized for Big Data, but readability and writing speed are quite poor.
Parquet is the best choice for performance when choosing a data storage format in Hadoop, considering factors like integration with third-party applications, schema evolution, and support for specific data types.
Compression algorithms play a significant role in reducing the amount of data and improving performance.
CSV is typically the fastest to write, JSON the easiest to understand for humans, and Parquet the fastest to read a subset of columns.
Columnar formats like Parquet are suitable for fast data ingestion, fast random data retrieval, and scalable data analytics.
Parquet is used for further analytics after preprocessing, where not all fields are required.
Currently, IRIS does not support the Parquet format, even though it is a very important standard for Data Fabric, Big Data, Data Ingestion and Data Interoperability projects. But now, it is possible, through the iris-parquet application (https://openexchange.intersystems.com/package/iris-parquet) to write or read data in IRIS in Parquet files. Just follow the steps below:
1. Install parquet-iris using Docker or ZPM.
If Docker:
docker-compose build
docker-compose up -d
If ZPM:
USER> zpm install iris-parquet
Install hadoop files and set ENV variable to HADOOP_HOME (for ZPM installation only):
wget https://dlcdn.apache.org/hadoop/common/hadoop-3.3.6/hadoop-3.3.6.tar.gz && \
tar -xzf hadoop-3.3.6.tar.gz && \
echo "export HADOOP_HOME=//hadoop-3.3.6"
2. Open http://localhost:<port>/swagger-ui/index.html and explore the parquet-api _spec endpoint:
3. Run the method /generate-persons one or more to generate sample person fake data:
4. Run the method /sql2parquet with this query on body: select * from dc_irisparquet.SamplePerson:
5. Download the parquet file on the link Download file:
6. Open the Parquet file on VSCode (install the parquet-viewer extension to see the parquet content from VSCode - https://marketplace.visualstudio.com/items?itemName=dvirtz.parquet-viewer):
Excellent! Can you explain your inspiration for this project? Were you inspired 😉?
Have you considered a project to migrate parquet lake to Delta Lake? Thanks Mike, my inspiration is a target to looking for IRIS current gaps on important themes and help to eliminate the gaps with new DC projects.
IRIS with sharding enabled is great option for Lake projects
Article
Yuri Marx · Feb 23, 2023
IoT (Internet of Things) is a network of interconnected things, including vehicles, machines, buildings, domestic devices or any other thing with embedded TCP/IP remote connection available, allowing it to receive and send execution instructions and data. Each thing provides one or more services to the IoT network. For instance, smart light bulbs provide services of turning off and turning on the lights; smart air conditioners maintain the environment temperature; smart cameras send notifications when capturing movement. Yet, all those devices can operate together to provide building security, for example.When the camera detects movement, the lights turn on. The orchestration of those processes that can deliver business and integrated services, such as building security, is done by a server software controller. It can receive and send data and instructions from a dozen smart devices to a TCP/IP network (like the Internet) to operate these things as major services.
There are two options of protocols used by things and server software to interoperate data and instructions: MQTT (Message Queuing Telemetry Transport) and REST (Representational State Transfer) API. The first one is more common because it has a simpler structure. Some devices have limited memory and processor features and don’t have enough resources to use REST. Thus, this article will detail how to implement the server-side software to interoperate the IoT using MQTT.
About MQTT
MQTT is a lightweight, publish-subscribe, machine to machine network protocol for message queue/message queuing service. It is designed for connections with remote locations that have devices with resource constraints or limited network bandwidth. It must run over a transport protocol that provides ordered, lossless, bi-directional connections—typically, TCP/IP. It is an open OASIS standard and an ISO recommendation (ISO/IEC 20922). See more on https://en.wikipedia.org/wiki/MQTT.
The MQTT protocol has two actors to interoperate data between things: the message broker and the clients. The first one is a server software used to receive and publish messages to be produced or consumed by one or more clients. The last ones are the smart devices and server softwares used to provide major services using the thing’s services, such as a server software to provide a building automation service, or a software to control the traffic in the city using smart semaphores and public cameras services.
MQTT Broker
There are various MQTT message brokers on the market, but the most popular one is the Eclipse Mosquitto (https://mosquitto.org/), so we will use it in our example.The MQTT Broker is similar to JMS or MQ brokers. It is an intermediate employed to receive messages and post them into topics. Thus, the connected clients can subscribe to one or more topics to produce or consume messages from those topics. The topics allow the clients (software and devices) to do their job in an uncoupled and asynchronous way. It means that each client observes a message and takes action if necessary. They can also post new messages themselves. For instance, if a thermostat sends a message indicating high temperature in the topic “building A”, the air conditioning installed in that building and listed in the topic “building A” can take this message from there and turn itself on to reduce the temperature. However, it is common to see server-side software with business rules automated to turn on the air conditioning when the temperature reaches a specific value. The MQTT broker can also guarantee message delivery if it is configured to retain the messages when down and restore them when on again.
Other advantages of the MQTT Broker include (from Wikipedia):
Eliminating vulnerable and insecure client connections, if configured to do so.
Being able to easily scale from a single device to thousands of gadgets.
Managing and tracking all client connection states, including security credentials and certificates, if configured to do so.
Reducing network strain without compromising the security, if configured to do so (when working with a cellular or satellite network).
MQTT messages
There are 3 types of messages:
Connect: establish a connection between the broker and clients in a TCP/IP session.
Disconnect: disconnect the server and the client from a TCP/IP session.
Publish: put a message into a topic and send a copy of that message to each client that subscribed to the topic.
Check out an example of messages in action:
InterSystems IRIS support to IoT and MQTT protocol
According to the InterSystems IRIS documentation, the currently supported version is MQTT 3.1. This MQTT specification is defined as an OASIS standard, MQTT Version 3.1.1. The Interoperability module from IRIS defines an Inbound (to consume MQTT messages) and an Outbound (to produce MQTT messages) MQTT Adapter. It is possible to develop custom business services and operations using these adapters, or you can use the built-in business service and operation, EnsLib.MQTT.Service.Passthrough and EnsLib.MQTT.Operation.Passthrough, respectively.
If you want to use the MQTT protocol outside of an interoperability production, you can use the lower-level %Net.MQTT classes. The MQTT classes use the Eclipse Paho MQTT C Client Library.
To create a Business Service to consume MQTT messages
Class packagename.NewService1 Extends Ens.BusinessService
{
Parameter ADAPTER = "EnsLib.MQTT.Adapter.Inbound";
Method OnProcessInput(pInput As EnsLib.MQTT.Message, pOutput As %RegisteredObject) As %Status
{
set tsc=$$$OK
set messageContent = pInput.StringValue
….
Quit tsc
}
}
To create a Business Operation to produce/send MQTT messages
Class packagename.NewOperation1 Extends Ens.BusinessOperation
{
Parameter ADAPTER = "EnsLib.MQTT.Adapter.Outbound";
Parameter SETTINGS = "-SendSuperSession";
Method OnMessage(pRequest As packagename.Request, Output pResponse As packagename.Response) As %Status
{
#dim tSC As %Status = $$$OK
#dim e As %Exception.AbstractException
Try {
Set message = ##class(EnsLib.MQTT.Message).%New()
Set message.Topic = ..Adapter.Topic
Set jsonValue = {}
Set jsonValue.message = "Response message”
Set message.StringValue = jsonValue.%ToJSON()
Set tSC=..Adapter.Send(message.Topic,message.StringValue)
Set pResponse = ##class(packagename.Response).%New()
Set pResponse.message = “Message response”
} Catch e {
Set tSC=e.AsStatus()
}
Quit tSC
}
Settings for the Inbound and Outbound MQTT Adapter
Both Business Service and Business Operation can be configured with the following parameters (source: https://docs.intersystems.com/irisforhealthlatest/csp/docbook/DocBook.UI.Page.cls?KEY=EMQTT_reference):
Client ID: This is the string that identifies this client to the broker. It must be ASCII-encoded and contain between 1 and 23 characters.
Connect Timeout: This is the connection timeout. Connecting to a busy server may take some time, so this timeout can be used to avoid a premature connection failure. It specifies the number of seconds to wait before a connection attempt fails.
Credentials Name: This is the ID name of the set of credentials values used to access the MQTT broker. The username and password defined in your Credentials item must be ASCII-encoded. It is not needed if the broker does not require login credentials.
Keep Alive: Specifies the maximum number of seconds to pass between messages sent by the client to the broker.
QOS: This determines the quality of service required. It can have either of these two values: 0 - QOSFireAndForget: Do not wait for a response from the broker and 1 - QOSWaitForDelivery: Wait for a response from the broker and issue an error if the broker does not respond.
Retained: This is the flag that indicates to the broker whether the message should be retained.
Topic: This is the name of the topic to which you wish to publish or subscribe. The topic must be ASCII-encoded. The topic is typically a hierarchical string with levels of subtopics separated by a / (forward slash). In a subscription, a topic can have wildcards as a topic level.
URL: This is the URL of the broker with which you wish to communicate. The scheme is either “tcp” or “ssl” followed by the domain name and the port delimited by a “:”, for example, “tcp://BIGBADAPPLE.local:1883”. Typically TLS-enabled endpoints are configured with a port of 8883, but this is not mandatory.
Download and Install the Sample application
The IRIS IoT Sample is a simple application to show you how to consume (receive) and produce (send) MQTT messages using an Interoperability Production. To get it, go to https://openexchange.intersystems.com/package/IoT-Sample. Now, execute the next steps.
1. Clone/git pull the repo into any local directory
$ git clone https://github.com/yurimarx/iris-iot-sample.git
2. Open the terminal in this directory and run:
$ docker-compose build
3. Run the IRIS container with your project:
$ docker-compose up -d
If you want to install it using ZPM, follow the next steps:
1. Open IRIS Namespace with Interoperability Enabled.2. Open Terminal and call:
USER>zpm "install iris-iot-sample"
Run the Sample Application.
1. Open the production and start it. It will begin observing the MQTT topic /DeviceStatusInputTopic and produce responses to the MQTT topic /DeviceStatusOutputTopic. Check out:
2. Use an MQTT client to send a message and test the production. To do it, go to https://chrome.google.com/webstore/detail/mqttbox/kaajoficamnjijhkeomgfljpicifbkaf on your Google Chrome browser. It is the Chrome Plugin MQTTBox. Just click Add to Chrome and then Add App.
3. In your Google Chrome browser go to chrome://apps/ and select MQTTBox (if required, click Open Anyway).
4. Click Create MQTT Client button.
5. Configure the MQTT connection with these settings:
Client name: Local
Protocol: mqtt / tcp
Host: localhost:1883
Username: admin
Password: admin
All other settings stay the default values.
6. Configure the MQTT topics to send and receive MQTT messages:
Topic to publish: /DeviceStatusInputTopic
Topic to subscribe: /DeviceStatusOutputTopic
Payload:
{
"deviceId":"Air Conditioner Level 1",
"statusDate":"2023-01-07 14:03:00",
"status": 0
}
7. Click the button Subscribe to check the messages on the topic /DeviceStatusOutputTopic.
8. Click the button Publish to send a message to /DeviceStatusInputTopic and see the results produced by IRIS production on /DeviceStatusOutputTopic.
9. Check out the message processing session on IRIS Management Portal Visual Trace
The Sample Application source code
The Dockerfile
ARG IMAGE=intersystemsdc/irishealth-community
ARG IMAGE=intersystemsdc/iris-community
FROM $IMAGE
WORKDIR /home/irisowner/irisbuild
ARG TESTS=0
ARG MODULE="iris-iot-sample"
ARG NAMESPACE="USER"
RUN --mount=type=bind,src=.,dst=. \
iris start IRIS && \
iris session IRIS < iris.script && \
([ $TESTS -eq 0 ] || iris session iris -U $NAMESPACE "##class(%ZPM.PackageManager).Shell(\"test $MODULE -v -only\",1,1)") && \
iris stop IRIS quietly
The latest version of InterSystems IRIS Community is used to create a docker instance of InterSystems IRIS with a namespace called USER.
The docker-compose file
version: '3.6'
services:
mosquitto:
image: eclipse-mosquitto:2
container_name: mosquitto
user: root
volumes:
- ./mosquitto/config/:/mosquitto/config/
- ./mosquitto/log/:/mosquitto/log/
- ./mosquitto/data/:/mosquitto/data/
ports:
- 1883:1883
- 9001:9001
iris:
build:
context: .
dockerfile: Dockerfile
restart: always
command: --check-caps false
ports:
- 1972
- 52795:52773
- 53773
volumes:
- ./:/irisdev/app
Two docker container instances are now created, and up and running. The first one is for the MQTT broker (mosquitto service), based on the Eclipse Mosquitto product, and the second one is for the MQTT server-side software, responsible for consuming and producing MQTT messages in order to provide Device Monitoring services.The mosquitto docker instance is configured in the file /mosquitto/config/mosquitto.conf. The port, message persistence, security and log questions are defined there. The file /mosquitto/config/password.txt determines the user as admin with the password admin too. However, it is encrypted by the command mosquitto_passwd -U password.txt (you can read more about this at https://mosquitto.org/man/mosquitto_passwd-1.html).
The iris.script file
zn "%SYS"
Do ##class(Security.Users).UnExpireUserPasswords("*")
zn "USER"
Do ##class(EnsPortal.Credentials).SaveItem(0, "mosquitto_cred","mosquitto_cred","admin","admin","")
zpm "load /home/irisowner/irisbuild/ -v":1:1
halt
This file creates the credentials for the business service and business operation. Login to the MQTT Broker and run the ZPM file module.xml.
The module.xml
This file is used to compile the source code on the server and to install the sample application when using ZPM.
<?xml version="1.0" encoding="UTF-8"?>
<Export generator="Cache" version="25">
<Document name="iris-iot-sample.ZPM">
<Module>
<Name>iris-iot-sample</Name>
<Description>A simple IRIS interoperability application - for IoT using MQTT.</Description>
<Version>1.0.8</Version>
<Packaging>module</Packaging>
<Dependencies>
<ModuleReference>
<Name>sslclient</Name>
<Version>1.0.1</Version>
</ModuleReference>
</Dependencies>
<SourcesRoot>src</SourcesRoot>
<Resource Name="dc.irisiotsample.PKG"/>
<SystemRequirements Version=">=2020.1" Interoperability="enabled" />
</Module>
</Document>
</Export>
On the SourceRoot tag there are source packages to be compiled.
The DeviceStatus persistent class
Class dc.irisiotsample.DeviceStatus Extends %Persistent
{
Property deviceId As %String;
Property statusDate As %TimeStamp;
Property status As %Boolean;
Storage Default
{
<Data name="DeviceStatusDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>deviceId</Value>
</Value>
<Value name="3">
<Value>statusDate</Value>
</Value>
<Value name="4">
<Value>status</Value>
</Value>
</Data>
<DataLocation>^dc.irisiotsample.DeviceStatusD</DataLocation>
<DefaultData>DeviceStatusDefaultData</DefaultData>
<IdLocation>^dc.irisiotsample.DeviceStatusD</IdLocation>
<IndexLocation>^dc.irisiotsample.DeviceStatusI</IndexLocation>
<StreamLocation>^dc.irisiotsample.DeviceStatusS</StreamLocation>
<Type>%Storage.Persistent</Type>
}
}
This class is used to persist the DeviceStatus in an SQL table.
The DeviceStatusRequest class
Class dc.irisiotsample.DeviceStatusRequest Extends Ens.Request
{
Property deviceId As %String;
Property statusDate As %TimeStamp;
Property status As %Boolean;
Storage Default
{
<Data name="DeviceStatusRequestDefaultData">
<Subscript>"DeviceStatusRequest"</Subscript>
<Value name="1">
<Value>deviceId</Value>
</Value>
<Value name="2">
<Value>statusDate</Value>
</Value>
<Value name="3">
<Value>status</Value>
</Value>
</Data>
<DefaultData>DeviceStatusRequestDefaultData</DefaultData>
<Type>%Storage.Persistent</Type>
}
}
This class is employed to exchange data between interoperability components.
The DeviceStatusService
Class dc.irisiotsample.DeviceStatusService Extends Ens.BusinessService
{
Parameter ADAPTER = "EnsLib.MQTT.Adapter.Inbound";
Method OnProcessInput(pInput As EnsLib.MQTT.Message, pOutput As Ens.StringResponse) As %Status
{
set tsc=$$$OK
set DeviceStatusValue = ##class(%DynamicAbstractObject).%FromJSON(pInput.StringValue)
set DeviceStatus = ##class(dc.irisiotsample.DeviceStatusRequest).%New()
set DeviceStatus.deviceId = DeviceStatusValue.deviceId
set DeviceStatus.statusDate = DeviceStatusValue.statusDate
set DeviceStatus.status = DeviceStatusValue.status
set tsc =..SendRequestSync("DeviceStatusProcess", DeviceStatus, .Response, -1, "Device Status Process")
set pOutput = Response
quit tsc
}
}
The parameter adapter is indicating the use of the MQTT adapter when it comes to receiving MQTT messages. The method OnProcessInput receives the MQTT message on pInput and sends it to the Business Process, calling the method SendRequestSync as a synchronous message.
The DeviceStatusProcess
Class dc.irisiotsample.DeviceStatusProcess Extends Ens.BusinessProcess
{
Method OnRequest(request As dc.irisiotsample.DeviceStatusRequest, Output response As Ens.StringResponse) As %Status
{
Set tsc = 1
Set response = ##class(Ens.StringResponse).%New()
Set DeviceStatus = ##class(dc.irisiotsample.DeviceStatus).%New()
Set DeviceStatus.deviceId = request.deviceId
Set DeviceStatus.status = request.status
Set DeviceStatus.statusDate = request.statusDate
Set tsc = DeviceStatus.%Save()
If $$$ISOK(tsc) {
Set tsc =..SendRequestSync("DeviceStatusOperation", request, .pResponse, -1, "Device Status Operation")
Set response.StringValue = "Device id "_pResponse.deviceId_" has the status "_pResponse.status
} Else {
Set response.StringValue = "Error on save the device status"
Set SuspendMessage = 1
}
quit tsc
}
Storage Default
{
<Type>%Storage.Persistent</Type>
}
}
This class receives the message from the business service, saves it to the database, and calls the DeviceStatusOperation to send (produce) a message with the results.
The DeviceStatusOperation
Class dc.irisiotsample.DeviceStatusOperation Extends Ens.BusinessOperation
{
Parameter ADAPTER = "EnsLib.MQTT.Adapter.Outbound";
Parameter SETTINGS = "-SendSuperSession";
Method NotifyDeviceStatus(pRequest As dc.irisiotsample.DeviceStatusRequest, Output pResponse As dc.irisiotsample.DeviceStatusResponse) As %Status
{
#dim tSC As %Status = $$$OK
#dim e As %Exception.AbstractException
Try {
Set message = ##class(EnsLib.MQTT.Message).%New()
Set message.Topic = ..Adapter.Topic
Set jsonValue = {}
Set jsonValue.message = "Device "_pRequest.deviceId_" has status "_pRequest.status
Set message.StringValue = jsonValue.%ToJSON()
Set tSC=..Adapter.Send(message.Topic,message.StringValue)
Set pResponse = ##class(dc.irisiotsample.DeviceStatusResponse).%New()
Set pResponse.deviceId = pRequest.deviceId
Set pResponse.status = pRequest.status
} Catch e {
Set tSC=e.AsStatus()
}
Quit tSC
}
XData MessageMap
{
<MapItems>
<MapItem MessageType="dc.irisiotsample.DeviceStatusRequest">
<Method>NotifyDeviceStatus</Method>
</MapItem>
</MapItems>
}
}
This class receives a message from the Business Process and produces an MQTT message to a topic with the process response.
The DeviceStatusProduction
Class dc.irisiotsample.DeviceStatusProduction Extends Ens.Production
{
XData ProductionDefinition
{
<Production Name="dc.irisiotsample.DeviceStatusProduction" LogGeneralTraceEvents="false">
<Description></Description>
<ActorPoolSize>2</ActorPoolSize>
<Item Name="DeviceStatusService" Category="" ClassName="dc.irisiotsample.DeviceStatusService" PoolSize="1" Enabled="true" Foreground="false" Comment="" LogTraceEvents="false" Schedule="">
<Setting Target="Adapter" Name="ClientID">InterSystemsIRIS</Setting>
<Setting Target="Adapter" Name="Topic">/DeviceStatusInputTopic</Setting>
<Setting Target="Adapter" Name="Url">tcp://mosquitto:1883</Setting>
<Setting Target="Adapter" Name="CredentialsName">mosquitto_cred</Setting>
</Item>
<Item Name="DeviceStatusProcess" Category="" ClassName="dc.irisiotsample.DeviceStatusProcess" PoolSize="1" Enabled="true" Foreground="false" Comment="" LogTraceEvents="false" Schedule="">
</Item>
<Item Name="DeviceStatusOperation" Category="" ClassName="dc.irisiotsample.DeviceStatusOperation" PoolSize="1" Enabled="true" Foreground="false" Comment="" LogTraceEvents="false" Schedule="">
<Setting Target="Adapter" Name="ClientID">InterSystemsIRIS</Setting>
<Setting Target="Adapter" Name="Topic">/DeviceStatusOutputTopic</Setting>
<Setting Target="Adapter" Name="Url">tcp://mosquitto:1883</Setting>
<Setting Target="Adapter" Name="CredentialsName">mosquitto_cred</Setting>
</Item>
</Production>
}
}
This class configures the Business Service, the Business Process, and the Business Operation with the required parameters, and puts these components to work together.
The main configured parameters are:
URL: to set the MQTT Broker address.
Topic: the queue to receive and send MQTT messages. DeviceStatusInputTopic for the business service and DeviceStatusOutputTopic for the Business Operation.
CredentialsName: set the credentials with the username and password to connect with the MQTT broker.
ClientID: it is a logical name assigned for the InterSystems IRIS and required by the broker to identify MQTT clients.
As you can see, the IRIS interoperability adapter for MQTT is a very easy and powerful tool for consuming and producing MQTT messages and automating the business flow when it comes to IoT devices. So, enjoy it. Very good article, Yuri.
Wonder when Intersystems will support AMQP protocol, to integrate seamlessly with ASB (Azure Service Bus) Thanks! Yaron, I'd appreciate if you submit it as an idea This is cool. Thank you very much. This gives you the opportunity to do a "Smart Home"" server in COS :-) - funny. Many thanks Thanks Yuri for this article and app.
As MQTTBox doesn't work for me (no longer compatible), I'm using MQTT Explorer app to send messages :https://apps.apple.com/app/apple-store/id1455214828 Thanks! Of course MQTT Explorer also works on Windows, Ubuntu and any Linux platforms.
http://mqtt-explorer.com/
https://github.com/thomasnordquist/MQTT-Explorer/releases
Announcement
Vadim Aniskin · Feb 27, 2023
Hey Community!
Welcome to the 4th edition of the InterSystems Ideas News bulletin!
Here's what you can expect from it:
Support your teammates with their ideas and votes.
Users implemented ideas and gained tech bonuses for a contest.
Dark version for InterSystems Developer Community: Yay or Nay?
Recently added ideas.
The most important piece of news is that in the last programming contest, 5 participants got extra points for implementing ideas from the InterSystems Ideas portal! Here they are:
Developer(s)
Application name
Implemented idea
@Lorenzo.Scalese
OpenAPI-Suite
Add a wizard similar to the SOAP wizard to generate a REST client from OpenAPI specification
@José.Pereira @Henrique @henry
iris-tripleslash
Add a project that helps to generate unittests for an ObjectScript class
@MikhailenkoSergey
gateway-sql, apptools-admin
Create a UI for convenient and easy transfer of projects (classes, globals, applications, users, roles, privileges, grants, namespace mapping, SQLgateways, libraries, etc.) to other system instances for fast deployment.
We added a new filter "My organization". From now on, people from the same company can filter the ideas and votes of people from the same organization.
The other day, @Guillaume.Rongier7183 posted an idea concerning adding a dark theme for the Developer Community. We would appreciate your feedback on this idea via "polls" on the Ideas Portal home page, or by voting and commenting on this idea.
Recently added ideas
Dark version of InterSystems Community
Add job location for job opportunities section on DC
Searchable Index of New Features
Set password through environment variable
Schedule the article publication on DC site
Support JSON on DC text editor
Support linux bash language on DC text editor
Change data capture from IRIS to kafka using SQL commands
Cross-production Interoperability Messages, Service and Operation
Additional Data Types for ISC Products
Automatic XML Formatting of XData in DTL, BPL and Rulesets
Assert CodeAssist prompting
IRIS classes for OpenAI API
Display UserValues contents on message property tab
IRIS as a service
Allow graphical editing of Interoperability components BPL, DTL and Business Rules in VS Code
Add Source control for all the IRIS interoperability components
Audit Lookuptables
Add a "watch list" to WRC tickets (problems, issues)
Connect InterSystems Documentation and Developer Community
Publish the InterSystems IRIS Native SDK for Node.js on npm
As usual, post your ideas on InterSystems Ideas, vote and comment on existing ideas, and implement Community Opportunity ideas.
And stay tuned for our next News bulletin! 
Article
Evgeny Shvarov · Apr 4, 2023
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!
List all the tables in database:
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
Credit to @Dmitry.Maslennikov
Create table with current date and current time as defaults:
CREATE TABLE CUSTOMER
(
ID INTEGER PRIMARY KEY NOT NULL,
NAME VARCHAR(100) NOT NULL,
PASSWORD VARCHAR(20) NOT NULL,
LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
REGISTRATION_DATE DATE DEFAULT CURRENT_DATE NOT NULL
);
notice functions CURRENT_TIMESTAMP and CURRENT_DATE are without parenthesis.
Create stored procedure/function that uses ObjectScript inside:
CREATE OR REPLACE FUNCTION IRIS_VERSION()
RETURNS VARCHAR
LANGUAGE OBJECTSCRIPT
{
Return $ZV
}
Call Stored procedure/function:
select IRIS_VERSION() as "Version"
Call internal functions.
Return IRIS Version:
Select $version
Return current namespace:
Select $namespace
Run SQL from file (credit @Raj.Singh5479 ):
DO $SYSTEM.SQL.Schema.ImportDDL("c:\InterSystems\mysqlcode.txt",,"IRIS")
Run SQL from file using python terminal(credit @Dmitry.Maslennikov):
irissqlcli iris://_SYSTEM:SYS@localhost:1972/USER < c:\InterSystems\mysqlcode.txt
Open SQL shell in terminal(credit @Chad.Severtson):
USER>:sql
Open SQL shell in webterminal (credit @Nikita.Savchenko7047 ):
SAMPLES > /sql
SAMPLES:SQL > select top 5 * from Cinema.Film
ID
Category
Description
Length
PlayingNow
Rating
TicketsSold
Title
1
1
A post-modern excursion into family dynamics and Thai cuisine.
130
1
PG-13
47000
Her Spicy Brothers
2
1
A gripping true story of honor and discovery
121
1
R
50000
Einstein's Geisha
3
1
A Jungian analysis of pirates and honor
101
1
PG
5000
A Kung Fu Hangman
4
1
A charming diorama about sibling rivalry
124
1
G
7000
Holy Cooking
5
2
An exciting diorama of struggle in Silicon Valley
100
1
PG
48000
The Low Calorie Guide to the Internet
SAMPLES: SQL > /sql
SAMPLES > write "COS!"
cos!
Add yours? nice! Thanks ... I didn't know you could do things like Select $zversion :) here it s in practice for those interested:
USER>d $system.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter <command>, 'q' to quit, '?' for help.
[SQL]USER>>select $zversion
1. select $zversion
Expression_1
IRIS for Windows (x86-64) 2022.1.2 (Build 574U) Fri Jan 13 2023 15:08:27 EST
1 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.1884s/34,356/143,526/0ms
execute time(s)/globals/cmds/disk: 0.0007s/0/394/0ms
cached query class: %sqlcq.USER.cls1 is there no quicker way to list all database tables? Like the postgresql \t? in irissqlcli, you can use \dt or .tables Try ":sql" instead of
d $system.SQL.Shell() Holy cow @Chad.Severtson, that's really cool! Do you know when that was added / where I can find more information? I don't see it in the docs on SQL Shell (https://docs.intersystems.com/iris20221/csp/docbook/Doc.View.cls?KEY=GSQL_shell).
Also, what is the ":" shorthand for and where else can it be used? Would be nice to have Run SQL from a file:
DO $SYSTEM.SQL.Schema.ImportDDL("c:\InterSystems\mysqlcode.txt",,"IRIS") or
irissqlcli iris://_SYSTEM:SYS@localhost:1972/USER < c:\InterSystems\mysqlcode.txt
:py for embedded python
@Dmitry.Maslennikov - thank you! Docs reference? Do you know what the ":" syntax is? Hey @Benjamin.Spead,
You can find out more about the alias capability of iris session here: Using the Terminal Interactively | Using the Terminal | InterSystems IRIS Data Platform 2022.3
It's quite useful. You can even provide parameter substitution with $1, $2, etc. USER>:?
:<number> Recall command # <number>
:? Display help
:py Do $system.Python.Shell()
:mdx Do $system.DeepSee.Shell()
:sql Do $system.SQL.Shell()
:tsql Do $system.SQL.TSQLShell()
:alias Create/display aliases
:clear Clear history buffer
:history Display command history
:unalias Remove aliases
HTH wow. Didn't know we have that! And aliases can be setup and transferred too? E.g. like a package (IPM)? Creating Aliases for Commonly Used Commands from docs:If you are using a UNIX® or Linux system, you can provide a list of alias definitions which the Terminal will set automatically at the start of every session. Define these aliases (one per line) in a file named .iris_init in the home directory.never tried this. I rarely work on *X or
[SQL]USER>> run filename.sql
You can also save the current query
[SQL]USER>>save filename.sql
Another very cool aspect of the feature :) So it looks like this is just a built-in alias which ships with InterSystems IRIS. Nice work @Evgeny.Shvarov those typical things you want to do quickly and never remember and have to read the docs :-) holy cow #2 :-) so much precious info on this thread! seriously! it's amazing how much knowledge there is to be gleaned within this community :) It has been around for a few years.Personally, I hesitate to use the alias capability lest I find myself in an environment without them! Thanks, Luca! Just a note...
LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
This field will only define the LAST_UPDATED field at INSERT, not for an UPDATE. You probably want something like:
LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) NOT NULL,
This will cause LAST_UPDATED to be set at INSERT and UPDATE to the current timestamp to microsend precision. Thank you @Jeffrey.Drumm !!! For those that posted "I never knew that! Wow!" about the aliases: didn't you read this 2 years ago?😞