Introduction to Caché SQL

SQL-92 Compliance

The SQL-92 standard is imprecise with regard to arithmetical operator precedence; assumptions on this matter differ amongst SQL implementations. Caché SQL supports two system-wide alternatives for SQL arithmetic operator precedence:

• By default, Caché SQL parses 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.

• You can configure Caché SQL 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.

Caché 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

Caché SQL supports a number of useful extensions. Many of these are related to the fact that Caché offers 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.

JDBC

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

The Caché JDBC driver offers the following features:

• High-performance

• A pure Java implementation

• Unicode support

• Thread-safety

You can use Caché 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)Opens in a new tab.

ODBC

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

The Caché ODBC driver offers the following features:

• High-performance

• Portability

• Native Unicode support

• Thread-safety

You can use Caché 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)Opens in a new tab.

Embedded SQL

Within ObjectScript, Caché 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; it is either compiled at the same time as the ObjectScript routine (the default), or you can defer Embedded SQL compilation until runtime.

Embedded SQL is quite powerful when used in conjunction with the object access capability of Caché. For example, the following method finds the Object ID of the Product with a given SKU code and uses it to create an in-memory object instance:

ClassMethod FindBySKU(sku As %String)
{
    &sql(SELECT %ID INTO :id FROM Product WHERE SKU = :sku)
      IF SQLCODE<0 {SET baderr="SQLCODE ERROR:"_SQLCODE_" "_%msg
                    RETURN baderr }
      ELSEIF SQLCODE=100 {SET nodata="Query returns no data"
                    RETURN nodata }
      ELSE {
        // ask the product to display details about itself
        SET product = ##class(Product).%OpenId(id)
        DO product.DisplayDetails()
    }
}

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

Dynamic SQL

As part of its standard library, Caché provides an %SQL.StatementOpens in a new tab class that you can use to execute dynamic (that is, defined at runtime) SQL statements. You can use Dynamic SQL within ObjectScript and Caché Basic 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 = cache ]
{
  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"
 }

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.”