Skip to main content
Previous sectionNext section

SQL

Use SQL within InterSystems IRIS; access third-party ODBC- or JDBC-compliant databases; access InterSystems IRIS as an ODBC- or JDBC-compliant database.

Background

SQL (Structured Query Language) is a programming language designed for managing data in relational database management systems (RDBMS).

Available Tools

InterSystems SQL

InterSystems IRIS provides an implementation of SQL, known as InterSystems SQL, which you can use within various programmatic contexts.

Also, you can 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.

For an introduction, see Using InterSystems SQL.

For reference information, see the InterSystems SQL Reference.

Availability: All namespaces.

%SYSTEM.SQL class and classes in the %SYSTEM.SQL package

Includes methods related to InterSystems SQL. These include methods that do the following tasks:

  • Implement SQL functions

  • Check SQL privileges

  • Purge cached queries

  • Access the %ROWID and SQLCODE variables

  • Import DDL files

  • Launch SQL shells

  • Modify SQL configuration settings

  • Modify SQL Gateway connections

  • And others

Availability: All namespaces.

Provides utilities for SQL migration. This class provides methods like the following;

  • CopyOneView()

  • DSNFetch()

  • DropTable()

  • ExecSql()

  • And others

It also provides several class queries.

Availability: All namespaces.

InterSystems SQL Gateway

Enables your InterSystems IRIS applications to access third-party relational databases (via ODBC or JDBC). Using the SQL Gateway, applications can:

  • Access data stored in third-party relational databases within InterSystems IRIS applications using objects and/or SQL queries.

  • Store persistent InterSystems IRIS objects in external relational databases.

For details, see Using the InterSystems SQL Gateway.

Availability: All namespaces.

InterSystems ODBC driver

Enables you to access InterSystems IRIS as a ODBC-compliant database. See Using the InterSystems ODBC Driver.

Availability: All namespaces.

InterSystems JDBC driver

Enables you to access InterSystems IRIS as a JDBC-compliant database. See Using Java with the InterSystems JDBC Driver.

Availability: All namespaces.

Enable you to modify and obtain information about the [SQL], [SqlSysDatatypes], and [SqlUserDatatypes] sections of the CPF. (Note that you usually modify this file via the Management Portal. See the System Administration Guide.)

Config.SqlSysDatatypes and Config.SqlUserDatatypes each provide the List() class query.

The class documentation includes examples and details.

Availability: %SYS namespace.

SQL Adapters

Enable productions to execute SQL statements against a remote database via an ODBC-defined or JDBC-defined Data Source Name (DSN). See Using SQL Adapters in Productions.

Availability: All interoperability-enabled namespaces.

Reminder

The special variable $SYSTEM is bound to the %SYSTEM package. This means that instead of ##class(%SYSTEM.class).method(), you can use $SYSTEM.class.method().

See Also