Overview of InterSystems IRIS® SQL usage options - Part 1

Hi Community,

In this series of articles, we will explore the following InterSystems SQL usage options:

1. Embedded SQL

2. Dynamic SQL

3. Class Queries

SQL Overview

InterSystems SQL provides a full set of standard relational features, including the ability to define table schema, execute queries, and define and execute stored procedures. You can execute InterSystems SQL interactively from the Management Portal or programmatically from using a SQL shell interface. Embedded SQL enables you to embed SQL statements in your ObjectScript code, while Dynamic SQL enables you to execute dynamic SQL statements from ObjectScript at runtime.

 
1. Embedded SQL

Within ObjectScript, InterSystems SQL supports Embedded SQL: the ability to place an SQL statement within the body of a method (or other code). Using Embedded SQL, you can query a single record, or define a cursor and use that to query multiple records. Embedded SQL is compiled. By default, it is compiled the first time it is executed (runtime), not when the routine that contains it is compiled. Embedded SQL is quite powerful when used in conjunction with the object access capability of InterSystems IRIS.

2. Dynamic SQL

Dynamic SQL refers to SQL statements that are prepared and executed at runtime. In Dynamic SQL preparing and executing an SQL command are separate operations. Dynamic SQL lets you program within InterSystems IRIS in a manner similar to an ODBC or JDBC application (except that you are executing the SQL statement within the same process context as the database engine). Dynamic SQL is invoked from an ObjectScript program. Dynamic SQL queries are prepared at program execution time, not compilation time. 

3. Class Queries

A class query is a tool,  contained in a class and meant for use with dynamic SQL,  to look up records that meet specified criteria. With class queries, you can create predefined lookups for your application. For example, you can look up records by name, or provide a list of records that meet a particular set of conditions, such as all the flights from Paris to Madrid.

Before moving to the first option, let us create a  persistent class Demo.Person, that also extends the %Populate class to populate some data.

Class Demo.Person Extends (%Persistent, %Populate)
{
/// Person's name.
Property Name As %String(POPSPEC = "Name()") [ Required ];
/// Person's Social Security number. This is validated using pattern match.
Property SSN As %String(PATTERN = "3N1""-""2N1""-""4N") [ Required ];
/// Person's Date of Birth.
Property DOB As %Date(POPSPEC = "Date()");
/// Person's City
Property CITY As %String;
}

Run the following command to check the table data after compiling the above class:

SELECT
ID, CITY, DOB, Name, SSN
FROM Demo.Person

Now run the following command to populate 20 records:

do ##class(Demo.Person).Populate(20)

Run the select query again

We have created the table and populated it with some data. In the upcoming article, we will review Embedded SQL.

Thanks