Skip to main content

SQL

Use SQL within Caché; access third-party ODBC- or JDBC-compliant databases; access Caché 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

Caché SQL

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

Also, you can execute Caché 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 Caché SQL.

For reference information, see the Caché SQL Reference.

Availability: All namespaces.

Includes methods related to Caché 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.

Caché SQL Gateway

Enables your Caché 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 Caché applications using objects and/or SQL queries.

  • Store persistent Caché objects in external relational databases.

For details, see Using Caché with ODBC and see “Using the Caché SQL Gateway” in Using Caché SQL.

Availability: All namespaces.

Caché ODBC driver

Enables you to access Caché as a ODBC-compliant database. See Using Caché with ODBC.

Availability: All namespaces.

Caché JDBC driver

Enables you to access Caché as a JDBC-compliant database. See Using Caché with JDBC.

Availability: All namespaces.

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

Config.SqlSysDatatypesOpens in a new tab and Config.SqlUserDatatypesOpens in a new tab each provide the List() class query.

The class documentation includes examples and details.

Availability: %SYS namespace.

Ensemble SQL Adapters

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

Availability: All Ensemble-enabled namespaces.

Reminder

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

See Also

FeedbackOpens in a new tab