#SQL

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.

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?

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

Here is what I have for this. In it's current state it throws tons of errors:

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.

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')"

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

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

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.

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

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.

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.

InterSystems IRIS Cloud SQL is a fully managed cloud service that brings the power of InterSystems IRIS relational database capabilities used by thousands of enterprise customers to a broad audience of application developers and data professionals. InterSystems IRIS Cloud IntegratedML is an option to this database-as-a-service that offers easy access to powerful Automated Machine Learning capabilities in a SQL-native form, through a set of simple SQL commands that can easily be embedded in application code to augment them with ML models that run close to the data.

Today, we announce the Developer Access Program for these two offerings. Application developers can now self-register for the service, create deployments and start building composable applications and smart data services, with all provisioning, configuration and administration taken care of by the service.