Embedded SQL is a tool that allows us to execute SQL statements in Caché Object Script. For example, to select the name of a person with a particular SSN from the Sample.Person class we can do the following:
The 2021.2 release of the InterSystems IRIS Data Platform includes many exciting new features for fast, flexible and secure development of your mission-critical applications. Embedded Python definitely takes the limelight (and for good reason!), but in SQL we've also made a massive step forward towards a more adaptive engine that gathers detailed statistical information about your table data and exploits it to deliver the best query plans. In this brief series of articles, we'll take a closer at three elements that are new in 2021.2 and work together towards this goal, starting with Run Time Plan Choice.
It's hard to figure out the right order to talk about these (you can't imagine how often I've reshuffled them in writing this article!) because they fit together in such a nice way. As such, feel free to go on a limb and read these in random order .
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.
As we all know, Caché is a great database that accomplishes lots of tasks within itself. However, what do you do when you need to access an external database? One way is to use the Caché SQL Gateway via JDBC. In this article, my goal is to answer the following questions to help you familiarize yourself with the technology and debug some common problems.
There is a Link Procedure Wizard option within the Management Portal (System > SQL >Wizards > Link Procedure) which I had reliability issues with so I decided to use this solution instead.
Earlier in this series, we've presented four different demo applications for iKnow, illustrating how its unique bottom-up approach allows users to explore the concepts and context of their unstructured data and then leverage these insights to implement real-world use cases. We started small and simple with core exploration through the Knowledge Portal, then organized our records according to content with the Set Analysis Demo, organized our domain knowledge using the Dictionary Builder Demo and finally build complex rules to extract nontrivial patterns from text with the Rules Builder Demo.
This time, we'll dive into a different area of the iKnow feature set: iFind. Where iKnow's core APIs are all about exploration and leveraging those results programmatically in applications and analytics, iFind is focused specifically on search scenarios in a pure SQL context. We'll be presenting a simple search portal implemented in Zen that showcases iFind's main features.
A password manager is an important security tool that allows users to store and manage their passwords without the need to remember or write them down in insecure places. In this article, we will explore the development of a simple password manager using the Flask framework and the InterSystems IRIS database.
Key Features
Our password manager application will provide the following key features:
Using SQL Gateway with Python, Vector Search, and Interoperability in InterSystems Iris
Part 3 – REST and Interoperability
Now that we have finished the configuration of the SQL Gateway and we have been able to access the data from the external database via python, and we have set up our vectorized base, we can perform some queries. For this in this part of the article we will use an application developed with CSP, HTML and Javascript that will access an integration in Iris, which then performs the search for data similarity, sends it to LLM and finally returns the generated SQL. The CSP page calls an API in Iris that receives the data to be used in the query, calling the integration. For more information about REST in the Iris see the documentation available at https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...
The object and relational data models of the Caché database support three types of indexes, which are standard, bitmap, and bitslice. In addition to these three native types, developers can declare their own custom types of indexes and use them in any classes since version 2013.1. For example, iFind text indexes use that mechanism.
With IRIS 2021.1, we released a significant revision our SQL utilities API at %SYSTEM.SQL. Yes, that's a while ago now, but last week a customer asked a few questions about this and then @Tom Woodfin applied gentle mental pressure ;-) to make me describe the rationale of these changes in a little more detail on the Developer Community. So here we go!
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."
In this article, I will introduce my application iris-RAG-Gen .
Iris-RAG-Gen is a generative AI Retrieval-Augmented Generation (RAG) application that leverages the functionality of IRIS Vector Search to personalize ChatGPT with the help of the Streamlit web framework, LangChain, and OpenAI. The application uses IRIS as a vector store.
To achieve optimized AI performance, robust explainability, adaptability, and efficiency in healthcare solutions, InterSystems IRIS serves as the core foundation for a project within the x-rAI multi-agentic framework. This article provides an in-depth look at how InterSystems IRIS empowers the development of a real-time health data analytics platform, enabling advanced analytics and actionable insights. The solution leverages the strengths of InterSystems IRIS, including dynamic SQL, native vector search capabilities, distributed caching (ECP), and FHIR interoperability. This innovative approach directly aligns with the contest themes of "Using Dynamic SQL & Embedded SQL," "GenAI, Vector Search," and "FHIR, EHR," showcasing a practical application of InterSystems IRIS in a critical healthcare context.
Anton Umnikov Sr. Cloud Solutions Architect at InterSystems AWS CSAA, GCP CACE
AWS Glue is a fully managed ETL (extract, transform, and load) service that makes it simple and cost-effective to categorize your data, clean it, enrich it, and move it reliably between various data stores.
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.
Benjamin De Boe wrote this great article about Universal Cached Queries, but what the heck is a Universal Cached Query (UCQ) and why should I care about it if I am writing good old embedded SQL? In Caché and Ensemble, Cached Queries would be generated to resolve xDBC and Dynamic SQL. Now i
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.
Presenter: Andreas Dieckow Task: Apply SQL security to multiple servers in a distributed system Approach: Provide code samples for using new API calls to apply SQL security statements to multiple instances of our products
Description: The requirement that started at all. See examples on how to use this new feature and integrate it into your application by discuss code examples.
Problem: SQL Security is local to the instance and most of time driven by customer application code. That it is only local to the instance and is not automatically going to other instances requires a solution.
Solution: With application code use new API calls to issue SQL security statements that is applied to multiple instances.
Content related to this session, including slides, video and additional learning content can be found here.
If you are a customer of the new InterSystems IRIS® Cloud SQL and InterSystems IRIS® Cloud IntegratedML® cloud offerings and want access to the metrics of your deployments and send them to your own Observability platform, here is a quick and dirty way to get it done by sending the metrics to Google Cloud Platform Monitoring (formerly StackDriver).
Beginning in Caché 2013.1, InterSystems introduced Outlier Selectivity to improve query plan selection involving fields with one atypical value.
In this article, I hope to use an example 'Projects' table to demonstrate what Outlier Selectivity is, how it helps SQL performance and a few considerations for writing queries.
By replies.png)
Open Exchange app
..png)
