InterSystems SQL Basics
This chapter provides an overview of the features of InterSystems
SQL, especially those that are not covered by the SQL standard or
are related to the InterSystems IRIS® data platform unified data
architecture. It assumes prior knowledge of SQL and is not designed
to serve as an introduction to SQL concepts or syntax.
Tables
Within
InterSystems 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:
Data Terms |
Relational Database Terms |
InterSystems IRIS Terms |
database |
schema |
package |
table |
persistent class |
field |
column |
property |
record |
row |
|
For further details, refer to “Introduction to the Default SQL Projection” in the “Introduction to Persistent Objects” chapter of Defining and Using Classes.
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).
To find out more on how to define tables, see the chapter “Defining Tables.”
To find out more on how to define views, see the chapter “Defining Views.”
In order to make queries against tables more efficient, you
can define indexes on tables. See the chapter “Defining and Building Indexes.” in
the SQL Optimization Guide.
In order to enforce referential integrity you can define foreign
keys and triggers on tables. See the chapters “Defining Foreign Keys” and “Defining Triggers.”
Schemas
SQL schemas provides a means
of grouping sets of related tables, views, stored procedures, and
cached queries. The use of schemas helps prevent naming collisions
at the table level, because a table, view, or stored procedure name
must only be unique within its schema. An application can specify
tables in multiple schemas.
SQL schemas correspond to persistent class packages. Commonly
a schema has the same name as its corresponding package, but these
names may differ because of different schema naming conventions or because different names have
been deliberately specified. Schema-to-package mapping is further
described in SQL to Class
Name Transformations.
Schemas are defined within a specific namespace. A schema name
must be unique within its namespace. A schema (and its corresponding
package) is automatically created when the first item is assigned
to it and automatically deleted when the last item is deleted from
it.
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, InterSystems IRIS supplies the schema as
follows:
To view all the existing schemas within a namespace:
-
From the Management Portal select System Explorer, then SQL. Select a namespace with the Switch option
at the top of the page; this displays the list of available namespaces.
Select 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. Select a schema from this list; the selected
name appears in the Schema box.
-
The applies to drop-down list
allows you to select Tables, Views, Procedures, or Cached Queries,
or All of these that belong to the schema. After setting this option,
click the triangles to view a list of the items. If there are no items,
clicking a triangle has no effect.
Privileges
InterSystems SQL provides a way to limit access to tables, views,
and so on via privileges. You can define a set
of users and roles and grant
various privileges (read, write, and so on) to them. See the chapter “SQL Users, Roles, and Privileges.”
Data Display Options
InterSystems 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(), LogicalToODBC(), DisplayToLogical(), 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. InterSystems
IRIS 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 > 2000–01–01 (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 2000–01–01 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.DateOpens in a new tab, %Library.TimeOpens in a new tab, %Library.PosixTimeOpens in a new tab, %Library.TimeStampOpens in a new tab, and %MV.Date). With the exception of %Library.TimeStampOpens in a new tab, these data types use different representations for Logical, Display,
and ODBC modes. In several of these data types InterSystems IRIS 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. InterSystems IRIS stores %PosixTime
timestamps as an encoded 64-bit signed integer. 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.FormatOpens in a new tab. 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.TimeStampOpens in a new tab data type also uses this ODBC format for Logical and Display modes.
-
%List data type. InterSystems IRIS 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. For
required fields, 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.
For non-required fields, non-matching values are converted to NULL
values.
-
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 the current process, using the SetOption("SelectMode")Opens in a new tab method.
-
For a InterSystems SQL Shell
session, using the SET SELECTMODE command.
-
For a query result set from the Management
Portal “Execute
Query” user interface (System Explorer, SQL), using the "Display Mode" drop-down list.
-
For a Dynamic
SQL %SQL.Statement instance, using the %SelectMode property.
-
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 RuntimeModeOpens in a new tab property setting is: Logical,
Display, or ODBC. The RuntimeMode default is
Logical.
-
For the SQL commands CREATE QUERY, CREATE METHOD, CREATE PROCEDURE, and CREATE FUNCTION using the SELECTMODE
keyword.
-
For an individual column within an SQL query by using
the %EXTERNAL, %INTERNAL, and %ODBCOUT functions.
Data Collation
Collation specifies how values are ordered and compared, and
is part of both InterSystems SQL and InterSystems IRIS 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.
For further details refer to the “Collation” chapter of Using InterSystems
SQL.
Executing SQL
InterSystems IRIS supports numerous ways to write and execute
SQL code. These include:
You can use InterSystems IRIS objects (classes and methods)
to: