InterSystems SQL

InterSystems IRIS provides an implementation of SQL, known as InterSystems SQL. You can use InterSystems SQL within methods and within routines.

You can also execute InterSystems SQL directly within the SQL Shell (in the Terminal) and in the Management Portal. Each of these includes an option to view the query plan, which can help you identify ways to make a query more efficient.

InterSystems SQL supports the complete entry-level SQL-92 standard with a few exceptions and several special extensions. InterSystems SQL also supports indexes, triggers, BLOBs, and stored procedures (these are typical RDBMS features but are not part of the SQL-92 standard). For a complete list, see Using InterSystems SQL.

Using SQL from ObjectScript

You can execute SQL from ObjectScript using either or both of the following ways:

Dynamic SQL (the %SQL.StatementOpens in a new tab and %SQL.StatementResultOpens in a new tab classes), as in the following example:

 SET myquery = "SELECT TOP 5 Name, DOB FROM Sample.Person"
 SET tStatement = ##class(%SQL.Statement).%New()
 SET tStatus = tStatement.%Prepare(myquery)
 SET rset = tStatement.%Execute()
 DO rset.%Display()
 WRITE !,"End of data"

You can use dynamic SQL in ObjectScript methods and routines.

Embedded SQL, as in the following example:

 &sql(SELECT COUNT(*) INTO :myvar FROM Sample.Person)
    IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg  QUIT}
    ELSEIF SQLCODE=100 {WRITE "Query returns no results"  QUIT}
 WRITE myvar

You can use embedded SQL in ObjectScript methods and routines.

Using SQL from Python

You can execute SQL from Python using either or both of the following ways:

You can execute the SQL query directly, as in the following example:

import iris
rset = iris.sql.exec("SELECT TOP 5 Name, DOB FROM Sample.Person") 
for row in rset:
    print(row)

You can also prepare the SQL query first, then execute it, as in the following example:

import iris
statement = iris.sql.prepare("SELECT TOP 5 Name, DOB FROM Sample.Person")
rset = statement.execute()
for row in rset:
    print(row)

You can use either of these approaches to execute SQL queries in the Python terminal or in Python methods.

Object Extensions to SQL

To make it easier to use SQL within object applications, InterSystems IRIS includes a number of object extensions to SQL.

One of the most interesting of these extensions is ability to follow object references using the implicit join operator (–>), sometimes referred to as “arrow syntax.” For example, suppose you have a Vendor class that refers to two other classes: Contact and Region. You can refer to properties of the related classes using the implicit join operator:

SELECT ID,Name,ContactInfo->Name
FROM Vendor
WHERE Vendor->Region->Name = 'Antarctica'

Of course, you can also express the same query using SQL JOIN syntax. The advantage of the implicit join operator syntax is that it is succinct and easy to understand at a glance.

Special Options for Persistent Classes

Introduction