#SQL

Hello,

I have a global whose structure is multi-level and I am trying through a class and a SQL query to display a table which includes all the values ​​and levels.

on red hat, but I would also be interested in a wider answer.

after running a benchmark for 40 minutes, I have been asked if any rollbacks occurred in that time. Rollbacks from SQL or objects.

The application does not record this, so I am looking for a system level record.

The journal entries do the necessary reverse sets and commit just like any transaction. So i don't think i can detect them there.

The SQL documentation says "Messages indicating that a rollback occurred, and errors encountered during the rollback operation are logged in the cconsole.

The program below works perfectly when I call it directly from the Terminal, however when I call it from within a CSP it does not work (It does not do the SELECT).

In the USER namespace, the program works both in the Terminal and on the CSP , but in another namespace it only works when called directly in the Terminal.

PropList(class) ;
 k pl S c=0 s class="'"_class_"'"
 K ^PropList s ^PropList=class
 S lista=$SYSTEM.SQL.Execute("SELECT * FROM Projet.GerePageCSP")
 while lista.%Next() {
 S c=c+1,pl(c)=lista.

Overview

The online documentation contains a reference Defining and Using Class Queries -

Customizing Stored Procedures with ObjectScript directly has been useful to access NoSQL storage and external messaging via integration, to present output in tabular format.

For example: An application that already uses 90% SQL interaction from a front end, can then also extend this access to the other 10% of required platform functionality, via the same SQL access.

The purpose of this article is to explore how to achieve the same effect via Embedded Python methods.

InterSystems FAQ rubric

By specifying the start and end values ​​of the IDs for which you want to rebuild indexes in the arguments of the %BuildIndices() method provided in the persistent class (=table) definition, you can rebuild only the indexes within that range.

For example, to rebuild the NameIDX index and ZipCode index in the Sample.Person class only for ID=10 to 20, execute the following code (the ID range is specified in the 5th and 6th arguments). 

 set status = ##class(Sample.Person).%BuildIndices($LB("NameIDX","ZipCode"),1,,1,10,20) 

$LB() is the $ListBuild() function

Introduction

In some of the last few articles I've talked about types between IRIS and Python, and it is clear that it's not that easy to access objects from one side at another. 

Fortunately, work has already been done to create SQLAlchemy-iris (follow the link to see it on Open Exchange), which makes everything much easier for Python to access IRIS' objects, and I'm going to show the starters for that.

Thank you @Dmitry Maslennikov !

The FHIR® SQL Builder, or Builder, is a component of InterSystems IRIS for Health. It is a sophisticated projection tool used to create custom SQL schemas using data in an InterSystems IRIS for Health FHIR repository without moving the data to a separate SQL repository. The Builder is designed specifically to work with FHIR repositories and multi-model databases in InterSystems IRIS for Health.

The objective of the Builder is to enable data analysts and business intelligence developers to work with FHIR using familiar analytic tools, without having to learn a new query syntax.

I'm looking for an efficient way in DBeaver to filter system tables (ex: belonging to a schema starting with "%").

By using a user with the %All role, DBeaver shows us a long list of system schemas, which forces us to go down the list before accessing the user tables.

I cannot get any further hint from the error message in red in this screenshot. Is there any clue or details about the connection failure that I could look for?

A bit of an old school CSP question.  <csp:search> is a really nice, quick way to make easy lookups for CSP pages, especially for back office stuff that doesn't need to look pretty for the modern web app user.

At any rate, I'm here at the moment:

<csp:search SHOWSQL=1 NAME="OrgSearch" SELECT="OrganizationName Organization Name,Abbreviation,Software->Abbreviation Software" WHERE="OrganizationName Organization Name,Abbreviation,Software->Abbreviation Software" STARTVALUES=",,Software" PREDICATES="%startswith,%startswith,%startswith" CLASSNAME="AppLibrary.

I found the thread that discusses object mapping, in particular mapping a common global among more than one namespace. The example that is given is a simple one when it's ^global(sub1, ^global(sub2, etc. However I'm having trouble getting this to compile/work when the global has a fixed subscript amongst variable ones.

I have this global in namespaces LAB and ARK in the following format:
^CB(1,sub1)=....

^CB(1,sub2)=...

^CB(1,sub3)=...

Hello Everyone,

Last month, we asked for input from the IRIS community regarding exam topics for our InterSystems IRIS SQL Specialist and Expert exams. We will close the window for providing feedback on the exam topics on Thursday, August 31st, 2023. Thus, if you would like to have your say in what topics are covered on the exam, this is your last chance!

To show our appreciation for helping us validate our exam design, we will hold a raffle where 15 survey respondents will be chosen to receive a $50 gift card. The gift card is a Tango Card that can be redeemed at any number of online retailers.

hi， I followed the example of the system and wrote a method to connect to an external database to obtain a result set, but I couldn't get the result. can anyone please help on this.

/// correct
ClassMethod PersonSets(name As %String = "", state As %String = "MA") As %Integer [ ReturnResultsets, SqlName = PersonSets, SqlProc ]
{
		// %sqlcontext is automatically created for a method that defines SQLPROC

		// SQL result set classes can be easily prepared using dynamic SQL. %Prepare returns a
		// status value. The statement's prepare() method can also be called directly.

Hy,

I have a question how to call another methode inside the main method ?

If I had:
________________________________________
Class MN.Student Extends %Persistent
{
Property Name As %String;
________________________________________
Class MN.Course Extends %Persistent
{
Property TeacherName As %String;
Relationship StudentName As MN.Student [ Cardinality = one, Inverse = Name ];
________________________________________

For "Cardinality = one" the SQL line below would be sufficient for archiving
INSERT INTO MN.Course (TeacherName, StudentName) VALUES ('Pierre','3')"

________________________________________

But if I had "Cardinality = many"
Relationship StudentName As MN.

Hi,

Any recommendations on how i can add a logic if an incoming record that is already existing in the table but different total amount value. I need to subtract the totalamt value on the input file vs the totalamt value on the table and update the table with the difference of totalamt. Any help is much appreciated. Thanks!

Set tSC = $$$OK Try {
s tEntEpicMoopRecord = ##class(MC.Data.EntEpicMoopFile).MemberIDIndexOpen(pRequest.MemberID)
if $ISOBJECT(tEntEpicMoopRecord) {
s tEntEpicMoopRecord.DateUpdated = +$h
s tSC = tEntEpicMoopRecord.%Save()
}
else {
s tEntEpicMoopRecord = ##class(MC.Data.

InterSystems IRIS currently limits classes to 999 properties.

But what to do if you need to store more data per object?

This article would answer this question (with the additional cameo of Community Python Gateway and how you can transfer wide datasets into Python).

The answer is very simple actually - InterSystems IRIS currently limits classes to 999 properties, but not to 999 primitives. The property in InterSystems IRIS can be an object with 999 properties and so on - the limit can be easily disregarded.

Hi all,

I am using %Library.ResultSet to execute my query which I have something like this.
Query GetABC() As %SqlQuery [SqlProc]{

}

After I execute this query and a resultset it returns from this. Now What I want to know is the SQL statement which will have the values dynamically added into while execution.

Hi All,

I am facing some issues. I am having a SQL query that is executed using %SQL.Statement ExecDirect() method. Now I want to convert it into Embedded SQL using &SQL().
But where clause is dynamic in my case and It may contain 3-4 clauses.

Hi all, 

We are doing a SQL server upgrade to SQL server v.15 from a legacy server which had v.10 . I have our Cache server as a linked server int order to pull data from our system. When building out the ROWSPEC, I have my fields typed as %String with various lengths with one field having a max length of 15000. The SQL server is trying to interpret this as a text data type instead of varchar(n). Text is a depracated data type since SQL server 2008.

I'm trying to execute SQL on a EC2 via SSM:

import boto3

instanceid = "i-123456789"
sql = """SELECT path FROM Security.Applications WHERE ID = '/csp/sys'"""
template = """su - irisusr -c 'cat << EOF | iris sql iris -U %SYS
                """ + sql + """
                        q
                        EOF'
                       """
template = [line.strip() for line in template.splitlines()] 
template = """\n""".join(template) 
ssm_client = boto3.client('ssm') 
response = ssm_client.send_command(
            InstanceIds=[instanceid],
            DocumentName="AWS-RunShellScript",
            Comment=AWS,
            Parameters={'commands': template})

Hey Developers,

Enjoy watching the new video on InterSystems Developers YouTube:

⏯ Projecting Data into InterSystems IRIS with Foreign Tables

Hi All,

I am trying to fetch resultset using the below code - 

set rs = ##class(%ResultSet).%New("Simple.Person:ValidateAge")

do rs.Execute()

it is giving me error while the same code I run it through iris terminal worked fine. 
I want to understand that problem behind this. Also how can I check the possible methods that I can use on 'rs' somewhat dir() does in python.

Hi everybody,

I am writing an SQL query of the following form:

SELECT
(SELECT COUNT(*) FROM DataTable WHERE Condition1 AND Condition2),
(SELECT COUNT(*) FROM DataTable WHERE Condition1 AND Condition3),
(SELECT COUNT(*) FROM DataTable WHERE Condition1 AND Condition4),
(SELECT COUNT(*) FROM DataTable WHERE Condition1 AND Condition5),
user.id
FROM UserTable user

where Condition1 depends on user and is the same for all four COUNT(*) subqueries. This query is very slow, and I believe that it can be sped up significantly if Condition1 would be checked once rather than four times.

Is "time" a reserved word in the %CONTAINS function?

When I run the following SQL query, I get 0 matching results

SELECT ID, text
FROM Test.Sentence
WHERE text %CONTAINS('time') OR text %CONTAINS('time ') OR text %CONTAINS(' time')

However, when I run a query where the search string contains words other than "time" alone, it returns the expected matching results

SELECT ID, text
FROM Test.Sentence
WHERE text %CONTAINS('providers time money') OR text %CONTAINS('providers time') OR text %CONTAINS('time money')

It also works if I try to search for "tim" or "ime"

SELECT ID, text 
FROM Test.

Hi all,

I am using ExecDirect() method to get SQL resultset of type %SQL.StatementResult. 

now I am fetch a column something like this rs.%Get("Amount") but if amount column is empty then it is return me empty string. If There is any way to set something like this in IRIS rs.%Get("Amount",0.00) if that is possible.

Hello Everyone,

The Certification Team of InterSystems Learning Services is in the process of developing two exams focused on using SQL in InterSystems IRIS and we need input from our InterSystems IRIS SQL community. Your input will be used to evaluate and establish the contents of the exam.

How do I provide my input? We will present you with a list of job tasks, and you will rate them on their importance as well as other factors.

How much effort is involved? It takes about 20-30 minutes to fill out each survey. You can be anonymous or identify yourself and ask us to get back to you.

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:

1. User registration with account creation.
2. User authentication during login.
3. Adding new passwords with a title, login, and password.

Hi all,
I am using ExecDirect() method to execute my SQL query which is something like "SELECT * from Account where AccountNumber = ? "
But when I am providing a accountnumber that don't exists it doesn't return anything as aspected. while I am trying to check resultset.%SQLCODE in this case it is giving me 0 not 100. What's wrong with my SQL query and Also I there any other Property through which I check for if my resultset is null or it has some values.

In show Query messages in the message viewer the head.%Id is always used. How do you do this via your own sql/objectscript as fast as the portal does a search as using dates searching Ens.MessageHeader on portal is slow. 

For instance if you try do a search saying (TimeProcessed >='2023-06-01 00:00:00.000' and TimeProcessed <'2023-06-02 00:00:00.000') it is slow but using the portal the search would know this is head.%ID >= 5344549861 AND head.%ID <= 5347641372. How do you utilize this in your own queries as can't see the logic in EnsPortal.MsgFilter.Assistant