Skip to main content
Previous sectionNext section

Introduction to InterSystems SQL

InterSystems SQL provides uncompromising, standard relational access to data stored within an InterSystems IRIS® data platform database.

InterSystems SQL offers the following benefits:

  • High performance and scalability — InterSystems SQL offers performance and scalability superior to other relational database products. In addition, InterSystems SQL runs on a wide variety of hardware and operating systems; from laptop computers to high-end, multi-CPU systems.

  • Integration with InterSystems IRIS objects technology — InterSystems SQL is tightly integrated with InterSystems IRIS object technology. You can mix relational and object access to data without sacrificing the performance of either approach.

  • Low maintenance — Unlike other relational databases, InterSystems IRIS applications do not require index rebuilding and table compression in deployed applications.

  • Support for standard SQL queries — InterSystems SQL supports SQL-92 standard syntax and commands. In most cases, you can migrate existing relational applications to InterSystems IRIS with little difficulty and automatically take advantage of the higher performance and object capabilities of InterSystems IRIS.

You can use InterSystems SQL for many purposes including:

  • Object- and web-based applications — You can use SQL queries within InterSystems IRIS Object and Web Server Page applications to perform powerful database operations such as lookups and searches.

  • Online transaction processing — InterSystems SQL offers outstanding performance for insert and update operations as well as the types of queries typically found within transaction processing applications.

  • Business intelligence and data warehousing — The combination of the InterSystems IRIS multidimensional database engine and bitmap indexing technology make it an excellent choice for data warehouse-style applications.

  • Ad hoc queries and reports — You can use the full-featured ODBC and JDBC drivers included with InterSystems SQL to connect to popular reporting and query tools.

  • Enterprise application integration — The InterSystems SQL Gateway gives you seamless SQL access to data stored in external relational databases that are ODBC- or JDBC-compliant. This makes it easy to integrate data from a variety of sources within InterSystems IRIS applications.

Architecture

The core of InterSystems SQL consists of the following components:

  • The Unified Data Dictionary — a repository of all meta-information stored as a series of class definitions. InterSystems IRIS automatically creates relational access (tables) for every persistent class stored within the Unified Dictionary.

  • The SQL Processor and Optimizer — a set of programs that parse and analyze SQL queries, determine the best search strategy for a given query (using a sophisticated cost-based optimizer), and generate code that executes the query.

  • The InterSystems SQL Server — a set of InterSystems IRIS server processes that are responsible for all communications with the InterSystems ODBC and JDBC drivers. It also manages a cache of frequently used queries; when the same query is executed multiple times, its execution plan can be retrieved from the query cache instead of having to be processed by the Optimizer again.

Features

InterSystems SQL includes a full set of standard, relational features. These include:

  • The ability to define tables and views (DDL or Data Definition Language).

  • The ability to execute queries against tables and views (DML or Data Manipulation Language).

  • The ability to execute transactions, including INSERT, UPDATE, and DELETE operations. When performing concurrent operations, InterSystems SQL uses row-level locks.

  • The ability to define and use indices for more efficient queries.

  • The ability to use a wide variety of data types, including user-defined types.

  • The ability to define users and roles and assign privileges to them.

  • The ability to define foreign keys and other integrity constraints.

  • The ability to define INSERT, UPDATE, and DELETE triggers.

  • The ability to define and execute stored procedures.

  • The ability to return data in different formats: ODBC mode for client access; Display mode for use within server-based applications.

Note:

We continue to add support for additional features within InterSystems SQL. If you require a feature that is not supported within this release, please feel free to check with the InterSystems Worldwide Response Center (WRC) to see if it will be included in a newer release.

SQL-92 Compliance

The SQL-92 standard is imprecise with regard to arithmetical operator precedence; assumptions on this matter differ amongst SQL implementations. InterSystems SQL supports configuring your system for either of the following system-wide alternatives for SQL arithmetic operator precedence:

  • InterSystems SQL can be configured to parse arithmetic expressions in strict left-to-right order, with no operator precedence. This is the same convention used in ObjectScript. Thus, 3+3*5=30. You can use parentheses to enforce the desired precedence. Thus, 3+(3*5)=18.

  • InterSystems SQL can be configured to parse arithmetic expressions using ANSI precedence, which gives higher precedence to multiplication and division operators than addition, subtraction, and concatenation operators. Thus, 3+3*5=18. You can use parentheses to override this precedence, where desired. Thus, (3+3)*5=30.

The default for SQL operator precedence depends on your version of InterSystems IRIS. Refer to SQL arithmetic operator precedence for details.

InterSystems SQL supports the complete entry-level SQL-92 standard with the following exceptions:

  • There is no support for adding additional CHECK constraints to a table definition.

  • The SERIALIZABLE isolation level is not supported.

  • Delimited identifiers are not case-sensitive; the standard says that they should be case-sensitive.

  • Within a subquery contained in a HAVING clause, one is supposed to be able to refer to aggregates which are “available” in that HAVING clause. This is not supported.

Extensions

InterSystems SQL supports a number of useful extensions. Many of these are related to the fact that InterSystems IRIS provides simultaneous object and relational access to data.

Some of these extensions include:

  • Support for user-definable data type and functions.

  • Special syntax for following object references.

  • Support for subclassing and inheritance.

  • Support for queries against external tables stored within other databases.

  • A number of mechanisms for controlling the storage structures used for tables to achieve maximum performance.

Interoperability

InterSystems SQL supports a number of ways to interoperate relationally with other applications and software tools.

JDBC

InterSystems IRIS includes a standards-compliant, level 4 (all pure Java code) JDBC client.

The InterSystems JDBC driver offers the following features:

  • High-performance

  • A pure Java implementation

  • Unicode support

  • Thread-safety

You can use InterSystems JDBC with any tool, application, or development environment that supports JDBC. If you encounter problems or have questions about compatibility, contact the InterSystems InterSystems Worldwide Response Center (WRC). You can contact the WRC from the Management Portal by using the Contact button found in the upper right corner.

ODBC

The C-language call level interface for InterSystems SQL is ODBC. Unlike other database products, the InterSystems ODBC driver is a native driver — it is not built on top of any other proprietary interface.

The InterSystems ODBC driver offers the following features:

  • High-performance

  • Portability

  • Native Unicode support

  • Thread-safety

You can use InterSystems ODBC with any tool, application, or development environment that supports ODBC. If you encounter problems or have questions about compatibility, contact the InterSystems InterSystems Worldwide Response Center (WRC). You can contact the WRC from the Management Portal by using the Contact button found in the upper right corner.

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. For this reason, it is important to check for SQLCODE errors at runtime. It is also possible to compile Embedded SQL at the same time as the ObjectScript routine that contains it.

Embedded SQL is quite powerful when used in conjunction with the object access capability of InterSystems IRIS. For example, the following method finds the RowID of the record with a given Name value:

ClassMethod FindByName(fullname As %String)
 {
    &sql(SELECT %ID INTO :id FROM Sample.MyTable WHERE Name = :fullname)
      IF SQLCODE<0 {SET baderr="SQLCODE ERROR:"_SQLCODE_" "_%msg
                    RETURN baderr }
      ELSEIF SQLCODE=100 {SET nodata="Query returns no data"
                    RETURN nodata }
      RETURN "RowID="_id
 }
Copy code to clipboard

For more details, see the chapter “Using Embedded SQL.”

Dynamic SQL

As part of its standard library, InterSystems IRIS provides an %SQL.Statement class that you can use to execute dynamic (that is, defined at runtime) SQL statements. You can use Dynamic SQL within ObjectScript methods. For example, the following method queries for a specified number of people born in the 21st century. The query selects all people born after December 31, 1999, orders the selected records by date of birth, then selects the top x records:

ClassMethod Born21stC(x) [ language=objectscript ]
{
  SET myquery=2
  SET myquery(1) = "SELECT TOP ? Name,%EXTERNAL(DOB) FROM Sample.Person "
  SET myquery(2) = "WHERE DOB > 58073 ORDER BY DOB"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute(x)
  DO rset.%Display()
  WRITE !,"End of data"
 }
Copy code to clipboard

When you prepare a query, an optimized version of that query is stored as a cached query. This cached query is executed for subsequent invocations of the query, avoiding the overhead of re-optimizing a query each time it is executed.

For more details, see the chapter “Using Dynamic SQL.”

Limitations

Note the following limitations of InterSystems SQL:

  • NLS can be used to specify the behavior of $ORDER for a particular national locale behavior for individual globals, as well as for local variables in the currently running process. InterSystems SQL can be used and works well within any National Language locale. However, a current limitation of InterSystems SQL is that for any particular process, all the relevant globals it references have to be using the same national locale as the current process locale. See “SQL Collation and NLS Collations” in the “Collation” chapter of this guide.