About This Book

This book describes how to use the Caché SQL, which provides standard relational access to data stored within a Caché database.

The book addresses the following topics:

The Caché SQL language:

“Introduction to Caché SQL” provides an overview of Caché SQL as it relates to software standards and interoperability.
“Caché SQL Basics” describes the fundamental features of Caché SQL (such as tables and queries), especially those that are not covered by the SQL standard or are related to the Caché unified data architecture.
“Language Elements” describes how Caché SQL handles the basic elements common to any programming language: numbers, strings, operators, NULL, and comments.
“Identifiers” describes the conventions used for naming entities within Caché SQL.

Data Definition: creating tables and views:

“Defining Tables” describes how to define tables in Caché SQL, by defining persistent classes or by using an SQL DDL statement.
“Defining Views” describes how to define views in Caché SQL by using the Management Portal or by using a DDL statement.
“Defining Foreign Keys” describes how to define foreign keys in Caché SQL.
“Defining Triggers” describes how to define triggers in Caché SQL.
“Defining and Using Stored Procedures” discusses stored procedures in Caché SQL.
“Storing and Using BLOBs and CLOBs” describes stream data and how to store and use BLOBs and CLOBs in Caché SQL.
“Users, Roles, and Privileges” addresses connections between Caché SQL and Caché security features.

Data Management: querying and modifying data:

“Querying the Database” describes how to create and use SELECT queries.
“Implicit Joins” describes a Caché SQL extension that provides arrow syntax for implicit joins. Caché SQL also provides standard syntax for explicit joins.
“Modifying the Database” describes how to use INSERT, UPDATE, and DELETE to modify data, and how to use transactions to group multiple data modifications.

SQL execution interfaces:

“Using Embedded SQL” describes how to write and execute SQL code embedded within ObjectScript code. This chapter also describes SQL cursors, which enable you to access multiple rows of data.
“Using Dynamic SQL” describes how ObjectScript can include SQL that is executed at runtime.
“Using the SQL Shell” describes how to write and execute SQL statements from the Terminal.
“Using the Management Portal SQL Interface” describes how to write and execute SQL statements from the Management Portal.
“Importing SQL Code” describes how to execute SQL statements by importing them from a text file. This interface can be used for Caché SQL code or SQL code in other vendor formats. Import SQL code can be used to define tables and to populate tables with data; it cannot be used to query data.

SQL interface:

“Using the Caché SQL Gateway” describes how to obtain access to external databases via JDBC and ODBC, enabling you to treat external tables as if they were native Caché tables.
“Importing and Exporting SQL Data” (an appendix) discusses tools in the Management Portal that enable you to import or export data.

For a detailed outline, see the Table of Contents.

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

The Caché SQL Reference provides details on individual SQL commands and functions, as well as information on the Caché SQL data types and reserved words.
Caché SQL Optimization Guide describes how to optimize a table definition by defining and building indices, how to use Tune Table to optimize table metadata based on typical data, and how to optimize query execution using cached queries, ShowPlan, frozen plans, and other optimization techniques.
Caché Programming Orientation Guide is an orientation guide for programmers who are new to Caché or who are familiar with only some kinds of Caché programming.
In Using Caché Objects, the chapter “Introduction to Persistent Objects” summarizes how Caché object technology interoperates with SQL. Later chapters provide additional detail.
Using Caché with JDBC describes how to access Caché tables from external applications via JDBC.
Using Caché with ODBC describes how to access Caché tables from external applications via ODBC.
Caché Advanced Configuration Settings Reference describes the SQL configuration settings.
Caché Error Reference lists the SQLCODE error messages.