New post

#SQL

16

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

I've asked a lot of questions leading up to this, so I wanted to share some of my progress.

The blue line represents the number of messages processed. The background color represents the average response time. You can see ticks for each hour (and bigger ticks for each day). Hovering over any point in the graph will show you the numbers for that period in time.

This is super useful for "at a glance" performance monitoring as well as establishing patterns in our utilization.

5 2
0 482

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.

4 3
2 439

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 320
Article
· Jun 22, 2023 1m read
Countermeasures against SQL injection

InterSystems FAQ rubric

Countermeasures against SQL injection have been published on various websites, but we believe that it is possible to prevent SQL injection in applications using InterSystems SQL as well as other RDBMS by implementing these countermeasures appropriately. In addition, InterSystems Data Platform (hereinafter referred to as IRIS) incorporates several measures that make SQL injection more difficult than general RDBMS.

4 0
0 393
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 240

Hey Community,

See how cubes are constructed for use in business intelligence, and learn about SQL and MDX query languages. Physical and virtual cubes are used in InterSystems IRIS® Business Intelligence and Adaptive Analytics:

⏯ Introduction to Cubes in Business Intelligence

https://www.youtube.com/embed/l6XFj1JQ5Fw
[This is an embedded link, but you cannot view embedded content directly on the site because you have declined the cookies necessary to access it. To view embedded content, you would need to accept all cookies in your Cookies Settings]

4 0
0 318

Recently I wanted to get a list of all cached queries and their texts. Here's how to do that.

First create an SQL Procedure returning Cache Query text from a Cached Query routine name:

Class test.CQ
{

/// SELECT test.CQ_GetText()
ClassMethod GetText(routine As %String) As %String [ CodeMode = expression, SqlProc ]
{
##class(%SQLCatalog).GetCachedQueryInfo(routine)
}

}

And after that you can execute this query:

4 3
0 563

Hi Community,

See how the new embedded Python feature enables you to write user-defined SQL functions and stored procedures in Python:

⏯ Embedding Python in SQL: Write Your Stored Procedures in Python

https://www.youtube.com/embed/S7v4zDn0N1c
[This is an embedded link, but you cannot view embedded content directly on the site because you have declined the cookies necessary to access it. To view embedded content, you would need to accept all cookies in your Cookies Settings]

4 0
0 265

Let's say we have two serial classes, one as a property of another:

Class test.Serial Extends %SerialObject
{
Property Serial2 As test.Serial2;
}

Class test.Serial2 Extends %SerialObject
{
Property Property As %String;
}

And a persistent class, that has a property of test.Serial type:

Class test.Persistent Extends %Persistent
{

Property Datatype As %String;

Property Serial As test.Serial;

}

So it's a serial, inside a serial, inside a persistent object.

4 2
0 406

Hi Everyone!

New video, recorded by @Stefan Wittmann, is already on InterSystems Developers YouTube:

SQL and Object Access

https://www.youtube.com/embed/i6AkBtB3F-M
[This is an embedded link, but you cannot view embedded content directly on the site because you have declined the cookies necessary to access it. To view embedded content, you would need to accept all cookies in your Cookies Settings]

4 0
2 248

It is often necessary to sort the results of a query on a string field containing a combination of alphabetic and numeric characters. In cases like this the default string collation may not always return the data in the expected sequence.

An example of this may be where a select from Samples.Person should order the results by the street address, but firstly ordered by the street number part as numeric, and then by the street name.

The default query will return the results as follows:

4 4
0 442

In addition to its general security, Caché offers SQL security with a granularity of a single row. This is called row-level security. With row-level security, each row holds a list of authorized viewers, which can be either users or roles. By default access is determined at object modification Some time ago I became interested in determining row-level security at runtime. Here's how to implement it.

4 9
0 717

Attached to this post is a PDF document outlining some of the key enhancements included with 2016.2. I will be giving a WebEx session that is open to all tomorrow at 11 AM EST. Once the WebEx is over I will be adding a link to the recording for those who cannot attend.

2016.2 Field Test Launch
Tuesday, February 2, 2016
11:00 am | Eastern Standard Time (New York, GMT-05:00) | 30 mins

Meeting number: 747 673 229

Join!

4 0
0 394

The InterSystems IRIS has a series of facilitators to capture, persist, interoperate, and generate analytical information from data in XML format. This article will demonstrate how to do the following:

  1. Capture XML (via a file in our example);
  2. Process the data captured in interoperability;
  3. Persist XML in persistent entities/tables;
  4. Create analytical views for the captured XML data.

Capture XML data

The InterSystems IRIS has many built-in adapters to capture data, including the next ones:

Open Exchange app
3 0
0 29

Hi Developers!

Here're the technology bonuses for the InterSystems Vector Search, GenAI, and ML contest 2024 that will give you extra points in the voting:

  • Vector Search usage - 5
  • IntegratedML usage - 3
  • Embedded Python - 3
  • LLM AI or LangChain usage: Chat GPT, Bard, and others - 3
  • Questionnaire - 2
  • Docker container usage - 2
  • ZPM Package deployment - 2
  • Online Demo - 2
  • Implement InterSystems Community Idea - 4
  • Find a bug in Vector Search, or Integrated ML, or Embedded Python - 2
  • First Article on Developer Community - 2
  • Second Article On DC - 1
  • First Time Contribution - 3
  • Video on YouTube - 3
  • Suggest a new idea - 1

See the details below.<--break->

3 0
0 73

Hi,

I was struggling with a procedure that was meant to receive a string and use it as a filter, I've found that since I want the procedure to do some data transformation and return a dataset, I needed to use objectScript language.

I've created the procedure using the SQL GUI in the portal, and everything works fine when calling the procedure from the SQL GUI but not through a JDBC connection here is the call "call spPatientOS('2024-04-07T12:35:32Z')"

3 2
0 129

Pandas is not just a popular software library. It is a cornerstone in the Python data analysis landscape. Renowned for its simplicity and power, it offers a variety of data structures and functions that are instrumental in transforming the complexity of data preparation and analysis into a more manageable form. It is particularly relevant in such specialized environments as ObjectScript for Key Performance Indicators (KPIs) and reporting, especially within the framework of the InterSystems IRIS platform, a leading data management and analysis solution.

3 4
1 73

In ObjectScript you have a wide collection of functions that return some value
typically:

set variable = $somefunction(param1,param2, ...)

There is nothing special about that.
But there is a set of functions that I classify as LEFT SIDED
The specialty of them is that you can use them also on the left of the equal operator
as a target in the SET command:

set $somefunction(param1,param2, ...) = value

3 0
1 124

Hello Everyone,

The Certification Team of InterSystems Learning Services is developing an InterSystems IRIS Developer Specialist certification exam, and we are reaching out to our community for feedback that will help us evaluate and establish the contents of this exam.

3 0
1 166

Hey Developers,

Watch the latest video on InterSystems Developers YouTube:

⏯ Working with InterSystems IRIS Cloud SQL

https://www.youtube.com/embed/okMOwIW-yXY
[This is an embedded link, but you cannot view embedded content directly on the site because you have declined the cookies necessary to access it. To view embedded content, you would need to accept all cookies in your Cookies Settings]

3 1
0 114