This chapter provides an overview of the features of Caché SQL, especially those that are not covered by the SQL standard or are related to the Caché Unified Data Architecture. It assumes prior knowledge of SQL and is not designed to serve as an introduction to SQL concepts or syntax.
This chapter discusses the following topics:
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. The following terms are roughly equivalent:
||Relational Database Terms
There are two basic types of tables: 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).
SQL schemas provides a means of grouping sets of related tables, views, stored procedures, and cached queries. Schemas are defined within a specific namespace. The use of schemas helps prevent naming collisions at the table level, because a table name must only be unique within its schema. SQL schemas correspond to packages in Caché objects; schema-to-package mapping is further described in SQL to Class Name Transformations
An application can specify tables in multiple schemas.
You can specify an SQL name as qualified or unqualified. A qualified name specifies the schema: schema.name
. An unqualified name does not specify the schema: name
. If you do not specify the schema, Caché supplies the schema using some combination of the following techniques:
For create and query operations, Caché uses the system-wide Default SQL Schema Name, as defined in the Management Portal
SQL configuration settings. This default applies to all namespaces. For further details, refer to System-wide Default Schema
For query operations, Caché can use either the system-wide default schema name or a user-supplied list of schemas within the current namespace. This list may constitute an ordered search path of schema names. Different techniques are used to supply a list of schemas in Dynamic SQL
and Embedded SQL
To view all the existing schemas within a namespace:
From the Management Portal
select System Explorer
, then SQL
([Home] > [SQL]
). Select a namespace with the Switch
option at the top of the page; this displays the list of available namespaces. After selecting a namespace, select the Schema
drop-down list on the left side of the screen. This displays a list of the schemas in the current namespace with boolean flags indicating whether there are any tables or any views associated with each schema.
Select a schema from this list; it appears in the Schema
box. Just above it is a drop-down list that allows you to select Tables, System Tables, Views, Procedures, or All of these that belong to the schema. After setting this option, open the corresponding folder to view the member items. If there are no member items, opening the folder displays a blank page. (If you have not selected the corresponding per-schema option, or All, opening the folder displays the member items for the entire namespace.)
Select any of the listed member items to display the Catalog Details
for the item. Among the details you can select are the cached queries
for that item.
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
, and DELETE
You can use SQL queries in a number of ways:
Caché SQL uses a SelectMode option
to specify how data is to be displayed or stored. The available options are Logical, Display, and ODBC. Data is stored internally in Logical mode, and can be displayed in any of these modes. Every data type class can define transformations between internal Logical format and Display format or ODBC format by using the LogicalToDisplay()
, and ODBCToLogical()
methods. When SQL SelectMode is Display, the LogicalToDisplay transformation is applied, and returned values are formatted for display. The default SQL SelectMode is Logical; thus by default returned values are displayed in their storage format.
SelectMode affects the format that in which query result set data is displayed, SelectMode also affects the format in which data values should be supplied, for example in the WHERE clause. Caché applies the appropriate transformation method based on the storage mode and the specified SelectMode. A mismatch between a supplied data value and the SelectMode can result in an error or in erroneous results. For example, if DOB is a date stored in $HOROLOG Logical format, and a WHERE clause specifies WHERE DOB > 20000101
(ODBC format), SelectMode = ODBC returns the intended results. SelectMode = Display generates SQLCODE -146 Unable to convert date input to a valid logical date value
. SelectMode = Logical attempts to parse 20000101
as a Logical date value, and returns zero rows.
For most data types, the three SelectMode modes return the same results. The following data types are affected by the SelectMode option:
Date, Time, and Timestamp data types
. InterSystems SQL supports numerous Date, Time, and Timestamp data types (%Library.Date
, and %MV.Date
). With the exception of %Library.TimeStamp
, these data types use different representations for Logical, Display, and ODBC modes. In several of these data types Caché stores dates in $HOROLOG
format. This Logical mode internal representation consists of an integer count of the number of days from an arbitrary starting date (December 31st, 1840
), a comma separator, and an integer count of the number of seconds since midnight of the current day. In Display mode, dates and times commonly appear in the format specified by the data type’s FORMAT parameter or the date and time format defaults for the current locale in %SYS.NLS.Format
. The default for the American locale is DD/MM/YYYY hh:mm:ss
. In ODBC mode, dates and times are always represented as YYYY-MM-DD hh:mm:ss.fff
. The %Library.TimeStamp
data type also uses this ODBC format for Logical and Display modes.
%List data type
. Caché Logical mode stores lists using two non-printing characters that appear before the first item in the list, and appear as a separator between list items. In ODBC SelectMode, list items are displayed with a comma separator between list items. In Display SelectMode, list items are displayed with a blank space separator between list items.
Data types that specify VALUELIST and DISPLAYLIST
. If you are in display mode and you insert a value into a table where the field has a DISPLAYLIST, the display value you enter must exactly match one of the items in the DISPLAYLIST.
Empty strings, and empty BLOBs (stream fields). In Logical mode empty strings and BLOBs are represented by the non-display character $CHAR(0). In Display mode they are represented by an empty string ("").
The SQL SelectMode may be specified as follows:
For Embedded SQL, using the ObjectScript #SQLCompile Select
preprocessor directive setting. This directive allows for a fourth value, Runtime, which sets the select mode to whatever the RuntimeMode
property setting is: Logical, Display, or ODBC. The RuntimeMode
default is Logical.
Collation specifies how values are ordered and compared, and is part of both Caché SQL and Caché objects.
You can specify a collation type as part of field/property definition. Unless otherwise specified, a string field/property defaults to the namespace default collation. By default, the namespace default collation for strings is SQLUPPER. SQLUPPER collation transforms strings into uppercase for the purposes of sorting and comparing. Thus, unless otherwise specified, string ordering and comparison is not case-sensitive.
You can specify a collation type as part of index definition, or use the collation type of the indexed field.
An SQL query can override the defined field/property collation type by applying a collation function to a field name. The ORDER BY
clause specifies the result set sequence for a query; if a specified string field is defined as SQLUPPER, query results order is not case-sensitive.
Caché supports numerous ways to write and execute SQL code. These include:
You can use Caché objects (classes and methods) to: