Using Caché SQL

Version 5.1
15 June 2006

InterSystems Corporation 1 Memorial Drive Cambridge MA 02142 [Link]

Using Caché SQL
Caché Version 5.1 15 June 2006
Copyright © 2006 InterSystems Corporation.
All rights reserved.

This book was assembled and formatted in Adobe Page Description Format (PDF) using tools and information from
the following sources: Sun Microsystems, RenderX, Inc., Adobe Systems, and the World Wide Web Consortium at
[Link]. The primary document development tools were special-purpose XML-processing applications built
by InterSystems using Caché and Java.

The Caché product and its logos are trademarks of InterSystems Corporation.

The Ensemble product and its logos are trademarks of InterSystems Corporation.

The InterSystems name and logo are trademarks of InterSystems Corporation.

This document contains trade secret and confidential information which is the property of InterSystems Corporation,
One Memorial Drive, Cambridge, MA 02142, or its affiliates, and is furnished for the sole purpose of the operation
and maintenance of the products of InterSystems Corporation. No part of this publication is to be used for any other
purpose, and this publication is not to be reproduced, copied, disclosed, transmitted, stored in a retrieval system or
translated into any human or computer language, in any form, by any means, in whole or in part, without the express
prior written consent of InterSystems Corporation.

The copying, use and disposition of this document and the software programs described herein is prohibited except
to the limited extent set forth in the standard software license agreement(s) of InterSystems Corporation covering
such programs and related documentation. InterSystems Corporation makes no representations and warranties
concerning such software programs other than those set forth in such standard software license agreement(s). In
addition, the liability of InterSystems Corporation for any losses or damages relating to or arising out of the use of
such software programs is limited in the manner set forth in such standard software license agreement(s).

THE FOREGOING IS A GENERAL SUMMARY OF THE RESTRICTIONS AND LIMITATIONS IMPOSED BY

INTERSYSTEMS CORPORATION ON THE USE OF, AND LIABILITY ARISING FROM, ITS COMPUTER
SOFTWARE. FOR COMPLETE INFORMATION REFERENCE SHOULD BE MADE TO THE STANDARD SOFTWARE
LICENSE AGREEMENT(S) OF INTERSYSTEMS CORPORATION, COPIES OF WHICH WILL BE MADE AVAILABLE
UPON REQUEST.

InterSystems Corporation disclaims responsibility for errors which may appear in this document, and it reserves the
right, in its sole discretion and without notice, to make substitutions and modifications in the products and practices
described in this document.

Caché, InterSystems Caché, Caché SQL, Caché ObjectScript, Caché Object, Ensemble, InterSystems Ensemble,
Ensemble Object, and Ensemble Production are trademarks of InterSystems Corporation. All other brand or product
names used herein are trademarks or registered trademarks of their respective companies or organizations.

For Support questions about any InterSystems products, contact:

InterSystems Worldwide Customer Support

Tel: +1 617 621-0700
Fax: +1 617 374-9391
Email: support@[Link]
Table of Contents
1 Introduction to Caché SQL ............................................................................................ 1
1.1 Architecture .............................................................................................................. 2
1.2 Features .................................................................................................................... 3
1.2.1 SQL-92 Compliance ....................................................................................... 3
1.2.2 Extensions ...................................................................................................... 4
1.3 Interoperability ......................................................................................................... 4
1.3.1 JDBC .............................................................................................................. 5
1.3.2 ODBC ............................................................................................................. 5
1.3.3 Embedded SQL .............................................................................................. 5
1.3.4 Dynamic SQL ................................................................................................. 6
1.3.5 The Caché SQL Gateway ............................................................................... 6
2 Configuration................................................................................................................... 7
2.1 SQL Options ............................................................................................................ 7
2.2 ODBC Configuration............................................................................................... 7
2.2.1 ODBC Environment Variables and Logging .................................................. 9
2.2.2 Windows Client Management ...................................................................... 10
2.2.3 UNIX Client Management ........................................................................... 14
2.3 JDBC Client Configuration.................................................................................... 24
3 Caché SQL Basics .......................................................................................................... 25
3.1 Tables ..................................................................................................................... 25
3.2 Queries ................................................................................................................... 26
3.3 Privileges ................................................................................................................ 26
3.4 Identifiers............................................................................................................... 26
3.4.1 UNICODE Identifiers................................................................................... 27
3.4.2 Delimited Identifiers..................................................................................... 27
3.4.3 SQL Reserved Words ................................................................................... 28
3.5 Collation ................................................................................................................. 28
3.5.1 Collation of Indexed Values .......................................................................... 30
3.5.2 Legacy Collation Functions .......................................................................... 32
4 DefiningTables ............................................................................................................... 33
4.1 DefiningTables Using Classes ............................................................................... 33
4.2 DefiningTables Using DDL .................................................................................. 34

Using Caché SQL iii

 4.2.1 Using DDL in Embedded SQL .................................................................... 35
4.2.2 Executing DDL Using Dynamic SQL .......................................................... 35
4.2.3 Running DDL Scripts from the Command Line .......................................... 36
5 Views ............................................................................................................................... 37
5.1 Updatable Views .................................................................................................... 37
5.1.1 The WITH CHECK Option .......................................................................... 38
5.2 Read-Only Views ................................................................................................... 39
6 Indices ............................................................................................................................. 41
6.1 Defining Indices..................................................................................................... 41
6.1.1 Defining Indices using a Class Definition .................................................... 41
6.1.2 Defining Indices using DDL........................................................................ 42
6.2 Standard Indices ..................................................................................................... 42
6.2.1 Indices on Multiple Properties ..................................................................... 42
6.2.2 Unique Indices .............................................................................................. 43
6.2.3 Storing Data with Indices ............................................................................. 43
6.3 Bitmap Indices ....................................................................................................... 44
6.3.1 Bitmap Index Operation ............................................................................... 44
6.3.2 Defining Bitmap Indices using a Class Definition ....................................... 46
6.3.3 Defining Bitmap Indices using DDL............................................................ 46
6.3.4 Restrictions on Bitmap Indices ..................................................................... 46
6.3.5 Choosing Index Type .................................................................................... 47
6.4 Building Indices ..................................................................................................... 47
6.4.1 Building Indices with the System Management Portal ................................ 48
6.4.2 Building Indices Programmatically .............................................................. 48
7 Querying the Database .................................................................................................. 51
7.1 The SELECT Statement ......................................................................................... 51
7.1.1 The SELECT List ......................................................................................... 51
7.1.2 The FROM Clause ........................................................................................ 52
7.1.3 The WHERE Clause ..................................................................................... 52
7.1.4 The ORDER BY Clause ............................................................................... 52
7.2 JOIN ....................................................................................................................... 53
7.2.1 OUTER JOIN Limitations ............................................................................ 53
8 Modifying the Database ................................................................................................ 57
8.1 INSERT Statements ............................................................................................... 57
8.2 UPDATE Statements .............................................................................................. 57

iv Using Caché SQL

 8.3 DELETE Statements .............................................................................................. 57
9 Embedded SQL .............................................................................................................. 59
9.1 The Macro Preprocessor ........................................................................................ 59
9.2 Syntax and Use ...................................................................................................... 59
9.2.1 The &sql Directive ....................................................................................... 60
9.2.2 Schema Name Resolution ............................................................................ 60
9.2.3 Literal Values ................................................................................................ 60
9.2.4 Host Variables ............................................................................................... 61
9.2.5 Data Format .................................................................................................. 62
9.2.6 Simple SQL Statements ................................................................................ 63
9.2.7 SQL Cursors ................................................................................................. 64
9.2.8 The SQLCODE Variable and Error Handling .............................................. 66
9.2.9 The %ROWCOUNT Variable ....................................................................... 66
9.2.10 The %ROWID Variable .............................................................................. 67
9.2.11 NULL Handling ......................................................................................... 67
10 Dynamic SQL ............................................................................................................... 69
10.1 Dynamic SQL versus Embedded SQL ................................................................. 69
10.2 The %[Link] Class ............................................................................. 70
10.2.1 Creating a %[Link] Object ....................................................... 70
10.2.2 Preparing a Query ....................................................................................... 70
10.2.3 Finding Meta-information about a Query .................................................. 71
10.2.4 Executing a Query ...................................................................................... 71
10.2.5 Fetching Data from a Query ....................................................................... 72
10.2.6 Closing a Query .......................................................................................... 72
10.3 The Query Cache ................................................................................................. 73
11 Optimizing Performance ............................................................................................. 75
11.1 Indices .................................................................................................................. 75
11.2 EXTENTSIZE and SELECTIVITY .................................................................... 75
11.2.1 EXTENTSIZE ............................................................................................ 75
11.2.2 SELECTIVITY .......................................................................................... 76
12 Foreign Keys ................................................................................................................. 79
13 Triggers ......................................................................................................................... 81
13.1 DefiningTriggers ................................................................................................. 81
13.2 Trigger Code ........................................................................................................ 82

Using Caché SQL v

14 Stored Procedures ........................................................................................................ 83
14.1 Defining Stored Procedures................................................................................. 83
14.1.1 Defining a Stored ProcedureWithin a Class Definition............................. 84
14.1.2 Defining a Stored Procedure Using DDL................................................... 85
14.2 Using Stored Procedures ...................................................................................... 85
14.2.1 Stored Functions ......................................................................................... 85
15 BLOBs, CLOBs, and Streams .................................................................................... 87
15.1 Defining BLOBs and CLOBs Using DDL.......................................................... 87
15.2 Using BLOBs and CLOBs within Caché Methods .............................................. 88
15.3 Using BLOBs and CLOBs from ODBC .............................................................. 88
15.4 Using BLOBs and CLOBs from JDBC ............................................................... 89
16 External Tables ............................................................................................................ 91
17 Users, Roles, and Privileges ........................................................................................ 93
17.1 Users .................................................................................................................... 93
17.2 Roles .................................................................................................................... 94
17.3 Privileges .............................................................................................................. 95
18 Special Features ........................................................................................................... 97
18.1 Implicit Joins ........................................................................................................ 97
18.1.1 References .................................................................................................. 97
18.2 Syntax .................................................................................................................. 99
18.2.1 COUNT(*) with Additional Columns ........................................................ 99
18.3 Functions and Operators ...................................................................................... 99
18.3.1 %ID ............................................................................................................ 99
18.3.2 %STARTSWITH ........................................................................................ 99
18.4 User-defined Functions...................................................................................... 100
Index ................................................................................................................................ 101

vi Using Caché SQL

List of Figures
Caché ODBC Data Source Setup Dialog ........................................................................... 11
Person Table ........................................................................................................................ 44
State Bitmap Index ............................................................................................................. 45
Age Bitmap Index ............................................................................................................... 45
Using Multiple Indices ....................................................................................................... 45

Using Caché SQL vii

List of Tables
Primary Collation Functions ............................................................................................... 29

viii Using Caché SQL

1
Introduction to Caché SQL
Caché SQL provides uncompromising, standard relational access to data stored within a
Caché database.
Caché SQL offers the following benefits:
• High Performance and Scalability—Caché SQL offers performance and scalability
superior to other relational database products. In addition, Caché SQL runs on a wide
variety of hardware and operating systems; from laptop computers to high-end, multi-
CPU systems.
• Integration with Caché Objects—Caché SQL is tightly integrated with Caché Objects.
You can mix relational and object access to data without sacrificing the performance of
either approach.
• Low Maintenance—Unlike other relational databases, Caché applications do not require
index rebuilding and table compression in deployed applications.
• Support for Standard SQL Queries—Caché SQL supports SQL-92 standard syntax and
commands. In most cases, you can migrate existing relational applications to Caché with
little difficulty and automatically take advantage of Caché's higher performance and
object capabilities.

You can use Caché SQL for many purposes including:

• Object- and web-based applications—you can use SQL queries within Caché Object and
Caché Server Page applications to perform powerful database operations such as lookups
and searches.

Using Caché SQL 1

Introduction to Caché SQL

• Online Transaction Processing—Caché 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 Caché's multidimen-
sional 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 Caché SQL to connect to popular reporting and query tools.
• Enterprise Application Integration—the Caché SQL Gateway gives you seamless SQL
access to data stored in external relational databases. This makes it easy to integrate data
from a variety of sources within Caché applications.

To learn more about Caché SQL:

• Refer to Objects and SQL in Using Caché Objects for details on Caché Objects and how
it interoperates with SQL.
• Refer to the Caché SQL Reference for details on individual SQL commands and functions.
• The Caché System Management Portal lets you manage Caché SQL tables, views, roles,
and users. In addition, you can use it to import relational data from other databases into
Caché.

1.1 Architecture
The core of Caché SQL consists of the following components:
• The Unified Data Dictionary—a repository of all meta-information stored as a series of
class definitions. Caché automatically creates relational access (tables) for veery 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 Caché SQL Server—a set of Caché server processes that are responsible for all
communications with the Caché ODBC and JDBC drivers. It also manages a cache of
frequently used queries; when the same query is executed multiple times, its execution

2 Using Caché SQL

 Features

plan can be retrieved from the query cache instead of having to be processed by the
Optimizer again.

1.2 Features
Caché 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 opera-
tions. When performing concurrent operations, Caché 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 INSER
T, UPDATE, and DELETE triggers.
• The ability to define and xeecute stored procedures.
• The ability to return data in different formats: ODBC mode for client access; Display
mode for use within server-based applications (such as CSP pages).

Note: We continue to add support for additional features within Caché SQL. If you require
a feature that is not supported within this release, please feel free to check with
InterSystems' Technical Support to see if it will be included in a newer release.

1.2.1 SQL-92 Compliance

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.
• The precedence of arithmetical operators in SQL expressions differs from the SQL-92
standard. Caché SQL parses arithmetic expressions in strict left-to-right order, with no

Using Caché SQL 3

Introduction to Caché SQL

operator precedence. This is the same convention used in Caché ObjectScript. Thus,
3+3*5=30. You can use parentheses to enforce the desired precedence. Thus, 3+(3*5)=18.

• Delimited identifiers are case-insensitive; 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.

In addition, Caché SQL does not support the following (which are not part of entry-level
SQL-92):
• OUTER JOINS must be based on equality conditions between fields; there is no support
for OUTER JOIN on non-equal, range, or OR conditions.

Refer to OUTER JOIN Restrictions for a more complete list of OUTER JOIN limitations.

1.2.2 Extensions
Caché SQL supports a number of useful extensions. Many of these are related to the fact that,
as a post-relational database, 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 sub-classing 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.

These and others are described in more detail in the Special Features section.

1.3 Interoperability
Caché SQL supports a number of ways to interoperate relationally with other applications
and software tools.

4 Using Caché SQL

 Interoperability

1.3.1 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 please contact
InterSystems' Technical Support.

1.3.2 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 sup-
ports ODBC. If you encounter problems or have questions about compatibility please contact
InterSystems' Technical Support.

1.3.3 Embedded SQL

Within Caché ObjectScript, Caché SQL supports embedded SQL: the ability to place an SQL
statement within the body of a method (or other code).

Using Caché SQL 5

Introduction to Caché SQL

Embedded SQL is quite powerful when used in conjunction with Caché's object access. For
example, the following method finds the Object ID of theProduct 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) {
// ask the product to display details about itself
Set product = ##class(Product).%OpenId(id)
Do [Link]()
}
}

For more details, refer to the chapter on Embedded SQL.

1.3.4 Dynamic SQL

As part of its standard library, Caché provides a %[Link] class that you can use to
execute dynamic (that is, defined at runtime) SQL [Link] can use dynamic SQL
within Caché ObjectScript and Basic methods. For example, the following method lists all
available products ordered by price:
ClassMethod ShowByPrice() [ language = basic ]
{
rs = New %[Link]()
[Link]("SELECT SKU, Name FROM [Link] ORDER BY Price")
[Link]()

While ([Link]())
PrintLn [Link]("SKU") & ":" & [Link]("Name")
Wend
}

For more details, refer to the chapter on Dynamic SQL.

1.3.5 The Caché SQL Gateway

Using the Caché SQL Gateway, you access tables within external relational databases as if
they were stored natively within Caché.
For more information, refer to the Caché SQL Gateway documentation.

6 Using Caché SQL

2
Configuration
Caché SQL is automatically installed as part of a Caché installation. After Caché is configured,
Caché SQL does not typically require any additional configuration or setup. Most of the
Caché SQL configuration options deal with the operation of DDL (how different flavors of
DDL map to Caché) and with management of cached queries.
For Windows systems, there is an “SQL client only” installation option available that installs
only the client access (ODBC driver) components of Caché.

2.1 SQL Options

You can view and modify the various Caché SQL configuration settings using the System
Management Portal. Select System Configuration and then Advanced Settings. From the pull-
down Category list, select SQL. This displays the SQL configuration options.

2.2 ODBC Configuration

By default, Caché performs a full ODBC installation with a standard installation. This section
describes how to set up and configure the ODBC driver. The architecture of an ODBC system
has five parts:
• The client application—An application makes calls according to Microsoft's ODBC API.
ODBC calls establish a connection from the client to a Data Source Name (DSN), which,
for Caché, is a mapping that specifies a particular namespace for a particular Caché

Using Caché SQL 7

Configuration

installation, as well as other attributes. For a client application to connect to a particular

DSN, that DSN must be registered with the ODBC driver manager.
• The ODBC driver manager—The driver manager accepts calls from applications using
the ODBC API and hands them off to a registered client driver, such as the Caché client
driver. The driver manager also performs any necessary tasks so that the client application
can communicate with the client driver and, ultimately, the database server. The Windows
driver manager is created by Microsoft and included as part of the operating system; for
UNIX, several are available.
• The ODBC client driver—A database-specific application that accepts calls from a client
application through the ODBC driver manager and provides communication to the database
server. It also performs the range of ODBC-related data conversions that the application
may request.
• The database server—The actual database ultimately receiving the calls from the ODBC
client application. It can be on the same or a different machine than the ODBC client
driver from which it is receiving calls. Each Caché database can support multiple DSNs,
each of which can support multiple connections.
• An initialization file—A set of configuration information for the dri ver manager;
depending on the operating system, it may also contain client driver information. On
UNIX, this is an actual file, frequently [Link]. On Windows, it is a registry entry.

Note: For a particular vendor database, that vendor may offer its own version of the ODBC
driver for that platform. Oracle, for example, supplies its own ODBC driver for use
with Oracle databases on Windows. This may be preferred in some case since the
vendor driver may take advantage of its knowledge of how the database works
internally to optimize performance or enhance reliability.

In practice, a connection is established as follows:

1. A client application includes ODBC calls that attempt to connect to a particular DSN. A
client application is linked to an ODBC driver manager, which accepts the calls.
2. The ODBC driver manager reads the Caché ODBC initialization file to obtain the location
of the Caché ODBC client driver and load the client driver into memory.
3. Once loaded into memory, the Caché client driver uses the Caché ODBC initialization
file to locate connection information for the DSN, including the host on which Caché is
running, the name of the Caché installation, and the namespace containing the data. Using
both the DSN and other information from the initialization file, the client driver connects
to the specified Caché installation and namespace.

8 Using Caché SQL

 ODBC Configuration

4. Having established the connection, the client driver maintains communications with the
Caché database server.

2.2.1 ODBC Environment Variables and Logging

This section describes the ODBC environment variables and what they do. They are:
• CACHEODBCDEFTIMEOUT
• CACHEODBCHEADER
• CACHEODBCPID
• CACHEODBCTRACE (UNIX only)
• CACHEODBCTRACEFILE

To set a Windows environment variable, go to the Windows Control Panel, select the System
icon, then select the Advanced tab. In that tab, in the Environment Variables section, select
the New..., Edit..., or Delete... button to perform whatever task you need.
To set a UNIX environment variable on the Bourne or Korn shell, use the following commands:
VARIABLE=value
export VARIABLE

To set a UNIX environment variable on the C-Shell, use either of the following commands:
setenv VARIABLE value
set VARIABLE=value

To turn off an environment variable in a UNIX shell, run unset or set it equal to 0. If you
never initially set its value, it remains disabled.

[Link] CACHEODBCDEFTIMEOUT
This variable allows you to specify the duration of a timeout for a default login. Its value is
in seconds.

[Link] CACHEODBCHEADER
This boolean variable specifies that the driver writes header information to the log file. Header
information is printed when the driver is loaded and includes build version, date, and time.
A value of 1 turns on header generation and a value of 0 turns off header generation. By
default, header generation is off.

Using Caché SQL 9

Configuration

[Link] CACHEODBCPID
This boolean variable enables the automatic appending of the process ID number to the log
file name.A value of 1 enables appending and a value of 0 disables it. By default, appending
is off.
With CACHEODBCPID enabled, if the base log file [Link] and is in your current
directory, then the process ID of 21933 generates a full log file name of
“[Link].21933” .
CACHEODBCPID interacts with CACHEODBCTRACEFILE. If you use
CACHEODBCTRACEFILE to set the base file name of the log file (for instance, to
C:/home/mylogs/[Link] and enable CACHEODBCPID, then log file names generated will
be of the form C:/home/mylogs/[Link].21965.

[Link] CACHEODBCTRACE (UNIX Only)

This boolean variable enables client driver logging. The default name for this file is
[Link]. On Windows, enable client driver logging by checking the ODBC Log check
box on the DSN setup dialog.
To enable driver manager logging on UNIX, set Trace entry in the Initialization file;to enable
driver manager logging on Windows, use the ODBC Data Source Administrator dialog .

[Link] CACHEODBCTRACEFILE
This variable specifies the location and name of the trace file.
This can be useful for placing
the trace file in a unique directory or giving it a unique name. For UNIX, the trace file is
generated in the current directory by default; for Windows, the trace file is generated in
C:/WinNT or C:/Windows by default.

2.2.2 Windows Client Management

The tasks you can perform on Windows include:
• Client Driver Configuration
• Enabling Driver Manager Logging

10 Using Caché SQL

 ODBC Configuration

[Link] Windows Client Driver Configuration

The configuration of the ODBC client is performed using theCaché ODBC Data Source Setup
dialog. This is accessible by clicking the ODBC Data Sources icon, either in Windows Control
Panel or its Administrative Tools subpanel:

Caché ODBC Data Source Setup Dialog

The InterSystems Caché ODBC Data Source Setup dialog is used to configure a specific Caché
ODBC data source. You then can use this data source to make an ODBC connection to Caché
from an ODBC-aware application (such as a development tool or report writer). The elements
of this dialog are analogous to those in each section heading of the ODBC initialization file.
The fields within theData Source dialog are listed below and are required unless otherwise
specified:
• Name—Specifies the name of the DSN.
• Description—Specifies an optional description of the DSN.
• Host IP Address—Specifies the IP address of the DSN in dotted decimal or dotted quad
form, such as “[Link]” .
• Host Port Number—Specifies the port for connecting to the [Link] default for Caché
is 1972.
• Caché Namespace—Specifies the namespace for the DSN.
• User Name—Specifies the user name for logging into the DSN. By default, this is
“_SYSTEM” and is not case sensitive (optional).

Using Caché SQL 11

Configuration

• Password—Specifies the password for the account specified by theUID entry. For the
SYSTEM user name, the password is “sys” and is not case sensitive (optional).
• ODBC Log—If checked, specifies the creation of a log file of ODBC client dri
ver activities.
This log is for troubleshooting; you should not turn logging on during normal operation
as it will dramatically slow down ODBC performance. Note that for Windows 2003, you
must perform the procedure specified in the section Setting Up Logging forWindows
2003. To enable client driver logging on UNIX, use the ODBCTRACE variable. To
perform driver manager logging on Windows, enable it; on UNIX, configure theinitial-
ization file.
• Static Cursors—If checked, enables the Caché ODBC driver's static cursor support. If
this flag is off, then the cursor support provided by the ODBC Cursor Library will be
used. In general, this flag should be off unless you have a specific reason for not using
the ODBC Cursor Library.
• Disable Query Timeout—If checked, causes the ODBC driver to ignore the value of the
ODBC query timeout setting.
The ODBC query timeout setting specifies how long a client should wait for a specific
operation to finish. If an operation does not finish within the specified time, it is automat-
ically cancelled. The ODBC API provides functions to set this timeout value program-
matically. Some ODBC applications, however, hard-code this value. If you are using an
ODBC application that does not allow you to set the timeout value and the timeout value
is too small, you can use the Disable Query Timeout option to disable timeouts.
• Unicode SQL Types—If checked, turns on reporting of a Unicode SQL type (
“SQL_WVARCHAR (-9) SQLType” ) for string data. This allows Microsoft Office 2000
and Visual Basic applications to allocate the properly sized buffers to hold multibyte
data. This functionality is only relevant if you are working with a multibyte character
set, such as in Chinese, Hebrew, Japanese, or Korean locales. If you are only using single-
byte character set data, do not check this box.
If an application encounters a “SQL data type out of range” error from the Microsoft
Driver Manager using SQLBindParameter, it can be caused by having selected this
check box.

After you have configured an ODBC Data Source, you can use theTest Connection button
to see if your data source is working correctly.
On Windows 64-bit, use the Windows Control Panel ODBC Administrator to create user
DSNs that function for both 32- and 64-bit programs. To configure a system DSN for a 32-
bit program, run %SystemRoot%\SysWow64\[Link].

12 Using Caché SQL

 ODBC Configuration

[Link] Enabling Driver Manager Logging on Windows

To enable driver manager logging on Windows, click the Start Tracing Now button on the
Tracing panel of the ODBC Data Source Administrator dialog. This dialog is accessible through
the Windows Control Panel, either from the Administrative Tools subpanel or directly as the
ODBC Data Sources choice, depending on which version of Windows is running. The file
specified in theLog file Path field determines the location of the trace file.
To enable driver manager logging on UNIX, use the Trace entry in the initialization file. To
enable client driver logging on Windows, use the ODBC Log check box in the Windows ODBC
Data Source Setup dialog; to enable client driver logging on UNIX, use the CACHEODBC-
TRACE environment variable.

[Link] Setting Up Logging for Windows 2003

There are special requirements for setting up logging when running an ODBC client on
Windows 2003, specifically for the situation where ODBC is being run by theWeb server
process. In addition to ensuring that the ODBC client has permission to write to the appropriate
logging directory, you need to perform the following procedure:
1. Specify a location for the log files which is not on the default path (where the Windows
directory is the default).
a. To do this, from the Windows Control Panel, select System, then the Advanced tab,
then the Environment Variables button, which displays the Environment Variables
dialog. In the System Variables section of this dialog, click the New button to display
the New System Variable dialog. For the Variable Name, enter CACHEODBCTRACEFILE;
for the Variable Value, enter C:\ODBC_Logs\[Link].
b. When specifying the log file information, you also have the option of defining the
CACHEODBCPID environment variable to include PID information. To do this
create another new variable with a name of CACHEODBCPID and a value of 1. For
more information on the use of this variable, see the “CACHEODBCPID” subsection
of the “ODBC Environment Variables and Logging” section.

2. Create the folder C:\ODBC_Logs.

3. Activate ODBC logging by selecting ODBC Log checkbox in the DSN setup screen.
4. Specify the appropriate authorizations to allow ODBC to perform logging. To do this:
a. From the Windows Control Panel, select Administrative Tools, then Computer Man-
agement, then Add Local Users and Groups, then Users, then ASPNET, then Member

Using Caché SQL 13

Configuration

of, then Add, then Enter the Object Names to Select. Enter Administrators — if
this is not already defined on your system.
b. From the Windows Control Panel, select Administrative Tools, then Computer Man-
agement, then Add Services and Applications. Here, right-click Internet Information
Services, then select All Tasks, then Restart IIS, then Restart Internet Services on
<your machine name here> and select OK.

2.2.3 UNIX Client Management

This section covers the following topics:
• Supported Drivers and Platforms
• Installing and Configuring a Caché UNIX ODBC Client Driver as Part of a Full Install
• Installing and Configuring a Stand-alone Caché UNIX ODBC Client Driver
• The ODBC Initialization File, “[Link]”
• Testing an ODBC Client Driver for UNIX
• Troubleshooting for Shared Objects
• Compiling and Linking select.c
• Installing and Configuring the iODBC Driver
• Configuring PHP withApache and the Driver Manager for [Link]

[Link] Supported Drivers and Platforms

See the Supported Server Platforms section of the Caché Supported Platforms document for
a list of supported platforms.
To use the Caché ODBC Driver, you must have an ODBC driver manager on UNIX. The
suppliers of UNIX ODBC driver managers for use with the Caché ODBC driver are:
• iODBC — Freeware with open source code. This is the default driver installed with UNIX
Caché.
• unixODBC — Freeware with open source code. unixODBC supports a Unicode driver
and is the default driver for many versions of UNIX. For information on using the
unixODBC Unicode driver (libcacheodbcw), contact InterSystems.

14 Using Caché SQL

 ODBC Configuration

InterSystems currently supports the ODBC narrow interface on all UNIX platforms through
the libcacheodbc library. Caché ships with a pre-built iODBC driver manager.

[Link] Installing and Configuring a Caché UNIX ODBC Client Driver as Part of
a Full Install
This section describes tasks for using ODBC as part of a complete Caché installation. You
can also perform a stand-alone client installation.
1. During Caché installation, there is the following prompt:
Do you want to install ODBC and SQL Gateway <Yes>?

Answer “Yes,” which is the default. This ensures that you have the appropriate ODBC
software on your machine.
2. After installation, start Caché with the ccontrol command:
# ccontrol start <configname>

where confignameis the configuration name that you chose during installation.

What Gets Installed

The installation places the following components under the cachesys directory:
• ./bin/[Link]—The Caché ODBC driver.

• ./bin/[Link]—The Unicode Caché ODBC driver for use with the C++ binding.

• ./bin/stlport_gcc.so—The Linux stream support library.

• ./bin/[Link]—The SQLGateway product for connecting from Caché to other ODBC

drivers.
• ./mgr/[Link]—A sample ODBC initialization file used inside Caché by theSQL-
Gateway to connect to other DSNs and by the sample scripts.
• ./dev/odbc/doc/[Link]—This document.

• ./dev/odbc/redist/iodbc—iODBC shared object and include files.

• ./dev/odbc/redist/unixodbc—unixODBC shared object and include files.

• ./dev/odbc/samples/select/*

- select.c—A sample ODBC program that prompts for a SELECT statement.

Using Caché SQL 15

Configuration

- select—A sample ODBC program already linked with the iODBC driver manager.
To run this program, type in ./select dsnname and enter a SELECT statement to
connect to a running Caché, such as
./select samples
SELECT * from [Link]

- [Link]—A shell script that sets up the environment variables needed to find the
[Link] file (in themgr directory that was also created as part of the installation),
sets up the search paths (that is, LD_LIBRARY_PATH, or LIBPATH on AIX) to find
[Link], and runs the above SELECT statement against [Link].

• ./dev/odbc/samples/sqlgateway/*

- [Link]—A script for testing SQL Gateway connectivity using the “SAM-
PLES” namespace.
- [Link]—A routine that makes the callout to the Caché “SAMPLES”
namespace using iODBC and the Caché ODBC driver [Link].

[Link] Installing and Configuring a Stand-alone Caché UNIX ODBC Client Driver
To create a stand-alone client installation, the steps are:
1. Create the directory where you wish to install the client, such as /usr/cacheodbc/:
# mkdir /usr/cacheodbc

2. Copy the zipped tar containing the files for client driver from the mounted CD drive to
the directory where you just created with a command of the form:
# cp <CD-dir>/dist/ODBC/ODBC-<release-code>-<platform>.tar.Z <client-install-dir>

where CD-dir is the mounted location of the Caché, release-code and platform are release-
specific and operating-system–specific codes (see ne xt step), client-install-dir is the client
installation directory that you created in the previous step.
3. Go to the client installation directory and manually unpack the .tar file. From the same
directory on the CD where the cinstall program located, the .tar file is in the./dist/ODBC/
directory.
To manually unpack the .tar file, the procedure is:
# cd <client-install-dir>
# gunzip ODBC-<release-code>-<platform>.tar.Z
# tar xvf ODBC-<release-code>-<platform>.tar

16 Using Caché SQL

 ODBC Configuration

where release-code is a release-specific code (that varies among Caché versions and
releases) and platform specifies the operating system for the driver. Possible values of
platform are:
• decunix5—DECUnix Tru64
• decunix5clu—DECUnix Tru64 for clusters
• hp11—HP/UX (32 bit)
• hp64—HP/UX (64 bit)
• linux—Linux, all supported platforms
• usparc—Solaris
• ppc—AIX (32 bit)
• ppc64—AIX (64 bit)

This creates bin and dev directories, and installs a set of files.
4. Run the ODBCInstall program, which will be in the directory that you created previously.
This program creates the several sample scripts and [Link] under the
mgr directory. For example:

# pwd
/usr/cacheodbc
# ./ODBCInstall

5. Configure the installation'sinitialization file.

6. Test the installation.

[Link] The ODBC Initialization File

To establish connections between a client application and an ODBC driver, the iODBC driver
manager uses an initialization [Link] file provides information so that the driver manager
can locate and connect to an available DSN, including the path of the ODBC driver required
for that connection. The Caché ODBC driver, [Link], references the initialization
file to specify DSN options (such as a timeout duration) when connecting on UNIX platforms.
The file can have any name, but, typically, it is called .[Link] when it is located in a user's
personal directory, [Link] when located in an ODBC-specific directory; the Caché-specific
version of it is called [Link] and is located in a the /<cache-install-dir>/mgr directory.
To locate this file, the Caché ODBC driver mimics the search order of iODBC release 3.0.6.
It looks for the file in the following places (in this order):

Using Caché SQL 17

Configuration

1. The file specified by the

ODBCINI environment variable, if this is [Link] defined,
this variable specifies a path and file, such as:
ODBCINI=/usr/cachesys/[Link]
export ODBCINI

2. The .[Link] file in the directory specified by the user's

$HOME variable, if $HOME is
defined [Link] exists.
3. If $HOME is not defined, [Link] file in the “home” directory specified in the
passwd file.

4. The file specified by the system-wideSYSODBCINI environment variable, if this is

[Link] with ODBCINI, when defined, this variable specifies a path and file, such
as:
SYSODBCINI=/usr/cachesys/[Link]
export SYSODBCINI

5. The [Link] file located in the default directory for building the iODBC driver manager
(/etc/), so that the full path and file name are/etc/[Link].

The iODBC 3.0.6 driver manager ships with Caché releases, built as a nonthreaded shared
object. The driver manager is used for SQLGateway ([Link]) and with a select program we
build for testing ODBC. Our examples use [Link] in the mgr directory which is con-
figured during the install for accessing the samples and user [Link] should modify
[Link] to add additional DSNs for use with [Link] and iODBC, as Caché sets the
ODBCINI environment variable to access this file. If you wish to use a different [Link] file
then you should delete/rename the [Link] file to allow the driver manager to search
the $HOME or /etc/[Link] paths.
The following is a sample initialization file:
[ODBC Data Sources]
samples=samples

[samples]
Driver = /usr/cachesys/bin/[Link]
Description = Cache' 5.x ODBC driver
Host = localhost
Namespace = SAMPLES
UID = _SYSTEM
Password = sys
Port = 1972
Protocol = TCP
Query Timeout = 1
Static Cursors = 0
Trace = off
TraceFile = [Link]

[Default]
Driver = /usr/cachesys/bin/[Link]

18 Using Caché SQL

 ODBC Configuration

This file includes the following variables:

• ODBC Driver Sources—Lists all DSNs for the file. Each entry is of the form
“DSNName=SectionHeading” , where DSNName is the name specified by the client
application and the SectionHeading specifies the heading under which DSN information
appears in this file.
• Driver—Specifies the location of the client driver file,[Link].
• Description—Contains an optional description of the DSN.
• Host—Specifies the IP address of the DSN in dotted decimal or dotted quad form, such
as “[Link]” .
• Namespace—Specifies the namespace for the DSN.
• UID—Specifies the user name for logging into the DSN. By default, this is “_SYSTEM”
and is not case sensitive.
• Password—Specifies the password for the account specified by theUID entry. For the
SYSTEM user name, the password is “sys” and is not case sensitive.
• Port—Specifies the port for connecting to the [Link] default for Caché is 1972.
• Protocol—Specifies the protocol for connecting to the DSN. For Caché, this is always
TCP.
• Query Timeout—If 1, causes the ODBC driver to ignore the value of the ODBC query
timeout setting.
The ODBC query timeout setting specifies how long a client should wait for a specific
operation to finish. If an operation does not finish within the specified time, it is automat-
ically cancelled. The ODBC API provides functions to set this timeout value program-
matically. Some ODBC applications, however, hard-code this value. If you are using an
ODBC application that does not allow you to set the timeout value and the timeout value
is too small, you can use the Disable Query Timeout option to disable timeouts.
• Static Cursors—If 1, enables the Caché ODBC driver's static cursor support. If 0, then
the cursor support provided by the ODBC Cursor Library will be used. In general, this
flag should be off (that is, set to 0) unless you have a specific reason for not using the
ODBC Cursor Library.
• Trace—Specifies whether the driver manager performs logging ( “on” ) or not ( “off” );
by default, logging is off. To enable logging for the client driver itself, use the
CACHEODBCTRACE environment variable. (To enable driver manager logging on
Windows, use the Tracing panel of the ODBC Data Source Administrator dialog; to enable

Using Caché SQL 19

Configuration

client driver logging on Windows, check the ODBC Log check box on the DSN config-
uration screen of the ODBC Data Source Administrator.)
• TraceFile—If logging is enabled by the Trace entry, specifies the location of the driver
manager log file.

[Link] Testing a Caché ODBC Client Driver for UNIX

This section describes how to test the UNIX ODBC client driver:
1. Run the select program to ensure that the client can read data:
# cd <cache-install-dir>/dev/odbc/samples/select
# ./[Link]

What is the [Link] script doing? It calls the select program that is installed with the
kit; the script also provides the select program with a particular DSN ( “SAMPLES” ).
The select program is linked to the iODBC driver manager ([Link]); the driver manager
reads the Caché initialization file <cache-install-dir>/mgr/[Link]
( ) to get connection
information for the “SAMPLES” DSN. The driver manager determines the location of
the Caché ODBC client driver and loads it into memory. The client driver then establishes
a TCP/IP connection to port 1972 and is connected to the Caché “SAMPLES” namespace
using the DSN information inside the Caché initialization file. Once the connection is
established, the client application runs an SQL select statement against the Caché database.
Specifically , the statement is “SELECT * from [Link] WHERE ID < 11” , which
obtains the first ten rows of the [Link] table in the “SAMPLES” namespace.
To see the application ODBC calls, refer to select.c.
2. With a client driver that is part of a full Caché installation (not a stand-alone client), you
can also test SQL Gateway access from Caché:
./sqlgateway/[Link]

from the same directory as the previous step.

The [Link] script starts a Caché session and runs the routine SQLGatewayTest
in the “SAMPLES” namespace. This application routine then loads a shared object
called [Link], which is linked against the iODBC driver manager. As in the previous
example, the driver manager load the client driver using information from the Caché
initialization [Link] client driver then establishes a TCP/IP connection to port 1972
and is connected to the Caché “SAMPLES” namespace using the DSN information
inside the Caché initialization [Link] application routine runs the same query as above.

20 Using Caché SQL

 ODBC Configuration

The difference between this example and the simple select example above is that in this
example, the Caché process making the initial call is the client application. Typically, a
Gateway call from Caché calls the DSN of another vendor's database.

The [Link] and [Link] scripts are designed to work with Caché login or unauthen-
ticated modes and in Minimal or Normal security installations. To create your own calls,
examine the content of the files for these projects for code that you can modify for your own
needs.

[Link] Troubleshooting for Shared Object Dependencies

To validate dependencies on other shared objects and correct any problems, the process is:
1. Use the ldd command against an executable or shared object:
# ldd <cache-install-dir>/bin/[Link]

If any dependencies are not found, ldd displays an error similar to the following:
libstlport_gcc.so => not found

2. If there are no errors, then all dependencies are valid; if there are errors, run the following
commands to force the shared object loader to look in the current directory:
# sh
# cd <cache-install-dir>/bin
# LD_LIBRARY_PATH=`pwd`:$LD_LIBRARY_PATH
# export LD_LIBRARY_PATH

The sh command starts the Bourne shell; the cd command changes to the appropriate
directory; and the export command sets the path to look up shared objects.
3. Once you have added the current directory to the path, run ldd again and check for
missing dependencies. If any shared objects cannot be found, add them to the same
directory as the ODBC driver.
4. To permanently make shared objects accessible, begin by copying the shared objects to
/usr/lib or /usr/local/lib, depending on your operating system. Consult the documentation
for your operating system to determine which is the appropriate directory and if there are
any special commands for this, such as registering them on Linux with the ldconfig
command.

[Link] Compiling and Linking select.c

To compile and link the select.c sample program:

Using Caché SQL 21

Configuration

1. Go to <cachesys>/dev/odbc/samples/select, the directory containing the select.c file.

2. Compile the program using cc, the UNIX compiler, ensuring that you include any include
files. For a default Caché installation, the command is:
# cc -c select.c -I../../redist/iodbc/include

3. Link the file usingcc, ensuring that you link to any required libraries. Again, for a default
installation, the command is:
# cc -o select select.o -L../../redist/iodbc -ldl -liodbc

You can also compile and link against a custom installation of iODBC. For example, if it is
installed in /usr/local, the commands are:
# cc -c select.c -I/usr/local/iodbc/include
# cc -o select select.o -L/usr/local/iodbc/lib -ldl -liodbc

[Link] Installing and Configuring the iODBC Driver

By default, each Caché installation includes an iODBC driver manager (v. 3.0.6) that is built
as a single threaded executable under <cache-install-dir>/bin/[Link] or [Link]. This
built to run as installed.
If you want to build your own iODBC driver manager to operate under custom conditions,
the process is described below. For all builds, the iODBC executable and include files should
be in the <cache-install-dir>/dev/odbc/redist/iodbc/ directory. You need to set
LD_LIBRARY_PATH (LIBPATH on AIX) and the include path in order to use these directories
to build your applications.

Note: The following references are provided as an example. Please check the documentation
provided by iODBC for the correct version number as well as complete installation
instructions and release notes.

The process of building the iODBC driver manager is:

1. Download the iODBC Driver Manager.
2. Log in as user root and uncompress/unzip and un-tar the kit in /tmp or other temporary
area:
# gunzip /tmp/[Link]
# tar xvf [Link]

3. In the installation directory, configure the iODBC source code:

# cd /tmp/libiodbc-3.0.6
# ./configure --prefix=/usr/local --with-iodbc-inidir=/etc

22 Using Caché SQL

 ODBC Configuration

This configures iODBC for the default installation directory of /usr/local and the default
initialization directory of /etc.
4. Compile the package with the make command:
# make

5. Optionally, run any self-tests that come with iODBC as follows:

# make check

6. Install the test tools, samples, shared objects, data files, and documentation:
# make install

[Link] Configuring PHP with Apache and the Driver Manager for [Link]
You can use the ODBC functionality of Caché in conjunction with PHP (PHP: Hypertext
Processor, which is a recursive acronym). PHP is a scripting language that allows developers
to create dynamically generated pages. The process is as follows:
1. Get or have root privileges on the machine where you are performing the installation.
2. Install either the iODBC or unixODBC driver manager.
• For iODBC, download the kit, then perform a standard installation and configuration;
then configure the driver manager for use with PHP.
Note that LD_LIBRARY_PATH (LIBPATH on AIX) in the iODBC PHP example
does not get set, due to security protections in the default PHP [Link],
copy [Link] to /usr/lib and run ldconfig to register it without using
LD_LIBRARY_PATH.
• For unixODBC, download the source kit, install it, and configure it for use with PHP;
the iODBC instructions may be helpful even for use with unixODBC.

3. Download the PHP source kit and un-tar it.

4. Download the Apache HTTP server source kit and un-tar it.
5. Build PHP and install it.
6. Build the Apache HTTP server, install it, and start it.
7. Test PHP and the Web server using [Link] in the Apache root directory, as specified in
the Apache configuration file (often [Link]). The URL for this is
[Link]

Using Caché SQL 23

Configuration

8. Copy the Caché-specific initialization file,

[Link] to /etc/[Link] as this location
functions better with the Apache Web server if the $HOME environment variable is not
defined.
9. Configure and test [Link] driver with the steps for the basic testing.
10. Copy the [Link] file from the Caché ODBC kit toApache root directory (that is, the
directory where [Link] is located), and tailor it to your machine for the location of Caché.
You can then run the [Link] program, which uses the Caché “SAMPLES” names-
pace, by pointing your browser to [Link]

2.3 JDBC Client Configuration

The configuration of the JDBC client consists of the following steps:
1. Installing the Caché JDBC driver (this is automatically installed by Caché within the
<cachesys>/dev/java/lib/[Link] file).

2. Setting up your CLASSPATH environment variable to refer to this .jar file.

For more information refer to Installation and Configurationin Using Java with Caché.

24 Using Caché SQL

3
Caché SQL Basics
In this document, we assume that you have some prior knowledge of SQL. This document
is not designed to serve as a complete SQL reference or as an SQL tutorial. If you wish to
learn more about the basics of SQL there are dozens of good books on this topic available
online or at any technical bookstore.
This document will try to provide an overview of Caché SQL's features, especially those that
are not covered by the SQL standard or are related to Caché's Unified DataArchitecture.

3.1 Tables
Within Caché SQL, data is presented within tables. Each table is defined to contain a number
of columns. A table may contain zero or more rows of data values.
There are two basic types of table: base tables (which contain data and are usually referred
to simply as tables) and views (which present a logical view based on one or more tables).
To find out more on how to define tables refer toDefiningTables.
To find out more on how to define views refer to DefiningViews.
In order to make queries against tables more efficient, you can define
indices on tables.
In order to enforce referential integrity you can defineforeign keys and triggers on tables.

Using Caché SQL 25

Caché SQL Basics

3.2 Queries
Within Caché SQL you view and modify data within tables by means of queries. Roughly
speaking, queries come in two flavors: those that retrieve data (SELECT statements), and
those that modify data (INSERT, UPDATE, and DELETE statements).
You can use SQL queries in a number of ways:
• Using Embedded SQL within Caché ObjectScript.
• Using Dynamic SQL within Caché ObjectScript or Basic.
• Using the ODBC or JDBC interfaces from a variety of other environments.

Queries are part of Caché objects or Caché ObjectScript routines.

3.3 Privileges
Caché SQL provides a way to limit access to tables, views, etc. via privileges. You can define
a set of users and roles and grant various privileges (read, write, etc.) to them.
To find out more about roles and privileges refer to Users, Roles, and Privileges.

3.4 Identifiers
An SQL identifier is the name of a SQL entity such as a table, column, stored procedure, or
trigger.
The first character of an SQL identifier must be
gin with one of the following:
• A letter. On a UNICODE system, this may be a UNICODE letter. (The Caché ObjectScript
$ZNAME function should return TRUE for the character).
• An underscore (_).
• A percent sign (%).

The remaining characters of an identifier may consist of zero or more:

• Letters (including UNICODE characters).

26 Using Caché SQL

 Identifiers

• Numbers.
• Underscores (_).
• At signs (@).
• Pound signs (#).
• Dollar signs ($).

A regular identifier may not be an SQLreserved word. Any SQL reserved word used as an
identifier must be represented as a delimited identifier within an SQL statement.
The Caché ObjectScript Binary Modulo operator (#) can only be used with spaces between
the operands and the operator:
NumericField # 10

is valid, while:
NumericField#10

will be treated as a single regular identifier.

Any identifiers not following the rules above must be represented as a delimited identifier
within an SQL statement.
By default, the name of an SQL entity is the same as its corresponding entity within a class
definition. If you want the SQL name to be different, you must specify an SQL alias within
your class definition. For example:
Property Insert As %String [SqlFieldName = "X_Insert"];

3.4.1 UNICODE Identifiers

If you have installed the UNICODE version of Caché, you can use any valid UNICODE
(16bit) character within an SQL identifier as described in the preceding section.

3.4.2 Delimited Identifiers

The SQL Standard supports delimited identifiers—identifiers that are enclosed within double
quotation marks. Delimited identifiers can contain any characters (including white space,
punctuations marks, and reserved words).
For example, the following query makes use of delimited identifiers for both column and
table names:

Using Caché SQL 27

Caché SQL Basics
SELECT "My Field" FROM "My Table" WHERE "My Field" LIKE 'A%'

Note that the string literal, “A%” , is enclosed within single quotes.
Class and property names cannot use delimited identifiers. If you wish to use delimited
identifiers within SQL tables that are defined using class definitions, you must vide
pro an
SQL alias within the corresponding class definitions:
Property MyField As %String [SqlFieldName = "My Field"];

Within Caché SQL, support for delimited identifiers is optional. By default, delimited identi-
fiers support is disabled.
When delimited identifier support is disabled, characters within double quotes are treated as
string literals.
You can enable or disable delimited identifier support using the System Management Portal:
1. Start the System Management Portal (available from the Caché Cube menu).
2. Select System Configuration and then Advanced Settings.
3. From the pull-down Category list, select SQL.
4. Find the Support Delimited Identifiers option and change it to the desired setting.

3.4.3 SQL Reserved Words

SQL includes a long list of reserved words that cannot be used as identifiers.
For a list of SQL reserved words refer to the Reserved Words page within the Caché SQL
Reference.

3.5 Collation
Collation specifies how values are ordered within SQL. Collation is specified by means of a
collation function: a built-in function that transforms a value into a collated value. For
example, the SQLUPPER collation (which provides for case-insensitive collation) transforms
strings into Upper case (NOTE: the SQLUPPER transform is NOT the same as the result of
the SQL function UPPER).
Caché provides the following collation functions:

28 Using Caché SQL

 Collation

Primary Collation Functions

Collation Function Description

EXACT Orders values exactly as is including trailing space and case
differences. This should not be used for string data if any of
the string values begin with a number (for example '123' or
'123abc' or '-123abc').
SQLSTRING Orders values in case-sensitive string order. Translates the
value into a string, strips trailing whitespace (spaces, tabs,
and so on), and adds one leading blank space to the
beginning of the string. It collates any value containing only
whitespace (spaces, tabs, and so on) as the SQL empty string.
SQLSTRING(n) Same as SQLSTRING, but truncates the string to the first n
characters, where n is a positive integer. This collation is used
to improve indexing and sorting on long strings.
SQLUPPER Orders values in case-insensitive string order. Translates the
value into a string, strips trailing whitespace (spaces, tabs,
and so on), and adds one leading blank space to the
beginning of the string. It collates any value containing only
whitespace (spaces, tabs, and so on) as the SQL empty string.
SQLUPPER(n) Same as SQLUPPER, but truncates the string to the first n
characters, where n is a positive integer. This collation is used
to improve indexing and sorting on long strings.

SQL uses the collated value of a field for the following operations:
• Whenever the field is used within anORDER BY clause.
• Whenever an SQL comparison operator (such as =, >, <, or BETWEEN) is used with the
field.
• Whenever a %STARTSWITH operation is used with the field.
• Whenever MIN, MAX, and DISTINCT and GROUP BY operations are used with the
field. Note that UNION involves a DISTINCT operation.

Note: The LIKE comparison operator does not use collation.

The collation of a field is defined as follo

ws:

Using Caché SQL 29

Caché SQL Basics

1. The data type used by a field specifies a def

ault collation. The default for string data
types (such as %String) is SQLUPPER. The collation functions currently provided are
not useful for non-string data types.
2. A specific property (field) definition canverride
o its default collation by providing a
value for the COLLATION parameter. For example, the following class contains a Name
property that uses EXACT collation:
Class [Link] Extends %Persistent [ClassType = persistent]
{
/// Patient name
Property Name As %String(COLLATION = "Exact");

/// ...
}

In general, you do not need to worry about collations; the defaults provided by the system
are correct for most applications.

Note: Changing the collation for a property in a class that already has data stored for it will
make any indices on the property invalid. If you do need to change the collation on
a property with stored data you will have to rebuild any indices based on this property.

3.5.1 Collation of Indexed Values

By default, an index on a given property (or properties) uses the collated form of the property
data. For example, suppose you have defined a propertyName of type %String:
Class [Link] Extends %Persistent [ClassType = persistent]
{
Index NameIDX On Name;
Property Name As %String;
}

The collation for Name is SQLUPPER (the default for %String). If the Person table contains
the following data:

ID Name
1 Jones
2 JOHNSON
3 Smith
4 jones
5 SMITH

30 Using Caché SQL

 Collation

then an index on Name will contain the following entries:

Name ID(s)
JOHNSON 2
JONES 1, 4
SMITH 3, 5

The SQL Engine can use this index directly for ORDER BY or comparison operations using
the Name field.
You can override the default collation used for an index by adding an As clause to the index
definition within Studio:
Class [Link] Extends %Persistent [ClassType = persistent]
{
Index NameIDX On Name As Exact;
Property Name As %String;
}

In this case the NameIDX index will now store values in EXACT (uncollated) form. Using
the data from the above example:

Name ID(s)
JOHNSON 2
Jones 1
jones 4
SMITH 5
Smith 3

In this case, the SQL Engine can take advantage of this index for any queries requiring EXACT
collation.
In general, you should not have to change the collations of indices. If you want to use a dif-
ferent collation function, it is better to define it at the property level and let any indices on
the property pick up the correct collation.
If your index is defined to use multiple properties, you can specify the collation of each
individually:
Index MyIDX On (Name As Exact, Code As SQLString);

Using Caché SQL 31

Caché SQL Basics

3.5.2 Legacy Collation Functions

Caché includes a number of older collation functions for compatibility with older products
including %ALPHAUP, UPPER, and %STRING.

32 Using Caché SQL

4
Defining Tables
There are a two fundamental ways to define tables within Caché SQL:
1. Creating persistent class definitions.
2. Using SQL DDL commands.

Both of these are described below.

4.1 Defining Tables Using Classes

The primary way to define tables within Caché is to useCaché Studio to create persistent
class [Link] these classes are saved and compiled within the Caché database, they
automatically create a relational table that corresponds to the class definition: each class
represents a table; each property represents a column, etc.
For example, the following defines aPerson class within Studio:
Class [Link] Extends %Persistent [ ClassType = persistent]
{
Property Name As %String(MAXLEN=50) [Required];
Property SSN As %String(MAXLEN=15) [Required];
Property DateOfBirth As %Date;
Property Sex As %String(MAXLEN=1);
}

When compiled, this creates a relational table, Person (within the “MyApp” schema). This
table could have been defined using DDL (which would still result in a class and table being
created):

Using Caché SQL 33

Defining Tables
CREATE TABLE [Link] (
Name VARCHAR(50) not null,
SSN VARCHAR(15) not null,
DateOfBirth date,
Sex varchar(1)
)

For details on how the object view of the database corresponds to the relational view, refer
to the Objects and SQL chapter of Using Caché Objects.

4.2 Defining Tables Using DDL

You can define tables in Caché SQL using standard DDL [Link] statements
include:
• ALTER TABLE
• ALTER USER
• ALTER VIEW
• CREATE INDEX
• CREATE ROLE
• CREATE TABLE
• CREATE USER
• CREATE VIEW
• DROP INDEX
• DROP ROLE
• DROP TABLE
• DROP USER
• DROP VIEW

These are described in detail within the Caché SQL Reference.

You can execute DDL statements in a variety of ways, including:
• Using ODBC calls.
• Using JDBC calls.
• Using DDL Script Files (see below).

34 Using Caché SQL

 Defining Tables Using DDL

• Using embedded SQL statements within methods or routines to execute DDL statements
(see below).

4.2.1 Using DDL in Embedded SQL

Within a Caché ObjectScript method or routine you can use embedded SQL to invoke DDL
commands.
For example, the following method creates a [Link] table:
ClassMethod CreateTable() As %Integer
{
&sql(CREATE TABLE [Link] (
EMPNUM INT NOT NULL,
NAMELAST CHAR (30) NOT NULL,
NAMEFIRST CHAR (30) NOT NULL,
STARTDATE TIMESTAMP,
SALARY MONEY,
ACCRUEDVACATION INT,
ACCRUEDSICKLEAVE INT,
CONSTRAINT EMPLOYEEPK PRIMARY KEY (EMPNUM)))

Write "SQL: ",SQLCODE,!

QUIT SQLCODE
}

When this method is invoked it will attempt to create a [Link] table (as well as
the corresponding [Link] class). If successful, the variable SQLCODE will be set
to 0. If unsuccessful, SQLCODE will contain an SQL Error Code indicating the reason for
the failure.
The most common reasons that a DDL statement such as this one will fail are:
• SQL ERROR -99 (Privilege Violation): This error indicates that you do not have permis-
sion to execute the desired DDL command. Typically this is because an application has
not established who the current user is. You can do this programmatically using the Login
method of a $[Link] ([Link] under Basic) class:
Do $[Link](username,password)

• SQL ERROR –201 (Table or View name not unique): This error indicates that you are
attempting to create a new table using the name of a table that already exists.

4.2.2 Executing DDL Using Dynamic SQL

Within Caché ObjectScript or Basic, you can use the %[Link] object to prepare
and execute DDL statements using dynamic SQL.

Using Caché SQL 35

Defining Tables

For example, the following Basic method creates a [Link] table:

ClassMethod Create(user As %String, pwd as %String) As %Status [language=Basic]
{
[Link](user,pwd)
rs = New %[Link]()

' Create a string containing a DDL command

ddl = "CREATE TABLE [Link] "
ddl = ddl & "(NAME VARCHAR (30) NOT NULL, SSN VARCHAR (15) NOT NULL)"

' Prepare and Execute the DDL command

[Link](ddl)
ret = [Link]()
Return ret
}

As with the embedded SQL example, this method will fail if there is no current user logged
in or if the table [Link] already exists.

4.2.3 Running DDL Scripts from the Command Line

If you are migrating tables from a relational database to Caché, you may have one or DDL
scripts within text files. Caché includes a number of command line utilities to help load such
tables into Caché. Refer to the %[Link] class for details (particularly the Oracle,
Sybase, and MSQLServer methods).
For example, to load an Oracle DDL file from the Caché command line:
1. Start a Caché Terminal session using the Terminal command in the “Caché Cube” menu.
2. Switch to the namespace in which you wish to load the table definitions:
ZN "MYNAMESPACE"

3. Invoke the Oracle DDL Command-line Utility:

Do $[Link]()

and follow the directions.

36 Using Caché SQL

5
Views
Caché SQL supports the ability to define and xeecute queries on Views.
You can defineViews in several ways:
• Within the Caché System Management Portal.
• Using the DDL CREATE VIEW command (either in a DDL script or via JDBC or ODBC).

For example, the following DDL statement creates a view based on the Employees table:
CREATE VIEW EmployeePay AS
SELECT Name, (Salary + Commission) AS TotalPay
FROM Employees

All views are either updatable or read-only as described within the following sections.

5.1 Updatable Views

An updateable view is one on which you can perform INSERT, UPDATE, and DELETE
operations. A view is considered updatable only if the following conditions are true:
• The FROM clause of the view's query contains only one table reference. This table refer-
ence must identify either an updatable base table or an updatable view.
• The value expressions within the SELECT list of the view's query must all be column
references.
• The view's query must not specify GROUP BY, HAVING, or SELECT DISTINCT.
• The view is not a class query projected as a view.

Using Caché SQL 37

Views

• The view's class does not contain the class parameter READONLY=1 (true if the view
definition contains aWITH READ ONLY clause).

5.1.1 The WITH CHECK Option

In order to prevent an INSERT or UPDATE operation on a view which would result in a row
in the underlying base table which is not part of the derived view table, Caché SQL supports
the WITH CHECK OPTION clause within a View [Link] WITH CHECK OPTION
can only be used with updatable views.
The WITH CHECK OPTION clause specifies that any INSERT or UPDATE operations on
an updatable view must validate the resulting row against the WHERE clause of the view
definition to make sure the inserted or modified row will be part of the derived view table.
For example, the following DDL statement defines an updatableGoodStudent view containing
all Students with a high GPA (grade point average):
CREATE VIEW GoodStudent AS
SELECT Name, GPA
FROM Student
WHERE GPA > 3.0
WITH CHECK OPTION

Because the view contains a WITH CHECK OPTION, any attempt to INSERT or UPDATE
a row in the GoodStudent view with a GPA value of 3.0 or less will fail (such a row would
not represent a “good student” ).
There are two flavors of WITH CHECK OPTION:
• WITH LOCAL CHECK OPTION means that only the WHERE clause of the view
specified in the INSER
T or UPDATE statement is checked.
• WITH CASCADED CHECK OPTION (and WITH CASCADE CHECK OPTION)
means that the WHERE clause of the view specified in the INSER T or UPDATE statement
as well as ALL views on which that view is based are checked, regardless of the appear-
ance or absence of other WITH LOCAL CHECK OPTION clauses in those view defini-
tions.

The default is CASCADED if just WITH CHECK OPTION is specified.

During an UPDATE or INSERT, the WITH CHECK OPTION conditions are checked after
all default values and triggered computed fields have been calculated for the underlying table's
fields and prior to the regular table's validation (required fields, data type validation, con-
straints, etc.).

38 Using Caché SQL

 Read-Only Views

After the WITH CHECK OPTION validation passes, the INSERT or UPDATE operation
continues as if the INSERT or UPDATE was performed on the base table itself. All constraints
are checked, triggers pulled, and so on.
If the %NOCHECK option is specified on theINSERT or UPDATE statement, the WITH
CHECK OPTION validation is not checked.
The are two SQLCODE values related to the WITH CHECK OPTION validation (the
INSERT/UPDATE would have resulted in a row not existing in the derived view table):
• SQLCODE -136—View's WITH CHECK OPTION validation failed in INSERT.
• SQLCODE -137—View's WITH CHECK OPTION validation failed in UPDATE.

5.2 Read-Only Views

An read-only view is one on which you cannot perform INSERT, UPDATE, and DELETE
operations. Any view that does not meet the criteria for updatable views is a read-only view.
A view definition may specify aWITH READ ONLY clause to force it to be a read-only
view.
If you attempt to compile/prepare an INSERT, UPDATE, or DELETE statement against a
read-only view an SQLCODE error code of -35 will be returned.

Using Caché SQL 39

6
Indices
Caché SQL supports the ability to define indices on column values within tables. These
indices are automatically maintained whenever object- or SQL-based INSERT, UPDATE,
or DELETE operations are carried out against the database. The SQL Query Processor makes
use of available indices when preparing and executing SQL queries.
Caché SQL supports a variety of index types and options for specific [Link] are described
within the chapter.
Every index has a unique name (unique within its class or table). This name is used for
database administrative purposes (reporting, index building, dropping indices, etc.).

6.1 Defining Indices

There are several ways to define [Link] are described below:

6.1.1 Defining Indices using a Class Definition

Within Caché Studio, you can add index definitions to a class definition using either the Ne
w
Index Wizard or by editing the text of the class definition:
Class [Link] Extends %Persistent [ ClassType = persistent ]
{
Index NameIDX On Name;
Index GPAIDX On GPA;

Property Name As %String;

Property GPA As %Float;
}

Using Caché SQL 41

Indices

Note that if you use Studio to add (or remove) an index definition for a class that has data
stored within the database, you must manually populate the index by using the Build Index
mechanism described below.

6.1.2 Defining Indices using DDL

If you are using DDL statements to define tables, you can also use the following DDL com-
mands to create and remove indices:
• CREATE INDEX
• DROP INDEX

The DDL index commands do the following:

1. They update the corresponding class and table definitions on which an index is being
added or removed. The modified class definition is recompiled.
2. They add or remove index data in the database as needed: The CREATE INDEX command
will populate the index using the data currently stored within the database. Similarly, the
DROP INDEX command will delete the index data (that is, the actual index) from the
database.

6.2 Standard Indices

A standard index (as opposed to a bitmap index) is simply a persistent array that associates
the indexed value(s) with the RowId(s) of the row(s) that contains the value(s). Any index
not explicitly defined as abitmap index is a standard index.

6.2.1 Indices on Multiple Properties

You can define indices on combinations of two or more properties (fields).Within a class
definition you do this by specifying a list of properties within the On clause of the index
definition:
Class [Link] Extends %Persistent [ ClassType = persistent ]
{
Index MainIDX On (State,Salary);
Property Name As %String;
Property Salary As %Integer;
Property State As %String(MAXLEN=2);
}

42 Using Caché SQL

 Standard Indices

An index on multiple properties may be useful if you need to perform queries that use a
combination of field value. For example:
SELECT Name,State,Salary
FROM Employee
ORDER BY State, Salary

6.2.2 Unique Indices

A unique index is simply a standard index that is used to enforce a uniqueness constraint for
a class (table).
Within a class definition, you can specify a unique index by adding the Unique keyword to
the index definition:
Class [Link] Extends %Persistent [ ClassType = persistent ]
{
Index SSNIDX On SSN [Unique];

Property Name As %String;

Property SSN As %String(MAXLEN=20);
}

You cannot define a unique, bitmap index (as this would not make sense).

6.2.3 Storing Data with Indices

You can specify that a copy of one or more data values be stored within an index using the
Index Data keyword:
Class [Link] Extends %Persistent [ ClassType = persistent ]
{
Index NameIDX On Name [Data = Name];
Property Name As %String;
Property SSN As %String(MAXLEN=20);
}

In this case, the index, “NameIDX” , is subscripted by the collated (upper case) value of the
various Name values. A copy of the actual (uncollated) value of the Name is stored within
the index. These copies are maintained when changes (INSERT, UPDATE, or DELETE) are
made to the Person table.
Maintaining a copy of data along within an index can be helpful in cases where you frequently
perform selective (selecting a few rows out of many) or ordered searches that return a few
columns out of many.
For example, consider the following query against the Person table:
SELECT Name FROM [Link] ORDER BY Name

Using Caché SQL 43

Indices

The SQL Engine could decide to satisfy this request entirely by reading from the NameIDX
and never reading the master data for the table.
You cannot store data values with a bitmap index.

6.3 Bitmap Indices

A bitmap index is a special type of index that uses a series of bit strings to represent the set
of object ID values that correspond to a given indexed value.
Within Caché, bitmap indices have the following important features:
• Bitmaps are highly compressed: bitmap indices can be significantly smaller than regular
indices. This reduces disk and cache usage considerably.
• Bitmaps operations are optimized for transaction processing: you can use bitmap indices
within tables with no performance penalty as compared with using regular indices.
• Logical operations on bitmaps (counting, AND, and OR) are optimized for high perfor-
mance.
• The SQL Engine includes a number of special optimizations that can take advantage of
bitmap indices.

Subject to the restrictions listed below, bitmap indices operate in the same manner as regular
indices. Indexed values are collated and you can index on combinations of multiple fields.

6.3.1 Bitmap Index Operation

Bitmap indices work in the following way. Suppose you have a Person table containing a
number of columns:
Person Table

44 Using Caché SQL

 Bitmap Indices

Each row in this table has a system-assigned ID number (a set of increasing integer values).
A bitmap index uses a set of bit strings (a string containing 1 and 0 values). Within a bit
string, the ordinal position of a bit corresponds to the ID (row number) of the indexed table.
For a given value, say where State is “NY” , there will be a string of bits with a 1 for every
position that corresponds to a row containing “NY” and a 0 in every other position.
For example, a bitmap index on State might look like this:
State Bitmap Index

While an index on Age might look like this:

Age Bitmap Index

In addition to using bitmap indices for standard operations, the SQL engine can use bitmap
indices to efficiently perform special set-based operations using combinations of multiple
indices. For example, to find all instances ofPerson that are 24 years old and live in New
York, the SQL Engine can simply perform the logical AND of the Age and State indices:
Using Multiple Indices

The resulting bitmap contains the set of all rows that match the search criteria. The SQL
Engine uses this to return data from these rows.
The SQL Engine can use bitmap indices for the following operations:
• ANDing of multiple conditions on a given table.
• ORing of multiple conditions on a given table.
• RANGE conditions on a given table.
• COUNT operations on a given table.

Using Caché SQL 45

Indices

6.3.2 Defining Bitmap Indices using a Class Definition

Within Caché Studio, you can add bitmap index definitions to a class definition using either
the New Index Wizard or by editing the text of the class definition in the same way that you
would create a standard index. The only difference is that you need to specify the index Type
as being “bitmap” :
Class [Link] Extends %Persistent [ ClassType = persistent ]
{
Index AgeIDX On Age [type = bitmap];

Property Name As %String;

Property Age As %Integer;
}

6.3.3 Defining Bitmap Indices using DDL

If you are using DDL statements to define tables, you can also use the following DDL com-
mands to create and remove bitmap indices:
• CREATE INDEX
• DROP INDEX

This is identical to creating regular indices, except that you must add the BITMAP keyword
to the CREATE INDEX statement:
CREATE BITMAP INDEX AgeIDX ON TABLE Person (Age)

6.3.4 Restrictions on Bitmap Indices

Bitmap indices have the following restrictions:
• You cannot define a bitmap index on a UNIQUE column.
• You cannot store data values within a bitmap index.
• You can only define bitmap indices in tables (classes) that use system-assigned, numeric
ID values. You cannot define bitmap indices for tables that use the IDKEY mechanism
to define custom ID values nor can you use bitmap indices on child tables within parent-
child relationships.

46 Using Caché SQL

 Building Indices

6.3.5 Choosing Index Type

The following is a general guideline for choosing between bitmap and regular indices.
In general, use regular indexes for indexing on all flavors of keys and references:
• Primary Key
• Foreign Key
• Unique Keys
• Relationships
• Simple object references

Otherwise, assuming that your table uses system-assigned numeric ID numbers, generally
use bit map indexes.
In general, you should favor separate indexes on each property rather than indexes on multiple
properties (because the SQL engine can efficiently combine these usingAND and OR oper-
ations). Exceptions to this: If you know that a property (or set of properties that you really
need to index together) have more than 10,000-20,000 distinct values (or value combinations),
consider regular indexes. If, however, these values are very unevenly distributed so that a
small number of values will account for a substantial fraction of rows, then a bitmap index
could be much better. In general the goal is to reduce the overall size required by the index.

6.4 Building Indices

Caché automatically generates methods (provided by the %Persistent class) that will build
(provide values for) or purge (remove values for) every index defined for a class (table).You
can use these methods directly to programmatically build indices or interactively via the
Caché System Management Portal.
Building an index does the following:
1. Removes the current contents of the index.
2. Scans (reads every row) of the main table and adds index entries for each row in the table.
If possible, the special $SortBegin and $SortEnd functions are used to make sure that
building of large indices is efficient.

Using Caché SQL 47

Indices

Note that index building methods are only provided for classes (tables) that use Caché default
storage structure. Classes mapped to legacy storage structures do not support index building
as it assumed that the legacy application will manage the creation of indices.

6.4.1 Building Indices with the System Management Portal

You can build indices for a table within System Management Portal by doing the following:
1. Choose SQL from the Data Management column of the Portal home page. This displays
the SQL page.
2. From the SQL page, choose the namespace that contains the table for which you want to
build an index.
3. Next, select Browse SQL Schemas from the SQL Operations choice. This displays the
Schemas page, which holds a list of selectable schemas.

4. Select either the schema containing the table for which you wish to build an index or its
Tables link. Either choice displays a list of tables in the schema.

5. From the list of tables, click on the name of the table for which you wish to build an
index. This displays the Table Properties page for the table ([Home] > [SQL] > [Schemas]
> [Tables] > [Table]).

6. On this page, click the Rebuild Indices option; after receiving confirmation, Caché rebuilds
the indices for the selected table.

6.4.2 Building Indices Programmatically

To build an index (or indices) programmatically, you can invoke the %BuildIndices method
provided by the %Persistent class. Invoking this method with no arguments will build (provide
values for) all the indices defined for a given class (table):
Do ##class([Link]).%BuildIndices()

You can also selectively build indices by passing this method a $List of index names:
Do ##class([Link]).%BuildIndices($ListBuild("NameIDX","AgeIDX"))

The %BuildIndices method does the following:

1. Invokes the $SortBegin function on any (non-bitmap) indices that will be rebuilt (this
initiates a high performance sorting operation for these indices).

48 Using Caché SQL

 Building Indices

2. Loops over the main data for the class (table), gathers the values used by indices, and
adds these values to the indices (with appropriate collation transformation).
3. Invokes the $SortEnd function to finish the process of sorting the indices.

If your indices already have values, you must call the %PurgeIndices method before the
%BuildIndices method in order to remove the old values.

Using Caché SQL 49

7
Querying the Database
You can use SQL to query the contents of a database. This is done using the SELECT state-
ment:
SELECT Name FROM [Link] ORDER BY Name

You can use the SELECT statement to:

• Select one or more columns of data from a given table or view.
• Sort data by different values.
• Restrict data using various criteria
• Join data from two or more tables.

7.1 The SELECT Statement

The SELECT statement selects one or more rows of data from one or more tables or views.

7.1.1 The SELECT List

The SELECT list specifies one or more columns or xe pressions whose value will be included
within a query's result set:
SELECT Name FROM [Link]

Multiple columns within the SELECT list are separated by commas:

Using Caché SQL 51

Querying the Database
SELECT Name,SSN FROM [Link]

You can provide columns in a SELECT list with an alias:

SELECT Name As TheName,SSN As TheSSN FROM [Link]

A SELECT list can include expressions as well as literal values:

SELECT 'Doctor ' || Name As TheName, Age * 2 As DoubleAge
FROM [Link]

7.1.2 The FROM Clause

The FROM clause specifies one or more tables which will provide the data for the query.

7.1.3 The WHERE Clause

The WHERE clause contains one or more conditions that are used to restrict the values
returned by a query:
SELECT Name FROM [Link]
WHERE Name %STARTSWITH 'A'

The WHERE clause can contain multiple conditions joined by AND or OR:
SELECT Name,Age FROM [Link]
WHERE (Name %STARTSWITH 'A') OR (Age > 50)

It can also contain any of a number of predicates, which are logical tests to qualify or dis-
qualify specific rows from the query selection. The supported predicates are: logical compar-
isons, BETWEEN, EXISTS, IN, LIKE, and NULL.

Note: In Caché SQL, expressions are evaluated in strict left-to-right order; there is no
operator precedence. To avoid problems with complex expressions (and to be prepared
in case a future release changes this behavior to be SQL 92 compliant) you should
use parentheses to explicitly specify the precedence of operators.

7.1.4 The ORDER BY Clause

The ORDER BY clause specifies one or more columns that should be used to sort the values
returned by a query:
SELECT Name FROM [Link] ORDER BY Name

You can also specify whether ordering should be ascending (the default) or descending:

52 Using Caché SQL

 JOIN
SELECT Name FROM [Link] ORDER BY Name DESC

7.2 JOIN
A JOIN provides a way to link data in one table with data in another table.
Within SQL, a JOIN is an operation that combines two tables to produce a third, subject to
a restrictive condition. Every row of the resulting table must satisfy the restrictive condition.

7.2.1 OUTER JOIN Limitations

Caché SQL supports both LEFT and RIGHT outer joins. It does not support FULL outer join.
There is partial support for NATURAL and USING joins.
There are two types of limitations within OUTER JOIN support:
• Restrictions on the right operand of a LEFT JOIN (equivalently, on the left operand of
RIGHT JOIN).
There are no operand restrictions on the left operand of a LEFT JOIN or the right operand
of a RIGHT JOIN.
• Restrictions on what can go in the ON clause specifying the join.

These restrictions are described in the following sections.

[Link] LEFT JOIN Operand Restrictions

For a LEFT JOIN, the right operand cannot be a join expression. (In early versions, it also
could not be a view or a table subquery.)
For example, the following is not supported:
T1 LEFT JOIN T2 JOIN T3 ON (...1) ON (...2)

because the right operand of the LEFT JOIN is:

T2 JOIN T3 ON (...1)

which is a join expression. It should usually be satisfactory to re-write such a case as one of
the following (generally, only one of these would make sense in a particular case):
T1 LEFT JOIN T2 ON (...2) LEFT JOIN T3 ON (...1)
T1 LEFT JOIN T3 ON (...2) LEFT JOIN T2 ON (...1)

Using Caché SQL 53

Querying the Database

Alternatively, you could re-write it as:

T1 LEFT JOIN (SELECT ... FROM T2 JOIN T3 ON (...1)) ON (...2)

However, this form will usually be much slower than the prior approach.

[Link] RIGHT JOIN Operand Restrictions

For a RIGHT JOIN, the left operand cannot be a join expression, a view, or a table subquery
(Note that RIGHT JOIN has more restrictions than LEFT JOIN).
Examples that are not supported:
T1 RIGHT JOIN T2 ON (...1) RIGHT JOIN T3 ON (...2)

Note that in this case the left operand of RIGHT JOIN T3 ... is:
T1 RIGHT JOIN T2 ON (...1).

Other examples:
view1 RIGHT JOIN T2 ON (...)
(SELECT ...) RIGHT JOIN T2 ON (...)

The simplest approach to these is to rewrite these as LEFT JOINS and use the techniques
above for LEFT JOIN operands.

[Link] ON Clause Restriction for LEFT JOIN

The conditions in the ON clause can be combined only with using AND. OR and NOT cannot
appear in the ON clause.
Let L be the left operand of the join and R be the right operand. Then all conditions in the
ON clause must one of the following:
• A column from L = a column from R.
• A column from R = literal value or parameter.

The order of the equality operands doesn't matter:

parameter=[Link]

is just as good as:

[Link]=parameter.

At least one of the conditions must be a comparison of columns.

54 Using Caché SQL

 JOIN

The same column from R cannot be used in more than one comparison (there is no such
restriction for columns from L).

Note: Views in L are to be treated as expanded in applying these rules (but not R as a view).
Thus, if a view column in L is used in the ON clause, it must expand to a base table
column, not to an expression of any kind. The same goes for table subqueries in L.
If the view or table subquery uses UNION, GROUP BY, or DISTINCT, then there
is no limitation on SELECTing expressions. In many cases, then, adding DISTINCT
to the SELECT clause of the view or table subquery can work around this limitation.

[Link] ON Clause Restriction for RIGHT JOIN

The ON clause restrictions for RIGHT JOIN are exactly the same as for LEFT JOIN, except
that Land R are interchanged.

Using Caché SQL 55

8
Modifying the Database
You can use SQL queries to modify the contents of a database. If there are indices defined
on the table, SQL will automatically update them to reflect the changes. If there are any data
or referential integrity constraints defined, SQL will automatically enforce them.

8.1 INSERT Statements

The INSERT statement inserts a new row into an SQL table:
INSERT INTO [Link]
(Name,SKU,Price)
VALUES ('Ginsu','DPV1486',22.95)

8.2 UPDATE Statements

The UPDATE statement modifies values in one or more existing rows within an SQL table:
UPDATE [Link]
SET HairColor = 'Red'
WHERE %ID = 435

8.3 DELETE Statements

The DELETE statement removes one or more existing rows into an SQL table:

Using Caché SQL 57

Modifying the Database
DELETE FROM [Link]
WHERE HairColor = 'Aqua'

58 Using Caché SQL

9
Embedded SQL
Caché SQL supports the ability to embed SQL statements within Caché ObjectScript code.
These embedded SQL statements are converted to optimized, executable code at compilation
time.

Note: Embedded SQL is not supported within Basic; To use SQL within Basic code, you
can do one of the following: use Dynamic SQL or use embedded SQL with Caché
ObjectScript methods and call these methods from Basic.

9.1 The Macro Preprocessor

You can used embedded SQL within methods (provided that they are defined to use Caché
ObjectScript) or within Caché ObjectScript .MAC routines. A .MAC routine (or a method
using Caché ObjectScript) is processed by the Caché Macro Preprocessor and converted to
.INT (intermediate) code which is subsequently compiled to executable code. The Macro
Preprocessor replaces all embedded SQL statements it finds with the code that actually xeecutes
the SQL statement.

9.2 Syntax and Use

The syntax and use of embedded SQL is described below.

Using Caché SQL 59

Embedded SQL

9.2.1 The &sql Directive

Embedded SQL statements are set off from the rest of the code by the &sql() directive. For
example:
Method CountStudents() As %Integer
{
&sql(SELECT COUNT(*) INTO :count
FROM [Link])

Quit count
}

The &sql directive is case-insensitive; you can use &sql, &SQL, &Sql, etc. The &sql directive
must be immediately followed by an open parenthesis.
The body of an &sql directive should contain a valid SQL statement, enclosed in parentheses.
You can format your SQL statements in any way you like: white space and new lines are
ignored by SQL. Caché Studio recognizes the &sql directive and uses a SQL-aware colorizer
to syntax color SQL statements.
When the Macro Preprocessor encounters an &sql directive, it hands the enclosed SQL
statement to the SQL Query Processor. The Query Processor returns the code needed (in
Caché ObjectScript INT format) to execute the query). The Macro Preprocessor then replaces
the &sql directive with this code (or a call to a label containing the code). From within Caché
Studio, you can view the generated code, if you like, by looking at the INT code generated
for a class or routine (using the View Other command in the View menu).
If an &sql directive contains an invalid SQL statement (it may have syntax errors or refer to
tables or columns that do not exist at compile time), then the Macro Preprocessor will generate
a compilation error.

9.2.2 Schema Name Resolution

Tables names within embedded SQL statement may include a schema name or not. If not,
the table name is resolved (the correct schema name is found) using the Import statements
in effect for the class or routine that contains the SQL statement.
See the Packages chapter in the Caché Objects Programming Guide for more details.

9.2.3 Literal Values

Embedded SQL queries may contain literal values (strings, numbers, or dates). Strings should
be enclosed within single (') quotes (double quotes indicate delimited identifiers within SQL):

60 Using Caché SQL

 Syntax and Use
&sql(SELECT 'Dr.' || Name INTO :name
FROM [Link]
WHERE State = 'NY')

Numeric values can be used directly:

&sql(SELECT Name INTO :name
FROM [Link]
WHERE Age > 50)

9.2.4 Host Variables

Embedded SQL statements can include host variables in any place that a literal value can be
used or within an INTO clause. A host variable is the name of a local variable, preceded by
a “:” character, that provides (or receives) any values used by (or returned by) an SQL
statement.
Host variable values have the following restrictions:
• Input host variables are never valid after embedded SQL.
• Output host variables are only reliably valid after embedded SQL when SQLCODE = 0.

For further details, refer to Host variable in the Caché SQL Reference.
In the following example, an embedded SQL statement finds the name of aPerson and returns
it into the local variable name in the INTO clause:
&sql(SELECT Name INTO :name
FROM [Link]
WHERE %ID = 1)

You can use host variables within the WHERE clause as well:
Set minval = 10000
Set maxval = 50000
&sql(SELECT Name,Salary INTO :name, :salary
FROM [Link]
WHERE Salary > :minval AND Salary < :maxval)

You can use arrays as host variables:

&sql(SELECT Name, Title INTO :val(1), :val(2)
FROM [Link]
WHERE %ID = :emp("ID") )

In Caché 5.0 and all subsequent releases, you can also use object properties as host variables:
&sql(SELECT Name, Title INTO :[Link], :[Link]
FROM [Link]
WHERE %ID = :id )

Using Caché SQL 61

Embedded SQL

In this case, obj must be a valid reference to an object that has mutable (that is, they can be
modified) propertiesName and Title.

9.2.5 Data Format

Within embedded SQL, data values are in “logical mode” ; that is, values are in the native
format used by the SQL Query Processor. For string, integers, and other data types that do
not define aLogicalToODBC or LogicalToDisplay conversion, this has no effect. The main
case where data format matters is with date data types. The %Date and %Time data types
provided by Caché use Caché's internal date representation ($HOROLOG format) as their
logical format. The %TimeStamp data type uses ODBC date-time format (YYYY-MM-DD
HH:MM:SS) for it logical, display, and ODBC format.
For example, consider the following class definition:
Class [Link] Extends %Persistent [ClassType = persistent]
{
/// Patient name
Property Name As %String(MAXLEN = 50);
/// Date of birth
Property DOB As %String;

/// Date and time of last visit

Property LastVisit As %TimeStamp;
}

A simple embedded SQL query against this table will return values in logical mode. For
example, this query:
&sql(SELECT Name, DOB, LastVisit
INTO :name, :dob, :visit
FROM Patient
WHERE %ID = :id)

will return logical value for the three properties into the host variables name, dob, and visit:

Host Variable Value

name "Weiss,Blanche"
dob 44051
visit "2001-03-15 11:11:00"

Note that dob is in $Horolog format. You can convert this to a display format using the
$ZDateTime function:
Set dob = 44051
Write $ZDT(dob,3),!

62 Using Caché SQL

 Syntax and Use

The same consideration as true within a WHERE clause. For example, to find all Patients
with a given birthday, you must use a logical value in the WHERE clause:
&sql(SELECT Name INTO :name
FROM Patient
WHERE DOB = 43023)

or, alternatively, using a host variable:

Set dob = $ZDH("01/02/1999",1)

&sql(SELECT Name INTO :name

FROM Patient
WHERE DOB = :dob)

In this case, we use the $ZDATEH function to convert a display format date into its logical,
$HOROLOG equivalent.

9.2.6 Simple SQL Statements

You can use a simple SQL statement (a single embedded SQL statement) for a variety of
operations including:
• INSERT, UPDATE, and DELETE statements.
• DDL statements.
• GRANT and REVOKE statements.
• SELECT statements that return only a single row (or if you are only interested in the first
returned row).

Simple SQL statements are also referred to as non-cursor–based SQL statements.

For example, the following statement finds the name of the (one and only)Patient with ID
of 43:
&sql(SELECT Name INTO :name
FROM Patient
WHERE %ID = 43)

If you use a simple statement for a query that can return multiple rows, then only the first
row is returned:
&sql(SELECT Name INTO :name
FROM Patient
WHERE Age = 43)

Depending on the query, there is no guarantee which row will actually be returned first.

Using Caché SQL 63

Embedded SQL

9.2.7 SQL Cursors

If you wish to use embedded SQL to execute a query that returns multiple rows, then you
must use an SQL cursor. An SQL Cursor is DECLAREd and given a name. You then use
this name to OPEN, FETCH data from, and CLOSE the cursor.
A cursor name must be unique within a class or routine. The DECLARE statement must
occur within a routine before any statements that use the cursor.
The following example, uses a cursor to execute a query and display the results to the principal
device:
&sql(DECLARE C1 CURSOR FOR
SELECT %ID,Name
INTO :id, :name
FROM [Link]
ORDER BY Name
)
&sql(OPEN C1)
&sql(FETCH C1)

While (SQLCODE = 0) {
Write id, ": ", name,!
&sql(FETCH C1)
}

&sql(CLOSE C1)

This example does the following:

1. It declares a cursor, C1, that returns a set of Person rows ordered by Name.
2. It opens the cursor.
3. It calls FETCH on the cursor until it reaches the end of the data. After each call to FETCH,
the SQLCODE variable will be set to 0 if there is more data to fetch. After each call to
FETCH, the values returned are copied into the host variables specified by the INTO
clause of the DECLARE statement.
4. It closes the cursor.

[Link] The DECLARE Cursor Statement

The DECLARE statement specifies both the name and SQL SELECT statement that defines
the cursor. For example, the following statement declares a cursor called MyCursor:

64 Using Caché SQL

 Syntax and Use
&sql(DECLARE MyCursor CURSOR FOR
SELECT Name, DOB
FROM [Link]
WHERE Home_State = :state
ORDER BY Name
)

A DECLARE statement may include an optional INTO clause that specifies the names of
the local host variables that will receive data as the cursor is traversed. For example, we can
add an INTO clause to the previous example:
&sql(DECLARE MyCursor CURSOR FOR
SELECT Name, DOB
INTO :name, :dob
FROM [Link]
WHERE Home_State = :state
ORDER BY Name
)

The number of host variables within an INTO clause must exactly match the number of
columns within the cursor's SELECT list or you will receive a “Cardinality Mismatch” error
when the statement is compiled.
If the DECLARE statement does not include an INTO clause, then the INTO clause must
appear within the FETCH statement (q.v.).

[Link] The OPEN Cursor Statement

The OPEN statement prepares a cursor for subsequent execution:
&sql(OPEN MyCursor)

Upon a successful call to OPEN, the SQLCODE variable will be set to 0.

You cannot FETCH data from a cursor without first calling OPEN.
Depending on the actual query used for the cursor, the OPEN statement may do very little
actual work or it may perform some initialization work for the query.

[Link] The FETCH Cursor Statement

The FETCH statement fetches the data for the next row of the cursor (as defined by the cursor
query):
&sql(FETCH MyCursor)

You must DECLARE and OPEN a cursor, before you can call FETCH on it.

Using Caché SQL 65

Embedded SQL

A FETCH statement may contain an INTO clause that specifies the names of the local host
variables that will receive data as the cursor is traversed. For example, we can add an INTO
clause to the previous example:
&sql(FETCH MyCursor INTO :a, :b)

If both the DECLARE and FETCH statements contain an INTO clause, the host variables
specified by the FETCH statement will be used.
The number of host variables within an INTO clause must exactly match the number of
columns within the cursor's SELECT list or you will receive a “Cardinality Mismatch” error
when the statement is compiled.
Upon a successful call to FETCH, the SQLCODE variable will be set to 0; if there is no more
data to FETCH, then SQLCODE will be set to 100 (No more data).
Depending on the query, the first call to FETCH may perform additional tasks (such as sorting
values within a temporary data structure).

[Link] The CLOSE Cursor Statement

The CLOSE statement terminates the execution of a cursor:
&sql(CLOSE MyCursor)

The CLOSE statement cleans up any temporary storage used by the execution of a query.
Programs that fail to call CLOSE will experience resource leaks (such as unneeded increase
of the CACHETEMP temporary database).
Upon a successful call to CLOSE, the SQLCODE variable will be set to 0.

9.2.8 The SQLCODE Variable and Error Handling

Embedded SQL statements set, as a side effect, the variable SQLCODE to indicate success
or failure. An SQLCODE of 0 indicates success. The various SQL error codes are listed in
the SQL Error Code reference.

9.2.9 The %ROWCOUNT Variable

Embedded SQL statements set, as a side effect, the variable %ROWCOUNT to indicate the
number of rows affected by a particular statement.
For example, the following code updates the salaries of all employees making less than $50000
per year and prints out the number of employees effected by the change:

66 Using Caché SQL

 Syntax and Use
&sql(UPDATE [Link]
Set Salary = (Salary * 1.1)
WHERE Salary < 50000)

Write "Employees: ", %ROWCOUNT,!

Keep in mind that all embedded SQL statements (within a given process) modify the
%ROWCOUNT variable. If you need the value provided by %ROWCOUNT, be sure to get
its value before executing additional embedded SQL statements.
Also note that explicitly rolling back a transaction will not affect the value of %ROWCOUNT.
For example, the following will report that changes have been made, even though they have
been rolled back:
TSTART // start an explicit transaction
&sql(UPDATE [Link]
Set Salary = (Salary * 1.1)
WHERE Salary < 50000)
TROLLBACK // force a rollback; this will NOT modify %ROWCOUNT
Write "Employees: ", %ROWCOUNT,!

Implicit transactions (such as if an UPDATE fails a constraint check) are reflected by

%ROWCOUNT.

9.2.10 The %ROWID Variable

Embedded SQL INSERT statements set, as a side effect, the variable %ROWID to indicate
the value of the row ID (Object ID) assigned to the new row.
For example, the following code inserts a new Person into the database:
&sql(INSERT INTO [Link]
(Name,SSN)
VALUES ('Swift,Jonathan','111-22-3333'))

If (SQLCODE = 0) {
Write "New Person inserted with ID: ", %ROWID,!
}

9.2.11 NULL Handling

Within embedded SQL, the SQL NULL value is represented as an empty string within a host
variable. For example, suppose that the Person with ID of 43 has a NULL (never specified)
Occupation. After executing this query:

&sql(SELECT Name, Occupation

INTO :name, :occupation
FROM Person
WHERE %ID = 43)

Using Caché SQL 67

Embedded SQL

The host variable, occupation, will be set to "" (an empty string) to indicate a NULL value.
In the rare case that Occupation contains an empty string (such as if an application explicitly
set the field to an empty string), then the host variable will contain the special marker value,
$C(0) (a string containing only a single, ASCII 0 character).
Note that this NULL behavior is only true within host-mode queries; within ODBC and JDBC,
NULL values are represented using the special values defined by the ODBC and JDBC
specifications.

68 Using Caché SQL

10
Dynamic SQL
Caché offers support for dynamic SQL—queries that are prepared and executed at runtime
using the %[Link] class. Dynamic SQL lets you program within Caché in the same
way that you would within an ODBC or JDBC application (except that you are executing the
query within the same process context as the database engine).
For example, the following Basic code prepares and executes a dynamic SQL statement:
result = New %[Link]()
[Link]("SELECT Name,SSN FROM [Link] ORDER BY Name")
[Link]()
While ([Link]())
PrintLn [Link]("Name") & ", " & [Link]("SSN")
Wend

The operation of dynamic SQL is described below.

10.1 Dynamic SQL versus Embedded SQL

Dynamic SQL differs from embedded SQL in the following ways:
• Dynamic SQL queries are prepared at program execution time, not compilation time.
This means that the compiler cannot check for errors at compilation time. It also means
that programs can create specialized queries in response to user or other input.
• Dynamic SQL executes slightly less efficiently than embedded SQL as it does not generate
in-line code for queries.

Using Caché SQL 69

Dynamic SQL

• Input parameters within dynamic SQL are indicated using the “?” character; embedded
SQL uses host variables (for example, :var).
• Output values from dynamic SQL is retrieved using the API of the %[Link]
object (that is, the Data property); embedded SQL uses host variables (for example, :var).
• Dynamic SQL can be used within Basic as well as Caché ObjectScript.
• Dynamic SQL provides an easy way to find query meta-information (such as quantity
and names of columns).
• Queries prepared by dynamic SQL are maintained within the Query Cache so that subse-
quent calls to prepare the same query can reuse previously generated code. Embedded
SQL generated inline code at compilation time and does not need to use the query cache.

Dynamic SQL and embedded SQL use the same data representation (logical mode by default,
but this can be changed) and NULL handling.

10.2 The %[Link] Class

Dynamic SQL is supported via the %[Link] class. Applications create an instance
of the %[Link] class and use it to prepare, execute, and iterate over queries.

10.2.1 Creating a %[Link] Object

Within Basic you can create an instance of the %[Link] class using the New com-
mand:
result = New %[Link]()

Alternatively, you can do the same within Caché ObjectScript:

Set result = ##class(%[Link]).%New()

At this point the %[Link] object is ready to prepare an SQL statement.

10.2.2 Preparing a Query

You can prepare an SQL statement using the Prepare method of the %[Link] class.
This takes, as its first argument, a string containing an SQL query to prepare for subsequent
execution. For example, in Basic:

70 Using Caché SQL

 The %[Link] Class
[Link]("SELECT Name FROM [Link] ORDER BY Name")

The Prepare method returns a %Status value indicating success (the query string contains a
valid SQL statement) or failure (it does not).
You can specify input parameters (variables for use within a WHERE clause) by using the
“? ” character:
[Link]("SELECT Name FROM [Link] WHERE Salary > ?")

The value for each input parameter is specified when the query is xeecuted (see the next
section).
You are not limited to SELECT statements within dynamic SQL: you can use the Prepare
method to prepare other statements, such as DDL, INSERT, UPDATE, or DELETE statements.

10.2.3 Finding Meta-information about a Query

After preparing a query, you can find meta-information about the query by using the methods
of the %[Link] class. These methods include:

Method Description
GetColumnCount Returns the number of columns in the query.
GetColumnName Returns the name of a specific column, indexed by column
number.
GetParamCount Returns the number of input parameters for the query.

10.2.4 Executing a Query

After preparing a query, you can execute it by calling the Execute method of the
%[Link] class. In the case of a non-SELECT statement, Execute invokes the desired
operation (such as performing an INSERT). In the case of a SELECT query, Execute prepares
the query for subsequent traversal and data retrieval.
The Execute method takes a variable number of arguments that correspond to any input
parameters (indicated by “?” ) within the prepared SQL statement. The input parameters
correspond to the order in which the “?” characters appear within the SQL statement: the
first argument is used for the first “?” and so on.
For example, the following Basic code demonstrates the execution of a query containing 2
input parameters:

Using Caché SQL 71

Dynamic SQL
result = New %[Link]()
sql = "SELECT Name FROM [Link] WHERE Salary > ? AND Salary < ?"
' prepare the query
[Link](sql)

'find everyone with salary between 10000 and 20000

[Link](10000,20000)

The Execute method returns a %Status value indicating success or failure.

10.2.5 Fetching Data from a Query

You can iterate over the results of a SELECT query using the Next method of the
%[Link] class:

While ([Link]())
PrintLn [Link]("Name")
Wend

The Next method fetches the data for the next row within the query results and places this
data in the Data property of the %[Link] object. The Next method returns a true
value as long as there are additional rows to fetch; it returns false when it reaches the end of
the query results.
The Data property is an array, subscripted by column name, containing the data values fetched
for the current row.
The column name is specified by the SQL query . If you have multiple columns with the same
name, then you cannot retrieve them both via the Data property. Instead you should provide
unique column names by using aliases within your SQL statement:
result = New %[Link]()
' create the query string
sql = "SELECT [Link] As AName, [Link] As BName "
sql = sql & "FROM MyApp.Table1 A, MyApp.Table2 B "
sql = sql & "WHERE [Link] = [Link]"
' prepare the query
[Link](sql)

The %[Link] class also provides the GetData method for getting the data for the
current row indexed by column number.

10.2.6 Closing a Query

When you are done with a dynamic SQL query you can close it (release any resources used
by the query) by either a) destroying the %[Link] object (such as letting it go out
of scope) or b) explicitly calling the Close method of the %[Link] class:

72 Using Caché SQL

 The Query Cache
[Link]()

Calling the Close method allows you to execute and fetch from the same query without having
to re-prepare it.

10.3 The Query Cache

Caché automatically maintains a cache of frequently used dynamic SQL queries (those created
via ODBC, JDBC, or Dynamic SQL).
When you first prepare a dynamic SQL query , the SQL Engine optimizes it and generates a
program (a set of one or more Caché routines) that will execute the query. The query text is
then placed in the query cache. If you subsequently attempt to prepare the same (or a similar)
query, the SQL Engine will find it in the cache and directly xeecute the code for the query,
bypassing the need to optimize and code generate.
The query cache provides the following benefits:
• Subsequent execution of frequently used queries is faster. More importantly, this perfor-
mance boost is available automatically without having to code cumbersome stored pro-
cedures. Most relational database products recommend using only stored procedures for
database access. This is not necessary with Caché.
• The query cache is shared among all database users; if User 1 prepares a query, then User
1023 can take advantage of it.
• The Query Optimizer is free to use more time to find the best solution for a given query
as this price only has to be paid the first time a query is prepared.

Whenever you modify (alter or delete) a table, any queries based on that table are automatically
purged from the query cache.
You can view (and manage) the contents of the query cache using the Caché System Manage-
ment Portal.
There is no need for a cache for Embedded SQL as embedded SQL statements are replaced
with inline code at compilation time.

Using Caché SQL 73

11
Optimizing Performance
The are a number of things you can do to ensure the maximum performance of Caché SQL
queries.

11.1 Indices
Determining which fields should be indexed requires some thought: too few or the wrong
indices and key queries will run too slowly; too many indices can slow down INSERT and
UPDATE performance (as the index values must be set or updated).

11.2 EXTENTSIZE and SELECTIVITY

When the Query Optimizer decides the most efficient way to execute a specific SQL query ,
two of the things it considers are the EXTENTSIZE values of any tables used within the query
and the SELECTIVITY values of any columns used by the query. In order to ensure that the
optimizer can make the correct decisions, it is important that these values are set correctly.

11.2.1 EXTENTSIZE
The EXTENTSIZE value for a table is simply the number of rows (roughly) stored within the
table.

Using Caché SQL 75

Optimizing Performance

At development time, you can provide this value by defining anEXTENTSIZE parameter
within the class definition that defines the table:
Class [Link] Extends %Persistent [ ClassType = persistent]
{
Parameter EXTENTSIZE = 50000;
// ...
}

Typically, you provide an estimate of what you expect the typical size of this table will be
when used within an application. It is not important to have an exact number. This value is
used to compare the relative costs of scanning over different tables; the most important thing
is to make sure that the relative values of EXTENTSIZE between tables are correct (that is,
small table should have a small value and large tables a large one).
If your table has real (or realistic) data, you can automatically calculate and set its
EXTENTSIZE value using the Tune Table facility within the Caché System Management
Portal.

11.2.2 SELECTIVITY
Within a Caché SQL table (class), every column (property) has a SELECTIVITY value asso-
ciated with it.
SELECTIVITY is defined as follows:

SELECTIVITY
The SELECTIVITY value for a column is the percentage of rows within a table that
would be returned as a result of query searching for a typical value of the column.
For example, suppose a table contains a Sex column whose value is evenly distributed between
“M” and “F” . The SELECTIVITY value for the Sex column would be “50%” , as a typical
query based on Sex would find 50% of the rows within the table. The SELECTIVITY value
for a more unique property, such as TaxID, is typically a small percentage that varies
according to the size of the table.
At development time, you can provide this value by defining anSELECTIVITY property
parameter within the class definition that defines the table:
Class [Link] Extends %Persistent [ ClassType = persistent]
{
Property Sex As %String(MAXLEN=1,SELECTIVITY = "50%");
}

Typically you provide an estimate of what you expect the SELECTIVITY will be when used
within an application. As with EXTENTSIZE, it is not important to have an exact number.

76 Using Caché SQL

 EXTENTSIZE and SELECTIVITY

Many of the data type classes provided by Caché will provide reasonable default values for
SELECTIVITY.
If your table has real (or realistic) data, you can automatically calculate and set its
SELECTIVITY values using the Tune Table facility within the Caché System Management
Portal.

Note: After running the Tune Table utility, the resulting SELECTIVITY values are saved
in the class' Storage Definition where they overwrite the values explicitly set in the
class' property SELECTIVITY [Link] should use the values gener-
ated by the Tune Table utility and not set SELECTIVITY parameter values unless
they are well-researched in advance.

Using Caché SQL 77

12
Foreign Keys
To enforce referential integrity between tables you can define foreign keys. When a table
containing a foreign key constraint is modified (INSER
T, UPDATE, or DELETE), the foreign
key constraints are checked.
There are several ways to define foreign keys in Caché SQL:
1. You can define aRelationship between two classes. Defining a relationship automatically
projects a foreign key constraint to SQL.
2. You can add an explicit Foreign Key definition to a class definition (for cases not co
vered
by relationships).
3. You can add or remove foreign keys using DDL commands.

Using Caché SQL 79

13
Triggers
Caché SQL supports the ability to define triggers—lines of code that are xeecuted in response
to certain SQL events.

Note: At this time, triggers can only be implemented using Caché ObjectScript.

13.1 Defining Triggers

There are several ways to define a trigger for a specific table:
• Using Caché Studio to add an SQL Trigger definition to the class definition that corre-
sponds to the table:
Class [Link] Extends %Persistent [ClassType = persistent]
{

/// This trigger updates the LogTable after every insert

Trigger LogEvent [ Event = INSERT, Time = AFTER ]
{
// get row id of inserted row
Set id = {ID}

// INSERT value into Log table

&sql(INSERT INTO LogTable
(TableName, IDValue)
VALUES ('[Link]', :id))
}

• Using the DDL CREATE TRIGGER command to create a trigger.

Using Caché SQL 81

Triggers

Each trigger is defined to fire when either an INSERT, UPDATE, or DELETE event occurs
on a table (the trigger Event keyword). You can also specify whether the trigger should be
fired BEFORE orAFTER the event occurs (the trigger Time keyword).
You can associate multiple triggers with the same event and time; in this case, you can control
the order in which multiple triggers are fired using the triggerOrder keyword. Triggers with
a lower Order value are fired first. If multiple triggers ha
ve the same Order value, then the
order in which they are fired is not specified.

13.2 Trigger Code

Each trigger contains one or more lines of Caché ObjectScript code. This code is invoked by
the SQL Engine whenever the event associated with the trigger occurs.
Within trigger code, you can refer to field values (for the fields belonging to the table the
trigger is associated with) using a special {fieldname} syntax. For example:
Class [Link] Extends %Persistent [ClassType = persistent]
{
/// This trigger updates the LogTable after every insert
Trigger LogEvent [ Event = INSERT, Time = AFTER ]
{
// get row id of inserted row
Set id = {ID}

// INSERT value into Log table

&sql(INSERT INTO LogTable
(TableName, IDValue)
VALUES ('[Link]', :id))
}

82 Using Caché SQL

14
Stored Procedures
Like most relational database systems, Caché allows you to create SQL Stored Proce-
dures—small programs that you can invoke within an SQL context (such as via ODBC or
JDBC).
Unlike relational databases, Caché lets you define stored procedures as methods of classes.
In fact, a stored procedure is nothing more than a class method that is made available to SQL.
Within a stored procedure, you can use the full range of Caché's object-based features.
Within Caché SQL there are two main types of stored procedure:
• Those that return a set of records. These are referred to as Result Set Stored Procedures.
• Those that do not return a set of records. These are referred to as Method Stored Proce-
dures.

Method stored procedures that return a value are sometimes referred to as “Stored Functions”
.

14.1 Defining Stored Procedures

As with most aspects of Caché SQL there are two ways of defining Stored Procedures: using
classes and using DDL. These are described in the following sections.

Using Caché SQL 83

Stored Procedures

14.1.1 Defining a Stored Procedure Within a Class Definition

To define a method stored procedure, simply define a class method and set its
SqlProc key-
word:
Class [Link] Extends %Persistent [ClassType = persistent]
{

/// This procedure finds total sales for a territory

ClassMethod FindTotal(territory As %String) As %Integer [SqlProc]
{
// use embedded sql to find total sales
&sql(SELECT SUM(SalesAmount) INTO :total
FROM Sales
WHERE Territory = :territory
)
Quit total
}
}

After this class is compiled, the FindTotal method will be projected to SQL as the stored
procedure MyApp.Person_FindTotal. You can change the name that SQL uses for the
procedure using the method's SqlName keyword.
To define a result set stored procedure, simply define a class query and set its
SqlProc keyword:
Class [Link] Extends %Persistent [ClassType = persistent]
{
/// This procedure returns a set of persons ordered by name
Query ListPersons(name As %String = "") As %SQLQuery [ SqlProc ]
{
SELECT ID, Name
FROM Person
ORDER BY Name
}
}

After this class is compiled, the ListPersons query will be projected to SQL as the stored
procedure MyApp.Person_ListPersons. You can change the name that SQL uses for the
procedure using the query's SqlName keyword.
When MyApp.Person_ListPersons is called from SQL, it will automatically return the
record set defined by the query's SQL statement.
It is possible to create a record set stored procedure that is based on custom-written code (not
an SQL statement).

84 Using Caché SQL

 Using Stored Procedures

14.1.2 Defining a Stored Procedure Using DDL

You can define a Stored Procedure using DDL via theCREATE FUNCTION and CREATE
PROCEDURE, commands.

14.2 Using Stored Procedures

You can use stored procedures in two distinct ways:
• From ODBC or JDBC you can invoke a stored procedure using the SQL CALL statement.
(Embedded and Dynamic SQL do not support the CALL function, as you can directly
invoke the stored procedure method from within Caché).
• You can use Stored Functions (that is, a method-based stored procedure that returns a
value) as if it was a built-in function within an SQL query.

14.2.1 Stored Functions

A stored function is simply a method-based (does not return a record set) stored procedure
that returns a value. For example, the following class defines a stored function,Cube, that
returns the cube of a given value:
Class [Link] Extends %Persistent [ClassType = persistent]
{
ClassMethod Cube(val As %Integer) As %Integer [SqlProc]
{
Quit val * val * val
}
}

The stored function is simply a class method with its SqlProc keyword set.
You can use a stored function within an SQL query as if it were a built-in SQL function. The
name of the function is the SQL name of the stored function (in this case “Cube” ) qualified
by the schema (package) name it was defined in (in this case “MyApp” ).
The following query uses the Cube function:
SELECT Cost, MyApp.Utils_Cube(Cost) As CubeCost FROM Products

If you define multiple stored functions within the same package (schema), you must make
sure that they have unique SQL names.

Using Caché SQL 85

15
BLOBs, CLOBs, and Streams
Caché SQL supports the ability to store BLOBs (Binary Large Objects) and CLOBs (Character
Large Objects) within the database. BLOBs are used to store binary information, such as
images, while CLOBs are used to store character information. BLOBs and CLOBs can store
up to 4 Gigabytes of data (the limit imposed by the JDBC and ODBC specifications).
The operation of the BLOBs and CLOBs is identical in every respect except how they handle
character encoding conversion (such as UNICODE to Multi-byte) when accessed via an
ODBC or JDBC client: the data in a BLOB is treated as binary data and is never converted
to another encoding while the data in a CLOB is treated as character data and is converted
as necessary.
BLOBs and CLOBs have the following restrictions:
• You cannot define indices on BLOB or CLOB fields.
• You cannot use a BLOB or CLOB field in aWHERE clause.
• You cannot UPDATE/INSERT multiple rows containing a BLOB or CLOB field; you
must do it row by row.

From the object point of view, BLOBs and CLOBs are represented as Stream objects. For
more information, refer to the Streams chapter of Using Caché Objects.

15.1 Defining BLOBs and CLOBs Using DDL

Within DDL, CLOB fields are defined using the LONG
VARCHAR SQL data type. BLOB
fields are defined using the LONG
VARBINARY SQL data type.

Using Caché SQL 87

BLOBs, CLOBs, and Streams
CREATE TABLE [Link] (
Name VARCHAR(50) not null,
Notes LONGVARCHAR,
Photo LONGVARBINARY
)

15.2 Using BLOBs and CLOBs within Caché

Methods
You cannot use a BLOB or CLOB value using embedded or dynamic SQL directly within a
Caché method; instead you use SQL to find the Stream Identifier for a BLOB or CLOB and
then create an instance of %AbstractStream object to access the data.
For example, you can use dynamic SQL to read a stream in a Basic method as follows:
/// Display the memos for all Persons with a given city
/// within an HTML table
ClassMethod DisplayMemo(city As %String = "") [language = basic]
{
' Define a query to find all the Stream Id values for memo
rs = New %[Link]()
[Link]("SELECT Name,Memo FROM [Link] WHERE Home_City = ?")
[Link](city)

' iterate over the results

PrintLn "<TABLE>"
While ([Link]())
PrintLn "<TR>"
' display the person's name
PrintLn "<TD>" & [Link]("Name") & "</TD>"

' Now open the stream object containing the memo

stream = OpenId %Stream([Link]("Memo"))
Print "<TD>"

' Write the contents of the stream to the current device

[Link]()
PrintLn "</TD></TR>"
Wend
PrintLn "</TABLE>"
}

15.3 Using BLOBs and CLOBs from ODBC

The ODBC specification does not provide for any recognition or special handling for BLOB
and CLOB fields. Caché SQL represents CLOB fields within ODBC as ha ving type LONG
VARCHAR. BLOB fields are represented as having type LONG VAR BINARY.

88 Using Caché SQL

 Using BLOBs and CLOBs from JDBC

The ODBC driver/server uses a special protocol to access BLOB and CLOB [Link]
you have to write special code within ODBC application to use CLOB and BLOB fields; the
standard reporting tools typically do not support them.

15.4 Using BLOBs and CLOBs from JDBC

Within a Java program you can retrieve or set data from a BLOB or CLOB using the standard
JDBC BLOB and CLOB interfaces. For example:
Statement st = [Link]();
ResultSet rs = [Link]("SELECT MyCLOB,MyBLOB FROM MyTable");
[Link](); // fetch the Blob/Clob
[Link] clob = [Link](1);
[Link] blob = [Link](2);

// Length
[Link]("Clob length = " + [Link]());
[Link]("Blob length = " + [Link]());

// ...

Using Caché SQL 89

16
External Tables
Caché SQL supports the ability to create “external tables” ; tables that are defined within
the Caché dictionary but are stored within an external relational database. The access to
external database is provided by the Caché SQL Gateway, which offers transparent connec-
tivity using ODBC.
You can define an xe ternal table using the Link Table Wizard within the Caché System
Management portal. This wizard reads a table definition and creates a corresponding Caché
class definition (table).
External tables act as if they were native Caché tables: you can issue queries against them
and perform INSERT, UPDATE, and DELETE operations.
At this time, the following restrictions apply to external tables:
• You cannot mix native and external tables within the same query.
• You cannot mix tables from different external sources within the same query.

Refer to the Using the Caché SQL Gateway for more details.

Using Caché SQL 91

17
Users, Roles, and Privileges
Caché SQL provides security through the user of users and their granted privileges.
Caché SQL enforces privilege checking for ODBC, JDBC, and Dynamic SQL access.
Embedded SQL statements do not perform privilege checks — it is assumed that applications
using embedded SQL will check for privileges before using embedded SQL statements.

17.1 Users
A Caché SQL user is the same as a user defined for CachéAdvanced Security. You can define
a user in the System Management Portal on the General tab of [Home] > [Security Management]
> [Users] > [Edit User] page. Once you create a user, the page's other tabs become available,
where you can specify which roles a user holds, which general SQL privileges the user holds,
which table-level privileges the user holds, which views are available, and which stored
procedures can be executed.
If a user has SQL table privileges, or general SQL privileges, then roles granted or revoked
on the user's Roles tab do not affect a user's access to tables through SQL-based services,
such as ODBC. This is because, in the SQL-based services, table-based privileges take
precedence over resource-based privileges.

Using Caché SQL 93

Users, Roles, and Privileges

17.2 Roles
The [Home] > [Security Management] > [Roles] page provides a list of role definitions for a
Caché instance. To view or change details on a particular role, select the Edit link for the role.
On the [Home] > [Security Management] > [Roles] > [Edit Role] page that appears, there is
information regarding the roles privileges and which users or roles hold it. (Roles can hold
other roles.)
The General tab lists a role's privileges for Caché Advanced Security resources. If a role only
holds SQL privileges, the General tab's Resources table lists the role's privileges as “None
defined.
”
The SQL Privileges tab lists a role's privileges for Caché SQL resources, where a drop-down
list of namespaces allows you to view each namespace's resources. Because privileges are
listed by namespace, the listing for a role holding no privileges in a particular namespace
displays “None.”

Note: You should define privileges using roles and associate specific users with these roles.
There are two reasons for this:
1. It is much more efficient for the SQL Engine to determine privilege levels by
checking a relatively small role database than by checking individual user entries.
2. It is much easier to administer a system using a small set of roles as compared
with a system with many individual user settings.

For example, you can define a role called“ACCOUNTING” with certain access privileges.
As the Accounting Department grows, you can define new users and associate them with the
ACCOUNTING role. If you need to modify the privileges for ACCOUNTING, you can do
it once and it will automatically cover all the members of the Accounting Department.
In addition to using the System Management Portal, you can also define users and roles with
the following SQL Commands: CREATE USER, CREATE ROLE, ALTER USER, GRANT,
DROP USER, and DROP ROLE.

94 Using Caché SQL

 Privileges

17.3 Privileges
Caché SQL supports two types of privileges: object and system. Object privileges cover
access to specific named objects (in the SQL sense of the word: a table, a view, and so on).
System privileges cover the creation, altering, and deleting of types of objects, such as the
permission to create tables.
You can grant privileges in the following ways:
• Interactively, using the System Management Portal.
• Programmatically, using the SQL GRANT command.

The following administrative privileges are namespace-specific:

• %ALTER_TABLE
• %ALTER_VIEW
• %CREATE_FUNCTION
• %CREATE_METHOD
• %CREATE_PROCEDURE
• %CREATE_QUERY
• %CREATE_TABLE
• %CREATE_TRIGGER
• %CREATE_VIEW
• %DROP_FUNCTION
• %DROP_METHOD
• %DROP_PROCEDURE
• %DROP_QUERY
• %DROP_TABLE
• %DROP_TRIGGER
• %DROP_VIEW
• %DB_OBJECT_DEFINITION

Using Caché SQL 95

18
Special Features
Caché SQL includes a number of powerful features not included within the SQL-92 standard.
These are described within this chapter.
Caché SQL also supports Indices, Triggers, BLOBs, and Stored Procedures (these are typical
RDBMS features but are not part of the SQL-92 standard). These are described in their
respective chapters.

18.1 Implicit Joins

Caché SQL provides a special –> operator as a short-hand for getting values from a related
table without the complexity of specifying explicit JOINs in certain common cases.

18.1.1 References
You can use the –> operator as a short-hand for getting values from a “referenced table” .
For example, suppose you define two classes: Company:
Class [Link] Extends %Persistent [ClassType = persistent ]
{
/// The Company name
Property Name As %String;
}

and Employee:

Using Caché SQL 97

Special Features
Class [Link] Extends %Persistent [ClassType = persistent ]
{
/// The Employee name
Property Name As %String;

/// The Company this Employee works for

Property Company As Company;
}

The Employee class contains a property that is a reference to a Company object. Within an
object-based application, you can follow this reference using dot syntax. For example, to find
the name of a company that an employee works for:
name = [Link]

You can perform the same task using an SQL statement that uses an OUTER JOIN to join
the Employee and Company tables:
SELECT [Link] AS EmpName, [Link] AS CompName
FROM [Link] LEFT OUTER JOIN [Link]
ON [Link] = [Link]

Using the –> operator, you can perform the same OUTER JOIN operation more succinctly:
SELECT Name AS EmpName, Company->Name AS CompName
FROM [Link]

You can use the –> operator any time you have a reference column within a table; that is
column whose value is the ID of a referenced table (essentially a special case of foreign key).
You can use the –> operator anywhere you can use a column expression within a query. For
example, in a WHERE clause:
SELECT Name
FROM [Link]
WHERE Company->Name = 'XYZ Corp'

This is equivalent to:

SELECT [Link]
FROM [Link], [Link]
WHERE [Link] = [Link] AND
[Link] = 'XYZ Corp'

Note that in this case, this equivalent query uses an INNER JOIN.
You could also use the –> operator within an ORDER BY clause:
SELECT Name AS EmpName, [Link] AS CompName
FROM [Link]
ORDER BY Company->Name

98 Using Caché SQL

 Syntax

18.2 Syntax
Caché SQL provides additional syntax to make it easier to perform common operations.

18.2.1 COUNT(*) with Additional Columns

Within Caché SQL, a query containing COUNT() (or any other aggregate function) can also
contain additional columns.
For example:
SELECT COUNT(*) As Total, Name FROM [Link]

18.3 Functions and Operators

Caché SQL contains a number of additional functions and operators.

18.3.1 %ID
%ID is a pseudo-column name that returns the Row ID (Object ID) value for the current row
within a query.
For example:
SELECT %ID FROM [Link] ORDER BY %ID

This pseudo-column name is provided so that applications can find the row id for given row
without having to know its actual column name (such as ID).

18.3.2 %STARTSWITH
The %STARTSWITH operator (described in the SELECT page) tests if a string value starts
with the specified characters:
SELECT %ID, Name FROM [Link] WHERE Name %STARTSWITH 'A'

The %STARTSWITH operator is similar to the SQL LIKE (also described in the operator
SELECT page). In most cases it is a more efficient alternative as it is not as general purpose
as the LIKE operator.

Using Caché SQL 99

Special Features

18.4 User-defined Functions

Caché SQL allows you to invoke class methods within SQL queries. This provides a powerful
mechanism for extending the syntax of SQL.
You can use this feature as follows:
1. Within a persistent Caché class, define a class method with a literal (non-object) return
value. This has to be a class method as there will not be an object instance within an SQL
query on which to invoke an instance method. It also has to be defined as being an SQL
Stored Procedure.
For example, we can define aCube method within the class [Link]:
Class [Link] Extends %Persistent [ClassType = persistent, language = basic]
{
/// Find the Cube of a number
ClassMethod Cube(val As %Integer) As %Integer [SqlProc]
{
Return val * val * val
}
}

2. Within an SQL query, invoke the method as if it was a built-in SQL function:
SELECT %ID, Age, MyApp.Person_Cube(Age) FROM [Link]

For each value of Age, this query will invoke the Cube method and place its return value
within the results.

100 Using Caché SQL

Index
D
DDL Files, 36

L
Loading Oracle DDL, 36

O
Oracle DDL, Loading, 36

Using Caché SQL 101