About This Book

This book describes how to use the InterSystems SQL, which provides standard relational access to data stored within a database on InterSystems IRIS® data platform.

The book addresses the following topics:

The InterSystems SQL language:

• “Introduction to InterSystems SQL” provides an overview of InterSystems SQL as it relates to software standards and interoperability.

• “InterSystems SQL Basics” describes the fundamental features of InterSystems SQL (such as tables and queries), especially those that are not covered by the SQL standard or are related to InterSystems IRIS® unified data architecture.

• “Language Elements” describes how InterSystems SQL handles the basic elements common to any programming language: numbers, strings, operators, NULL, and comments.

• “Identifiers” describes the conventions used for naming entities within InterSystems SQL.

Data Definition: creating tables and views:

• “Defining Tables” describes how to define tables in InterSystems SQL, by defining persistent classes or by using an SQL DDL statement.

• “Defining Views” describes how to define views in InterSystems SQL by using the Management Portal or by using a DDL statement.

• “Defining Foreign Keys” describes how to define foreign keys in InterSystems SQL.

• “Defining Triggers” describes how to define triggers in InterSystems SQL.

• “Defining and Using Stored Procedures” discusses stored procedures in InterSystems SQL.

• “Storing and Using BLOBs and CLOBs” describes stream data and how to store and use BLOBs and CLOBs in InterSystems SQL.

• “Users, Roles, and Privileges” addresses connections between InterSystems SQL and InterSystems security features.

Data Management: querying and modifying data:

• “Querying the Database” describes how to create and use SELECT queries.

• “Implicit Joins” describes an InterSystems SQL extension that provides arrow syntax for implicit joins. InterSystems SQL also provides standard syntax for explicit joins.

• “Modifying the Database” describes how to use INSERT, UPDATE, and DELETE to modify data, and how to use transactions to group multiple data modifications.

SQL execution interfaces:

• “Using Embedded SQL” describes how to write and execute SQL code embedded within ObjectScript code. This chapter also describes SQL cursors, which enable you to access multiple rows of data.

• “Using Dynamic SQL” describes how ObjectScript can include SQL that is executed at runtime.

• “Using the SQL Shell” describes how to write and execute SQL statements from the Terminal.

• “Using the Management Portal SQL Interface” describes how to write and execute SQL statements from the Management Portal.

• “Importing SQL Code” describes how to execute SQL statements by importing them from a text file. This interface can be used for InterSystems SQL code or SQL code in other vendor formats. Import SQL code can be used to define tables and to populate tables with data; it cannot be used to query data.

SQL interface:

• “Importing and Exporting SQL Data” (an appendix) discusses tools in the Management Portal that enable you to import or export data.

For a detailed outline, see the Table of Contents.

When using InterSystems SQL, you may find the following additional sources useful:

• InterSystems SQL Reference provides details on individual SQL commands and functions, as well as information on the InterSystems SQL data types and reserved words.

• InterSystems SQL Optimization Guide describes how to optimize a table definition by defining and building indices, how to use Tune Table to optimize table metadata based on typical data, and how to optimize query execution using cached queries, ShowPlan, frozen plans, and other optimization techniques.

• Using the InterSystems SQL Gateway describes how to obtain access to external databases via JDBC and ODBC, enabling you to treat external tables as if they were native InterSystems IRIS® tables.

• Orientation Guide for Server-Side Programming is an orientation guide for programmers who are new to InterSystems or who are familiar with only some kinds of InterSystems IRIS® programming.

• In Defining and Using Classes, the chapter “Introduction to Persistent Objects” summarizes how InterSystems IRIS® object technology interoperates with SQL. Later chapters provide additional detail.

• Using Java with the InterSystems JDBC Driver describes how to access InterSystems IRIS® tables from external applications via JDBC.

• Using the InterSystems ODBC Driver describes how to access InterSystems IRIS® tables from external applications via ODBC.

• System Administration Guide lists the Management Portal SQL and Object Settings Pages.

• InterSystems Error Reference lists the SQLCODE error messages.