New post

#SQL

14

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

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

Open Exchange app
11 0
2 183
Article
· Sep 18, 2023 7m read
Vectors support, well almost

Nowadays so much noise around LLM, AI, and so on. Vector databases are kind of a part of it, and already many different realizations for the support in the world outside of IRIS.

Why Vector?

  • Similarity Search: Vectors allow for efficient similarity search, such as finding the most similar items or documents in a dataset. Traditional relational databases are designed for exact match searches, which are not suitable for tasks like image or text similarity search.
  • Flexibility: Vector representations are versatile and can be derived from various data types, such as text (via embeddings like Word2Vec, BERT), images (via deep learning models), and more.
  • Cross-Modal Searches: Vectors enable searching across different data modalities. For instance, given a vector representation of an image, one can search for similar images or related texts in a multimodal database.

And many other reasons.

So, for this pyhon contest, I decided to try to implement this support. And unfortunately I did not manage to finish it in time, below I'll explain why.

Open Exchange app
11 7
3 1K
Article
· Sep 26, 2023 1m read
Create JSON Objects and Arrays by SQL

The related package avoids adding %JSONAdaptor to each class but uses instead
SQL functions JSON_OBJECT() to create my JSON objects. With this approach, you can
add JSON to any class - even deployed ones - without any need for change or recompiling.

The trigger was the Export of M:N relationships as JSON objects or arrays.

Open Exchange app
11 0
0 359
Article
· Sep 13, 2022 8m read
CI/CD with IRIS 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.

11 6
0 860

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.

11 4
1 736
Announcement
· Aug 25, 2016
JSON changes in Caché 2016.2

As Bill has mentioned earlier in his post, we have carefully reviewed the JSON capabilities and made some adjustments to ensure they deliver the best benefit to you. In this post, I am going to describe the modifications in more detail and provide guidance for you to understand the implication for your code base.

11 8
1 3.9K

Hello IRIS Community,

InterSystems Certification is developing a certification exam for InterSystems IRIS SQL specialists, and if you match the exam candidate description given below, we would like you to beta test the exam. The exam will be available for beta testing on June 9 - 12, 2024 at InterSystems Global Summit 2024, but only for Summit registrants (visit this page to learn more about Certification at GS24). Beta testing will open for all other interested beta testers on June 24, 2024. However, interested beta testers should sign up now by emailing certification@intersystems.com (please let us know if you will be beta testing at Global Summit or in our online proctored environment). The beta testing must be completed by August 2, 2024.

10 5
8 1.2K

We have a yummy dataset with recipes written by multiple Reddit users, however most of the information is free text as the title or description of a post. Let's find out how we can very easily load the dataset, extract some features and analyze it using features from OpenAI large language model within Embedded Python and the Langchain framework.

10 3
2 292
Article
· Feb 22 4m read
IRIS 2024.1 Preview - New Feature

There is an interesting new feature in the recently announced 2024.1 preview, JSON_TABLE. JSON_TABLE is one of a family of functions introduced by the 2016 version of the SQL Standard (ISO Standard, published in early 2017). It allows for JSON values to be mapped to columns and queried using SQL. JSON_TABLE is valid in the FROM clause of some SQL statements.

The syntax of JSON_TABLE is quite large, allowing for exceptional conditions where the provided JSON values don't match expectations, nested structures and so on.

10 4
3 398

InterSystems is pleased to announce the General Availability of InterSystems IRIS Cloud SQL and InterSystems IRIS Cloud IntegratedML, two foundational services for developing cloud-native solutions powered by the proven, enterprise-class performance and reliability of InterSystems IRIS technology.

10 2
0 314

Intro

In a fast-paced digital era, effective communication is crucial. This article introduces a Java-based chat project, combining the strength of IRIS database and ChatGPT intelligence. Built on Java, it goes beyond real-time messaging, leveraging IRIS and ChatGPT for an enhanced chat experience. Also, the name of the project references the cultural classic - Star Wars.

Open Exchange app
10 6
3 288

GmOwl is a solution that offers an organized and engaging learning platform. It was developed to cater to the increasing need, for learning tools providing a versatile quiz environment that meets users requirements.

The main objective of GmOwl is to deliver an user experience for individuals participating in quizzes while giving administrators comprehensive control, over content and user engagement.

GmOwl uses Java EE with MVC template, and the InterSystems IRIS database is used to store data. The InterSystems JDBC Driver is used to connect to the database.

Open Exchange app
10 7
2 269
Announcement
· Mar 15, 2021
FHIR Analytics

Hi developers,

We have received quite a lot of interest in using SQL on FHIR data. As you know, FHIR data is encoded in the form of a complex directed graph, and thus you can not easily query it with traditional SQL queries or business intelligence tools. Some customers have noticed that the "FHIR search tables" in IRIS for Health have flattened part of the FHIR graph, and have tried to use them for analytics. This is an undocumented and unsupported part of IRIS for Health, and can change without notice.

10 1
0 383

Let me introduce my new project, which is irissqlcli, REPL (Read-Eval-Print Loop) for InterSystems IRIS SQL

  • Syntax Highlighting
  • Suggestions (tables, functions)
  • 20+ output formats
  • stdin support
  • Output to files

Install it with pip

pip install irissqlcli

Or run with docker

docker run -it caretdev/irissqlcli irissqlcli iris://_SYSTEM:SYS@host.docker.internal:1972/USER

Connect to IRIS

$ irissqlcli iris://_SYSTEM@localhost:1972/USER -W
Password for _SYSTEM:
Server:  InterSystems IRIS Version 2022.3.0.606 xDBC Protocol Version 65
Version: 0.1.0
[SQL]_SYSTEM@localhost:USER> select $ZVERSION
+---------------------------------------------------------------------------------------------------------+
| Expression_1                                                                                            |
+---------------------------------------------------------------------------------------------------------+
| IRIS for UNIX (Ubuntu Server LTS for ARM64 Containers) 2022.3 (Build 606U) Mon Jan 30 2023 09:05:12 EST |
+---------------------------------------------------------------------------------------------------------+
1 row in set
Time: 0.063s
[SQL]_SYSTEM@localhost:USER> help
+----------+-------------------+------------------------------------------------------------+
| Command  | Shortcut          | Description                                                |
+----------+-------------------+------------------------------------------------------------+
| .exit    | \q                | Exit.                                                      |
| .mode    | \T                | Change the table format used to output results.            |
| .once    | \o [-o] filename  | Append next result to an output file (overwrite using -o). |
| .schemas | \ds               | List schemas.                                              |
| .tables  | \dt [schema]      | List tables.                                               |
| \e       | \e                | Edit command with editor (uses $EDITOR).                   |
| help     | \?                | Show this help.                                            |
| nopager  | \n                | Disable pager, print to stdout.                            |
| notee    | notee             | Stop writing results to an output file.                    |
| pager    | \P [command]      | Set PAGER. Print the query results via PAGER.              |
| prompt   | \R                | Change prompt format.                                      |
| quit     | \q                | Quit.                                                      |
| tee      | tee [-o] filename | Append all results to an output file (overwrite using -o). |
+----------+-------------------+------------------------------------------------------------+
Time: 0.012s
[SQL]_SYSTEM@localhost:USER>

Open Exchange app
10 20
3 744

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.

10 0
1 553

In this article you will have access to the curated base of articles from the InterSystems Developer Community of the most relevant topics to learning InterSystems IRIS. Find top published articles ranked by Machine Learning, Embedded Python, JSON, API and REST Applications, Manage and Configure InterSystems Environments, Docker and Cloud, VSCode, SQL, Analytics/BI, Globals, Security, DevOps, Interoperability, Native API. Learn and Enjoy!

10 6
7 925
Article
· Jan 2, 2022 3m read
DB Migration using SQLgateway

Thanks to @Yuri Marx we have seen a very nice example for DB migration from Postgres to IRIS.
My personal problem is the use of DBeaver as a migration tool.
Especially as one of the strengths of IRIS ( and also Caché) before is the availability of the
SQLgateways that allow access to any external Db as long as for them an access usinig
JDBC or ODBC is available. So I extended the package to demonstrate this.

10 3
2 649

This is the third article in our short series around innovations in IRIS SQL that deliver a more adaptive, high-performance experience for analysts and applications querying relational data on IRIS. It may be the last article in this series for 2021.2, but we have several more enhancements lined up in this area. In this article, we'll dig a little deeper into additional table statistics we're starting to gather in this release: Histograms

10 0
0 482
Article
· May 11, 2021 8m read
IRIS in Astronomy

In this article we are going to show the results of the comparision between IRIS and Postgress when handling Astronomy data.

Introduction

Since the earliest days of human civilization we have been fascinated by the sky at night. There are so many stars! Everybody has dreamed about them and fantasized about life in other planets.

10 7
0 699

What I find really useful about IRIS when teaching my subject of Postrelational databases is the fact that it is a multi model database. Which means that I can actually go into architecture and structure and all that only once but then show the usage of different models (like object, document, hierarchy) using the same language and approach. And it is not a huge leap to go from an object oriented programming language (like C#, Java etc) to an object oriented database.

However, along with advantages (which are many) come some drawbacks when we switch from object oriented model to relational. When I say that you can get access to the same data using different models I need to also explain how it is possible to work with lists and arrays from object model in relational table. With arrays it is very simple - by default they are represented as separate tables and that's the end of it. With lists - it's harder because by default it's a string. But one still wants to do something about it without damaging the structure and making this list unreadable in the object model.

So in this article I will showcase a couple of predicates and a function that are useful when working with lists, and not just as fields.

9 1
0 152

With the release of InterSystems IRIS Cloud SQL, we're getting more frequent questions about how to establish secure connections over JDBC and other driver technologies. While we have nice summary and detailed documentation on the driver technologies themselves, our documentation does not go as far to describe individual client tools, such as our personal favourite DBeaver. In this article, we'll describe the steps to create a secure connection from DBeaver to your Cloud SQL deployment.

9 20
2 1.3K
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!

9 26
7 1.2K