#SQL

Hi Community,

Play the new video on InterSystems Developers YouTube:

⏯ Projecting Your Globals to SQL @ Global Summit 2023

IRIS External Table is an InterSystems Community Open Source Project, that allows you to use files, stored in the local file system and cloud object storage such as AWS S3 as SQL Tables. ![IRIS External Table][IRIS External Table]

It can be found on Github Open Exchange and is included in InterSystems Package Manager ZPM.

To instal External Table from GitHub use:

git clone https://github.com/antonum/IRIS-ExternalTable.git
iris session iris
USER>set sc = ##class(%SYSTEM.OBJ).LoadDir("

Hi folks, 

I made a solution (https://openexchange.intersystems.com/package/iris-pretty-gpt-1) and want to use it like 

CREATEFUNCTIONIN

I know that a FUNCTION can only return one result. But it seems that the PROCEDURE can return several values and then in theory it should work as in the example above

https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cl…

Can you tell me how this can be implemented? Then it would be very convenient to generate test data

Hi,

        What is the alternative for INTERSECT in cache. I tried Intersect but not working.

Thanks for suggestions.

thanks

Jude

Hi Community,

We're pleased to invite you to the upcoming webinar in Hebrew: 

👉Advanced JSON & DocDB👈

📅 Date & time: November 29th, 3:00 PM IDT

JSON has become the leading language in interfaces in recent years. 

In this Webinar we will learn about the use of JSON Adapter and its mappings,

How to convert information from SQL to JSON in a single command, 

And how can information be saved as raw JSON without saving as an object in DocDB.

Presenter:

@Keren Skubach, Senior Sales Engineer, InterSystems 

 ➡ Register today!

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.

Building Application

Building our dynamic chat application involves a seamless integration of key components and technologies. Java serves as the heart that drives server-side logic and business operations.

Case description

Let’s imagine that you are a Python developer or have a well-trained team specialized in Python, but the deadline you got to analyze some data in IRIS is tight. Of course, InterSystems offers many tools for all kinds of analyses and treatments. However, in the given scenario, it is better to get the job done using the good old Pandas and leave the IRIS for another time.
    For the abovementioned situation and many other cases, you might want to fetch tables from IRIS to manage data outside InterSystems’ products.

Hi Developers,
Our next online Developer Roundtable will take place on November 30 at 10 am ET | 4 pm CET.📍 

Tech talks:

1. Foreign Tables - by @Benjamin De Boe Manager, Analytics Product Management, InterSystems
2. Building "data products" with dbt and InterSystems IRIS - by @tomdProduct Manager, Machine Learning, InterSystems

We will have time for Q&A and open discussion.

▶ Update: watch the recording of the roundtable below:

Hi, Community!

This article is an overview of SQLAlchemy, so let's begin!

SQLAlchemy is the Python SQL toolkit that serves as a bridge between your Python code and the relational database system of your choice. Created by Michael Bayer, it is currently available as an open-source library under the MIT License. SQLAlchemy supports a wide range of database systems, including PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server, making it versatile and adaptable to different project requirements.

The SQLAlchemy SQL Toolkit and Object Relational Mapper from a comprehensive set of tools for working with databases and Python. It has several distinct areas of functionality which you can use individually or in various combinations. The major components are illustrated below, with component dependencies organized into layers:

I want to connect the app to Cloud SQL but when I registered and created the deployment (trial) I got the error:

Did anybody face such an error?
During the creation of an account, there was a message like 'username is taken' but the account have been created anyway. Maybe it could cause this problem.
 

Hi,

DBA has asked me to configure a working Business Operation using a SQL.OutboundAdapter (connecting to external MSSQL server) to run as TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

I would like help with an example of how this is done.

Thanks

How can IRIS productions be deployed more quickly and with greater peace of mind?

The aim of interoperability productions is to enable you to connect systems in order to transform and route messages between them. To connect systems, you develop, configure, deploy and manage productions that integrate several software systems.

That’s what the InterSystems documentation on its reference website tells us, but what do you actually have to do to deploy a production ?

Productions can be composed to connect external systems to the IRIS Data Platform.

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.

This question originally appeared in the comments of the post: [Video] InterSystems IRIS FHIR SQL Builder: Sneak Peek
 

Any chance the FHIR SQL Builder process could be automated? It'll help to refresh the SQL table so it'll be up to date!

Thanks

When using INSERT OR UPDATE command, 

How does the command know if the row exists? 

Hi Developers,

Hit play on the new video on InterSystems Developers YouTube:

⏯ Columnar Storage - The Lean Data Warehouse @ Global Summit 2023

Hello Community

IRISTEMP database consumes more disk space and make the production instance down when running Bulk SQL queries (maybe other processes aswell)

Is setting up the "Maximum" size it resolves the problem?. Is this setting impacts the performance? 

What are the approaches to resolve it

sample screenshot

This article is a simple quick starter (what I did was) with SqlDatabaseChain.

Hope this ignites some interest.

Many thanks to:

sqlalchemy-iris author @Dmitry Konnov Maslennikov

Your project made this possible today.

The article script uses openai API so caution not to share table information and records externally, that you didn't intend to.

A local model could be plugged in , instead if needed.

Creating a new virtual environment

mkdir chainsql

cd chainsql

python -m venv .

scripts\activate

pip install langchain

pip install wget

# Need to connect to IRIS so installing a fresh python driver
python -c "import wget;url='https://raw.githubusercontent.com/intersystems-community/iris-driver-distribution/main/DB-API/intersystems_irispython-3.2.0-py3-none-any.whl';wget.download(url)"

# And for more magic
pip install sqlalchemy-iris

pip install openai

set OPENAI_API_KEY=[ Your OpenAI Key ]

python

This applies to embedded and dynamic SQL queries in Caché ObjectScript. If I attempt to terminate the process via Management Portal, nothing happens. Neither does Ctrl C nor closing the terminal window in which my program is running, nor setting a stop flag in a global which is read by the program on each loop iteration. The only way to stop the query appears to be restarting the Caché server (which is running locally on my PC).

I am very new to IRIS. We are developing a PHP application that connects to an IRIS instance of one of our vendors. I am having trouble with a specific table. When I need to query this specific table for specific fields, I am receiving an error. However, if I change the call to get all columns (`*`), there is no error. I get 37K records returned.

Error:

PHP Fatal error:  Uncaught PDOException: SQLSTATE[SL009]: <<Unknown error>>: [unixODBC][Driver Manager]No columns were bound prior to calling SQLFetch or SQLFetchScroll (SQLFetchScroll[0] at /builddir/build/BUILD/php-8.1.20/ext/pdo_odbc/odbc_stmt.c:547) 

Following the documentation to insert into tableA from tableB in the SQL query box from the Management Portal :

INSERT INTO tableA (col1, col2)
SELECT col1, col2
FROM tableB

This straightforward query raises the following error :

parseExtFrom+19^%qaqcmx
*mt("f", "1^TABLEA") :

Could someone help me decipher this error message ? Thanks for your help.

For the upcoming Python contest, I would like to make a small demo, on how to create a simple REST application using Python, which will use IRIS as a database. Using this tools

• FastAPI framework, high performance, easy to learn, fast to code, ready for production
• SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL
• Alembic is a lightweight database migration tool for usage with the SQLAlchemy Database Toolkit for Python.
• Uvicorn is an ASGI web server implementation for Python.

Hello Community,

I have couple of question about the DynamicSQL.

1. The %ObjectSelectMode is working perfectly in the DynamicSQL for the object property in direct query. However this is not supported for class query. Is there any way to resolve this / Get the object value for class query result.

2. What is the reason behind for a stored query prepared using %PrepareClassQuery() you must use the %Get("fieldname") method.

ClassExtends%Persistent

Hi Community,

In this article, I listed 5 useful SQL functions with explanations and query examples 👇🏻
These 5 functions are

• COALESCE
• RANK
• DENSE_RANK
• ROW_NUMBER
• Function to Get Running Totals

So Let us start with COALESCE function

I have run the query and got ORU^R03 message from SQL Editor and reprocessed the message, But pdf not opened and the base64 content looks truncated.

Is there a way to get full content from the message from query? Why the content is not visible in DB that I can see in Message Viewer?

Thank you so much for your time to read this,

Hello My Friend,

I have a problem when I Query to get the data from SQL

Here's my code

Hey Developers,

Enjoy watching the new video on InterSystems Developers YouTube:

⏯ InterSystems Cloud Services - InterSystems IRIS SQL & IntegratedML @ Global Summit 2023

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.

I need to run a SQL query and use the output to map PV1 7.1. The query is :

SELECT ID
FROM TestTable
WHERE ProviderName = 'TEST,PROVIDER' AND IDType= 'BPI'

When I run this query with the 'TEST PROVIDER'  I do pull the ID in question but I can't figure out how to do it from the DTL given that there are various providers sent in PV 1 7 . Any assistance will be greatly appreciated. 

Hi,

I am trying to find out individual segment values in Message viewer and it display the values in Management portal, copied the query and run it on SQL editor, Segment values displayed with blank. Is there a way to get the individual segment values using query?