New post

#SQL

14

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

All time
Article
· Jan 28, 2022 2m read
Embedded SQL Bug and Workaround

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.

6 1
0 324

InterSystems FAQ rubric

It can be retrieved using the schema INFORMATION_SCHEMA.

INFORMATION_SCHEMA is a system schema and is not displayed by default in the SQL menu of the Management Portal.

The method to display it is as follows.

  1. Open Management Portal → System Explorer → SQL menu.
  2. Check "System" on the left of the schema drop-down.
  3. Select INFORMATION_SCHEMA from the schema dropdown.

4 0
1 340

Presenter: Anton Umnikov
Task: Run SQL queries quickly on huge amounts of data
Approach: Use a shared-nothing architecture to leverage a cluster of small, cost-effective servers

Explains how a shared nothing architecture allows you to leverage a cluster of small, costeffective servers to serve query results that would have taken much longer on a single, big and expensive box. This brings affordable horizontal scalability.

Content related to this session, including slides, video and additional learning content can be found here.

1 0
0 335
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 320
Article
· Feb 2, 2021 12m read
A custom SQL index with Python features

Image search like Google's is a nice feature that wonder me - as almost anything related to image processing.

A few months ago, InterSystems released a preview for Python Embedded. As Python has a lot of libs for deal with image processing, I decided to start my own attemptive to play with a sort of image search - a much more modest version in deed :-)

Open Exchange app
3 0
0 320

It's almost time to get your customers upgraded to new versions - are you worried about showing off your SQL Performance after upgrades? If you want to upgrade without worrying, then I have just the program for you!!! Check out this video from Global Summit 2016 featuring yours truly explaining how to upgrade a system without worrying about pesky SQL queries showing on your waistline!

https://www.youtube.com/watch?v=GfFPYfIoR_g

1 0
0 314

What is Unstructured Data?
Unstructured data refers to information lacking a predefined data model or organization. In contrast to structured data found in databases with clear structures (e.g., tables and fields), unstructured data lacks a fixed schema. This type of data includes text, images, videos, audio files, social media posts, emails, and more.

8 3
0 251

One of our apps uses a class query to support a ZEN Report and works just fine in that report, producing the expected results every time. We’ve since migrated to InterSystems Reports and noticed that, for a report using the same class query, 100s of extra rows with the same column values appear at its bottom.

3 0
1 307

Earlier this year I announced availability of a VS Code extension for coding in ObjectScript, Embedded Python or SQL using the notebook paradigm popularized by Jupyter. Today I published a maintenance release to correct a "getting started" problem.

Here's a video of the installation steps from the extension's README:

Why not try it for yourself?

6 3
0 240

Suppose you have an application that allows users to write posts and comment on them. (Wait... that sounds familiar...)

For a given user, you want to be able to list all of the published posts with which that user has interacted - that is, either authored or commented on. How do you make this as fast as possible?

Here's what our %Persistent class definitions might look like as a starting point (storage definitions are important, but omitted for brevity):

10 1
3 268

I just wrote up a quick sample to help a colleague load data into IRIS from R using RJDBC, and figured it's worth sharing here for future reference.

Ultimately it was pretty simple, aside from IRIS not liking "." in column names; the workaround is to just rename the columns. Someone better at R than me could probably provide some generic approach. smiley

5 2
2 240

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 213

In this article I will explain the usage of %SQL_Diag.Result and %SQL_Diag.Message table along with all-new LOAD DATA functionality.

It is recommended to go through LOAD DATA documentation first.

After successful operation LOAD DATA insert one record in %SQL_Diag.Result table and details are inserted in %SQL_Diag.Message table


Below is the basic command when table is already created and source file does not contain header row. 

LOAD DATA FROM FILE 'C://TEMP/mydata.txt' 
INTO MyTable

The file name must include a .txt or .csv (comma-separated values) suffix and both source and target have the same sequence of data columns.

Loading from File Source: Header

Open Exchange app
3 1
0 237

Introduction

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:

Open Exchange app
3 1
0 235
Article
· Jun 8, 2023 1m read
How to write SELECT ... FOR UPDATE

InterSystems FAQ rubric

Since SELECT ... FOR UPDATE is implemented in many RDBMS as a method of explicit row lock acquisition, I think there are many cases where this function is used.

This syntax is not an error for InterSystems products, but it does not acquire row locks as expected.

This article will show you how to achieve equivalent functionality.

4 0
0 248

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.

0 1
0 222

Customers who switch to Caché from relational databases report that their average performance is up to 20 time faster, running on the same hardware, with no changes to the application. What is it about Caché that lets applications run so fast?

0 0
0 240

Hello Community,

SQL language remains the most practical way to retrieve information stored in a database.

The JSON format is very often used in data exchange.

It is therefore common to seek to obtain data in JSON format from SQL queries.

Below you will find simple examples that can help you meet this need using ObjectScript and Python code.

7 1
4 217
Article
· Nov 24, 2023 4m read
A framework yes, but a suitable framework

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.

2 1
0 208