Mimer SQL Engine User's Manual

Introduction

Mimer SQL Engine is an advanced database management system developed by Mimer Information Technology AB.

Mimer SQL Engine, with its availability on all major UNIX/Linux, Windows Vista/XP/200n/NT, Mac OS X, and OpenVMS platforms, is ideally suited for open environments where interoperability is important.

The database management language Mimer SQL (Structured Query Language) is compatible in all essential features with the current SQL standard, see the Mimer SQL Reference Manual, Introduction to SQL Standards, for details.


About this Manual

This manual is intended primarily for users who have little or no experience of SQL (database query language). It provides an introduction to the concepts and usage of Mimer SQL, including how to create and how to manipulate the contents of a database.

Most examples in this manual are based on the example database, MIMER_STORE, that is described in Appendix A The Example Environment.

To access the example database, you can use:

  • DbVisualizer, available in the Mimer SQL program group.
  • Mimer BSQL, available in the Mimer SQL program group.
  • Any ODBC-based SQL tool.
  • Any JDBC-based SQL tool.
  • WSQL, an unsupported SQL tool for Windows, available for download from
    http://developer.mimer.com

Mimer BSQL is a command line oriented tool for executing SQL statements, wither interactively or in batch jobs. This manual includes a detailed description of the facilities provided in Mimer BSQL.

Refer to the Mimer SQL Reference Manual, SQL Statement Descriptions for a complete syntax description of the SQL statements supported in Mimer SQL.

The use of Mimer SQL together with application programming interfaces such as ADO.NET, JDBC, ODBC and embedded SQL (ESQL), is described in Mimer SQL Engine Programmer's Manual.

Prerequisites

There are no prerequisites for users of this manual. However, it is to the user's advantage to be familiar with the principles of the relational database model when working with SQL.

Related Mimer SQL Publications

· Mimer SQL Engine Reference Manual

Contains a complete description of the syntax and usage of all statements in Mimer SQL Engine and is a necessary complement to this manual.

· Mimer SQL Engine Programmer's Manual

Contains a description of how Mimer SQL Engine can be used within the context of application programs, written in conventional programming languages.

· Mimer SQL Engine System Management Handbook

Describes system administration functions, including export/import, backup/restore, databank shadowing and the statistics functionality. The information in this manual is used primarily by the system administrator, and is not required by application program developers. The SQL statements which are part of the System Management API are described in the Mimer SQL Engine Reference Manual.

· Mimer SQL platform-specific documents

Contain platform-specific information. A set of one or more documents is provided, where required, for each platform on which Mimer SQL is supplied.

· Mimer SQL Engine Release Notes

Contain general and platform-specific information relating to the Mimer SQL Engine release for which they are supplied.

Suggestions for Further Reading

We can recommend the many works of C. J. Date. His insight into the potential and limitations of SQL, coupled with his pedagogical talents, makes his books invaluable sources of study material in the field of SQL theory and usage.

In particular, we can mention: A Guide to the SQL Standard (Fourth Edition, 1997). ISBN: 0-201-96426-0. This work contains much constructive criticism and discussion of the SQL standard, including SQL99.

Technical discussions on a wide range of SQL issues can be found in SQL for Smarties (Expanded 2nd edition, 1999, ISBN: 1558605762), by Joe Celko.

SQL Standards

Official documentation of the accepted SQL standards may be found in:

ISO/IEC 9075:2003(E) Information technology - Database languages - SQL. This document contains the standard referred to as SQL-2003.

Acronyms, Terms and Trademarks

IEC

International Electrotechnical Commission

ISO

International Standards Organization

SQL

Structured Query Language

PSM

Persistent Stored Modules (i.e. Stored Procedures)

All other trademarks are the property of their respective holders.


Basic Concepts of Mimer SQL

This chapter provides a general introduction to the basic concepts of Mimer SQL databases and Mimer SQL objects.

Mimer SQL is a relational database system. This means that the information in the database is presented to the user in the form of tables. The tables represent a logical description of the contents of the database which is independent of, and insulates the user from, the physical storage format of the data.

The Mimer SQL database includes the data dictionary which is a set of tables describing the organization of the database and is used primarily by the database management system itself.

The database, although located on a single physical platform, may be accessed from many distinct platforms, even at remote geographical locations, linked over a network through client/server support.

Commands are available for managing the connections to different databases, see Managing Database Connections, so the actual database being accessed may change during the course of an SQL session.

At any one time, however, the database may be regarded as one single organized collection of information.


Tables

Data in a relational database is logically organized in tables, which consist of horizontal rows and vertical columns.

Columns are identified by a column-name. Each row in a table contains data pertaining to a specific entry in the database. Each field, defined by the intersection of a row and a column, contains a single item of data.

For example, a table containing information about currencies may have columns for the currency code, name and exchange rate against the euro:

CREATE TABLE currencies (
      code CHARACTER(3) PRIMARY KEY,
      currency CHARACTER(32) NOT NULL,
      exchange_rate DECIMAL(12, 4));

CURRENCIES

CODE
CURRENCY
EXCHANGE_RATE

AED

UAE Dirhams

3.1030

AFA

Afghanis

4092.0000

ALL

Leke

122.3000

AMD

Armenian Drams

-

ANG

Netherlands Antillian Guilders

1.4890

AOA

Kwanza

-

...

...

...

Each row in a table must have the same set of data items (one for each column in the table), but not all the items need to be filled in.

A column can have a default value defined (either as part of the column specification itself or by using a domain with a default value) and this is stored if an explicit value has not been specified.

If no default value has been defined for a column, the NULL value is stored when no data value is supplied (the way the NULL value is displayed depends on the application - in Mimer BSQL the minus sign is used).

A relational database is built up of several inter-dependent tables which can be joined together. Tables are joined by using related values that appear in one or more columns in each of the tables. Part of the flexibility of a relational database structure is the ability to add more tables to an existing database. A new table can relate to an existing database structure by having columns with data that relates to the data in columns of the existing tables. No alterations to the existing data structure are required.

All the fields in any one column contain the same data type with the same maximum length. See the Mimer SQL Reference Manual, Data Types in SQL Statements, for a detailed description of data types supported by Mimer SQL.

Base Tables and Views

The logical representation of data in a Mimer SQL database is stored in tables (this is what the user sees, as distinct from the physical storage format which is transparent to the user).

The tables which store the data are referred to as base tables. Users can directly examine data in the base tables.

In addition, data may be presented using views, which are created from specific parts of one or more base tables or views. To the user, views may look the same as tables, but operations on views are actually performed on the underlying base tables.

Access privileges on views and their underlying base tables are completely independent of each other, so views provide a mechanism for setting up specific access to tables.

The essential difference between a table and a view is underlined by the action of the DROP command, which removes objects from the database. If a table is dropped, all data in the table is lost from the database and can only be recovered by redefining the table and re-entering the data. If a view is dropped, however, the table or tables on which the view is defined remain in the database, and no data is lost.

Data may, however, become inaccessible to a user who was allowed to access the view but who is not permitted to access the underlying base table(s).

Note: Since views are logical representations of tables, all operations requested on a view are actually performed on the underlying base table, so care must be taken when granting access privileges on views.
Such privileges may include the right to insert, update and delete information. As an example, deleting a row from a view will remove the entire row from the underlying base table and this may include table columns the user of the view had no privilege to access.

Restriction Views

Views may be created to simplify presentation of data to the user by including only some of the base table columns in the view or only by including selected rows from the base table. Views of this kind are called restriction views.

For example, a view may be created on the COUNTRIES table to include only the COUNTRY and CURRENCY_CODE columns:

CREATE TABLE countries (
      code CHARACTER(2) PRIMARY KEY,
      country VARCHAR(48) NOT NULL,
      currency_code CHARACTER(3) NOT NULL);

CREATE VIEW countries_view
   AS SELECT country, currency_code
   FROM countries;

COUNTRIES_VIEW

COUNTRY
CURRENCY_CODE
Andorra

EUR

United Arab Emirates

AED

Afghanistan

AFA

Antigua and Barbuda

XCD

Anguilla

XCD

Albania

ALL

Armenia

AMD

...

...

Similarly, a view may be created to include only the rows in COUNTRIES where US dollars are used (CURRENCY_CODE = 'USD'):

CREATE VIEW usd_countries_view
    AS SELECT coutry
    FROM countries
    WHERE cyrrency_code = `USD';
Join Views

Views may also be created to combine information from several tables - join views.

Join views can be used to present data in more natural or useful combinations than the base tables themselves provide (the optimal design of the base tables will have been governed by rules of relational database modeling).

Join views may also contain restriction conditions.

For example, the join view below presents the countries that use the euro. The CURRENCY_CODE in COUNTRIES_VIEW is linked with the CODE column in the CURRENCIES table and a restriction of CURRENCY = 'Euros' is applied:

CREATE VIEW euro_countries
   AS SELECT country
   FROM countries_view JOIN currencies
      ON countries_view.currency_code = currencies.code
   WHERE currency = 'Euros';

EURO_COUNTRIES

COUNTRY
Andorra

Austria

Belgium

Finland

France

...


Synonyms

A synonym is an alternative name for a table, view or another synonym. Synonyms can be created or dropped at any time.

A synonym cannot be created for a function, procedure or a module.

Using synonyms can be a convenient way to address tables that are contained in another schema.

For example, if a view called customer_details is contained in the schema called mimer_store, the full name of the view is mimer_store.customer_details.

This view may be referenced from the schema called mimer_store_book by its fully qualified name as given above.

Alternatively, a synonym may be created for the view in schema mimer_store_book, e.g. cust_details. Then the name cust_details can simply be used to refer to the view mimer_store.customer_details.

Note: The name cust_details is contained in schema mimer_store_book and can only be used in that context.


Primary Keys and Indexes

Rows in a base table are uniquely identified by the value of the primary key defined for the table. The primary key for a table is composed of the values of one or more columns.

Primary keys are automatically indexed to facilitate effective information retrieval.

The primary key index is the most effective access path for the table.

Other columns or combinations of columns may be defined as a secondary index to improve performance in data retrieval. Secondary indexes are defined on a table after it has been created (using the CREATE INDEX statement).

An example of when a secondary index may be useful is when a search is regularly performed on a non-keyed column in a table with many rows, defining an index on the column may speed up the search. The search result is not affected by the index but the speed of the search is optimized.

It should be noted, however, that indexes create an overhead for update, delete and insert operations because the index must also be updated.

Indexes are internal structures which cannot be explicitly accessed by the user once created. An index will be used if the internal query optimization process determines it will improve the efficiency of a search.

SQL queries are automatically optimized when they are internally prepared for execution. The optimization process determines the most effective way to execute each query, which may or may not involve using an applicable index.


Data Integrity

A vital aspect of a Mimer SQL database is data integrity. Data integrity means that the data in the database is complete and consistent both at its creation and at all times during use.

Mimer SQL has eight built-in facilities that ensure the data integrity in the database:

  • Domains
  • Unique constraints and primary keys
  • Foreign keys (also referred to as referential integrity)
  • Check constraints in table definitions
  • Check options in view definitions
  • Default values
  • Triggers
  • Transactions

These features should be used whenever possible to protect the integrity of the database, guaranteeing that incorrect or inconsistent data is not entered into it. By applying data integrity constraints through the database management system, the responsibility of ensuring the data integrity of the database is moved from the users of the database to the database designer.

Domains

Each column in a table holds data of a single data type and length, specified when the column is created or altered. The data type and length may be specified explicitly (e.g. CHARACTER(20) or INTEGER) or through the use of domains.

A domain definition consists of a data type, optional check conditions and an optional default value. Data which falls outside the constraints defined by the check conditions will not be accepted in a column which is defined as belonging to the domain.

A column belonging to a domain for which a default value is defined (unless there is an explicit default value for the column) will automatically receive that value if row data is entered without a value being explicitly specified for the column.

Unique Constraints and Primary Keys

Rows in a base table are uniquely identified by the value of the primary key defined for the table. The primary key for a table is composed of the values of one or more columns. A table cannot contain two rows with the same primary key value. If the primary key contains more than one column, the key value is the combined value of all the columns in the key. Individual columns in the key may contain duplicate values as long as the whole key value is unique.

Apart from a primary key constraint its also possible to add one or more unique constraints. The primary key constraint and the unique constraint are similar, but treat NULLs in different ways. However, the definition of the primary key is also a definition of the most effective access path for the table.

Foreign Keys - Referential Integrity

A foreign key is one or more columns in a table defined as cross-referencing the primary key or a unique key of another table. Data entered into the foreign key must either exist in the key that it cross-references or be NULL. This maintains referential integrity in the database, ensuring that a table can only contain data that already exists in the selected key of the referenced table.

As a consequence of this, a key value that is cross-referenced by a foreign key of another table must not be removed from the table to which it belongs by an update or delete operation.

The DELETE and UPDATE rules defined for the referential constraint provide a mechanism for adjusting the values in a foreign key in a way that may permit a cross-referenced key value to effectively be removed.

Note: The referential integrity constraints are effectively checked at the end of an INSERT, DELETE or UPDATE statement.

The following example illustrates the column CURRENCY_CODE in the table COUNTRIES as a foreign key referencing the primary key of the table CURRENCIES.

CREATE TABLE countries (
      code CHARACTER(2) PRIMARY KEY,
      country VARCHAR(48) NOT NULL,
      currency_code CHARACTER(3) NOT NULL,
      FOREIGN KEY (currency_code) REFERENCES currencies(code));

COUNTRIES

CODE
COUNTRY
CURRENCY_CODE
AD

Andorra

EUR

AE

United Arab Emirates

AED

AF

Afghanistan

AFA

...

...

...

CURRENCIES

CODE
CURRENCY
EXCHANGE_RATE
AED

UAE Dirhams

3.1030
AFA

Afghanis

4092.0000
ALL

Leke

122.3000
...

...

...

In this example, the referential constraint means there cannot be a currency in the COUNTRIES table that does not exist, and a currency cannot be deleted if it is assigned to a country.

Foreign key relationships are defined when a table is created using the CREATE TABLE statement and can be added to an existing table by using the ALTER TABLE statement.

The cross-referenced table must exist prior to the declaration of foreign keys on that table, unless the cross-referenced and referencing tables are the same.

The exception to this rule is when foreign key relationships are defined for tables in a CREATE SCHEMA statement. Object references in a CREATE SCHEMA statement are not verified until the end of the statement, when all the objects have been created. Therefore, it is possible to reference a table that will not be created until later in the CREATE SCHEMA statement.

Check Conditions

Check conditions may be specified in table and domain definitions to make sure that the values in a column conform to certain conditions.

Check conditions are discussed in detail in Check Constraints.

Check Options in View Definitions

You can maintain view integrity by including a check option in the view definition. This causes data entered through the view to be checked against the view definition. If the data conflicts with the conditions in the view definition, it is rejected.

For example, the restriction view USD_COUNTRIES is created with the following SQL statement:

CREATE VIEW usd_countries
   AS SELECT code, country, currency_code
   FROM countries
   WHERE currency_code = 'USD'
   WITH CHECK OPTION;

This means that the view USD_COUNTRIES contains CODE, COUNTRY and CURRENCY_CODE columns from the COUNTRIES table on the condition that the value in the CURRENCY_CODE column is USD.

Any attempt to change contents of the CURRENCY_CODE column in the view or to insert data in the view where CURRENCY_CODE does not contain USD is rejected.

If check option is not used, a user could update a row in the view that is not returned by the view.


Sequences

A sequence is a private database object that can provide a series of integer values. A sequence can be defined as unique or non-unique.

A sequence has an initial value, an increment step value and a maximum value defined when it is created (by using the CREATE SEQUENCE statement).

A unique sequence will generate a series of values that change by the increment value from the initial value to a value that does not exceed the maximum value. A unique sequence never generates the same value twice.

A non-unique sequence generates a series of values by starting at the initial value and proceeding in increment steps. If all values in a non-unique sequence has been exhausted, the sequence will start over again with the initial value.

A sequence is created with an undefined value initially.

It is possible to generate the next value in the integer series of a sequence by using the NEXT_VALUE OF sequence_name construct. This is used for the first time after the sequence has been created to establish the initial value defined for the sequence. Subsequent uses will add the increment step value to the value of the sequence and the result will be established as the current value of the sequence.

It is possible to get the value of a sequence by using the CURRENT_VALUE OF sequence_name construct. This construct cannot be used until the initial value has been established for the sequence (i.e. using it immediately after the sequence has been created will raise an error). For each new database connection, NEXT_VALUE must be used before CURRENT_VALUE can be used.

When the current value of a unique sequence is equal to the last value in the series it defines, NEXT_VALUE OF sequence_name will raise an error and the value of the sequence will remain unaltered.

If the sequence is non-unique, NEXT_VALUE OF sequence_name will always succeed. If the current value of the sequence is equal to the last value in the series it defines, the initial value of the sequence will be returned.

The value of CURRENT_VALUE OF sequence_name and NEXT_VALUE OF sequence_name can be used where a value-expression would normally be used. The value may also be used after the DEFAULT clause in the CREATE DOMAIN, CREATE TABLE and ALTER TABLE statements.

An ident must hold USAGE privilege on the sequence in order to use it.

If a sequence is dropped, with the CASCADE option in effect, all object referencing the sequence will also be dropped.

Examples:

A non-unique sequence with initial value 1, increment 3 and maximum 10 will generate the following series of values: 1, 4, 7, 10, 3, 6, 9, 2, 5, 8, 1, 4, 7...

A unique sequence with initial value 1, increment 3 and maximum 10 will generate the following series of values: 1, 4, 7, 10, 3, 6, 9, 2, 5, 8.

Note: It is possible that not every value in the series defined by the sequence will be generated. If a database server crash etc. occurs during the life of a sequence it is possible that some of the values in the series might be skipped.


Databanks

A databank is the physical file where a tables and sequences are stored. A Mimer SQL database may include any number of databanks. There are two types of databanks - system databanks and user databanks.

System Databanks

System databanks contain system information used by the database manager. These databanks are defined when the system is created.

The system databanks are:

  • SYSDB containing the data dictionary tables
  • TRANSDB used for transaction handling
  • LOGDB used for transaction logging
  • SQLDB used in transaction handling and for temporary storage of internal work tables.

User Databanks

User databanks contain the user tables and sequences. These databanks are defined by the user(s) responsible for setting up the database. See the Mimer SQL Reference Manual, Specifying the Location of User Databanks, for details concerning path names.

The division of tables between different user databanks is a physical file storage issue and does not affect the way the database contents are presented to the user. Except in special situations (such as when creating tables), databanks are completely invisible to the user.

Note: In Mimer SQL, backup and restore can be performed on a per-databank basis rather than on the entire database file base, see the Mimer SQL System Management Handbook, Backing-up and Restoring Data for more information.


Shadows

Mimer SQL Shadowing can be used to create and maintain one or more copies of a databank on different disks. Shadowing provides extra protection from the consequences of disk crashes, etc.

Read more in the Mimer SQL System Management Handbook, Mimer SQL Shadowing.


Mimer SQL Character Sets

For character data, Mimer SQL uses the character set ISO 8859-1, also known as the Latin1 character set. By default, character data is sorted in the numerical order of its code according to the ISO8BIT collation.

For national character data, Mimer SQL uses the Unicode character set, which is a universal character set, see http://www.unicode.org for more information. National character data is sorted according to the UCS_BASIC collation. UCS_BASIC is a collation in which the ordering is determined entirely by the Unicode scalar values of the characters in the strings being sorted.

See the Mimer SQL Reference Manual, Appendix B, Character Sets for more information.


Collations and Linguistic Sorting

As stated in the previous section, character and national character data is sorted according to specific collations.

A collation, also known as a collating sequence, is a database object containing a set of rules that determines how character strings are compared, searched and alphabetically sorted. The rules in the collation determine whether one character string is less than, equal to or greater than another. A collation also determines how case-sensitivity and accents are handled.

You can specify a different collation for ordering characters when you create or alter a table or create a domain.

If you have specified a collation for a column, the collation is used implicitly in SQL statements.

You only need to explicitly use a collate clause in SQL statements if you want to override the default collation or the collation you specified when creating or altering the column or creating the domain.

For more information, see Collations.

Since Unicode is a universal character set the Unicode sorting order can be employed on any arbitrary character set that is a subset of Unicode.

The default Unicode sorting order is provided in http://www.unicode.org/reports/tr10/allkeys.txt (Unicode 3.1.1 mapping). This table (the Default Unicode Collation Element Table) provides a mapping from characters to collation elements for all the explicitly weighted characters.


Stored Procedures

In Mimer SQL you can define functions, procedures and modules, collectively known as stored procedures.

Mimer SQL stored procedures enable you to define and use powerful functionality through the creation and execution of routines. By using stored procedures, you can move application logic from the client to the server, thereby reducing network traffic. This will also allow the logic to be shared between different applications.

Stored procedures are stored in the data dictionary and you can invoke them when needed.

For a complete and detailed discussion of stored procedures, see Mimer SQL Reference Manual, Functions and the Point Display.

Stored procedures execute their statements using the user context of the creator of the stored procedure, independent of the actual current user.

Routines - Functions and Procedures

The term routine is a collective term for functions and procedures. Functions are distinguished from procedures in that they return a single value and the parameters of a function are used for input only. A function is invoked by using it where a value expression would normally be used.

Mimer SQL supports standard procedures and also result set procedures, which are procedures capable of returning the row value(s) of a result-set.

Standard procedures are invoked directly by using the CALL statement and can pass values back to the calling environment through the procedure parameters.

A result set procedure is invoked by using the CALL statement, and the result-set values are presented in the same way as for a SELECT statement.

In Embedded SQL, ODBC and JDBC, result set procedures are invoked by declaring a cursor which includes the procedure call specification and by then using the FETCH statement to execute the procedure and return the row(s) of the result-set.

The creator of a routine must hold the appropriate access rights on any database objects referenced from within the routine. These access rights must be held for the life of the routine.

Routine names, like those of other private objects in the database, are qualified with the name of the schema to which they belong.

Modules

A module is simply a collection of routines. All the routines in a module are created when the module is created and belong to the same schema.

If a module is dropped, all the routines contained in the module are dropped.

Under certain circumstances a routine may be dropped because of the cascade effect of dropping some other database object. If such a routine is contained in a module, it is implicitly removed from the module and dropped. The other routines contained in the module remain unaffected.

In general, care should be taken when using DROP or REVOKE in connection with routines, modules or objects referenced from within routines because the cascade effects can often affect many other objects. See Dropping Objects from the Database and Recursive Effects of Revoking Privileges for details.

Triggers

A trigger defines a number of procedural SQL statements that are executed whenever a specified data manipulation statement is executed on the table or view on which the trigger has been created.

The trigger can be set up to execute AFTER, BEFORE or INSTEAD OF the data manipulation statement. Trigger execution can also be made conditional on a search condition specified as part of the trigger.

Triggers are described in detail in the Point Display.


Idents

An ident is an authorization-id used to identify users, programs and groups. There are four types of idents in a Mimer SQL database: USER, OS_USER, PROGRAM, and GROUP idents.

USER Idents

USER idents identify individual users who can connect to a Mimer SQL database.

A USER's access to the database is protected by a password and is restricted by the specific privileges granted to the ident. USER idents are generally associated with specific physical individuals who are authorized to use the system.

OS_USER Idents

OS_USER idents are idents which reflect a user id defined by the operating system.

An OS_USER ident allows the user currently logged in to the operating system to access the Mimer SQL database without providing a user name or password.

For example: if the current operating system user is ALBERT and there is an OS_USER ident called ALBERT defined in Mimer SQL, ALBERT may start Mimer BSQL (for example) and connect directly to Mimer SQL simply by pressing <return> at the Username: prompt.

If an OS_USER ident is defined with a password in Mimer SQL, the ident may also connect to Mimer SQL in the same way as a USER ident (i.e. by providing the user name and password). An OS_USER ident may not have the same name as a USER ident in the database.

PROGRAM Idents

PROGRAM idents can be used by an application for effective administration of access rights and authorization control.

PROGRAM idents do not strictly connect to Mimer SQL, but they may be entered from within an application program by using the ENTER statement.

The ENTER statement may only be used by an ident who is already connected to a Mimer SQL database.

An ident is granted the privilege to enter a PROGRAM ident. A PROGRAM ident is set up to have certain privileges and these apply after the ENTER statement has been used. PROGRAM idents are generally associated with specific functions within the system, rather than with physical individuals.

The LEAVE statement is used to return to the state of privileges and database access that existed before PROGRAM was used.

GROUP Idents

GROUP idents are collective identities used to define groups of user and/or program idents.

Any privileges granted to or revoked from a GROUP ident automatically apply to all members of the group. Any ident can be a member of as many groups as required, and a group can include any number of members.

GROUP idents provide a facility for organizing the privilege structure in the database system. All idents are automatically members of the group PUBLIC. When a privilege is granted to PUBLIC, all users receive the privilege.


Schemas

A schema defines a local environment within which private database objects can be created. The ident creating the schema has the right to create objects in it and to drop objects from it.

When a USER, OS_USER or PROGRAM ident is created, a schema with the same name usually is created and the created ident becomes the creator of the schema. This happens by default unless WITHOUT SCHEMA is specified in the CREATE IDENT statement. A user without a schema is not allowed to create any database objects at all.

When a private database object is created, the name for it can be specified in a fully qualified form which identifies the schema in which it is to be created. The names of objects must be unique within the schema to which they belong, according to the rules for the particular object-type.

If an unqualified name is specified for a private database object, a schema name equivalent to the name of the connected ident is assumed.


Access Rights and Privileges

Privileges control how users may access database objects and the operations they can perform in the database.

USER and PROGRAM idents are protected by a password, which must be given together with the correct ident name in order for a user to gain access to the database or to enter a PROGRAM ident. Passwords are stored in encrypted form in the data dictionary and cannot be read by any ident, including the system administrator. An ident's password may only be changed by the ident or by the creator of the ident.

A set of privileges define the operations each ident is permitted to perform. There are three classes of privileges in a Mimer SQL database: system, object and access.

System Privileges

System privileges, which control the right to perform backup and restore operations, the right to execute the UPDATE STATISTICS statement as well as the right to create new databanks, idents, schemas and to manage shadows.

System privileges are granted to the system administrator when the system is installed and may be granted by the administrator to other idents in the database. As a general rule, system privileges should be granted to a restricted group of users.

Note: An ident who is given the privilege to create new idents is also able to create new schemas.

Object Privileges

Object privileges, which control membership in GROUP idents, the right to invoke functions and procedures, the right to enter PROGRAM idents, the right to create new tables in a specified databank and the right to use a domain or sequence.

The creator of an object is automatically granted full privileges on that object.

Thus the creator of:

  • a group is automatically a member of the group
  • a function or procedure may execute it
  • a PROGRAM ident may enter it
  • a schema may create objects in and drop objects from it
  • a databank may create tables and sequences in the databank
  • a table holds all privileges on the table
  • a domain may use that domain
  • a sequence may use that sequence.

The creator of an object generally has the right to grant any of these privileges to other users, in the case of functions and procedures this actually depends on the creator's privileges on objects referenced from within the routine.

Access Privileges

Access privileges, which define access to the contents of the database, i.e. the rights to retrieve data from tables or views, delete data, insert new rows, update data and to refer to table columns as foreign key references.

About Privileges

Granted privileges can be regarded as instances of grantor/privilege stored for an ident. An ident will hold more than one instance of a privilege if different grantors grant it.

A privilege will be held as long as at least one instance of that privilege is stored for the ident. All privileges may be granted with the WITH GRANT OPTION which means that the receiver has, in turn, the right to grant the privilege to other idents. An ident will hold a privilege with the WITH GRANT OPTION as long as at least one of the instances stored for the ident was granted with this option.

If the same grantor grants a privilege to an ident more than once, this will not result in more than one instance of the privilege being recorded for the ident. If a particular grantor grants a privilege without WITH GRANT OPTION and subsequently grants the privilege again with WITH GRANT OPTION, then WITH GRANT OPTION will be added to the existing instance of the privilege.

Each instance of a privilege held by an ident is revoked separately by the appropriate grantor. It is possible to revoke WITH GRANT OPTION without revoking the associated privilege completely. Revoking Privileges describes revoking privileges in more detail.


The Data Dictionary

The data dictionary contains information on all the database objects stored in a Mimer SQL database and how they relate to one another.

The data dictionary stores information about:

  • Databanks, see Databanks
  • Idents, see Idents
  • Schemas, see Schemas
  • Tables and Views, see Tables
  • Indexes, see Primary Keys and Indexes
  • Functions and procedures, see Stored Procedures
  • Modules, see Modules
  • Synonyms, see Synonyms
  • Triggers, see Triggers
  • Shadows, see Shadows
  • Sequences, see Sequences
  • Collations, see Collations and Linguistic Sorting
  • Domains, see Domains
  • Statements, see
  • Access rights and privileges, see Access Rights and Privileges.


Mimer SQL Statements

Mimer SQL is a language made up of a number of different statements, which may be divided into the following basic categories:

  • Data definition statements
  • Access Control Statements
  • Data manipulation statements
  • Connection statements
  • Transaction control statements
  • Database administration statements

The SQL statements are described in detail in subsequent chapters of this manual and in the Mimer SQL Reference Manual, SQL Statement Descriptions .

In addition, there is a set of commands specific to the BSQL environment, for managing output formatting and so on, see Mimer BSQL.

Note: In Mimer BSQL, statements are terminated by a semicolon (;). This is not part of the SQL statement syntax, but is included in the examples in this manual.

Data Definition Statements

Data definition statements are used to maintain objects in a database. For example:

  • CREATE, creates objects
  • ALTER, modifies objects
  • DROP, drops objects
  • COMMENT, documents objects.

Access Control Statements

Access Control Statements are used to manage privileges. For example:

  • GRANT grants privileges
  • REVOKE revokes privileges.

Data Manipulation Statements

Data manipulation statements are used to examine and change data in the database. For example:

  • SELECT retrieves data
  • INSERT adds new rows to tables
  • UPDATE changes data in existing rows
  • DELETE deletes data
  • CALL executes routines
  • SET value assignment.

Connection Statements

Connection statements are used to connect and disconnect user and program idents to or from the database. For example:

  • CONNECT connects a user ident to the database
  • DISCONNECT disconnects a user ident from the database
  • SET CONNECTION changes the active database connection
  • ENTER enters a PROGRAM ident
  • LEAVE leaves a PROGRAM ident.

Transaction Control Statements

Transaction control statements are used to control when database transactions begin and end, and when updates take effect. For example:

  • SET TRANSACTION sets transaction options for subsequent transactions
  • SET SESSION sets the default transaction options for the session
  • START starts a transaction build-up
  • COMMIT commits the current transaction
  • ROLLBACK abandons the current transaction.

Database Administration Statements

Database administration statements are used to manage backup/restore operations and the statistical information used to optimize queries. For example:

  • CREATE BACKUP creates a backup copy of a databank, with an optional incremental backup. Incremental backups may also be taken on their own with the statement CREATE INCREMENTAL BACKUP
  • ALTER DATABANK, the RESTORE variant of this statement recovers a databank from incremental backup information
  • SET DATABASE sets the database ONLINE or OFFLINE
  • SET DATABANK sets a databank ONLINE or OFFLINE
  • SET SHADOW sets one or more shadows ONLINE or OFFLINE
  • UPDATE STATISTICS updates the statistical information used for query optimization. DELETE STATISTICS deletes the statistical information.


Managing Database Connections

An application gains access to a Mimer SQL database by establishing a connection to it.

A program may have several database connections established simultaneously. Mimer SQL supports the ability to switch between different connections (i.e. accessing different databases) from within the same application program. Only one database connection is active at any one time.


Connecting to a Database

Only idents of type USER and OS_USER can be used to connect to a Mimer SQL database. A connection is established using a CONNECT statement. For example embedded SQL has the following form:

CONNECT TO 'DATABASE' [AS 'CONNECTION_NAME']
USER 'USER_NAME' USING 'password';

This statement establishes a connection between the user and a database, see the Mimer SQL Reference Manual, CONNECT, for details.

A connection may be established to any local or remote database, which has been made accessible from the current machine - see the Mimer SQL System Management Handbook, Registering the Database, for details. The database can be specified by name or by using the keyword DEFAULT.

Note: If the keyword DEFAULT is used, a user and password cannot be specified. If you wish to connect to the default database and specify a user and password, specify an empty string ('') for the database.

The database may be given an explicit connection name for use in DISCONNECT and SET CONNECTION statements. If no explicit connection name is specified, the database name is used as the connection name.

Changing Connections

An application program may make multiple connections to the same or different databases using the same or different idents, provided that each connection is identified by a unique connection name. In this situation only one connection is active and the other connections are inactive. A connection established by a successful CONNECT statement is automatically active.

A connection may be made active by the SET CONNECTION statement.

For example:

SET CONNECTION 'CONNECTION_NAME';

Disconnecting

The DISCONNECT statement breaks the connection between the user and a database. The connection to be broken is specified as the connection name or as one of the keywords ALL, CURRENT or DEFAULT.

DISCONNECT 'CONNECTION_NAME';

A connection does not have to be active in order to be disconnected. If an inactive connection is broken, the application still has uninterrupted access to the database through the current (active) connection, but the broken connection is no longer available for activation with SET CONNECTION.

If the active connection is broken, the application program cannot access any database until a new CONNECT or SET CONNECTION statement is issued.

Note: The distinction between breaking a connection with DISCONNECT and making a connection inactive by issuing a CONNECT or SET CONNECTION for a different connection is, a broken connection has no saved resources and cannot be reactivated by SET CONNECTION.

The table below summarizes the effect on the connection con1 of CONNECT, DISCONNECT and SET CONNECTION statements depending on the state of the connection.

Statement
con1 non-existent
con1 current
con1 inactive

CONNECT TO 'DB1' AS 'CON1'

con1 current

error - connection already exists

error - connection already exists

DISCONNECT 'CON1'

error - connection doesn't exist

con1 disconnected

con1 disconnected

SET CONNECTION 'CON1'

error - connection doesn't exist

ignored

con1 made current

CONNECT TO 'DB2' AS 'CON2'

-

con1 made inactive

con1 unaffected

DISCONNECT 'CON2'

-

con1 unaffected

con1 unaffected

SET CONNECTION 'CON2'

-

con1 made inactive

con1 unaffected


PROGRAM Idents - ENTER and LEAVE

PROGRAM idents may be entered from within an SQL session by using the ENTER statement. The current user must have EXECUTE privilege on the PROGRAM ident in order to perform an ENTER.

When a PROGRAM ident is entered, any privileges granted to that ident become current and privileges belonging to the previous ident (i.e. the ident issuing the ENTER statement) are suspended.

PROGRAM idents are disconnected with the LEAVE statement.

Example:
ENTER 'PROGRAM_NAME' USING 'secret';

LEAVE RETAIN;

The statements ENTER and LEAVE may not be issued within transactions, see Managing Transactions.


Retrieving Data

This chapter describes how to retrieve information from a Mimer SQL database. In a relational database, information retrieved from one or more source tables is returned in the form of a result table, also called a result-set.

The statement used to retrieve information is SELECT.

The examples in this chapter are based on the example database included with the Mimer SQL Engine distribution, see Appendix A The Example Environment.


Simple Retrieval

The simplest retrievals fetch information from one table.

The general form of the simple SELECT statement is:

SELECT column-list FROM table_name WHERE condition;

The column-list specifies which columns to select, and the WHERE condition determines which rows to select. If no WHERE condition is specified, all rows are retrieved from the source table or view.

Examples of Simple Retrieval

Find the format identifiers and their meaning:
SELECT format_id, format
   FROM formats;
Returns:

FORMAT_ID
FORMAT
1
Audio CD
2
Cassette
3
DVD Audio
4
Vinyl
5
Audio Cassette
6
Audio CD
7
Hardcover
8
Paperback
9
DVD Video
10
Video

Find the name and code for all countries that use Australian dollars (AUD).
SELECT country, code
   FROM countries
   WHERE currency_code = 'AUD';
Returns:
COUNTRY

CODE

Australia

AU

Cocos (Keeling) Islands

CC

Christmas Island

CX

Heard and McDonald Islands

HM

Kiribati

KI

Norfolk Island

NF

Nauru

NR

Tuvalu

TV

How to formulate selection conditions is described in detail in Selecting Specific Rows.


Result Order

The columns in the result table are ordered as they are written in the SELECT statement, irrespective of the ordering in the source table. For example:

SELECT format, format_id
   FROM formats;
Returns:

FORMAT
FORMAT_ID
Audio CD
1
Cassette
2
DVD Audio
3
Vinyl
4
Audio Cassette
5
Audio CD
6
Hardcover
7
Paperback
8
DVD Video
9
Video
10

Quick Select

A shorthand form for selecting all columns from a table is:

SELECT * FROM table_name ...

In this case, the columns in the result table are ordered as they are defined in the source table.


Table Names

Any table name in a SELECT statement may be qualified by the name of the schema to which the table belongs in the form schema.table.

Unqualified table names are implicitly qualified by the ident name of the current user.

The table name must be written in its qualified form if the name of the schema to which the table belongs differs from the name of the current user.

For example:
SELECT *
   FROM mimer_store.formats;
Returns:
FORMAT_ID
FORMAT
CATEGORY_ID
DISPLAY_ORDER
1
Audio CD
1
20
2
Cassette
1
30
3
DVD Audio
1
10
4
Vinyl
1
40
5
Audio Cassette
2
40
6
Audio CD
2
30
7
Hardcover
2
10
8
Paperback
2
20
9
DVD Video
3
10
10
Video
3
20


Setting Column Labels

Columns in the result table normally have the same name as the corresponding columns in the source table.

By using an AS clause after the column name in the SELECT statement, the column in the result table can be given an alternative name.

AS clauses can be used for as many columns as required. A label may be up to 128 characters long, and follows the same syntax rules as column names, see the Mimer SQL Reference Manual, SQL Identifiers.

For example:
SELECT format AS format_name, category_id
   FROM formats;
Returns:
FORMAT_NAME
CATEGORY_ID
Audio CD
1
Cassette
1
DVD Audio
1
Vinyl
1
Audio Cassette
2
Audio CD
2
Hardcover
2
Paperback
2
DVD Video
3
Video
3

Labels are particularly useful in queries that retrieve computed values, where the result table column is otherwise unnamed, see Retrieving Computed Values.


Eliminating Duplicate Values

The simple SELECT statement retrieves all rows which fulfill the selection conditions. The result may contain duplicate values.

For example:
SELECT category_id
   FROM formats;
Returns:

CATEGORY_ID
1
1
1
1
2
2
2
2
3
3

By adding the keyword DISTINCT before the column list you can eliminate all duplicate rows from the result table.

The keyword DISTINCT may only be used once in a simple SELECT statement.

For example:
SELECT DISTINCT category_id
   FROM formats;
Returns:

CATEGORY_ID
1
2
3

DISTINCT also eliminates duplicate rows containing NULL values, although technically NULL is not regarded as equal to NULL, see Handling NULL Values.

If the selected columns include the whole primary key in the source table, the keyword DISTINCT is unnecessary, since all rows in the result table will be unique. Remember however that a view may contain duplicate rows, so that selecting all columns does not always guarantee that the result does not contain duplicate rows.


Selecting Specific Rows

Rows are selected in the SELECT statement according to the search condition in the WHERE clause. This condition relates column value(s) to expressions.

Comparison Conditions and WHERE

Comparison operators that may be used in the WHERE clause are:

Operator
Explanation
=

equal to

<>

not equal to

<

less than

<=

less than or equal to

>

greater than

>=

greater than or equal to

Comparisons can be combined in the search condition using the logical operators AND and OR, and reversed using NOT.

Each comparison must be expressed in full; for example

WHERE price >= 10.00 AND price <= 20.00

may not be expressed as

WHERE price >= 10.00 AND <= 20.00
Comparing Character Strings

Character strings are compared character by character from left to right.

If strings are of different lengths, the shorter is conceptually padded to the right with blanks before the comparison is made (i.e. character difference takes precedence over length difference).

The default collation for characters is an extended Latin1 character set as defined by ISO 8859-1, see the Mimer SQL Reference Manual, Appendix B, Character Sets for the exact sequence.

For more information on collations, see the Mimer SQL User's Manual, Collations.

Retrieve the European Article Number (EAN), price and number in stock for all available items costing 100 euros and more:
SELECT ean_code, price, stock
    FROM items
    WHERE status = 'A'
    AND price >= 100.00;
Returns:

EAN_CODE
PRICE
STOCK
790051157548
115.98
14
790051155506
279.98
16
790051595920
227.98
10

Comparing Temporal Data

When stating conditions on temporal data in tables, datetime and interval literals can be specified. There are also the pseudo literals CURRENT_DATE, LOCALTIME and LOCALTIMESTAMP which read the server clock and return that value.

If there is more than one occurrence of these pseudo literals in a statement the clock is only read once.

List the EAN and price for any items released on the 5th September 1994:
SELECT ean_code, price
   FROM items
   WHERE release_date = DATE'1994-09-05';
Returns:

EAN_CODE
PRICE
9780001006041
7.00

Retrieve the EAN and price for any items with a release date in the future:
SELECT ean_code, price
   FROM items
   WHERE release_date > CURRENT_DATE;
Returns:

EAN_CODE
PRICE
7298976754871
13.98
7464376662256
15.98
9781990789861
13.99
9781993789639
6.99

For an example of interval literals, see Datetime Arithmetic and Functions.

Pattern Conditions

LIKE is used to search for character strings that match a specified pattern.

Patterns in the LIKE condition are written with wildcard characters (also called meta-characters):

Pattern
Explanation

_

(underscore) stands for any single character

%

stands for any sequence of zero or more characters

(Wildcards only have significance in LIKE predicates.)

Find all currencies whose names include the string "Islands":
SELECT currency
   FROM currencies
   WHERE currency LIKE '%Islands%';
Returns:
CURRENCY

Falkland Islands Pounds

Cayman Islands Dollars

Solomon Islands Dollars
Find all formats whose names do not contain the string "Audio":
SELECT format
   FROM formats
   WHERE format NOT LIKE '%Audio%';
Returns:
FORMAT

Cassette

Vinyl

Hardcover

Paperback

DVD Video

Video

Remember that character strings in Mimer SQL statements are always written within single quotation marks (' ').

A LIKE predicate where the pattern string does not contain any wildcard characters is essentially equivalent to a basic predicate using the '=' operator, except that comparison strings in an '='comparison are conceptually padded with blanks whereas those in the LIKE comparison are not.

For example:
'Dollars ' =    'Dollars'  is true
'Dollars ' LIKE 'Dollars ' is true
'Dollars ' LIKE 'Dollars%' is true

but

'Dollars ' LIKE 'Dollars' is false

The LIKE predicate may include an ESCAPE clause defining a character which is used to `escape' wildcard characters. A wildcard character immediately following an escape character is taken at face value. See the Mimer SQL Reference Manual, The LIKE Predicate, for more details.

More about Searching for Character Strings

Some other examples of searching for character strings are:

LIKE '%P%'

matches any string that contains an upper-case `P'.

LIKE '%P%' COLLATE english_1

matches any string that contains an upper or lower case `P'.

LIKE '_abc'

matches any four letter character string ending with lower-case `abc'.

LIKE '%A\%' ESCAPE '\'

matches any string ending with `A%'.

LIKE 'D_d_'

matches any four letter string with D and d in the first and third positions respectively. Examples of possible values: Dude, Dads.

Set Conditions

IN and Not IN

The operator IN finds the values that are contained in a set of values. The set is given as a comma-separated list enclosed in parentheses.

NOT IN finds values which are not contained in the specified set.

Which currencies are represented by the codes "SEK" or "GBP"?
SELECT currency
   FROM currencies
   WHERE code IN ('SEK', 'GBP');
Returns:

CURRENCY

Pounds Sterling

Swedish Kronor

List all the formats other than those for identifiers 1, 5 and 7:
SELECT format, format_id
   FROM formats
   WHERE format_id NOT IN (1, 7, 5);
Returns:

FORMAT
FORMAT_ID
Cassette
2
DVD Audio
3
Vinyl
4
Audio CD
6
Paperback
8
DVD Video
9
Video
10

BETWEEN and NOT BETWEEN

The operators BETWEEN and NOT BETWEEN are used to find values that are within or outside an interval. The interval includes the limits specified in the BETWEEN condition.

Find the EAN and release date for EANs outside the "Bookland" range (e.g. 978 prefix) that were released during January 1998:
SELECT ean_code, release_date
   FROM items
   WHERE ean_code NOT BETWEEN 9780000000000 AND 9789999999999
   AND release_date BETWEEN DATE'1998-01-01' AND DATE'1998-01-31';
Returns:
EAN_CODE
RELEASE_DATE
90431587720
1998-01-05
93624662426
1998-01-13
45778040629
1998-01-20

BETWEEN may also be used for character comparisons. Strings are compared character by character from left to right.

For example:
SELECT code, country
   FROM countries
   WHERE country BETWEEN 'South Africa' AND 'Suriname';
Returns:

CODE

CURRENCY

ES

Spain

LK

Sri Lanka

SD

Sudan

SR

Suriname

ZA

South Africa

Find which currencies have an exchange rate in the range of 1 to 2 to the euro:
SELECT currency
   FROM currencies
   WHERE exchange_rate BETWEEN 1.00 AND 2.00;
Returns:

CURRENCY

Netherlands Antillian Guilders

Australian Dollars

Aruban Guilders

Convertible Marka

Barbados Dollars

Leva

...


Retrieving Computed Values

You can retrieve computed values by using arithmetic and string operators in the SELECT clause of the statement.

When retrieving computed values, parentheses can be used to force the operation priority.

Without parentheses, the normal precedence rules for arithmetic apply, i.e. multiplication and division are performed before addition and subtraction, and operators with the same precedence are evaluated from left to right.

The following computational operators may be used:

Operator
Explanation
+

addition

-

subtraction

*

multiplication

/

division

||

string concatenation

See the Mimer SQL Reference Manual, SQL Syntax Elements, for information regarding the type and precision of the result of an arithmetic expression.

Show the exchange rate for the US Dollar if there was a -10% change:
SELECT exchange_rate, exchange_rate * 0.90
   FROM  currencies
   WHERE code = 'USD';
Returns:

EXCHANGE_RATE
0.8711
0.783990

Labels and Computed Values

The computed column is unnamed by default in the result table. A label may be used to provide a name.

For example:
SELECT exchange_rate, exchange_rate * 0.90 AS new_exchange_rate
   FROM currencies
   WHERE code = 'USD';
Returns:

EXCHANGE_RATE
NEW_EXCHANGE_RATE
0.8711
0.783990

Constant Values

A column may also be computed as a constant value, which adds an extra column to the result table.

For example:
SELECT exchange_rate, '10% reduction:',
       exchange_rate * 0.90 AS new_exchange_rate
   FROM currencies
   WHERE code = 'USD';
Returns:

EXCHANGE_RATE

NEW_EXCHANGE_RATE
0.8711
10% reduction:
0.783990

You may also retrieve a value computed using the values in two or more columns, providing that the data types are compatible.

Retrieve the currencies prefixed with the word "Currency:":
SELECT 'Currency: ' || currency
   FROM currencies
   WHERE code LIKE 'A%';
Returns:

Currency: UAE Dirhams

Currency: Afghanis

Currency: Leke

Currency: Armenian Drams

Currency: Netherlands Antillian Guilders

Currency: Kwanza

...

Padding Concatenated Strings

For string concatenation, column values are padded with trailing blanks to the length of the column definition, if the column data type is fixed-length (CHARACTER or NATIONAL CHARACTER).

For example:
SELECT currency || 'Currency'
   FROM currencies
   WHERE code LIKE 'A%';
Returns:

UAE Dirhams                      Currency

Afghanis                         Currency

Leke                             Currency

Armenian Drams                   Currency

Netherlands Antillian Guilders   Currency

Kwanza                           Currency

...

(If the column data type is variable length, i.e. VARCHAR or NCHAR VARYING, no blank padding is performed.)


Using Scalar Functions

Scalar functions operate on expressions or on a single value received from a SELECT statement.

Some of the scalar functions available are:

Scalar function
Description

CHAR_LENGTH

returns the length of a string.

EXTRACT

returns a single field from a DATETIME or INTERVAL value.

LOWER

converts all upper case letters in a character string to lower case.

POSITION

returns the starting position of the first occurrence of a specified string expression, starting from the left, in the given character string.

SOUNDEX

returns a character string containing six digits which represents an encoding of the sound of the given character string.

SUBSTRING

extracts a substring from a given string, according to specified start position and length of the substring.

TRIM

removes leading and/or trailing instances of a specified character from a string.

UPPER

converts all lower case letters in a character string to upper case.

The complete list of scalar functions can be found in the Mimer SQL Reference Manual, About Scalar Functions.

Examples of Scalar Functions

The following are examples that illustrate how the scalar functions may be used:

List all currencies that contain the letters "AU" in upper or lower case:
SELECT currency
   FROM currencies
   WHERE LOWER(currency) LIKE '%au%';
Returns:

CURRENCY

Australian Dollars

Mauritius Rupees

Saudi Riyals

Note: Alternatively, a case insensitive collation can be used to get the same result.

SELECT currency

FROM currencies

WHERE currency COLLATE english_1 LIKE '%au%';

Find the position of the first space character in the formats column:
SELECT format, POSITION(' ' IN format)
   FROM formats;
Returns:

FORMAT

Audio CD
6
Cassette
0
DVD Audio
4
Vinyl
0
Audio Cassette
6
Audio CD
6
Hardcover
0
Paperback
0
DVD Video
4
Video
0

Append the word "Currency" to the currencies (without trailing spaces):
SELECT TRIM(TRAILING FROM currency) || ' Currency'
   FROM currencies
   WHERE code LIKE 'A%';
Returns:

UAE Dirhams Currency

Afghanis Currency

Leke Currency

Armenian Drams Currency

Netherlands Antillian Guilders Currency

Kwanza Currency

...

Remove both leading and trailing spaces from the currencies and convert to uppercase; and get the significant length (in characters):
SELECT UPPER(TRIM(currency)), CHAR_LENGTH(TRIM(currency))
      FROM currencies;
Returns:

UAE DIRHAMS
11
AFGHANIS
8
LEKE
4
ARMENIAN DRAMS
14
NETHERLANDS
ANTILLIAN GUILDERS
30
KWANZA
6
...
...

Find the country that sounds the same as "ASTRALYA":
SELECT country
   FROM countries
   WHERE SOUNDEX(country) = SOUNDEX('astralya');
Returns:

COUNTRY

Australia

Extract the first 5 characters from each format:
SELECT SUBSTRING(format FROM 1 FOR 5)
   FROM formats;
Returns:

FORMAT

Audio

Casse

DVD A

Vinyl

Audio

Audio

Hardc

Paper

DVD V

Video


Using the CASE Expression

With a CASE expression, it is possible to specify a conditional value. Depending on the result of one or more conditional expressions, the CASE expression can return different values.

The rules for CASE expressions are fully described in the Mimer SQL Reference Manual, CASE Expression.

Case Expression Examples

The following select statements presents two examples of how CASE expressions can be used.

Simple Case Expression
Give a textual description to the DISPLAY_ORDER column and display them in the numeric order:
SELECT category_id,
       CASE display_order
          WHEN 10 THEN 'FIRST'
          WHEN 20 THEN 'SECOND'
          WHEN 30 THEN 'THIRD'
          WHEN 40 THEN 'FOURTH'
          ELSE 'UNKNOWN'
       END,
       format
   FROM formats
   ORDER BY category_id, display_order, format;
Returns:

CATEGORY_ID

FORMAT
1
FIRST

DVD Audio
1
SECOND

Audio CD
1
THIRD

Cassette
1
FOURTH

Vinyl
2
FIRST

Hardcover
2
SECOND

Paperback
2
THIRD

Audio CD
2
FOURTH

Audio Cassette
3
FIRST

DVD Video
3
SECOND

Video

This form of a case expression is known as a simple case expression, in which an operand is compared to a list of values.

If there is a match in one of the when clauses, the result is the value to the right of the then clause.

If none of these matches, the value in the else clause is returned.

If there is no else clause in a case expression and no when clause matches, a null value is returned.

Case Expression

The other form of the case expression can be seen in the following example.

Display the word "UNKNOWN" if the EXCHANGE_RATE value is undefined (i.e. NULL); and display the word "PARITY" if the rate is 1 to 1 against the euro; otherwise do not display anything:
SELECT currency,
       CASE
          WHEN exchange_rate IS NULL THEN 'UNKNOWN'
          WHEN exchange_rate = 1.0 THEN 'PARITY'
          ELSE ''
       END
   FROM currencies
   WHERE code LIKE 'A%';
Returns:

CURRENCY

UAE Dirhams

Afghanis

Leke

Armenian Drams

UNKNOWN

Netherlands Antillian Guilders

Kwanza

UNKNOWN

...

...

In this form it is possible that more than one of the when clauses evaluates to true, in which case the value in the first (from left) of the matching clauses is returned.


Using the CAST Specification

The CAST specification explicitly converts data of one data type to another data type.

Conversion between data types is allowed if the rules for assignment to the target data type are not violated. See the Mimer SQL Reference Manual, CAST Specification for more information.

Show the exchange rate for the US Dollar with a -10% change, force the result to four decimal places:
SELECT CAST(exchange_rate * 0.90 AS DECIMAL(12, 4))
   FROM currencies
   WHERE code = 'USD';
Returns:

0.7839

Retrieve the EAN, price and release date for any items with a release date in the future. Convert the release date (DATE'YYYY-MM-DD') to character with the format
DD/MM/YY:
SELECT ean_code, price,
       SUBSTRING(CAST(release_date AS CHAR(26)) FROM 14 FOR 2)
       || '/' ||
       SUBSTRING(CAST(release_date AS CHAR(26)) FROM 11 FOR 2)
       || '/' ||
       SUBSTRING(CAST(release_date AS CHAR(26)) FROM 8 FOR 2) AS date
   FROM items
   WHERE release_date > CURRENT_DATE;
Returns:

EAN_CODE
PRICE
DATE
7298976754871
13.98
16/03/02
7464376662256
15.98
23/03/02
9781990789861
13.99
19/03/02
9781993789639
6.99
15/03/02


Datetime Arithmetic and Functions

It is possible to use datetime and interval values in expressions to calculate new datetime and interval values.

Valid operations are:

  • addition or subtraction between an interval value and a datetime value
  • subtracting a datetime from another datetime value
  • adding or subtracting two interval values
  • multiplying or dividing an interval by a numerical value

The first of these operations yields a datetime value while the others result in an interval value.

Retrieve the EAN, price and the number of days to the release date for any items with a release date in the future:
SELECT ean_code, price,
       (release_date - CURRENT_DATE) DAY(3) AS days
   FROM items
   WHERE release_date > CURRENT_DATE;
Returns:

EAN_CODE
PRICE
DAYS
7298976754871
13.98
5
7464376662256
15.98
12
9781990789861
13.99
8
9781993789639
6.99
4

When taking the difference between two datetime values it is necessary to specify the type of the resulting interval.

It is also possible to specify the precision of the interval as shown in the above example; the default precision for day is 2.

Retrieve the EAN, price and the release date for any items with a release date in the next 100 hours:
SELECT ean_code, price, release_date
   FROM items
   WHERE CAST(release_date AS TIMESTAMP)
         BETWEEN LOCALTIMESTAMP
         AND LOCALTIMESTAMP + INTERVAL '100' HOUR(3);
Returns:

EAN_CODE
PRICE
RELEASE_DATE
9781993789639
6.99
2002-03-15

About Intervals

SQL distinguishes between YEAR-MONTH (long) intervals and DAY-TIME (short) intervals.

YEAR-MONTH intervals are: YEAR, MONTH and YEAR TO MONTH.

DAY-TIME intervals are: DAY, HOUR, MINUTE, SECOND, HOUR TO MINUTE, HOUR TO SECOND, MINUTE TO SECOND, DAY TO HOUR, DAY TO MINUTE and DAY TO SECOND.

Long intervals may be compared to other long intervals, and short intervals may be compared to other short intervals, but short and long intervals are not comparable.

Extracting Values

It is possible to extract part of a datetime value with the EXTRACT function. The function returns a numeric value.

Extract the month and year for any items with a release date in the next 4 days:
SELECT CASE EXTRACT(MONTH FROM release_date)
          WHEN  1 THEN 'January'
          WHEN  2 THEN 'February'
          WHEN  3 THEN 'March'
          WHEN  4 THEN 'April'
          WHEN  5 THEN 'May'
          WHEN  6 THEN 'June'
          WHEN  7 THEN 'July'
          WHEN  8 THEN 'August'
          WHEN  9 THEN 'September'
          WHEN 10 THEN 'October'
          WHEN 11 THEN 'November'
          WHEN 12 THEN 'December'
       END
       || ' ' ||
       CAST(EXTRACT(YEAR FROM release_date) AS CHAR(4))
   FROM items
   WHERE release_date BETWEEN CURRENT_DATE
                      AND CURRENT_DATE + INTERVAL '4' DAY;
Returns:

March         2002

DAYOFWEEK

Another useful function is DAYOFWEEK which returns the day number within a week. Monday has the value 1 and Sunday has the value 7.

Find the release day for any items with a release date in the next 4 days:
SELECT CASE DAYOFWEEK(release_date)
          WHEN 1 THEN 'Monday'
          WHEN 2 THEN 'Tuesday'
          WHEN 3 THEN 'Wednesday'
          WHEN 4 THEN 'Thursday'
          WHEN 5 THEN 'Friday'
          WHEN 6 THEN 'Saturday'
          WHEN 7 THEN 'Sunday'
       END
   FROM items
   WHERE release_date BETWEEN CURRENT_DATE
                      AND CURRENT_DATE + INTERVAL '4' DAY;
Result:

Friday


Using Set Functions

The functions listed below can be used in the column list of the SELECT statement to retrieve the result of the function on a specified column.

Set function
Explanation

AVG

average of values (numerical columns only)

COUNT

number of rows

MAX

largest value

MIN

smallest value

SUM

sum of values (numerical columns only)

About Set Functions

Set functions in SELECT statements are applied to data in the result table, not in the source table.

Set functions return a single value for the whole table unless a GROUP BY clause is specified, see Grouped Set Functions - the GROUP BY Clause.

For all set functions, NULL values are eliminated from the column before the function is applied. The special form COUNT(*) counts the number of rows including NULL values.

The keywords ALL and DISTINCT may be used to qualify set functions. ALL gives a result based on all values including duplicates. DISTINCT eliminates duplicates before applying the function. If neither keyword is specified, duplicates are not removed.

Note: Set functions may not be used together with direct column references in the SELECT list (unless the SELECT statement includes a GROUP BY clause, see Grouped Set Functions - the GROUP BY Clause).

Example of Set Functions

The set functions are illustrated with results from the following table:

Note: A hyphen - indicates NULL.

SAMPLE
1.0
2.0
2.0
2.0
3.0
3.0
4.0
5.0
-
-

COUNT(SAMPLE)	8
COUNT(*)	10
COUNT(DISTINCT SAMPLE)	5
SUM(SAMPLE)	22.0
SUM(ALL SAMPLE)	22.0
SUM(DISTINCT SAMPLE)	15.0
AVG(SAMPLE)	2.75000000000
AVG(ALL SAMPLE)	2.75000000000
AVG(DISTINCT SAMPLE)	3.00000000000
MAX(SAMPLE)	5.0
MIN(SAMPLE)	1.0

Note:	AVG(column) is equivalent to SUM(column)/COUNT(column). However, the expression SUM(column)/COUNT(*) will give a different answer if the column includes NULL values.

Thus, for the table above:

SUM(SAMPLE)/COUNT(SAMPLE)    2.75000000000   (22/8)

SUM(SAMPLE)/COUNT(*)         2.20000000000   (22/10)

More Set Functions Examples

Some further examples of set functions applied to the example database are given below.

How many rows are there in the CURRENCIES table?
SELECT COUNT(*)
   FROM currencies;
How many currencies have a defined exchange rate (i.e. EXCHANGE_RATE is not NULL)?
SELECT COUNT(ALL exchange_rate)
   FROM currencies;
What is the average exchange rate?
SELECT AVG(exchange_rate)
   FROM currencies;
Decimal Calculation

The AVG function returns an integer if the operand is an integer, and a decimal if the operand is decimal. To force decimal calculation of averages from an integer column, cast the column operand as decimal:

SELECT AVG(CAST(column AS DECIMAL)) ...


Grouped Set Functions - the GROUP BY Clause

Normally, set functions return a single value, calculated from the set of all values in the column or expression.

If the SELECT statement includes a GROUP BY clause, set functions will be applied to groups of values. Columns used for GROUP BY do not have to be included in the SELECT list.

Find the number of rows in each category within the FORMATS table:
SELECT category_id, COUNT(display_order)
   FROM formats
   GROUP BY category_id;
Returns:

CATEGORY_ID
1
4
2
4
3
2

Restrictions when Using GROUP BY

Using a GROUP BY clause places some restrictions on the SELECT statement. Only constants, columns used in the GROUP BY clause, and columns used in set functions may be included in the SELECT list.

Column References

In a statement with column references in the SELECT list, all columns not used in set functions must be used as grouping columns.

Null Values

For grouping purposes, NULL values are regarded as equivalent. Thus for the example table:

SAMPLE
1.0
2.0
2.0
2.0
3.0
3.0
4.0
5.0
-
-

The following statement:
SELECT sample, COUNT(*) as number
   ...
   GROUP BY sample;
Returns:

SAMPLE
NUMBER
1.0
1
2.0
3
3.0
2
4.0
1
5.0
1
-
2


Selecting Groups - the HAVING Clause

The HAVING clause restricts the selection of groups in the same way that a WHERE clause restricts the selection of rows. However, in contrast to the WHERE clause, a HAVING clause may use a set function on the left-hand side of a comparison.

The HAVING clause is most often used together with a GROUP BY clause, but may also be used to impose selection conditions on a column derived from a set function.

Find the currency codes that are used by more than one country:
SELECT currency_code, COUNT(currency_code)
   FROM countries
   GROUP BY currency_code
   HAVING COUNT(currency_code) > 1;
Returns:

CURRENCY_CODE

AUD
8
CHF
2
DKK
3
EUR
23
IDR
2
...
...


Ordering the Result Table

Strictly, the order of rows in a result table is undefined unless an ORDER BY clause is included in the SELECT statement.

Ascending or descending order may be specified; ascending order is the default.

Note: A SELECT statement without an ORDER BY clause may appear to give an ordered result in Mimer SQL, but you should include an ORDER BY clause if the ordering is important. Without the ORDER BY clause, a change in the database contents or updated Mimer SQL version can otherwise change the order.

Example
SELECT *
   FROM formats
   ORDER BY format DESC;
Returns:

FORMAT_ID
FORMAT
CATEGORY_ID
DISPLAY_ORDER
4
Vinyl
1
40
10
Video
3
20
8
Paperback
2
20
7
Hardcover
2
10
9
DVD Video
3
10
3
DVD Audio
1
10
2
Cassette
1
30
1
Audio CD
1
20
6
Audio CD
2
30
5
Audio Cassette
2
40

Ordering by More than One Column

More than one column may be specified in the ORDER BY clause.

Example:
SELECT *
   FROM formats
   ORDER BY category_id, display_order;
Returns:

FORMAT_ID
FORMAT
CATEGORY_ID
DISPLAY_ORDER
3
DVD Audio
1
10
1
Audio CD
1
20
2
Cassette
1
30
4
Vinyl
1
40
7
Hardcover
2
10
8
Paperback
2
20
6
Audio CD
2
30
5
Audio Cassette
2
40
9
DVD Video
3
10
10
Video
3
20

Ordering by Set Function

To order a result table by a set function, the column in the result table is given a label and the label is used in the ORDER BY clause.

Example:
SELECT category_id, MAX(display_order) AS maximum_value
   FROM formats
   GROUP BY category_id
   ORDER BY maximum_value;
Returns:

CATEGORY_ID
MAXIMUM_VALUE
3
20
1
40
2
40

Ordering by a Computed Value

To order a result table by a computed value, place the computation in the ORDER BY clause.

Example:
SELECT product
   FROM products JOIN items ON products.product_id = items.product_id
   WHERE format_id = 2
   ORDER BY stock * price;
Returns:

PRODUCT

The Wild, the Innocent and the E Street Shuffle

Greatest Hits

On How Life Is

Snowed In

Christmas Portrait

Falling into You

LaTouché IV: Traditional Cajun Dancin' Music

Atlanta Homecoming

Born in the U.S.A.


Retrieving Data from More than One Table

The examples presented up to now in this chapter have illustrated the essential features of simple SELECT statements with data retrieval from single tables. However, much of the power of SQL lies in the ability to perform joins through a single statement, i.e. to select data from two or more tables, using the search condition to link the tables in a meaningful way.

The Join Condition

In retrieving data from more than one table, the search condition or join condition specifies the way the tables are to be linked. For example:

List the product name in addition to the EAN and price for any items with a release date in the future:
SELECT product, ean_code, price
   FROM items JOIN products ON items.product_id = products.product_id
   WHERE items.release_date > CURRENT_DATE;

The join condition here is ITEMS.PRODUCT_ID = PRODUCTS.PRODUCT_ID, which relates the product identifier in table ITEMS (where codes are listed) to the product identifier in table PRODUCTS (where names are listed).

Returns:

PRODUCT
EAN_CODE
PRICE
Greatest Hits
7298976754871
13.98
Pieces Of Fish
7464376662256
15.98
The Sql Quiz Book
9781990789861
13.99
The Future Foretold
9781993789639
6.99

Conceptually, the join first establishes a table containing all combinations of the rows in PRODUCTS with the rows in ITEMS, then selects those rows in which the two PRODUCT_ID values are equal. See Conceptual Description of the Selection Process for a fuller description of the conceptual SELECT process.

This does not necessarily represent the order in which the operations are actually performed; the order of evaluation of a complex SELECT statement is determined by the SQL optimizer, regardless of the order in which the component clauses are written.

Cross Products

Without the join condition, the result is a cross product of the columns in the tables in question, containing all possible combinations of the selected columns, for example:

SELECT product, ean_code, price
   FROM items, products
   WHERE items.release_date > CURRENT_DATE;
Returns:

PRODUCT
EAN_CODE
PRICE
'Murder In The Cathedral'
7298976754871
13.98
'Reave The Just' And Other Tales
7298976754871
13.98
100 Anos
7298976754871
13.98
12 Golden Country Greats
7298976754871
13.98
12 Super Exitos
7298976754871
13.98
1492: Conquest Of Paradise
7298976754871
13.98
...
...
...

It is easy to see that a carelessly formulated join query can produce a very large result table. Two tables of 100 rows each, for instance, give a cross product with 10,000 rows; three tables of 100 rows each give a cross product with 1,000,000 rows!

The risk of generating large (erroneous) result tables is particularly high in interactive SQL (e.g. when using Mimer BSQL), where queries are so easily written and submitted.

Simple Joins

In simple joins, all tables used in the join are listed in the FROM clause of the SELECT statement. This is in distinction to nested joins, where the search condition for one SELECT is expressed in terms of another SELECT, see Nested Selects.

An example of a simple join is the query described in The Join Condition.

Example:
SELECT product, ean_code, price
   FROM items JOIN products ON items.product_id = products.product_id
   WHERE release_date > CURRENT_DATE;
Returns:

PRODUCT
EAN_CODE
PRICE
Greatest Hits
7298976754871
13.98
Pieces Of Fish
74643766622
15.98
The Sql Quiz Book
9781990789861
13.99
The Future Foretold
9781993789639
6.99

SELECT *

The form SELECT * may be used in a join query, but since this selects all columns in the result set, at least one column is usually duplicated.

Example:
SELECT *
      FROM items
      JOIN products ON items.product_id = products.product_id
      AND release_date > CURRENT_DATE;
Returns:

From ITEMS:

ITEM_ID

PRODUCT_ID

FORMAT_ID

RELEASE_DATE

STATUS

PRICE

STOCK

REORDER_LEVEL

EAN_CODE

PRODUCER_ID

IMAGE_ID

From PRODUCTS:

PRODUCT

PRODUCT_ID

PRODUCT_SEARCH

Columns in the join query that are uniquely identified by the column name may be specified by name alone. Columns that have the same name in the joined tables must be qualified by their respective table names.

There is an alternative formulation of the query above:

SELECT product, ean_code, price
   FROM items JOIN products ON items.product_id = products.product_id
   AND release_date > CURRENT_DATE;

All predicates that can be used in a where clause, except sub-selects, can be used in an on clause.

About Natural Joins

A natural join, joins the table on the condition of equality between any columns with the same name, in the two tables. In the first example, all columns from the two tables are present in the result. In the second example the join columns will only occur once. Thus, in the first case, the PRODUCT_ID column appears twice in the result, while there is only one occurrence of this column in the second result.

Nesting Join Clauses

It is possible to nest join-clauses, for example:

List the category in addition to the EAN and price for any items with a release date in the future:
SELECT ean_code, price, category
   FROM items
   NATURAL JOIN formats
   JOIN categories ON categories.category_id = formats.category_id
   WHERE release_date > CURRENT_DATE;
Result:

EAN_CODE
PRICE
CATEGORY
7298976754871
13.98
Music
7464376662256
15.98
Music
9781990789861
13.99
Books
9781993789639
6.99
Books

Complex Search Conditions and Joins

A join query can join any number of tables using complex search conditions to select the relevant information from each table.

List the product for any items with a release date in the future along with the item price in both Swedish and Danish crowns (SEK and DKK respectively):
SELECT product,
       CAST(price * exchange_rate AS DECIMAL(12,2)) AS cost,
       currency
   FROM items, products, currencies
   WHERE release_date > CURRENT_DATE
   AND products.product_id = items.product_id
   AND (currencies.code = 'SEK'
     OR currencies.code = 'DKK')
   ORDER BY product, currency;
Result:

PRODUCT
COST
CURRENCY

Greatest Hits
99.42
Danish Kronor

Greatest Hits
125.61
Swedish Kronor

Pieces Of Fish
113.64
Danish Kronor

Pieces Of Fish
143.58
Swedish Kronor

The Future Foretold
49.71
Danish Kronor

The Future Foretold
62.80
Swedish Kronor

The Sql Quiz Book
99.49
Danish Kronor

The Sql Quiz Book
125.70
Swedish Kronor

In formulating a search condition for a join query, it can help to write out the columns that would appear in a complete cross-product of the tables. The search condition is then formulated as though the query was a simple SELECT from the cross-product table.

Outer Joins

The joins in the previous sections were all inner joins. In an inner join between two tables, only rows that fulfill the join condition are present in the result.

An outer join, on the contrary, contains non-matching rows as well. The outer join has two options, LEFT and RIGHT.

Left Outer Join
Example:
SELECT ean_code, release_date, producer
   FROM items
   LEFT OUTER JOIN producers
      ON items.producer_id = producers.producer_id
   WHERE ean_code >= 800000000000
   ORDER BY ean_code;
Result:

EAN_CODE
RELEASE_DATE
PRODUCER
800488327626
1998-08-11
Giants Of Jazz (Ita)
801061007720
2000-10-31
Warp Records
4988002364947
1999-09-28
-
4988011353147
1998-06-30
-
5013145800423
2000-03-14
Mint / Cherry Red
5013929112322
1999-10-12
Cherry Red
5014438710221
1994-12-27
Receiver Records
5019317001728
1994-12-15
Receiver Records
7157761806273
1996-01-18
Status Records
...
...
...

In the example above all rows from the table to the left in the join clause, i.e. ITEMS, are present in the result; non-matching rows from the PRODUCERS table are filled with null values in the result.

Observe the difference in result for the next statement and the previous one.

SELECT ean_code, release_date, producer
   FROM items
   LEFT OUTER JOIN producers
      ON items.producer_id = producers.producer_id
      AND ean_code >= 800000000000
   ORDER BY ean_code;
Result:

EAN_CODE
RELEASE_DATE
PRODUCER
8811038120
1991-08-27
-
8811042127
1991-10-22
-
8811061326
1992-05-19
-
8811067021
1992-12-22
-
...
...
...
800488327626
1998-08-11
Giants Of Jazz (Ita)
801061007720
2000-10-31
Warp Records
4988002364947
1999-09-28
-
4988011353147
1998-06-30
-
5013145800423
2000-03-14
Mint / Cherry Red
...
...
...

The reason is that conditions in the where clause are applied to the result of the join-clause and not to the joined tables as is the case with the conditions in the on-clause.

Right Outer Join

A right outer join will take all records from the table to the right in the join-clause.

Nesting Outer Joins

As with inner joins, it is possible to nest join-clauses. Nested joins can be of different types, i.e. both inner and outer joins.

The result of nested outer joins can be somewhat unexpected though, as it is the result of the first join-clause that is the left table in the next join, and not the right table in the first join-clause.

Nested Selects

A form of SELECT, called a subselect, can be used in the search condition of a SELECT statement to form a nested query.

The main SELECT statement is then referred to as the outer select.

For example:

Select the products that have a release date in the future.
SELECT product
   FROM products
   WHERE product_id IN (SELECT product_id
                           FROM items
                           WHERE release_date > CURRENT_DATE);
Result:

PRODUCT

Greatest Hits

Pieces Of Fish

The Future Foretold

The Sql Quiz Book

To see how this works, evaluate the subselect first:

SELECT product_id
   FROM items
   WHERE release_date > CURRENT_DATE;
Result:

PRODUCT_ID
30206
30618
31082
31083

Then use the result of the subselect in the search condition of the outer select:

SELECT product
   FROM products
   WHERE product_id IN (30206, 30618, 31082, 31083);
Result:

PRODUCT

Greatest Hits

Pieces Of Fish

The Future Foretold

The Sql Quiz Book

Using Subselects

A subselect can be used in a search condition wherever the result of the subselect can provide the correct form of the data for the search condition.

Thus a subselect used with `=' must give a single value as a result.

A subselect used with IN, ALL or ANY must give a set of single values, see Retrieval with ALL, ANY, SOME.

A subselect used with EXISTS may give any result, see Retrieving Data Using EXISTS and NOT EXISTS.

Examples:
WHERE column = (subselect)
WHERE column IN (subselect)
WHERE column = ALL (subselect)
WHERE column = ANY (subselect)
WHERE EXISTS (subselect)
Subselects and ORDER BY Clauses

Subselects cannot include ORDER BY clauses. The UNION operator can be used to combine two or more subselects in more complex statements, see Union Queries.

Nested Queries

Many nested queries can equally well be written as simple joins. For example:

Select the products that have a release date in the future.
SELECT product
   FROM products
   WHERE product_id IN (SELECT product_id
                           FROM items
                           WHERE release_date > CURRENT_DATE);

or alternatively

SELECT DISTINCT product
   FROM products, items
   WHERE products.product_id = items.product_id
   AND items.release_date > CURRENT_DATE;

Both these queries give exactly the same result. In most cases, the choice of which form to use is a matter of personal preference. Choose the form which you can understand most easily; the clearest formulation is least likely to cause problems.

Subselects in Queries

Queries may contain any number of subselects, for example:

List the producers (manufacturers) which have items that are more expensive than any of the items produced by Sony.
SELECT producer
   FROM producers
   WHERE producer_id IN
         (SELECT producer_id
             FROM items
             WHERE price >
                      (SELECT MAX(price)
                          FROM items
                          WHERE producer_id =
                                   (SELECT producer_id
                                       FROM producers
                                       WHERE producer = 'SONY')));

Note the balanced parentheses for the nested levels.

It is particularly important at this level of complication to think carefully through the query to make sure that it is correctly formulated.

Often, writing some of the levels as simple joins can simplify the structure. The previous example may also be written:

SELECT DISTINCT producer
   FROM producers, items
   WHERE producers.producer_id = items.producer_id
   AND price > (SELECT MAX(price)
                   FROM items, producers
                   WHERE items.producer_id = producers.producer_id
                   AND producer = 'SONY');

Correlation Names

A correlation name is a temporary name given to a table to represent a logical copy of the table within a query.

There are three uses for correlation names:

  • simplifying complex queries
  • joining a table to itself
  • outer references in subselects
Simplifying Complex Queries Using Correlation Names

Using short correlation names into complicated queries can make the query easier to write and understand, particularly when qualified table names are used:

SELECT mimer_store_music.artists.artist,
       mimer_store.product_details.*
   FROM mimer_store.product_details,
        mimer_store_music.titles,
        mimer_store_music.artists
   WHERE mimer_store_music.titles.item_id =
         mimer_store.product_details.item_id
   AND mimer_store_music.artists.artist_id =
         mimer_store_music.titles.artist_id
   ORDER BY mimer_store_music.artists.artist;

may be rewritten

SELECT art.artist, pdt.*
   FROM mimer_store.product_details AS pdt,
        mimer_store_music.titles AS ttl,
        mimer_store_music.artists AS art
   WHERE ttl.item_id = pdt.item_id
   AND art.artist_id = ttl.artist_id
   ORDER BY art.artist;

The keyword AS in the FROM clause may be omitted, but is recommended for clarity.

About Correlation Names

Correlation names are local to the query in which they are defined.

When a correlation name is introduced for a table name, all references to the table in the same query must use the correlation name.

The following expression is not accepted:

   ...
   FROM mimer_store.product_details AS pdt,
        mimer_store_music.titles AS ttl,
   ...
   WHERE ttl.item_id = mimer_store.product_details.item_id
Joining a Table with Itself Using a Correlation Name

Joining a table with itself allows you to compare information in a table with other information in the same table. This can be done with a correlation name.

Select all currencies with the same exchange rate:
SELECT currencies.currency, currencies.code, currencies.exchange_rate
   FROM currencies, currencies AS copy
   WHERE currencies.exchange_rate = copy.exchange_rate
   AND currencies.currency <> copy.currency;
Result:

CURRENCY
CODE
EXCHANGE_RATE
Croatian Kuna
HRK
7.0820
Gourdes
HTQ
7.0820
Iraqi Dina
IQD
1551.0000
Uganda Shillings
UGX
1551.0000

Here, the table CURRENCIES is joined to a logical copy of itself called COPY.

The first search condition finds pairs of currencies with the same exchange rate against the euro, and the second eliminates 'pairs' with the same currency name. Without the second condition in the search criteria, all currencies would be selected!

Without correlation names, this kind of query cannot be formulated. The following query would select all the currencies from the table:

SELECT currency, code, exchange_rate
   FROM currencies
   WHERE currencies.exchange_rate = currencies.exchange_rate;
Outer References in Subselects Using Correlation Names

In some constructions using subselects, a subselect at a lower level may refer to a value in a table addressed at a higher level. This kind of reference is called an outer reference.

SELECT currency
   FROM currencies
   WHERE EXISTS (SELECT *
                    FROM countries
                    WHERE currency_code = currencies.code);

This kind of query processes the subselect for every row in the outer select, and the outer reference represents the value in the current outer select row. In descriptive terms, the query says 'For each row in CURRENCIES, select the CURRENCY column if there are rows in COUNTRIES containing the current CODE value'.

If the qualifying name in an outer reference is not unambiguous in the context of the subselect, a correlation name must be defined in the outer select.

A correlation name may always be used for clarity, as in the following example:

SELECT currency
   FROM currencies AS c
   WHERE EXISTS (SELECT *
                    FROM countries
                    WHERE currency_code = c.code);

Retrieving Data Using EXISTS and NOT EXISTS

EXISTS is used to check for the existence of some row or rows which satisfy a specified condition. EXISTS differs from the other operators in that it does not compare specific values; instead, it tests whether a set of values is empty or not. The set of values is specified as a subselect.

The subselect following the EXISTS clause most often uses of `SELECT *' as opposed to `SELECT column-list' since EXISTS only searches to see if the set of values addressed by the subselect is empty or not - a specified column is seldom relevant in the subquery.

EXISTS (subselect) is true if the result set of the subselect is not empty

NOT EXISTS (subselect) is true if the result set of the subselect is empty

SELECT statements with EXISTS almost always include an outer reference linking the subselect to the outer select.

Examples of EXISTS
Find all currencies that are used in the COUNTRIES table:
SELECT currency
   FROM currencies AS c
   WHERE EXISTS (SELECT *
                    FROM countries
                    WHERE currency_code = c.code);

Without the outer reference, the select becomes a conditional `all-or-nothing' statement: perform the outer select if the subselect result is not empty, otherwise select nothing.

List all products where the producer (manufacturer) is not known:
SELECT product
   FROM products
   WHERE EXISTS (SELECT *
                    FROM items
                    WHERE producer_id IS NULL
                    AND product_id = products.product_id);
Examples of NOT EXISTS

The next example illustrates NOT EXISTS:

List all products where the producer (manufacturer) is not known:
SELECT product
   FROM products
   WHERE NOT EXISTS (SELECT *
                        FROM items
                        NATURAL JOIN producers
                        WHERE product_id = products.product_id);
Result:

PRODUCT

Invictus

Middle Of Nowhere

Negated EXISTS

Negated EXISTS clauses must be handled with care. There are two semantic `opposites' to EXISTS, with very different meanings:

WHERE EXISTS (SELECT *
                 FROM artists
                 WHERE artist = 'Enigma')

is true if at least one artist is called Enigma.

WHERE NOT EXISTS (SELECT *
                     FROM artists
                     WHERE artist = 'Enigma')

is true if no artist is called Enigma.

But

WHERE EXISTS (SELECT *
                 FROM artists
                 WHERE artist <> 'Enigma')

is true if at least one artist is not called Enigma.

WHERE NOT EXISTS (SELECT *
                     FROM artists
                     WHERE artist <> 'Enigma')

is true if no artist is not called Enigma, that is if every artist is called Enigma.

Retrieval with ALL, ANY, SOME

Subselects that return a set of values may be used in the quantified predicates ALL, ANY or SOME. Thus

WHERE PRICE < ALL (subselect)

selects rows where the price is less than every value returned by the subselect

WHERE PRICE < ANY (subselect)

selects rows where the price is less than at least one of the values returned by the subselect

Select countries that have an exchange rate of less than one:
SELECT country
   FROM countries
   WHERE currency_code <> ALL (SELECT code
                                  FROM currencies
                                  WHERE exchange_rate >= 1.0);

If the result of the subselect is an empty set, ALL evaluates to true, while ANY or SOME evaluates to false.

An alternative to using ALL, ANY or SOME in a value comparison against a general sub-select, is to use EXISTS or NOT EXISTS to see if values are returned by a sub-select which only selects for specific values. For example:

Select countries where the associated currency code contains the letter 'E' as the middle character in the code:
SELECT country
   FROM countries
   WHERE currency_code = ANY (SELECT code
                                 FROM currencies
                                 WHERE code LIKE '_E_');

is equivalent to:

SELECT country
   FROM countries AS c
   WHERE EXISTS (SELECT *
                   FROM currencies
                   WHERE code LIKE '_E_'
                   AND code = c.currency_code);

Union Queries

The UNION operator combines the results of two or more subselect clauses.

UNION first merges the result tables specified by the separate subselects and then eliminates duplicate rows from the merged set.

Select the codes for currencies and countries that start with the letter 'D':
SELECT code
   FROM currencies
   WHERE code LIKE 'D%'
UNION
SELECT currency_code
   FROM countries
   WHERE country LIKE 'D%';

The result is obtained by merging the results of the two subselects and eliminating duplicates:

SELECT code                   SELECT currency_code
   FROM currencies               FROM currencies
   WHERE code LIKE 'D%;          WHERE country LIKE 'D%';

CODE

CODE

DJF

DJF

DKK

DKK

DOP

XCD

DZD

DOP

and the UNION gives the result table:

CODE
DJF
DKK
DOP
DZD
XCD

To retain duplicates in the result table, use UNION ALL in place of UNION, see the Mimer SQL Reference Manual, UNION or UNION ALL, for details.

Columns which are merged by UNION must have compatible data types (numerical with numerical, character with character).

Subselects addressing more than one result column are merged column by column in the order of selection. The number of columns addressed in each subselect must be the same.

About Column Names

The column names in the result of a UNION are taken from the names in the first subselect. Use labels in the first subselect to assign different column names to the result table:

Merge the codes and names of currencies where the code begins with 'D' with the codes and names of the countries where the country begins with 'D':
SELECT code, currency AS currency_or_country
   FROM currencies
   WHERE code LIKE 'D%'
UNION
SELECT currency_code, country
   FROM countries
   WHERE country LIKE 'D%';
Result:

CODE
CURRENCY_OR_COUNTRY
DJF
Djibouti
DJF
Djibouti Francs
DKK
Danish Kronor
DKK
Denmark
DOP
Dominican Pesos
DOP
Dominican Republic
DZD
Algerian Dinars
XCD
Dominica

UNION Restrictions

Subselects merged by UNION may not include an ORDER BY clause. However, the result of the UNION query may be ordered with an ORDER BY clause placed after the last query in the UNION.

UNION may not be used within a nested subselect.

More about Unions

Unions can also be used to combine information from the same table:

Find the lowest and highest exchange_rates:
SELECT 'Highest', MAX(exchange_rate) AS rate
   FROM currencies

UNION

SELECT 'Lowest', MIN(exchange_rate)
   FROM currencies

ORDER BY rate;
Result:

RATE
Lowest
0.2644
Highest
1035000.0000

Outer Joins and Unions

Unions can also be used to perform outer joins, joining information in a table or tables with information not listed in those tables (i.e. information that is null). For example:

List the EAN, price and producer for each item where the EAN is 800000000000 or greater. Include a row for items where the producer is not known:
SELECT ean_code, release_date, producer
   FROM items
   NATURAL JOIN producers
   WHERE ean_code >= 800000000000

UNION

SELECT ean_code, release_date, '*** undefined ***'
   FROM items
   WHERE ean_code >= 800000000000
   AND NOT EXISTS (SELECT *
                      FROM producers
                      WHERE producer_id = items.producer_id)

ORDER BY ean_code;
Result:

EAN_CODE
RELEASE_DATE
PRODUCER
800488327626
1998-08-11
Giants Of Jazz (Ita)
801061007720
2000-10-31
Warp Records
4988002364947
1999-09-28
*** undefined ***
4988011353147
1998-06-30
*** undefined ***
5013145800423
2000-03-14
Mint / Cherry Red
5013929112322
1999-10-12
Cherry Red
5014438710221
1994-12-27
Receiver Records
5019317001728
1994-12-15
Receiver Records
7157761806273
1996-01-18
Status Records
...
...
...

Note: UNION statements including DISTINCT treat NULL values as duplicates.

Unions and NULL

In UNION queries, you may need to add an empty column so that columns not represented in all of the queries in the statement are retained in the result set. This is done by casting a NULL value to a matching datatype.

Example
    SELECT ean_code, relase_date, producer
      FROM items INNER JOIN producers
        ON items.producer_id = producers.producer_id
    UNION
    SELECT ean_code, release_date, CAST(NULL AS char)
      FROM items
      WHERE NOT EXISTS
          (SELECT * FROM producers
           WHERE items.producer_id = producers.producer_id)


Handling NULL Values

NULL values require special handling in SQL queries. NULL represents an unknown value, and strictly speaking NULL is never equal to NULL. (NULL values are however treated as equal for the purposes of GROUP BY, DISTINCT and UNION).

Searching for NULL

The condition for selecting NULL values is

WHERE column IS NULL

The negated form (WHERE column IS NOT NULL) selects values which are not NULL (i.e. values which are known).

List all currencies, and their codes, where the exchange rate is not known:
SELECT currency, code
   FROM currencies
   WHERE exchange_rate IS NULL;
Result:

CURRENCY

CODE

Armenian Drams

AMD

Kwanza

AOA

Brunei Dollars

BND

Francs Congolais

CDF

Saint Helena Pounds

SHP

Somali Shillings

SOS

Somoni

TJS

List all EAN codes where the producer is not known:
SELECT ean_code
   FROM items
   WHERE producer_id IS NULL;
Result:

EAN_CODE
4988002364947
4988011353147

List all EAN codes issued to Llewellyn Publications, where the release date is not known:
SELECT ean_code
   FROM items
   WHERE release_date IS NULL
   AND PRODUCER_ID = (SELECT producer_id
                         FROM producers
                         WHERE producer = 'Llewellyn Publications');
Result:

EAN_CODE

9780875428697

9780875428949

9780875428260

9780875428680

9780875427386

Null values in ALL, ANY, IN and EXISTS Queries

Null values should be treated cautiously, particularly in ALL, ANY, IN and EXISTS queries.

The result of a comparison involving NULL is unknown, which is generally treated as false. This can lead to unexpected results.

For example, neither of the following conditions are true:

<null>     IN (...,null,...)
<null> NOT IN (...,null,...)

The first result is almost intuitive: since NULL is not equal to NULL, NULL is not a member of a set containing NULL.

But if NULL is not a member of a set containing NULL, the second result is intuitively true.

In fact, neither result is true or false: both are unknown. If NULL values are involved on either side of the comparison, IN and NOT IN are not complementary. Similar arguments apply to queries containing ALL or ANY, for example:

SELECT currency, code
   FROM currencies
   WHERE exchange_rate > ALL (SELECT exchange_rate
                                 FROM currencies
                                 WHERE currency LIKE 'D%');
Result:
CURRENCY

CODE

Belarussian Rubles

BYR

Maticais

MZM

Lei

ROL

Turkish Liras

TRL

This query works as long as there are no NULL values returned by the subselect. But perform the subselect against a range of currencies that contain a NULL value in the exchange rate, and the query results in an empty set:

SELECT currency, code
   FROM currencies
   WHERE exchange_rate > ALL (SELECT exchange_rate
                                 FROM currencies
                                 WHERE currency LIKE 'A%');

Moreover, the reverse query, currencies that have a lower exchange rate, also results in an empty set. A justification for this is that as long as an exchange rate is unknown, it is impossible to say whether other currency rates are greater or less.

Using Exists

It is always possible to rephrase a query using ALL, ANY or IN in terms of one using EXISTS (with an outer reference between the selection and the EXISTS condition). This is to be recommended if the NULL indicator is to be permitted in the comparison sets, since NULL handling is then written out explicitly in the query.

Distinctions between queries involving NULL comparisons are subtle and are easily overlooked.

It is essential that the aim of a query is stringently defined before the query is formulated in SQL, and that the possible effects of NULL values in the search condition are considered.

There are many real-life examples where the presence of NULL has resulted in unforeseen and sometimes misleading data retrievals. It is advisable to define all columns in the database tables as NOT NULL except those where unknown values have a specific meaning. In this way the risks of confusion with NULL handling are minimized.


Conceptual Description of the Selection Process

This section presents a conceptual step-by-step analysis of the evaluation of a SELECT statement.

It is intended as an aid in formulating complex SELECT statements, and can also help you in understanding details of the statement syntax.

Note: The description here is purely conceptual. It does not represent the actual sequence of events performed by the database manager. In particular, the computer resource requirements implied by the intermediate result set defined in a FROM clause do not necessarily reflect actual requirements.

Query Used

The query used in the analysis is:

List those producers and the average price for the goods that they manufacture where they make more than 10 items. Sort the result by the average price, with the largest first:
SELECT producer, AVG(price) AS average
   FROM producers AS p, items AS i
   WHERE p.producer_id = i.producer_id
   GROUP BY p.producer
   HAVING COUNT(*) > 10
   ORDER BY average DESC, producer;
Result:

PRODUCER
AVERAGE
BBC Audio (Spoken Word)
37.742727272727
MCA
27.798181818181
RCA
19.580000000000
Elektra/Asylum
18.265714285714
Warner Brothers
17.137894736842
Capitol
16.646666666666
Atlantic
14.798181818181
Sony
14.091111111111
...
...

Selection Process

  1. Subselects at the lowest nesting level are evaluated first.

    2. The first step in evaluating a select is to resolve subselects from the lowest level up, and conceptually replace the subselect with the result set. The example here does not use a nested select.

    When all subselects are resolved, a, possibly complicated, single-level SELECT statement remains.

  2. The FROM clause defines an intermediate result set.

    3. Tables addressed in the FROM clause are combined to form an intermediate result set which is the full cross product of the tables.

    The cross product is a table with one column for each column in each of the table, and one row for every combination of rows from the different tables.

    The columns in the result set are identified by the qualified column names from the table from which they are derived.

    FROM producers AS p, items AS i

    The FROM clause in the example produces an intermediate result set which is the full cross product of the PRODUCERS table and the ITEMS table.

  3. The WHERE clause selects rows from the intermediate set.

    4. The WHERE clause selects rows from the full cross product result set that meet the criteria specified.

    WHERE p.producer_id = i.producer_id

    In this example the WHERE clause selects only those result set rows where the value in the PRODUCER_ID column from the PRODUCERS table is equal to that in the PRODUCER_ID column from the ITEMS table.

The GROUP BY clause groups the remaining result set:

GROUP BY p.producer

PRODUCER
PRICE
404 Music Group
16.98
4AD Records
11.98
7-N Music
16.98
A&M Records
11.98
A&M Records
22.98
A&M Records
10.98
A&M Records
18.98
A&M Records
18.98
...
...

  1. The HAVING clause selects groups:

    2. HAVING COUNT(*) > 10

    PRODUCER
    PRICE
    Atlantic
    17.98
    Atlantic
    11.98
    Atlantic
    11.98
    Atlantic
    9.98
    Atlantic
    11.98
    Atlantic
    17.98
    Atlantic
    11.98
    Atlantic
    11.98
    ...
    ...

  2. The SELECT list selects columns, evaluates any expressions in the SELECT list, and reduces groups to single rows if set functions are used:

    3. SELECT producer, AVG(price) AS average

    PRODUCER
    AVERAGE
    Atlantic
    14.798181818181
    BBC Audio (Spoken Word)
    37.742727272727
    Capitol
    16.646666666666
    Collins
    7.529814814814
    Elektra/Asylum
    18.265714285714
    Geffen Records
    12.480000000000
    HarperCollins
    6.722187500000
    Marshall Editions
    9.842222222222
    ...
    ...

  3. The results of subselects joined by UNION are merged.

    4. This example does not include a UNION.

  4. The final result is sorted according to the ORDER BY clause:

    5. ORDER BY average DESC, producer;

    PRODUCER
    AVERAGE
    BBC Audio (Spoken Word)
    37.742727272727
    MCA
    27.798181818181
    RCA
    19.580000000000
    Elektra/Asylum
    18.265714285714
    Warner Brothers
    17.137894736842
    Capitol
    16.646666666666
    Atlantic
    14.798181818181
    Sony
    14.091111111111
    ...
    ...


Collations

Sorting and searching non-English text can cause a number of problems, a frequent one being how to handle accented letters, for example á, à and â.

The rules for sorting vary because the various natural languages sort words differently. There are occasions where the accented form of a letter is treated as a distinct letter for the purpose of comparison. For example, in Swedish, Å is a separate letter that is sorted after Z. In some languages, it is common to sort uppercase before lowercase, in other languages this is reversed; sometimes it is just a matter of personal preference.

A collation, also known as a collating sequence, is a database object containing a set of rules that determines how character strings are compared, searched and alphabetically sorted. The rules in the collation determine whether one character string is less than, equal to or greater than another. A collation also determines how case-sensitivity and accents are handled.

In Mimer SQL, a collation belongs to a SCHEMA. In this release, the pre-defined collations included belong to INFORMATION_SCHEMA.

When a collation is used, Mimer SQL first checks to see if it belongs to the ident's schema. If Mimer SQL does not find it there, it checks for it in INFORMATION_SCHEMA.


Character Sets and Collations

For character data, Mimer SQL uses the character set ISO 8859-1, also known as the Latin1 character set. By default, character data is sorted in the numerical order of their codes according to the ISO8BIT collation.

For national character data, Mimer SQL uses the Unicode character set. By default national character data is sorted according to the UCS_BASIC collation.

It is not possible to add, drop or modify a character set.

Every character set has one default collation.

Character Set
Default Collation
Data Types
ISO 8859-1

ISO8BIT

CHARACTER
CHARACTER VARYING
CLOB

UNICODE

UCS_BASIC

NCHAR
NCHAR VARYING
NCLOB

If you want to sort characters in a different way than the default, you can specify a collation at the column level when creating or altering a table or creating a domain. You can also override a collation by using a COLLATE clause in an SQL statement.


Using Collations

You can specify a different collation for ordering characters when you create or alter a table or create a domain.

If you have specified a collation for a column, the collation is used implicitly in SQL statements.

You only need to explicitly use a collate clause in SQL statements if you want to override the default collation or the collation you specified when creating or altering the table or creating the domain.

Character Strings

SQL only permits compatible character strings to be assigned or compared. That is, you can assign or compare character strings only if the source and target strings belong to the same collation or can be coerced into having the same collation.

A character string that is defined with a named collation can only be compared or assigned to a character string that is either defined with the same named collation or is defined without a collation.

In the case where one of the strings is not associated with a named collation then it will be implicitly coerced to the same collation as the other string.

String Comparison Examples

The following three comparisons are all legal (and equivalent):

job_title = 'developer' COLLATE english_1

job_title COLLATE english_1 = 'developer'

job_title COLLATE english_1 = 'developer' COLLATE english_1

But -

job_title COLLATE english_1 = 'developer' COLLATE swedish_1

is illegal because the collations have different names.

CREATE/ALTER TABLE

When creating or altering a table, you can specify a collation in the column-definition, for example:

CREATE TABLE employees (surname CHAR(20) COLLATE swedish_1
             ...

CREATE DOMAIN

When creating a domain, you can specify a collation for the character and national character string data types, for example:

CREATE DOMAIN name_type AS VARCHAR(48) COLLATE english_1;

All properties of a domain apply to the column when the domain is used in a CREATE TABLE or ALTER TABLE statement.

CREATE INDEX

To improve performance when retrieving data, you can create more than one index for a column using different collations, for example:

CREATE INDEX cnt_eng_ind ON countries (country COLLATE english_1);
CREATE INDEX cnt_swe_ind ON countries (country COLLATE swedish_1);

Collation Precedence

A collation specified in the column-definition will take precedence over a domain collation.

Continuing with the example above, the domain collation was set to english_1, but in the following example the column country is set to swedish_1, which takes precedence over the domain setting:

      CREATE TABLE countries (
      code CHARACTER(2),
      country name_type COLLATE swedish_1,
      ...

Altering Collations on Columns

You can change the collation specified for a column by using the ALTER TABLE statement, for example:

ALTER TABLE countries ALTER COLUMN country CHAR(20) COLLATE english_1;

To return to the default (ISO8BIT) sorting order, you would enter:

ALTER TABLE countries ALTER COLUMN country CHAR(20) COLLATE ISO8BIT;

By altering a collation, for example to the default ISO8BIT collation, you can remove any dependencies associated with the collation. This makes it possible to drop the collation - see the next section.

Dropping a Collation

You can drop a collation only if there are no dependencies, for example:

DROP COLLATION collation_name RESTRICT;

Finding Out the Default Collation For a Column

You can find out which collation a column uses by reviewing the INFORMATION_SCHEMA.COLUMNS view, for example:
SELECT *
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE table_name = 'table1'
   AND column_name = 'col1';

For more information, see the Mimer SQL Reference Manual, INFORMATION_SCHEMA.


Using Collations - Examples

The following sections contain examples of how to use collations and what effects collations can have on a result set.

The examples are based on the following (rather simple) table, table1:

colswe

coleng

col1

A

A

A

a

a

a

W

W

W

å

å

å

v

v

v

colswe uses the swedish_1 collation, coleng uses the english_1 collation and col1 uses the Mimer SQL default ISO8BIT collation.

ORDER BY

You can use a COLLATE clause together with an ORDER BY clause to sort result sets. For example, the following statement:

SELECT *
   FROM table1
   ORDER BY col1 COLLATE swedish_1;

retrieves the data and sorts it on col1 according to the swedish_1 collation:

colswe

coleng

col1

A

A

A

a

a

a

W

W

W

v

v

v

å

å

å

Similarly, the following statement:
SELECT *
   FROM table1
   ORDER BY col1 COLLATE english_1;

retrieves the data and sorts it according to the english_1 collation:

colswe

coleng

col1

A

A

A

a

a

a

å

å

å

v

v

v

W

W

W

Note: Where the collation defines a number of characters with the same sort-order value, the retrieval order within the sort-order value is not defined.

GROUP BY

Depending on the collation associated with a column, you might get differing results when using GROUP BY.

For example, the statement:

SELECT col1, COUNT(*)
   FROM table1
   GROUP BY col1 COLLATE swedish_1;

gives the following result:

col1

A
2
W
2
å
1

According to the swedish_1 collation, two instances of the character 'a' were found and one instance of 'å' which is considered a separate character in the Swedish language.

Similarly, using the english_1 collation in the statement:

SELECT col1, COUNT(*)
   FROM table1
   GROUP BY col1 COLLATE english_1;

gives the following result:

col1

A
3
v
1
W
1

According to the english_1 collation, three instances of the character 'a' were found, as the character 'å' has the same sort-order value as 'A' and 'a'.

Comparison Operators

You can qualify the comparison operators (=, <>, <, <=, ...) with a COLLATE clause. For example:
SELECT col1
   FROM table1
   WHERE col1 > 'm';

would give the following result:

col1

å

v

However, explicitly using the COLLATE clause and the english_1 collation:

SELECT col1
   FROM table1
   WHERE col1 > 'm' COLLATE english_1;

would give the following result:

col1

W

v

Similarly, explicitly using the COLLATE clause and the swedish_1 collation:

SELECT col1
   FROM table1
   WHERE col1 > 'm' COLLATE swedish_1;

would give the following result:

col1

W

å

v

Scalar String Functions

You can use the COLLATE clause with the scalar string functions SUBSTRING and TRIM.

Character strings that are derived from a single string, for example, those returned from the TRIM and SUBSTRING functions, inherit the collation from the source string.

TRIM and COLLATE

You should be aware of the consequences when you use a TRIM statement on a column that has a collation.

For example, referring to table1, see Using Collations - Examples, the following statement:

SELECT TRIM('v' FROM colswe)
   FROM table1;

would trim both 'W' and 'v' from the result set as the characters 'W' and 'v' have the same sort-order value in a Swedish case-insensitive collation.

Similarly, the following statement:
SELECT TRIM('a' FROM col1)
   FROM table1;

would trim 'A', 'a' and 'å' from the result set as the characters 'A', 'a' and 'å' have the same sort-order value in an English case-insensitive collation.

Concatenation Operator

Suppose you want to concatenate columns, colswe and col1, for example:

SELECT colswe || coleng
   FROM table1;

Because the columns use different collations the result set will have the default collation ISO8BIT.

However, if you want apply a collation to the result set, you can add a COLLATE clause. for example:

SELECT (colswe || coleng) COLLATE swedish_1
   FROM table1;

IN and BETWEEN

A collation will affect the results of a query that uses IN or BETWEEN.

For example, the following statement:

SELECT *
   FROM table1
   WHERE coleng BETWEEN 'a' and 'B';

returns:

colswe

coleng

col1

A

A

A

a

a

a

å

å

å

But, the next statement:

SELECT *
   FROM table1
   WHERE colswe BETWEEN 'a' and 'B';

returns

colswe

coleng

col1

A

A

A

a

a

a

UNION

When performing a UNION, you must know what collations are involved in order to ensure that you get the result you want.

For example, the following statement:
SELECT colswe
   FROM table1

UNION

SELECT coleng
   FROM table1;

raises an error because the UNION operator can't understand which duplicate rows to remove or not.

To apply a swedish_1 collation to the result, you would explicitly use a COLLATE clause, for example:

SELECT colswe
   FROM table1

UNION

SELECT coleng COLLATE swedish_1
   FROM table1;

which would return:

colswe

A

W

å

Similarly, for a result collated according to the english_1 collation, you would enter:

SELECT colswe COLLATE english_1
   FROM table1

UNION

SELECT coleng
   FROM table1;

which would return:

colswe

A

v

W

DISTINCT

When you use DISTINCT, you must consider the consequences of which collation is associated with a column.

In the following example:

SELECT DISTINCT col1
   FROM table1;

All entries in col1 are considered DISTINCT as it uses the Mimer SQL default collation ISO8BIT:

col1

A

W

a

v

å

However, in this next statement:

SELECT DISTINCT colswe
   FROM table1;

colswe uses the swedish_1 collation. 'å' is considered to be distinct and 'v' and 'W' are not:

colswe

A

W

å

Similarly, in this example:

SELECT DISTINCT coleng
   FROM table1;

coleng uses the english_1 collation, 'v' and 'W' are considered to be distinct and 'å' is not:

coleng

A

v

W


Working with Data

This chapter deals with manipulating the data in tables with the statements:

  • INSERT for inserting new rows into tables
  • UPDATE for updating rows
  • DELETE for deleting rows from tables
  • CALL for manipulating data by executing procedures.

Access Privileges

You must have the appropriate access privileges on the relevant table(s) in order to use INSERT, UPDATE or DELETE.

In addition, the table itself must be updatable. All base tables are updatable, but some views are not, see Updatable Views.

In order to make a CALL you must have EXECUTE privilege on the procedure.


Inserting Data

The INSERT statement is used to insert new rows into existing tables.

Values to be inserted may be specified explicitly, as constants or expressions, or in the form of a subselect, see below.

The data to be inserted must be of a type compatible with the corresponding column definition.

If the length of the inserted data differs from that of the column definition, the data is handled as follows:

Data
Explanation

Character strings

If the inserted data is longer than the column definition, an error is reported and the INSERT operation fails (trailing spaces are truncated without error).

If the inserted data is shorter than the column definition, it is padded to the right with spaces to the required length when inserted into a fixed-length character column. The inserted data is not padded when inserted into a VARCHAR or NCHAR VARYING column.

Decimal values

Decimal values which are longer than the column definition are truncated (not rounded) from the right to meet the column definition. Thus 12.3456 is inserted into DECIMAL(6,3) as 12.345.

Decimal values which are shorter than the column definition are padded to the right of the decimal point with zeros. Thus 12.3 is inserted into DECIMAL(6,3) as 12.300.

Integer values

If the inserted data has more digits than the column definition or is outside the range of the definition, an error is reported and the INSERT operation fails.

Floating point values

Floating point values are converted to decimal by truncating the fractional part of the value as required by the scale of the decimal target. An error occurs if the scale of the target cannot accommodate the integral part of the value.

Datetime values

Date values are converted to timestamp by setting the hour, minute and second fields to zero. Time values are converted to timestamp by taking values for the year, month and day fields from CURRENT_DATE. Timestamp values are converted to date or time by discarding the field values that do not appear in the target.

Interval values

Single field interval values are converted to exact numeric by truncating decimal digits or by padding decimal digits with zeros. If any loss of leading precision occurs, or if overflow occurs, an error is raised.

Inserting Explicit Values

The explicit INSERT statement has the general form:

INSERT INTO table [(column-list)]
   VALUES (value-list);

Values in the value-list are inserted into columns in the column-list in the order specified.

The order of columns in the column-list need not be the same as the order in the table definition. Any columns in the table definition which are not included in the column-list are assigned NULL values, or the column default value if one is defined.

An explicit INSERT statement can only insert a single row.

For example:

Insert the values 'GW', 'Guinea-Bissau' and 'XOF' into the CODE, COUNTRY and CURRENCY_CODE columns respectively into the COUNTRIES table:
INSERT INTO countries(code, country, currency_code)
   VALUES ('GW', 'Guinea-Bissau', 'XOF');

inserts the row:

CODE

COUNTRY

CURRENCY_CODE

GW

Guinea-Bissau

XOF

If you insert explicit values into all of the columns in a table, the column list can be omitted from the INSERT statement. The values specified are then inserted into the table in the order that the columns are defined in the table.

Thus the example above could also be written:

INSERT INTO countries
   VALUES ('GW', 'Guinea-Bissau', 'XOF');

Inserting Results of Expressions

You can also insert the result of an expression into a table:

INSERT INTO mimer_store.customers(customer_id,
                                  title, surname, forename,
                                  date_of_birth,
                                  address_1, address_2, town,
                                  postcode, country_code,
                                  email, password,
                                  registered)
   VALUES (DEFAULT,
           'Mr', 'Eriksson', 'Sven',
            mimer_store.cast_to_date('25/10/1953'),
           'Kungsgaten 64', 'Box 1713', 'Uppsala',
           '751 47', 'SE',
           'training@mimer.com', 'secret',
           CURRENT_DATE - INTERVAL '3' DAY);

Inserting with a Subselect

Values to be inserted can also be specified in the form of a subselect, i.e. fetched from another table in the database.

INSERT INTO formats
   SELECT 11, 'Book & Cassette', MAX(category_id),
       MAX(display_order) + 10
   FROM formats NATURAL JOIN categories
   WHERE category = 'Books';

Inserting the result of a subselect can insert a number of rows into a table. If any of the rows are rejected (e.g. because of a duplicate primary or unique key), the whole INSERT statement fails and no rows are inserted.

Inserting Sequence Values

The value to be inserted can be the value of a sequence. The constructs that return the current value or next value of a sequence can be used as column values in the INSERT statement:

INSERT INTO products(product, product_id)
   VALUES ('SQL Reference', NEXT_VALUE OF product_id_seq);

INSERT INTO mimer_store_music.titles(item_id, artist_id)
   VALUES (CURRENT_VALUE OF mimer_store.item_id_seq, 500999);

Inserting NULL Values

The keyword NULL may be used to insert the NULL value into a column, provided that the column is not defined as NOT NULL:

INSERT INTO tracks(item_id, track_no, track, length)
   VALUES (60099, 14, 'Bayamesa', NULL);

The NULL indicator is implicitly inserted into columns when no value is given for that column and the column definition does not include a default value.

Thus, the following INSERT statement will give the same results as the example above:

INSERT INTO tracks(item_id, track_no, track)
   VALUES (60099, 14, 'Bayamesa');


Updating Tables

Data in existing table rows can be changed with the UPDATE statement. This statement has the general form:

UPDATE table
   SET column = value
   WHERE search-condition;

The search condition specifies which rows in the table are to be updated. If no search condition is specified, all rows will be updated.

Update the exchange rate for US dollars to 0.8886:
UPDATE CURRENCIES
   SET EXCHANGE_RATE = 0.8886
   WHERE CODE = 'USD';
Discount all Sony prices by 10 percent:
UPDATE items
   SET price = price * 0.90
   WHERE producer_id = (SELECT producer_id
                           FROM producers
                           WHERE producer = 'Sony');

Primary key columns can be updated provided the table is stored in a databank with the TRANSACTION or LOG option.


Deleting Rows from Tables

The DELETE statement removes rows from a table, and has the general form:

DELETE FROM table
   WHERE search-condition;

The search condition specifies which rows in the table are to be deleted. If no search condition is specified, all rows will be deleted (the table is emptied but not dropped).

Delete all countries that begin with the letter 'D' from the COUNTRIES table:
DELETE FROM countries
   WHERE country LIKE 'D%';
Delete all rows from the CUSTOMERS table:
DELETE FROM customers;
Delete all Sony items:
DELETE FROM mimer_store.items
   WHERE producer_id = (SELECT producer_id
                           FROM mimer_store.producers
                           WHERE producer = 'Sony');


Calling Procedures

In addition to using data manipulation statements directly, as just described, it is also possible to manipulate table data by calling a procedure. Procedures perform the specific data manipulations laid out in the procedure definition.

Any SQL statement in the grouping procedural-sql-statement, see the Mimer SQL Reference Manual, Procedural SQL Statements, can be used in a procedure, and this includes all the data manipulation statements.

The use of procedures allows data manipulation within the database to be controlled both in terms of strictly defining which data manipulation operations are performed and also in terms of regulating which database objects can be affected.

In the CALL statement, the value-expressions or assignment targets specified for each of the procedure parameters must be of a data type that is assignment-compatible, see the Mimer SQL Reference Manual, Assignments, with the parameter data type.

See the Mimer SQL Reference Manual, CALL, for full details of the CALL statement and the Point Display, for a general discussion of the stored procedure functionality supported in Mimer SQL.

Examples of Calling Procedures

Invoke the procedure called SEARCH in the MIMER_STORE_MUSIC schema:
CALL mimer_store_music.search(:title, :artist, CAST(NULL as integer));


Updatable Views

INSERT, UPDATE and DELETE statements may be used on views.

The operation is then performed on the base table upon which the view is defined. However, certain views may not be updated (for example a view containing DISTINCT values, where a single row in the view may represent several rows in the base table).

A view is not updatable if any of the following conditions are true:

  • the keyword DISTINCT is used in the view definition
  • the select list contains components other than column specifications, or contains more than one specification of the same column
  • the FROM clause specifies more than one table reference or refers to a non-updatable view
  • the GROUP BY clause is used in the view definition
  • the HAVING clause is used in the view definition

    • Note: By defining an INSTEAD OF trigger any view can be made updatable. If all the INSTEAD OF triggers on the view are dropped, the view will revert to not updatable if one or more of the conditions are true.


Managing Transactions

This chapter discusses transaction principles, logging and handling transactions.


Transaction Principles

A transaction is an environment where it is possible to COMMIT all of the operations performed within it, or to ensure that all of them fail.

Transaction Phases

In general, three transaction phases exist:

  • build-up, during which the database operations are requested
  • prepare, during which the transaction is validated
  • commitment, during which the operations performed in the transaction are written to disk.

Transaction build-up, which may be started explicitly or implicitly; prepare and commitment are both initiated explicitly through a request to commit the transaction (using COMMIT).

In interactive application programs, build-up takes place typically over a time period determined by the user, while prepare and commitment are part of the internal process of committing a transaction, which occurs on a time-scale determined by machine operations.

The transaction begins by taking a snapshot of the database in a consistent state.

During build-up, changes requested to the contents of the database are kept in a write-set and are not visible to other users of the system. This allows the database to remain fully accessible to all users. The application program in which build-up occurs will see the database as though the changes had already been applied. All changes requested during transaction build-up become visible to other users when the transaction is successfully committed.

A major function of the transaction handling in Mimer SQL multi-user systems is concurrency control. This means protecting the database from inconsistency which might arise when two users attempt to change the same information at the same time.

Mimer SQL supports distributed transactions based on the XA interface as defined by the Open Group and Microsoft's Distributed Transaction Coordinator (DTC) protocol. This means that Mimer SQL can be used in application environments that support distributed transactions.

See the Point Display, for a more detailed discussion of transaction handling and database security.


Logging Transactions

Transaction control also provides the basis for protection of the database against hardware failure.

Changes made to a database may be logged, to provide back-up protection in the event of hardware failure, provided that the changes occur within a transaction and that the databanks involved have the LOG option. Transaction handling is, therefore, important even in standalone environments where concurrency control issues do not arise.

The system logging databank, LOGDB is where transaction changes are recorded. It contains a record of all transactions executed since the latest back-up copy of a databank was taken and the log cleared. The latest back-up copy of the databank, together with the contents of LOGDB, may be used to restore the databank in the event of a databank crash.

Logging Options

Transaction control and logging is determined at the databank level by options set when the databank is defined.

The options are:

Option
Description

LOG

All operations on the databank are performed under transaction control. All transactions are logged.

TRANSACTION

All operations on the databank are performed under transaction control. No transactions are logged.

WORK

All operations on the databank are performed without transaction control (even if they are requested within a transaction), and are not logged.

Sets of operations (DELETE, UPDATE or INSERT on several rows) which are interrupted will not be rolled back.

READ ONLY

Only read only operations are allowed, i.e. DELETE, UPDATE or INSERT can not be performed on tables in a databank with this option.

All important databanks should be defined with LOG option, so that valuable data is not lost by any system failure.


Handling Transactions

Transaction control statements in Mimer SQL are:

  • COMMIT
  • COMMIT BACKUP
  • ROLLBACK
  • SET TRANSACTION READ ONLY
  • SET TRANSACTION READ WRITE
  • SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  • SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
  • SET TRANSACTION ISOLATION LEVEL READ COMMITTED
  • SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  • SET TRANSACTION START EXPLICIT
  • SET TRANSACTION START IMPLICIT
  • SET TRANSACTION DIAGNOSTICS SIZE
  • SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY
  • SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE
  • SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE
  • SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ
  • SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
  • SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  • SET SESSION CHARACTERISTICS AS TRANSACTION DIAGNOSTICS SIZE
  • START BACKUP
  • START TRANSACTION

SQL Statement Restrictions in Transactions

The following SQL statements may not be used inside a transaction:

ENTER

SET SHADOW

LEAVE

SET TRANSACTION

SET DATABANK

START BACKUP

SET DATABASE

START TRANSACTION

SET SESSION

Data definition statements (e.g ALTER, DROP, UPDATE STATISTICS) may be used inside a transaction provided they are the only statement executed in that transaction.

Optimizing Transactions

It is strongly recommended that the SET TRANSACTION READ ONLY option be used for each transaction that does not perform updates to the database and that the SET TRANSACTION READ WRITE option be used only when a transaction performs updates.

Taking a little extra care to set these options appropriately will ensure the transaction performance remains optimal at all times.

The default transaction read option can be defined by using SET SESSION, see Default Transaction Options. If this has not been used to set the default transaction read option, the default is READ WRITE.

Consistency Within a Transaction

The SET TRANSACTION ISOLATION LEVEL options are provided to control the degree to which the updates performed by one transaction are affected by the updates performed by other transactions which are executing concurrently.

The default isolation level can be defined by using SET SESSION, see Default Transaction Options. If this has not been used to set a default isolation level, the default is REPEATABLE READ. This isolation level guarantees that the end result of the operations performed by two or more concurrent transactions is the same as if the transactions had been executed in a serial fashion, except that an effect known as `Phantoms' may occur.

This is where one transaction reads a set of rows that satisfy some search condition. Another transaction then performs an update which generates one or more new rows that satisfy that search condition. If the original query is repeated (using exactly the same search condition), extra rows appear in the result-set that were previously not found.

The other isolation levels are: READ UNCOMMITTED, READ COMMITTED and SERIALIZABLE.

All four isolation levels guarantee that each transaction will be executed completely or not at all and that no updates will be lost.

Refer to Mimer SQL Reference Manual, SET TRANSACTION, for a full description of the effects that are possible, or guaranteed never to occur, at each of the four isolation levels.

Default Transaction Options

· SET SESSION

The SET SESSION statement is provided so that default values for certain transaction control settings can be defined.

SET SESSION allows the default settings for SET TRANSACTION READ and SET TRANSACTION ISOLATION LEVEL to be defined.

· SET TRANSACTION READ and SET TRANSACTION ISOLATION LEVEL

The transaction control settings defined by SET TRANSACTION READ, see Optimizing Transactions, and SET TRANSACTION ISOLATION LEVEL, see Consistency Within a Transaction, apply to the single next transaction to be started. If these statements are not used explicitly before each transaction, the default settings apply.


Creating a Database

This chapter describes the SQL statements for creating and managing the database structure. Examples are based on the database listed in Appendix A The Example Environment.

In addition, Mimer BSQL provides specific commands for listing and describing database objects, see Mimer BSQL.

SQL includes statements for creating and modifying the database structure:

  • create idents, schemas, databanks, shadows, domains, sequences, tables, triggers, functions, procedures, modules, views, indexes and synonyms
  • saving documentary comments on objects
  • altering the definition of idents, databanks, shadows and tables
  • dropping objects from the database.

All information describing the database structure is stored in the data dictionary.

Database Modelling

Before the database is defined, it is extremely important to design the database model. Well-functioning and efficient databases cannot be created without a model as the foundation.

Without careful design, much of the flexibility and efficiency inherent in a relational database structure may be lost.


Creating Idents and Schemas

Idents are authorized users of the system or groups of users defined for easier ident management, see Idents.

A schema defines a local environment within which private database objects can be created. The ident creating the schema has the right to create objects in it and to drop objects from it.

Ident Names

The case of letters is insignificant for an ident name and it must be composed of a unique sequence of case-less characters (e.g. the idents ABC and aBc cannot both exist in the database because they are identical when case is ignored).

Passwords

Passwords are composed of case-significant characters and must be entered exactly as they are defined.

The statement for creating idents has the general form:

CREATE IDENT username
AS ident-type
[USING 'password']
[WITH | WITHOUT   SCHEMA];

Passwords are required for user and program idents but are not used for group idents. Passwords are optional for OS_USER idents, an OS_USER with a password may connect to Mimer SQL in the same way as any other user ident.

Schemas

When a USER, OS_USER or PROGRAM ident is created, a schema with the same name can also be created automatically and the created ident becomes the creator of the schema. This happens by default unless WITHOUT SCHEMA is specified in the CREATE IDENT statement.

All private database objects created by an ident must belong to a schema which, by default, is the schema with the same name as the ident. When any private database object is created, its name can be specified in the fully qualified form that explicitly identifies which schema the object is to belong to. An ident may create objects in schemas `owned' by it (i.e. the schema created automatically when the ident was created and any schemas explicitly created by the ident).

An ident with IDENT or SCHEMA privilege can create additional schemas by using the CREATE SCHEMA statement. The objects belonging to the schema can be defined in the CREATE SCHEMA statement and created at the same time as the schema, refer to the Mimer SQL Reference Manual, CREATE SCHEMA for details.

Creating Idents and Schemas, Examples

Create a user ident MIMER_ADM with the password 'adm':

Note: Schema MIMER_ADM will also be automatically created.

CREATE IDENT mimer_adm AS USER USING 'adm';
Create a program ident AUDIT with the password 'economy' without creating a schema:
CREATE IDENT audit AS PROGRAM USING 'economy' WITHOUT SCHEMA;
Create a group ident:
CREATE IDENT mimer_admin_group AS GROUP;
Create a schema called MIMER_STORE:
CREATE SCHEMA mimer_store;
Create table CURRENCIES in the schema MIMER_STORE:
CREATE TABLE mimer_store.currencies (
      code CHARACTER(3) PRIMARY KEY,
      ...
Create schema called MIMER_STORE_NEW that contains sequence Z:
CREATE SCHEMA mimer_store_new
   CREATE UNIQUE SEQUENCE z;


Creating Databanks

A databank is the file where tables and sequences are stored. A Mimer SQL database may contain any number of databanks.

Create Databank Statement

The statement for creating a databank has the general form:

  • The CREATE DATABANK clause defines the databank name.
  • The optional FILESIZE clause is used to specify the initial file size (it will be dynamically extended as space is required). If the clause is omitted, an initial file size of 2000 kB is assumed. The optional GOALSIZE, MAXSIZE and MINSIZE attributes are used to manage the file size, see Mimer SQL Reference Manual, CREATE DATABANK.
  • The optional FILE clause defines the file where the databank is to be stored (the form of the filename follows the operating system file naming conventions). If the FILE clause is omitted, the file is created in the database home directory with the same name as databank-name.
  • The optional OPTION clause defines the transaction handling and logging option, see Logging Transactions. If the OPTION clause is omitted, the TRANSACTION option is assumed.
Examples
Create a databank called MIMER_BLOBS with the default parameters:

Note: The default parameters are with TRANSACTION option and size 2000 kB. This databank is created in a file called 'mimer_blobs.dbf'

CREATE DATABANK mimer_blobs;
Create the MIMER_STORE databank with LOG option, allocate 1200 MB for it, and store it in a file called 'mimer_store.dbf':
CREATE DATABANK mimer_store SET FILESIZE 1200 M,
                                FILE 'mimer_store.dbf',
                                OPTION LOG;

At this point, the databank is empty.


Creating Tables

After the physical file space has been allocated on a disk for the databank, (CREATE DATABANK), you can create the tables. The basic CREATE TABLE statement defines the columns in the table, the primary key, any unique or foreign keys and which databank the table is to be stored in. Table names and column names may be up to 128 characters long.

As a convention, we have defined primary key column(s) as the first column(s) in the example definitions. However, this is not a necessity; primary key columns may be defined anywhere in the column list. Primary keys are always NOT NULL, so there is no need to explicitly state that in the table definition.

Create Table Statement

Example
Create the table CURRENCIES with three columns in the MIMER_STORE schema.

The table shall be as follows:

  • Name the first column CODE, make it of the CHARACTER data type with a maximum of three characters.
  • Name the second column CURRENCY, make it of the CHARACTER data type with a maximum of 32 characters and don't allow NULL values to be stored in the column.
  • Name the third column EXCHANGE_RATE and make it of the data type DECIMAL with a total of twelve digits, four of which can be decimal values.
  • Declare the CODE column as the primary key and place this table in the MIMER_STORE databank.
    • CREATE TABLE mimer_store.currencies (
      code CHARACTER(3),
      currency CHARACTER(32) NOT NULL,
      exchange_rate DECIMAL(12, 4),
      PRIMARY KEY(code))
   IN mimer_store;

The CREATE TABLE clause defines the name of the table followed by a column list, which includes the names of the columns in the table, their data type, if they should allow the NULL indicator and the primary key declaration. Each item in the column-list is separated from the next by a comma, and the entire list is enclosed in parentheses.

A table definition may only include one primary key clause. The primary key can be made up of more than one column.

The IN clause states which databank the table is to be stored in. This clause may be omitted; if the IN clause is not specified, Mimer SQL will select the `best' databank in which to place the table.

The empty table now exists in the databank. Data is inserted into the table with the INSERT statement, see Inserting Data.

The preceding example shows the simplest form of column list. The following variants may also be used:

  • columns belonging to domains
  • columns defined with collations
  • default values (overriding any domain default for the column)
  • columns not belonging to the primary key defined as NOT NULL
  • unique constraints (in addition to the primary key)
  • foreign key constraints
  • check constraints.
The ITEMS Table

The ITEMS table in the example database is defined with many of the options that can be used in creating tables. See the Mimer SQL Reference Manual, CREATE TABLE, for more information.

The ITEMS table is defined as follows:

CREATE TABLE items (
      item_id internal_id DEFAULT NEXT_VALUE OF item_id_seq,
      product_id internal_id CONSTRAINT itm_product_id_is_null NOT NULL,
      format_id format_id CONSTRAINT itm_format_id_is_null NOT NULL,
      release_date DATE,
      status CHAR DEFAULT 'A' CONSTRAINT itm_status_is_null NOT NULL
                              CONSTRAINT itm_status_illegal
                                 -- Available, Deleted
                                 CHECK (status IN ('A', 'X')),
      price euros CONSTRAINT itm_price_illegal
                     CHECK (price >= 4.99 AND price <= 366.00),
      stock SMALLINT CONSTRAINT itm_stock_is_null NOT NULL
                     CONSTRAINT itm_stock_illegal CHECK (stock >= 0),
      reorder_level SMALLINT CONSTRAINT itm_reorder_level_is_null NOT NULL,
      ean_code BIGINT CONSTRAINT itm_ean_code_is_null NOT NULL,
      producer_id internal_id DEFAULT NULL,
      image_id internal_id DEFAULT NULL,
      CONSTRAINT itm_primary_key PRIMARY KEY(item_id),
      CONSTRAINT itm_ean_code_exists UNIQUE (ean_code),
      CONSTRAINT itm_products
         FOREIGN KEY (product_id) REFERENCES products(product_id)
         ON DELETE CASCADE ON UPDATE NO ACTION,
      CONSTRAINT itm_formats FOREIGN KEY (format_id) REFERENCES formats
         ON DELETE CASCADE ON UPDATE NO ACTION,
      CONSTRAINT itm_producers FOREIGN KEY (producer_id) REFERENCES producers
         ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT itm_images FOREIGN KEY (image_id) REFERENCES images
         ON DELETE SET DEFAULT ON UPDATE NO ACTION) IN mimer_store;

The ordering of column specifications, key clauses and check conditions is not fixed. If desired, the key and check clauses can be written in association with the respective column specifications.

Each constraint is given a name which allows it to be dropped and modified separately. The constraint name is also useful if a program wants to find out which constraint failed for a particular statement.

Column Definitions

Columns should in general be defined as NOT NULL unless there is a specific reason for using the NULL value in the column (e.g. is the value not known, not applicable or given some other meaning). The presence of NULL values can often complicate the formulation of queries, see Handling NULL Values.

Note: Take particular care to exclude NULL from numerical columns which are to be used for mathematical operations.

Domains are used for many columns to help in maintaining database integrity. By using the same domain for columns in different tables, the column data types are guaranteed to be consistent. See Creating Domains for more information

The Primary Key Constraint

The purpose of a primary key is to define a key value that uniquely identifies each table row, therefore the primary key value for each row in the table must be unique.

The primary key constraint can consist of more than one column in the table. The choice of columns to use as the primary key is determined by the relational model for the database, which is outside the scope of this manual.

Unique Constraints

A unique constraint can be defined for one or more columns in the table. The list of columns that make up the unique constraint are specified in the UNIQUE clause for the table when it is created.

Specifying UNIQUE in the definition of a column in the table is equivalent to supplying a list of one column in the UNIQUE clause for the table and effectively specifies a one-column unique constraint.

Foreign Keys - Referential Constraints

Use foreign keys to maintain integrity between the contents of related tables.

The effect of a foreign key is to constrain table data in a way that only allows a row in the referencing table which has a foreign key value that matches the specified key value of a row in the referenced table.

A referencing table row which has a foreign key value with the NULL value in at least one of the columns will always fulfil the referential constraint and therefore be acceptable as a row in the referencing table.

A foreign key constraint can be defined with a foreign key clause at CREATE TABLE or added afterwards using ALTER TABLE.

The table referenced in a foreign key clause can be an existing table or a table defined in the current statement (allowing self-referencing foreign keys at CREATE TABLE and circular foreign keys at CREATE SCHEMA).

The number of columns listed as FOREIGN KEY must be the same as the number of columns in the primary key or unique key of the REFERENCES table. See the CREATE TABLE syntax in the Mimer SQL Engine Reference Manual for details.

The nth FOREIGN KEY column corresponds to the nth column in the primary key of the REFERENCES table, and the data types and lengths of corresponding columns must be identical.

A table definition may contain several FOREIGN KEY references. Each column in the table may be used in many FOREIGN KEY clauses, but only once per FOREIGN KEY clause.

Note: A table containing a foreign key reference or referenced in a foreign key must be stored in a databank with either the TRANSACTION or LOG option.

Foreign Key Example

The ITEMS table has four foreign key references:

CREATE TABLE items (
      item_id internal_id DEFAULT NEXT_VALUE OF item_id_seq,
      product_id internal_id CONSTRAINT itm_product_id_is_null NOT NULL,
      format_id format_id CONSTRAINT itm_format_id_is_null NOT NULL,
          .
          .
      producer_id internal_id DEFAULT NULL,
      image_id internal_id DEFAULT NULL,
          .
          .
      CONSTRAINT itm_products
         FOREIGN KEY (product_id) REFERENCES products(product_id)
         ON DELETE CASCADE ON UPDATE NO ACTION,
      CONSTRAINT itm_formats FOREIGN KEY (format_id) REFERENCES formats
         ON DELETE CASCADE ON UPDATE NO ACTION,
      CONSTRAINT itm_producers FOREIGN KEY (producer_id) REFERENCES producers
         ON DELETE NO ACTION ON UPDATE NO ACTION,
      CONSTRAINT itm_images FOREIGN KEY (image_id) REFERENCES images
         ON DELETE SET DEFAULT ON UPDATE NO ACTION)
          .
          .

These maintain referential integrity as follows:

· FOREIGN KEY (product_id) REFERENCES products(product_id)

Data that is not present in the PRODUCT_ID column of the PRODUCTS table will not be accepted in the PRODUCT_ID column in the ITEMS table.

· FOREIGN KEY (format_id) REFERENCES formats

Data that is not present in the FORMAT_ID column of the FORMATS table will not be accepted in the FORMAT_ID column in the ITEMS table.

· FOREIGN KEY (producer_id) REFERENCES producers

Data that is not present in the PRODUCER_ID column of the PRODUCERS table will not be accepted in the PRODUCER_ID column in the ITEMS table.

· FOREIGN KEY (image_id) REFERENCES images

Data that is not present in the IMAGE_ID column of the IMAGES table will not be accepted in the IMAGE_ID column in the ITEMS table.

Specifying ON DELETE

When defining a foreign key constraint it is possible to specify in an ON DELETE clause what action that shall take place if the corresponding record in the referenced table is deleted.

The possible actions are

· NO ACTION

Any attempt to delete a key value that is referenced by a foreign key will fail. This action is the default behavior.

· SET NULL

If a key value in the referenced table is deleted the corresponding values in the foreign key table is set to the null value

· SET DEFAULT

If a key value in the referenced table is deleted the corresponding values in the foreign key table is set to the default value for the columns in the foreign key

· CASCADE

If a key value in the referenced table is deleted the corresponding records in the foreign key table are also deleted

Check Constraints

Check constraints in table definitions are used to make sure that data in a column in the table fits certain conditions.

CREATE TABLE items (
          .
          .
      status CHAR (1) DEFAULT 'A' CONSTRAINT itm_status_is_null NOT NULL
                                  CONSTRAINT itm_status_illegal
                                     -- Available, Deleted
                                     CHECK (status IN ('A', 'X')),
      price euros CONSTRAINT itm_price_illegal
                     CHECK (price >= 4.99 AND price <= 366.00),
      stock SMALLINT CONSTRAINT itm_stock_is_null NOT NULL
                     CONSTRAINT itm_stock_illegal CHECK (stock >= 0),
          .
          .

The check clause defined on the PRICE column extends any limitations imposed by the EUROS domain definition. The extension applies only to this table, and does not affect other columns in the database that belong to the EUROS domain:

CREATE DOMAIN euros AS NUMERIC(7, 2)
             CONSTRAINT euros_value_is_null CHECK (VALUE IS NOT NULL)
             CONSTRAINT euros_value_illegal CHECK (VALUE > 0.0);

The constraint names, e.g. ITM_PRICE_ILLEGAL in the ITEMS table, can be used in an ALTER TABLE statement to drop the check constraint. All constraints, primary key, unique, not null and foreign key constraints can be named in this manner.

If no constraint name is given, a unique name is generated by the system. This name can be seen by using the describe statement in BSQL. See Mimer BSQL.

Ensure that either the customer's e-mail address and password are both defined or that neither is defined:
CREATE TABLE customers (
          .
          .
      email VARCHAR(128) COLLATE english,
      password VARCHAR(18),
          .
          .
      CONSTRAINT cst_email_password_cross_check
         CHECK (   ((email IS NULL OR email = '')
                    AND (password IS NULL OR password = ''))
                OR ((email IS NOT NULL AND email <> '')
                    AND (password IS NOT NULL AND password <> ''))))
          .
          .


Creating Sequences

A sequence can be used to provide the default value for a domain or a table column, etc.

A sequence returns a series of integer values which is defined by specifying an initial value, a maximum value, an increment and whether the sequence is to be unique or not.

A sequence that has been initialized has a current value, which is returned from the function CURRENT_VALUE. The function NEXT_VALUE is used to initialize a sequence and to subsequently advance the current value of the sequence through its defined series of values.

A unique sequence will never return the same value twice.

Examples of Sequences

Create a sequence that returns odd numbers:
CREATE SEQUENCE seq_1 INITIAL_VALUE = 1 INCREMENT = 2 IN DATABANK userdb;
Create a sequence that defines the following series of values: 1, 4, 7, 10, 3, 6, 9, 2, 5, 8:
CREATE UNIQUE SEQUENCE seq_2
   INITIAL_VALUE = 1
   INCREMENT = 3
   MAX_VALUE = 10
   IN DATABANK userdb;
Create a table that uses a sequence to set a column default value:
CREATE TABLE objinfo (objid INTEGER DEFAULT NEXT_VALUE OF obj_seq,
                      description NCHAR VARYING(1000));


Creating Domains

Domains are used as data types in column definitions when creating tables in order to:

  • assist in keeping the database consistent
  • limit the data (particular values or data type) accepted in the columns
  • define default values for columns.

Create Domain Statement

The statement for creating domains has the general form:

CREATE DOMAIN domain-name
   AS data-type
   [DEFAULT default-value]
   [{[CONSTRAINT constraint_name] CHECK (check-condition)}...];
  • The CREATE DOMAIN clause defines the domain name.
  • The AS clause defines the domain data type.
  • The DEFAULT clause defines a default value for the domain
  • The CHECK clause defines the domain limits.

It is a good practice for maintaining the integrity of the database to define domains for as many columns as possible.

Domains with a Default Value

The default clause defines values that are inserted into the column when an explicit value is not specified or the keyword DEFAULT is used in an INSERT statement.

Examples
Define the default value '000000' for the domain SIXDIGITS:
CREATE DOMAIN sixdigits AS CHAR(6) DEFAULT '000000';
Define the session user's name as the default value for the domain USER_NAME:
CREATE DOMAIN user_name AS VARCHAR(128)
   DEFAULT SESSION_USER;

Domains with a Check Clause

Domains defining default values can also include check clauses. You could define the SOUNDEX domain as:

CREATE DOMAIN sixdigits AS CHAR(6) DEFAULT '000000'
                           CHECK (VALUE IS NOT NULL)
                           CHECK (CHAR_LENGTH(TRIM(VALUE)) = 6
                                  AND VALUE BETWEEN '000000' AND '999999');

This means that the NULL indicator will not be accepted into columns belonging to this domain and that the value must be a character string of six digits.

If the default value is defined as being outside the check constraint this ensures that an explicit value must always be inserted into the column:

Searching and Check Clauses

Specification of a CHECK clause means that only values for which the specified search condition evaluates to true may be assigned to a column belonging to the domain.

The search condition, see the Mimer SQL Reference Manual, Search Conditions, in the CHECK clause may only reference the domain values (by using the keyword VALUE), constants, or the keywords CURRENT_USER, SESSION_USER and NULL.


Creating Functions, Procedures, Triggers and Modules

Functions and procedures are SQL routines that are stored in the data dictionary.

A module is a collection of SQL routines.

Triggers contain the same constructs as routines but are created on tables or views (depending on the type of trigger) and execute instead of, before or after a specified data manipulation operation.

Refer to the Mimer SQL Reference Manual, SQL Statement Descriptions for the syntax definitions for CREATE FUNCTION, CREATE MODULE, CREATE PROCEDURE and CREATE TRIGGER, and the Point Display for a general discussion of the stored procedure functionality in Mimer SQL.

Creating Functions and Procedures

The CREATE FUNCTION statement is used to create a function that does not belong to a module and the CREATE PROCEDURE statement is used to create a procedure that does not belong to a module.

The format of the routine definition is the same in the CREATE FUNCTION and CREATE PROCEDURE statements as it is in a function or procedure declaration in a module.

Creating a Module

A module is created by using the CREATE MODULE statement and all the routines that belong to the module are defined by declaring them within the CREATE MODULE statement.

Routines cannot be added to a module after the module has been created and a routine cannot be removed from the module it belongs to. The routines in a module behave in all respects as single objects (e.g. EXECUTE privilege is applied on individual routines in a module, not the module). If the module is dropped, all the routines in it are dropped.

Creating a Trigger

The CREATE TRIGGER statement is used to define a trigger on a table or view.

Examples

Note: The examples that follow show the `@' character which is used in Mimer BSQL to delimit SQL statements whose syntax involves use of the normal end-of-statement character `;' before the actual end of the statement.
This is the case for many of the SQL/PSM statements. See Mimer BSQL for details.
The `@' character may be used to delimit any statement. This is useful when dealing with large statement as the error reporting facility in BSQL shows more information in such cases.

Create a standalone function FUNC_1 with one input parameter of data type VARCHAR(20) that returns a value of data type INTEGER:
@
CREATE FUNCTION func_1(p1 VARCHAR(20)) RETURNS INTEGER
BEGIN
   ...
END
@
Create a standalone procedure PROC_1 with one input parameter of data type INTEGER and one output parameter of VARCHAR(20):
@
CREATE PROCEDURE proc_1(IN p_value1 INTEGER,
                        OUT p_value2 VARCHAR(20))
BEGIN
   ...
END
@
Create a module M1 containing 2 procedures, PROC_1 (with no parameters), PROC_2 (one input parameter, X, of data type INTEGER) and 1 function, FUNC_1 (with no parameters, returning an INTEGER):
@
CREATE MODULE m1
   DECLARE PROCEDURE proc_1()
   READS SQL DATA
   BEGIN
      ...
   END;

   DECLARE PROCEDURE proc_2(IN p_x INTEGER)
   MODIFIES SQL DATA
   BEGIN
      ...
   END;

   DECLARE FUNCTION func_1() RETURNS INTEGER
   READS SQL DATA
   BEGIN
      ...
   END;
END MODULE
@
Create a trigger that will execute after INSERT operations on table PRODUCTS:
@
CREATE TRIGGER products_after_insert AFTER INSERT ON products
REFERENCING NEW TABLE AS pdt
FOR EACH STATEMENT
BEGIN ATOMIC
   ...
END
@

Note:	It is recommended that all functions, procedures and triggers are created by executing a command file so that they may be easily re-created in the event of being unintentionally dropped because of CASCADE effects following a drop.
The effect of CASCADE can be quite far-reaching where routines and modules are concerned, see the Mimer SQL Programmer's Manual.
The use of a command file also facilitates module re-definition by dropping an existing module, altering the CREATE MODULE statement in the command file and creating the new, redefined module.


Creating Views

A view is a logical subset of one or more base tables or views where columns are chosen by naming them and rows are chosen through specified conditions relating to column values.

Views are created, for example, so that users who need not see all the data in a single table are shown only the parts of the table that interest them (restriction views). Views can also be created as a combination of a number of columns from several different tables (join views).

Operations on views are actually performed on the underlying base tables. Certain view definitions do not allow data to be changed in the view (read-only views). See Updatable Views for further details.

View names can be up to 128 characters long. Views are defined in terms of a SELECT statement; the result of the SELECT statement forms the contents of the view. There are no restrictions on which select statements that can be used in a view definition.

Creating a View

Create a restriction view on the CUSTOMERS table called CUSTOMER_DETAILS containing limited information:
CREATE VIEW customer_details
   AS SELECT surname, forename, address_1, address_2, town, postcode,
             title, date_of_birth, country_code, customer_id
   FROM customers;

The columns in the view are named after the columns listed in the SELECT clause, since the view definition does not include a list of column names.

Create a join view, including an outer join:
CREATE VIEW product_details
   AS SELECT product, COALESCE(producer, ' ') AS producer, format,
             price, stock, reorder_level, release_date, ean_code,
             status, product_search, item_id, category_id, product_id,
             display_order, image_id
   FROM products
   NATURAL JOIN items
   NATURAL JOIN formats
   NATURAL LEFT OUTER JOIN producers;

Check Option

The check option can be used in updatable view definitions to limit the data that can be inserted into the view. If a check option is specified, data which does not fulfill the definition of the view cannot be inserted into the view.

CREATE VIEW swedish_customers
   AS SELECT *
   FROM customer_details
   WHERE country_code = 'SE'
   WITH CHECK OPTION;

The check option in the view definition (WITH CHECK OPTION) means that the COUNTRY_CODE column must be set to SE if new rows are inserted to into the view or rows are updated using the view.

If there is an instead of trigger defined for the view, the WITH CHECK OPTION does not have any effect.

Creating Views Based on Other Views

Views can be based on other views. When a view is created based upon another view or views, the original view's limitations are carried over to the new view.

CREATE VIEW customer_addresses (surname, forename, recipient,
      address_1, address_2, town, postcode, country,
      salutation, customer_id)
   AS SELECT surname, forename,
      UPPER(recipient(title, forename, surname)), address_1,
      COALESCE(address_2, ' '), UPPER(town), UPPER(postcode), UPPER(country),
      salutation(title, forename, surname, date_of_birth, country_code),
      customer_id
   FROM customer_details
   JOIN countries ON code = country_code;


Creating Secondary Indexes

A secondary index is automatically used during searching when it improves the efficiency of the search.

Secondary indexes are maintained by the system and are invisible to the user.

Any column(s) may be specified as a secondary index, except columns declared using a LOB data type.

Columns in the PRIMARY KEY, the columns of a FOREIGN KEY and columns defined as UNIQUE are automatically indexed, (in the order in which they are defined in the key), and therefore creation of an index on these columns will not improve performance.

Secondary index tables are purely for Mimer SQL's internal use - you create the index, and Mimer SQL handles the rest.

If, for instance, you want to know which products were released on a specific date, Mimer SQL would have to search successively through the entire ITEMS table to find all items that matched the date you specified. If, however, you create a secondary index on release date, Mimer SQL would locate that date directly in the secondary index, which would save time.

Secondary indexes can improve the efficiency of data retrieval; but introduces an overhead for write operations (UPDATE, INSERT, DELETE). In general, you should create indexes only for columns that are frequently searched.

Indexes cannot be created directly on columns in views. However, since searching in a view is actually implemented as searching in the base table, an index on the base table will also be used in view operations.

Examples of Secondary Index

Create a secondary index called ITM_RELEASE_DATE on the RELEASE_DATE column in the ITEMS table:
CREATE INDEX itm_release_date ON items(release_date);

Primary key columns may also be included in a secondary index. If a table has the primary key columns A, B, and C, the primary index would cover all three columns of the primary key.

The following combinations of the columns in the primary key are automatically indexed: A, AB, and ABC. In addition, you could create secondary indexes on columns B, C, BC, AC etc.

An index may also be defined as UNIQUE, which means that the index value may only occur once in the table. (For this purpose, NULL is treated as equal to NULL). However, it is preferable to use unique constraints.

Create a UNIQUE secondary index called ITM_EAN_CODE on the EAN_CODE column in the ITEMS table:
CREATE UNIQUE INDEX itm_ean_code ON ITEMS(ean_code);

Sorting Indexes

The sorting order for indexes may be defined as ascending or descending. However, this makes no difference to the efficiency of the index, since Mimer SQL searches indexes forwards or backwards depending on the circumstances. I.e. the following two indexes are compatible, and only one of them is required.

CREATE INDEX idx_asc  ON t1 (c1 ASC)
CREATE INDEX idx_desc ON t1 (c1 DESC)

In some cases specifying the sort order makes sense. For example when ordering the result set by mixed orders, e.g:

SELECT * FROM t1
ORDER BY c1 ASC, c2 DESC;

In this case the index below is appropriate:

CREATE INDEX idx_mix ON t1 (c1 ASC, c2 DESC);


Creating Synonyms

Synonyms, or alternative names can be created for tables, views or other synonyms. You can create synonyms to personalize tables or just for your own convenience. Synonym names can be made up of a maximum of 128 characters.

Table names are `qualified' by the name of the schema to which they belong. The qualified form of the table name is the schema name followed by the table name and the two are separated by a period.

Synonyms are particularly useful when several users refer to a common table, such as MIMER_STORE.ITEMS, MIMER_STORE.CURRENCIES, etc. With synonyms, several users can work in the same apparent environment without needing to refer to the tables by their qualified names.

Synonym Examples

The table ITEMS in the schema MIMER_STORE has the qualified name:

MIMER_STORE.ITEMS

The ident called MIMER_STORE need only refer to it as:

ITEMS

If other users wish to use this table, they must refer to it by its fully qualified name since they do not have the same name as the schema to which the table belongs.

If a user named JAMES, who wishes to refer to the ITEMS table, belonging to the schema MIMER_STORE, as simply ITEMS, he can create a synonym.

In the following example, the schema name JAMES is implied by default (which must also have been created by user JAMES if the CREATE is to succeed) because the synonym name is specified in its unqualified form (and the default schema name is the name of the current ident):

CREATE SYNONYM items FOR mimer_store.items;

Another user can then create his own synonym for the ITEMS synonym that now exists in schema 'JAMES', which has the fully qualified name:

JAMES.ITEMS


Commenting Objects

Comments may be stored against any of the following objects:

COLUMN

FUNCTION

MODULE

SEQUENCE

TABLE

DATABANK

IDENT

PROCEDURE

SHADOW

TRIGGER

DOMAIN

INDEX

SCHEMA

SYNONYM

VIEW

Comments cannot be deleted - they can only be replaced by a new comment. A blank string may be provided as a comment if you want to suppress an existing comment.

Only the creator of the object may store a comment for it.

Comments are for information only and do not affect data retrieval or manipulation in any way.

Comments may be read with the DESCRIBE command, see DESCRIBE, or by retrieving the appropriate columns from the INFORMATION_SCHEMA views, see the Mimer SQL Reference Manual.

Comment Example

Store the comment 'Holds currency details' on the CURRENCIES table:
COMMENT ON TABLE currencies IS 'Holds currency details';


Altering Databanks, Tables and Idents

The following sections explain how to alter databanks, tables and idents. you can also read about which objects you cannot alter.

Altering a Databank

Databanks can only be altered by their creator.

There are three uses for the ALTER statement:

  • to change the physical file location for a databank
  • to change the transaction and logging options on the databank
  • to manage the file size allocated for the databank.
Examples
Change which file the MIMER_ORDERS databank is stored in from its previous file to file 'DISK2:MIMER_ORDERS.DBF':

Note: The file specification is in Alpha/OpenVMS format.

ALTER DATABANK mimer_orders SET FILE 'DISK2:MIMER_ORDERS.DBF';

Note:	This statement changes the file name stored for the databank in the data dictionary. It does not actually move the databank to the new location.
To move a databank, begin by copying or renaming the file in the operating system and then use ALTER DATABANK... SET FILE to change the file specification in the data dictionary.
Change the option on the MIMER_BLOBS databank from TRANSACTION to LOG:
ALTER DATABANK mimer_blobs SET OPTION LOG;
Set the size of the MIMER_BLOBS database to 2000 MB:
ALTER DATABANK mimer_blobs SET FILESIZE 2000 M;

Note:	Use of the ALTER DATABANK... SET FILESIZE statement is not strictly necessary because databank files are extended dynamically. However, increasing the file allocation by a relatively large figure can help to minimize file fragmentation and improve response times.

Altering Tables

The ALTER TABLE statement changes the definition of the specified table and may only be used by the creator of the schema to which the table belongs.

There are the following uses for the ALTER TABLE statement:

  • to add a new column or table constraint definition to an existing table
  • to drop a column or table constraint from an existing table
  • to change the default value for a column in an existing table
  • to drop the default value for a column in a table
  • to change a column in an existing table to have a specified data type, provided the old and new data types are assignment-compatible, see the Mimer SQL Reference Manual and the column is not referenced by any constraints or views

A new column created with the ALTER TABLE... ADD statement is appended to end of the existing column list. The new column will include the default value defined for the column or defined for the domain to which it belongs or, if no default value exists, the NULL value.

Note: If a column added to a table is defined as NOT NULL, then it must have a default value defined or belong to a domain which has a default value, because the NOT NULL column cannot be given NULL values.

Examples
Add a column called CREDIT_RATING with a data type of CHAR(1) to the CUSTOMERS table:
ALTER TABLE customers ADD credit_rating CHAR(1);

This creates a column containing the NULL value in each row in the table.

If a constraint is added to a table, the data in the table is checked to ensure it fulfills the restriction in the constraint.

Drop the column DATE_OF_BIRTH from the table CUSTOMERS, subject to the condition that there are no other objects dependent on this column:
ALTER TABLE customers DROP date_of_birth RESTRICT;
Drop the column DATE_OF_BIRTH from the table CUSTOMERS, if dependent objects exist, these are dropped as well:
ALTER TABLE customers DROP date_of_birth CASCADE;
Change the length of the column FORMAT in the table FORMATS:
ALTER TABLE formats ALTER COLUMN format VARCHAR(32);
Change the default value for the column REORDER_LEVEL, the new default value is one:
ALTER TABLE items ALTER reorder_level SET DEFAULT 1;
Drop the check constraint ITM_PRICE_ILLEGAL from the ITEMS table:
ALTER TABLE items DROP CONSTRAINT itm_price_illegal;
Redefine a foreign key constraint for the CUSTOMERS table:
ALTER TABLE customers DROP CONSTRAINT cst_countries;
ALTER TABLE customers ADD CONSTRAINT cst_countries
   FOREIGN KEY (country_code) REFERENCES countries
      ON DELETE CASCADE ON UPDATE NO ACTION;
Drop the default value for the column REGISTERED:
ALTER TABLE customers ALTER registered DROP DEFAULT;
Note on Dropping

When dropping a column from a table, the CASCADE and RESTRICT keywords can be used to specify the action that will be taken on objects that are dependent on the dropped column.

If CASCADE is specified, dependent objects are also dropped. For instance if a dropped column is part of a primary key, the primary key will also be dropped.

If RESTRICT (the default) is specified and there are other objects affected, the statement will be aborted, with an error condition. See also, Dropping Objects from the Database.

Altering Idents

Only passwords can be altered with the ALTER IDENT statement.

Ident names cannot be altered.

USER and PROGRAM idents can change their own password if they so wish.

Passwords can also be changed by the creator of the ident.

Change the ident MIMER_ADM's password to 'evjkl9u'.
ALTER IDENT mimer_adm USING 'evjkl9u';

Objects Which May Not Be Altered

Domains, functions, procedures, modules, triggers, views and indexes cannot be altered. It is therefore important that you think through your domains and views thoroughly and carefully before you create them to make sure that they suit the needs of your database.

The functions and procedures contained in a module are created when the module is created and thereafter no alterations can be made to the module (the module and all the routines contained in it can, of course, be dropped).

The next section will discuss dropping objects and the results of this on the database.


Dropping Objects from the Database

The DROP statement is used to drop the following objects from the database:

COLLATION

IDENT

SCHEMA

SYNONYM

DATABANK

INDEX

SEQUENCE

TABLE

DOMAIN

MODULE

SHADOW

TRIGGER

FUNCTION

PROCEDURE

STATEMENT

VIEW

The CASCADE or RESTRICT keywords may be used to specify the action to be taken if other objects exist that are dependent on the object being dropped:

  • If RESTRICT (the default) is specified, an error is returned if other objects are affected and the drop operation is aborted.
  • If CASCADE is specified, dependent objects are dropped as well.

System database objects can only be dropped by their creator. Private database objects can only be dropped by the creator of the schema to which they belong.

Therefore use caution when using the DROP statement with CASCADE, as the operation may have a recursive effect on all objects relating to it. For example, when a table is dropped, all views, synonyms, routines and triggers created on or referencing that table are also dropped.

The DROP statement removes whole objects from the database. It cannot be used to remove columns from tables, this is done by the ALTER TABLE statement, see Altering Tables.

Dropping Databanks and Tables

Drop the CURRENCIES table:
DROP TABLE currencies RESTRICT;

If the keyword CASCADE is specified, all views, synonyms and indexes based on CURRENCIES are also dropped as well as any functions, procedures and triggers referencing the table.

Drop the MIMER_STORE databank:
DROP DATABANK mimer_store RESTRICT;

If the keyword CASCADE is specified, all tables in the MIMER_STORE databank are also dropped and any views, synonyms, triggers and indexes based on those tables are also dropped as well as any functions, procedures and triggers referencing any of the dropped objects.

An attempt is automatically made to delete the physical databank file when a databank is dropped.

There may be occasions, because of access rights issues in the file system, when the database server's attempt to delete the physical databank file might fail. If recommended procedures for databank file management are followed, see the Mimer SQL System Management Handbook, the databank file should be deleted correctly.

Dropping Sequences

When a sequence is dropped, all the objects (i.e. constraints, domains, functions, procedures, default values, triggers and views) referencing the sequence are also dropped.

Drop the CUSTOMER_ID_SEQ sequence:
DROP SEQUENCE customer_id_seq CASCADE;

The specification of CASCADE ensures that the sequence is dropped even if it is being referenced by other objects in the database.

Dropping Domains

When a domain is dropped, columns using the domain retain the properties of the domain through the creation of column constraints.

Drop the EUROS domain:
DROP DOMAIN euros RESTRICT;

Note:	If you re-create a domain that has been dropped, the domain will be seen as a completely new domain and it will not be associated with any columns that belonged to the old domain.

To change the restrictions on the columns that were defined with a domain that has been dropped, use the ALTER TABLE statement.

Dropping Idents

When an ident is dropped, everything that the ident has created (including other idents and everything created by those idents) as well as all privileges granted by the ident are dropped. For this reason, physical users should never own objects, except for synonyms and personal views.

Drop the MIMER_ADM ident:
DROP IDENT mimer_adm RESTRICT;

Dropping Functions, Modules, Procedures and Triggers

The effect of using the keyword CASCADE can be rather dramatic when modules, routines and triggers are dropped. For this reason it is recommended that all modules, routines and triggers be created by running a command file so they can be easily reconstructed in case of being dropped by mistake.

Drop the function called MIMER_STORE_BOOK.FORMAT_ISBN:
DROP FUNCTION mimer_store_book.format_isbn CASCADE;
Drop the procedure called COMING_SOON:
DROP PROCEDURE coming_soon CASCADE;
Drop the module called MIMER_STORE_MUSIC.ROUTINES:
DROP MODULE mimer_store_music.routines CASCADE;
Drop the trigger called PRODUCTS_AFTER_INSERT:
DROP TRIGGER products_after_insert CASCADE;
About Dropping Modules and Routines

The following points should be noted when dropping modules and routines:

  • When a module is dropped, all the routines contained in it will be dropped.
  • If a routine is dropped and it is referenced from another object, the referencing object will also be dropped.
  • If a routine belonging to a module is to be dropped as a consequence of a cascade, only that routine is dropped (the other routines in the module and the module itself will remain unaffected).


Defining Privileges

Privileges control the operations which users are allowed to perform in the database. Well-structured privileges are essential for maintaining data security.

There are three types of privileges:

  • System privileges, which give the right to create global objects within the database.
  • Object privileges, which give rights over certain specified objects in the database.
  • Access privileges, which give rights of access to the data in a specified table or view.

System privileges are granted to the system administrator upon installation, and may be passed on to other idents. Objects and access privileges are initially granted only to the creator of an object. The creator may however pass the privileges on to other idents.

Granting and Revoking Privileges

Privileges are granted to idents with the GRANT statement and revoked from idents with the REVOKE statement.

All privileges may be granted with the `with grant option', which means that the receiver of the privilege in turn has the right to grant that privilege to other idents.

The creator of an object is automatically granted full privileges on that object with grant option. Thus the creator of:

  • a group is automatically a member of that group
  • a program ident may enter it
  • a table has full access privileges
  • a schema may create objects in it and drop them, etc.

When privileges that were granted with the `with grant option' are revoked, the right to grant those privileges to other idents is also revoked.

The `with grant option' can be revoked separately without revoking the privilege itself.

Idents may only grant privileges that they themselves possess to other idents, that is, idents cannot grant privileges to themselves.

Likewise, privileges may only be revoked by the grantor - idents cannot revoke privileges from themselves.

Certain operations are not controlled by explicit privileges, but may only be performed by the creator of the object involved. These operations include ALTER (with the exception of ALTER IDENT, which may be performed by either the ident himself or by the creator of the ident), DROP, and COMMENT.


Ident Structure

In the initial installation, one user ident, the system administrator with user ident name SYSADM, is automatically created.

SYSADM Privileges

The system administrator has the following privileges (with grant option):

  • BACKUP
  • DATABANK
  • IDENT
  • SCHEMA
  • SHADOW
  • SELECT access on all tables and views in the data dictionary.

The system administrator is ultimately responsible for the structure of the whole system. In other respects, however, the system administrator is an ordinary user ident in the system.

There is no ident in Mimer SQL with automatic right of access to all objects within the system.

It is quite possible, and may be advisable especially in large system, that the system administrator is prevented from accessing the actual contents of the database; the system administrator's job is to manage objects in the system, not work on the data.

About System Utilities

Certain system utilities may only be run by idents with BACKUP or SHADOW privilege, see the Mimer SQL System Management Handbook.

When granting privileges, the keyword PUBLIC refers to a logical group that covers all idents in the database, including those created in the future.

Recommendations for Ident Structure

The following general recommendations can be made for structuring the idents in a system:

  • Functional roles within the system, generally defined by one or more applications that are run, should be assigned to program idents. These are not coupled to any physical individual or group of individuals and thus have a lifetime independent of turnover of personnel.

    • The system administrator is just such a function, but is coupled to a user ident rather than a program ident for practical purposes.

  • People accessing the system are represented by USER or OS_USER idents. They may be dropped if the person concerned leaves the company.
    User idents should not be granted privileges directly, other than membership in groups. OS_USER idents are allowed access to the database on the authorization of a valid log-in to the operating system.
  • Group idents are used to represent logical users of the system. Privileges are granted to groups rather than to individual programs or users. The individual idents are granted membership in the group to which they belong, and thereby gain the correct access to the system.
  • USER and OS_USER idents should not in general be granted privileges to create objects (i.e. granted DATABANK, IDENT, SCHEMA, SHADOW or TABLE privileges). In this way, individual user idents may be dropped with no cascading effects except loss of views created by the user.
  • WITH GRANT OPTION should be used sparingly and the ident hierarchy kept shallow. This minimizes the chance of undesired cascading revocation of privileges.

If these recommendations are followed, maintenance of the ident structure in the system is simplified. Access to the contents of the database is granted to relatively few group idents instead of many individual programs or users, and when a physical individual leaves the company, their user ident can be dropped with no cascading consequences.


Granting Privileges

The following sections explain how to grant system, object and access privileges.

Granting System Privileges

System privileges are granted to the system administrator at the time of installation of the system. System privileges refer to global information, that affects the database as a whole.

The system privileges are:

Privilege
Explanation

BACKUP

The right to perform backup and restore operations.

DATABANK

The right to create databanks.

IDENT

The right to create idents and schemas.

SCHEMA

The right to create schemas.

SHADOW

The right to create shadows and perform shadow control operations.

STATISTICS

The right to execute the UPDATE STATISTICS statement.

Examples

Give the ident MIMER_STORE the privilege to create new databanks:
GRANT DATABANK TO mimer_store;
Give the ident MIMER_STORE the privilege to create new idents with grant option:
GRANT IDENT TO mimer_store WITH GRANT OPTION;

Granting Object Privileges

Object privileges are held by idents on database objects (functions, procedures, programs, groups, tables, domains and sequences).

The object privileges are:

Privilege
Explanation

EXECUTE

The right to execute a function or procedure or the right to enter a specified program ident.

MEMBER

Membership in a specified group ident.

TABLE

The right to create tables in a specified databank.

SEQUENCE

The right to create sequences in a specified databank.

USAGE

The right to specify the named domain where a data type would normally be specified (in contexts where use of domains is allowed) or the right to use a specified sequence.

Examples
Give MIMER_WEB the privilege to execute the COMING_SOON procedure:
GRANT EXECUTE ON PROCEDURE coming_soon TO mimer_web;
Make MIMER_ADM a member of the MIMER_ADMIN_GROUP group with grant option:
GRANT MEMBER ON mimer_admin_group TO mimer_adm WITH GRANT OPTION;
Give MIMER_ADM the privilege to create new tables in the MIMER_STORE databank:
GRANT TABLE ON mimer_store TO mimer_adm;
Give the members of the MIMER_ADMIN_GROUP group the privilege to use the NAME domain:
GRANT USAGE ON DOMAIN name TO mimer_admin_group;

Granting Access Privileges

Access privileges define what data the idents are allowed to manipulate in tables.

There are five access privileges:

Privilege
Explanation

SELECT

The right to read the table contents.

INSERT

The right to add new rows to the table (this privilege may be limited to specified columns within the table).

DELETE

The right to remove rows from the table.

UPDATE

The right to change the contents of existing rows in the table (this privilege may be limited to specified columns within the table).

REFERENCES

The right to use the primary or unique key of the table as a foreign key reference (this privilege may be limited to specified columns within the table).

The keyword ALL may be used as shorthand for all of privileges that the grantor holds with grant option.

Examples
Give the MIMER_ADMIN_GROUP group the privileges to read and update rows from the PRODUCERS table:
GRANT SELECT, UPDATE ON producers TO mimer_admin_group;
Give MIMER_USER_GROUP all privileges that you hold on the table COUNTRIES and give them the right to pass those privileges on:
GRANT ALL ON countries TO mimer_user_group WITH GRANT OPTION;
Give MIMER_ADMIN_GROUP the privilege to select all rows in the CURRENCIES table, with the privilege to only update the EXCHANGE_RATE column:
 GRANT SELECT, UPDATE(exchange_rate) ON currencies TO
   mimer_admin_group;
Give everyone the privilege to select all rows in the CURRENCIES table:
GRANT SELECT ON currencies TO PUBLIC;
Give MIMER_ADM the right to use the ITEMS table as a foreign key:
GRANT REFERENCES ON mimer_store.items TO mimer_adm;


Revoking Privileges

Privileges can only be revoked by the grantor. Care must be taken when revoking privileges, especially when those privileges were granted `with grant option'. Revoking such privileges from an ident can have recursive effects on all idents who have been granted privileges by that ident. See Recursive Effects of Revoking Privileges for details.

The keywords CASCADE and RESTRICT can be used in the REVOKE statements to control whether the recursive effects should be allowed or not. If RESTRICT (the default) is specified and any recursive effects are identified the whole revoke operation will fail, leaving all objects intact. If the keyword CASCADE is specified, the revoke operation will proceed with recursive effects.

Privileges granted to a group cannot be revoked separately from individual members of the group. To revoke a group privilege from an individual, either revoke the privilege from the group or revoke the membership of the individual in the group.

If a privilege has been granted with the WITH GRANT OPTION it is possible to revoke the grant option only. That is, the ident looses the right to grant the privilege to other idents. The ident still has the privilege, but privileges granted to other idents are revoked.

Revoking System Privileges

Revoking system privileges does not affect objects already created under the authorization of the privilege.

The following examples show how to revoke system privileges.

Take away the privilege to create new databanks from the ident MIMER_STORE:
REVOKE DATABANK FROM mimer_store RESTRICT;
Take away the privilege to create new idents from the ident MIMER_STORE:
 REVOKE IDENT FROM mimer_store RESTRICT;

Revoking Object Privileges

The following examples show how to revoke object privileges.

Take away the privilege to execute the COMING_SOON procedure from MIMER_WEB:
REVOKE EXECUTE ON PROCEDURE coming_soon FROM mimer_web;
Take away MIMER_ADM's membership of the MIMER_ADMIN_GROUP group:
REVOKE MEMBER ON mimer_admin_group FROM mimer_adm;
Take away the right to use the domain NAME from the group MIMER_ADMIN_GROUP:
REVOKE USAGE ON DOMAIN name FROM mimer_admin_group;

Note:	Revoking usage on domain prevents the ident from using that domain as a data type in new definitions, any existing definitions created by the ident will remain unaffected.

Revoking Access Privileges

The following examples show how to revoke access privileges.

Revoke the privilege to read and update rows from the PRODUCERS table from the group MIMER_STORE_GROUP:
REVOKE SELECT, UPDATE ON producers FROM mimer_admin_group;

When the REFERENCES privilege on a table is taken away from an ident, all foreign key links referencing that table are removed.

Revoke the right to use columns in the ITEMS table as a foreign key from MIMER_ADM:
REVOKE REFERENCES ON mimer_store.items FROM mimer_adm RESTRICT;
Revoke the right to grant select on the COUNTRIES table. Any grants that members of the group have made will also be revoked:
REVOKE GRANT OPTION FOR SELECT ON countries
   FROM mimer_user_group CASCADE;
The Keyword ALL

The keyword ALL may be used as a shorthand for all the privileges that may be revoked in the current context.

Recursive Effects of Revoking Privileges

If CASCADE is specified in a REVOKE statement, the following recursive effects may occur:

  • If a privilege WITH GRANT OPTION is revoked from an ident, all instances of that privilege granted to other idents under the authorization of the WITH GRANT OPTION are also revoked. Privileges granted for procedures, functions and triggers that reference objects accessed by the WITH GRANT OPTION will also disappear.
  • If SELECT privilege on a table is revoked from an ident, views created by the ident under the authorization of that SELECT privilege are dropped.
  • If REFERENCE privilege on a table is revoked from an ident, any FOREIGN KEY constraints in tables created by that ident under the authorization of that REFERENCE privilege are removed.
  • If the privilege held by an ident on an object referenced in a routine or trigger is revoked, the routine or trigger will be dropped. (This applies to EXECUTE on a routine, USAGE on a sequence or an access privilege on a table or view held WITH GRANT OPTION).
Dependencies

The recursive effect of revoking a privilege depends on how many instances of that privilege have been granted. An ident will hold more than one instance of a privilege when it has been granted more than once (by different idents, as an ident cannot grant the same privilege to the same ident more than once).

One or more of those instances may have been granted WITH GRANT OPTION.

The data dictionary keeps a record of which instance of a privilege has WITH GRANT OPTION and which does not.

The recursive effects will occur only when the last instance of the required privilege is revoked. That is, when the last instance of the privilege held WITH GRANT OPTION is revoked from an ident, all instances of the ident granting the privilege to others will be withdrawn; and when the last instance of the privilege is revoked from the ident, the cascade effects of the ident no longer holding the privilege will occur.

This is illustrated in the example cases that follow:

CASE 1
  1. A grants with grant option to M

    2. M grants to X

  2. B grants with grant option to M

    3. M grants to Y

  3. A revokes from M

    4. Both X and Y keep privileges

  4. B revokes from M

    5. Both X and Y lose privileges

CASE 2
  1. A grants with grant option to M
  2. B grants without grant option to M

    3. M grants to X

    M grants to Y

  3. A revokes from M

    4. M loses grant option

    Both X and Y lose privileges

  4. B revokes from M

    5. M loses privilege

As a consequence of the cascading effects of revoking privileges, careful advance planning of the hierarchical structure of idents in a system can be essential to the long term viability of the system.

An unplanned ident structure can easily become impossible to overview and control after a relatively short period of system use.


Mimer BSQL

This chapter discusses Mimer BSQL, a command line oriented tool for executing SQL statements both in batch jobs and interactively.

Other SQL Tools

DbVisualizer, by Minq Software (http://www.dbvis.com), is included in the Mimer SQL distribution. (Not for the Open VMS platform.)

Win: Another SQL tool for Windows is WSQL, which is free and unsupported. It can be download from
http://developer.mimer.com


Running BSQL

BSQL can be run from a batch job or from a terminal. Operation from a terminal can be used to execute statements entered directly or written in sequential files.

About Complex SQL Statements - @

Use the @ character to delimit a complex SQL statement where the normal end-of-statement character `;' appears before the end of the statement (e.g. CREATE FUNCTION, CREATE PROCEDURE, and CREATE TRIGGER).

Running BSQL from a Batch Job

To run BSQL unattended from a batch job, create a batch file with the following contents:

  • the command to start BSQL
  • the user name
  • the password
  • the SQL statements and BSQL commands
  • the EXIT command (or end of file).
Unicode Pipe Support in Console Programs on Windows

All Mimer SQL Engine console programs such as BSQL, can pipe Unicode files. The files can be any of the Unicode formats supported by Mimer SQL Engine such as UTF16 big and little endian, and UTF8.

When output is piped to a file, the input decides the type of the output file. If the input file is ASCII, the output will also be ASCII. If the input file is UTF16, the output will also be UTF16. If the input is from the keyboard, the output will be an UTF16 file on Windows Vista/NT/2000/XP. For example:

BSQL < UNIFILEIN.TXT > UNIFILEOUT.TXT
Security and Batch Jobs

For unattended operation, a batch file must either include the Mimer SQL ident user name and password in explicit form or connect as OS_USER.

For security reasons, make sure that your batch files are well protected and/or remove your password from the file after execution.

Alternatively, SQL statements and BSQL commands may be written in a sequential file without user name and password, and executed with the READ command from a BSQL terminal session.

Running BSQL via a Terminal

How you start BSQL depends on your operating system.

BSQL Command-line Arguments

Windows & UNIX-style
VMS-style
Function
-u username
--username=username

/USERNAME=username

Ident name to be used in connect. If the switch is not given the user is prompted for a username.

To connect as OS_USER, give -u "", --username="", or
/USERNAME="".

-p password
--password=password

/PASSWORD=password

Password for ident. If the switch is omitted the user is prompted for a password, unless OS_USER is specified with the username switch, as described above.

VMS: Note that in a Open VMS environment it might be necessary to enclose the password in quotation marks as the value otherwise is translated to upper case.

-m
--multi

/MULTI

Connects to the database in multi-user mode.

-s
--single

/SINGLE

Connects to the database in single-user mode.

-q query
--query=query

/QUERY=query

query can be any BSQL command or SQL statement. If a query is supplied, BSQL will terminate immediately after the query has been processed.

database

database

Specifies the name of the database to access. If a database name is not specified, the default database will be accessed, see Mimer SQL System Management Handbook, The Default Database.

-?
--help

/HELP

Show help text.

If a database name is not specified, the default database will be accessed.

If neither --single nor --multi is specified for the optional mode flag, the way the database is accessed will be determined by the setting of the MIMER_MODE variable, see Mimer SQL System Management Handbook, Appendix A, Specifying Single-user Mode Access.

If this is not set, it will be accessed in multi-user mode.

If multiple instances of a qualifier is given, the last one is used. For example

bsql --single --multi --username=t1 --username=t2

is valid and means connect user t2 in multi-user mode.

Unix examples

Start BSQL and connect user cosmo with password Kramer:

bsql --username=cosmo --password=Kramer

Start BSQL, connect as OS_USER, execute a query, and then leave:

bsql --user="" --query="select * from \"SomeTable\" where user = 'COSMO'"
VMS examples

Start BSQL and connect user cosmo with password Kramer:

bsql /USERNAME="cosmo" /PASSWORD="Kramer"

Start BSQL, connect as OS_USER, execute a query, and then leave:

bsql /USERNAME="" /QUERY="select * from ""SomeTable"" where user = 'COSMO'"

Note:	You can also use the UNIX-style syntax in OpenVMS.
Windows examples

Start BSQL and connect user cosmo with password Kramer:

bsql --username=cosmo --password=Kramer

Start BSQL, connect as OS_USER, execute a query, and then leave:

bsql --user="" --query="select * from \"SomeTable\" where user = 'Cosmo'"
To start Mimer BSQL from the Windows Start button:

Click Start, navigate to your Mimer SQL program group and select Batch SQL.

Logging IN

Starting BSQL displays the following:

$ bsql
Mimer SQL command line utility, version 9.3.7
Username:

After you have entered a user name and a correct password, the BSQL prompt is displayed:

SQL>

You can now enter BSQL specific commands and general SQL statements. Output will be echoed on the terminal.

BSQL Command Line Editing - UNIX

Command line editing is available in the BSQL program, which uses a line-oriented interface.

The following functions are available:

Use:
To:
ctrl-a

Move to beginning of command

ctrl-b

Move backwards in command

ctrl-d

Delete current character

ctrl-e

Move to end of command

ctrl-f

Move forwards in command

ctrl-h

Delete previous character

ctrl-k

Delete after current position in command

ctrl-n

Next command

ctrl-o

Execute retrieved command and get next from history list

ctrl-p

Previous command

ctrl-r

Retrieve command by search condition

ctrl-t

Change place for the previous two characters

ctrl-u

Delete command

ctrl-w

Delete before current position in command

ctrl-<space>

Set mark in command (or `esc <space>')

ctrl-x ctrl-x

Go to mark set by `ctrl <space>'

ctrl-x ctrl-h

Show the history list

ctrl-x ctrl-r

Retrieve command by history list number

esc h

Delete previous word

esc d

Delete next word

esc b

Move to previous word

esc f

Move to next word

You can use the arrow keys for command retrieval and for positioning the cursor within a line, i.e. the same function as for ctrl-b, ctrl-f, ctrl-n and ctrl-p.

To change the number of commands that can be held in the history list, the environment variable MIMER_HISTLINES can be used (the default is 23).

Note: The operating system may have control sequences set for the terminal that, if they overlap, override those described above. The terminal settings can be listed using the UNIX stty -a command.


BSQL Commands

Command
Function
CLOSE

Closes active log files, see CLOSE.

DESCRIBE

Describes a specified object, see DESCRIBE.

EXIT

Leaves BSQL, see EXIT.

GET DIAGNOSTICS

Presents status and diagnostics information, see GET DIAGNOSTICS

LIST

Lists information on a specified object, see LIST.

LOG

Logs input, output or both on a sequential file, see LOG.

READ INPUT

Reads commands from a sequential file, see READ INPUT.

READLOG

Obtains information about logged operations, see READLOG.

SET ECHO

Specifies whether lines are echoed to the terminal during READ INPUT, see SET ECHO.

SET EXECUTE

Activate or deactivate the execution of queries, see SET EXECUTE.

SET EXPLAIN

Activate or deactivate the explain facility, see SET EXPLAIN.

SET LINECOUNT

Sets the terminal page size, see SET LINECOUNT.

SET LINESPACE

Sets the number of blank lines between each output record, see SET LINESPACE.

SET LINEWIDTH

Sets the terminal page width, see SET LINEWIDTH.

SET LOG

Stops or resumes logging input, output or both, see SET LOG.

SET MAX_BINARY_LENGTH

Specifies the maximum display length for binary columns, see SET MAX_BINARY_LENGTH.

SET MAX_CHARACTER_LENGTH

Specifies the maximum display length for character columns, see SET MAX_CHARACTER_LENGTH.

SET MESSAGE

Specifies whether messages are displayed on the terminal, see SET MESSAGE.

SET OUTPUT

Specifies whether output should be written to the terminal, see SET OUTPUT.

SET PAGELENGTH

Defines the page length of output file, see SET PAGELENGTH.

SET PAGEWIDTH

Defines the page width of output file, see SET PAGEWIDTH.

SHOW SETTINGS

Displays current values of all set options, SHOW SETTINGS.

TRANSACTIONS

Displays the menu for administrating distributed transactions, see TRANSACTIONS.

WHENEVER

Sets action to be taken in response to an error or warning, see WHENEVER.

BSQL commands are not case sensitive.

About BSQL Syntax Descriptions

For information on how to read the syntax diagrams that follow, please refer to Mimer SQL Reference Manual, Reading SQL Syntax Diagrams.

CLOSE

Closes log files.

Syntax

Description

The command closes the specified log file. If no log type is specified, all active log files are closed.

DESCRIBE

Describes a specified object.

Syntax

Where object-type is one of the object types listed below.

Description

The DESCRIBE command presents the following menu, when no object is specified:

                          Menu for describe

1. Databank               8. View              15. Collation
2. Domain                 9. Module            16. Type
3. Ident                 10. Procedure         17. Method
4. Index                 11. Function          18. Specification
5. Synonym               12. Trigger           19. Specific
6. Table                 13. Sequence          20. Statement
7. View                  14. Shadow             0. Exit

Choosing an item presents a submenu for choosing between different DESCRIBE functions - see the table that follows for details.

Entering an exclamation mark (!) in the Select field returns to the previous menu level. Entering a double exclamation mark (!!) terminates the DESCRIBE session.

Specifying an object type and name in the command executes the first menu choice for that object. If no object name is given, the user is prompted for a name.

Selection numbers should not be used in batch files, since they may change in future versions.

Note: DESCRIBE is not available when connected to a Mimer SQL server of version 8.1 or older.

DESCRIBE DATABANK Options

DESCRIBE DATABANK
Result
BRIEF

Lists the following information on the specified databank:

creator
file space used
allocated size
file name
databank option
minsize
goalsize
maxsize
removable option
backup information
tables
sequences.

BY TABLE PRIVILEGE

Lists the following information on the specified databank:

idents with table privilege.

FULL

Lists the following information on the specified databank:

creator
file space used
allocated size
file name
databank option
minsize
goalsize
maxsize
removable option
backup information
tables
sequences
idents with table privilege
comment
creation date.

DESCRIBE DOMAIN Options

DESCRIBE DOMAIN
Result
BRIEF

Lists the following information on the specified domain:

data type
default value
check constraints.

BY REFERENCES

Lists the following information on the specified domain:

referenced objects
referencing objects.

BY ACCESS

Lists the following information on the specified domain:

idents with usage privilege.

FULL

Lists the following information on the specified domain:

data type
default value
check constraints
referenced objects
referencing objects
idents with usage privilege
comment
creation date.

DESCRIBE IDENT Options

DESCRIBE IDENT
Result
BRIEF

Lists the following information on the specified ident:

creator
ident type
privileges held by ident.

BY ACCESS

Lists the following information on the specified ident:

accessible objects.

BY OWNERSHIP

Lists the following information on the specified ident:

created objects.

FULL

Lists the following information on the specified ident:

creator
ident type
accessible objects
created objects
comment
creation date.

DESCRIBE INDEX Options

DESCRIBE INDEX
Result
BRIEF

Lists the following information on the specified index:

table name and columns on which the index is defined
sort order
uniqueness
comment
creation date.

DESCRIBE SYNONYM Options

DESCRIBE SYNONYM
Result
BRIEF

Lists the following information on the specified synonym:

schema and name of referenced table/view
comment
creation date

DESCRIBE TABLE Options

DESCRIBE TABLE
Result
VERY BRIEF

Lists the following information on the specified table or view:

column names and types.

BRIEF

Lists the following information on the specified table or view:

column names and types
default values
constraints
referenced domains
indexes
triggers.

BY ACCESS

Lists the following information on the specified table or view:

idents with access.

BY REFERENCES

Lists the following information on the specified table or view:

referencing objects
referenced objects.

FULL

Lists the following information on the specified table or view:

column names and types
default values
constraints
referencing objects
referenced objects
indexes
triggers
idents with access
databank location
comment
creation date
date when statistics were generated.

DESCRIBE VIEW Options

DESCRIBE VIEW
Result
BRIEF

Lists the following information on the specified view:

view definition
updatability
check option
comment
creation date.

DESCRIBE MODULE Options

DESCRIBE MODULE
Result
BRIEF

List the following information on the specified module:

module definition
comment
creation date.

DESCRIBE PROCEDURE Options

DESCRIBE PROCEDURE
Result
BRIEF

Lists the following information on the specified procedure:

parameters
return types
access mode.

BY ACCESS

Lists the following information on the specified procedure:

idents with execute privilege.

BY REFERENCES

Lists the following information on the specified procedure:

referencing objects
referenced objects.

FULL

Lists the following information on the specified procedure:

parameters
return types
access mode
idents with execute privilege
referencing objects
referenced objects
source definition
module name
comment
creation date.

DESCRIBE FUNCTION Options

DESCRIBE FUNCTION
Result
BRIEF

Lists the following information on the specified function:

parameters
return type
access mode.

BY ACCESS

Lists the following information on the specified function:

idents with execute privilege.

BY REFERENCES

Lists the following information on the specified function:

referencing objects
referenced objects.

FULL

Lists the following information on the specified function:

parameters
return type
access mode
idents with execute privilege
referencing objects
referenced objects
source definition
module name
comment
creation date.

DESCRIBE TRIGGER Options

DESCRIBE TRIGGER
Result
BRIEF

Lists the following information on the specified trigger:

table name on which trigger is defined
trigger event
trigger type
event time.

BY REFERENCES

Lists the following information on the specified trigger:

referenced objects.

FULL

Lists the following information on the specified trigger:

table name on which trigger is defined
trigger event
trigger type
event time
referenced objects
source definition
comment
creation date.

DESCRIBE SEQUENCE Options

DESCRIBE SEQUENCE
Result
BRIEF

List the following information about the specified sequence:

initial value
increment value
maximum value
databank location.

BY ACCESS

List the following information on the specified sequence:

idents with usage privilege.

BY REFERENCES

List the following information on the specified sequence:

referencing objects.

FULL

List the following information about the specified sequence:

initial value
increment value
maximum value
databank location
referencing objects
idents with usage privilege
comment
creation date.

DESCRIBE SCHEMA Options

DESCRIBE SCHEMA
Result
BRIEF

List the following information about the specified schema:

schema owner
contained objects
comment
creation date.

DESCRIBE SHADOW

DESCRIBE SHADOW
Result
BRIEF

List the following information on the specified shadow:

shadow creator
databank name
file name
comment
creation date

DESCRIBE COLLATION

DESCRIBE COLLATION
Result
BY REFERENCES

List the following information about the specified collation:

columns using the specified collation
all objects using the specified collation

FULL

List the following information on the specified collation:

character set schema
character set name
pad attribute
version
delta definition
idents with usage privilege on the specified collation
comment
creation date
columns using the specified collation
all objects using the specified collation

DESCRIBE SPECIFIC

DESCRIBE SPECIFIC
Result
BRIEF

List the following information about the specified routine:

schema name
routine name
statement type
return type (if function or method)
determinism
access mode

For each parameter:
parameter name
parameter mode
data type

BY ACCESS

List the following information about the specified routine:

idents with execute privilege on the routine

BY REFERENCE

Lists the following information about the specified routine

Objects referenced by the routine
Objects referencing the routine

FULL

Lists the following information about the specified statement

schema name
routine name
statement type
return type (if Function or method)
determinism
access mode

For each parameter:
parameter name
parameter mode
data type

Idents with execute privilege on the routine:
objects referenced by the routine
objects referencing the routine
creation date
comment

DESCRIBE STATEMENT

DESCRIBE STATEMENT
Result
BRIEF

List the following information about the specified statement:

schema name
statement name
statement type
statement definition

BY ACCESS

List the following information about the specified statement:

idents with execute privilege on the statement

BY REFERENCE

Lists the following information about the specified statement:

objects referenced by the statement

FULL

Lists the following information about the specified statement:

schema name
statement name
statement type
statement definition
Idents with execute privilege on the statement
objects referenced by the statement
creation date
comment

EXIT

Leave BSQL.

Syntax

Description

Terminates the BSQL session.

GET DIAGNOSTICS

Get diagnostics for statement.

Syntax

Description

Presents all status and diagnostics information for the preceding statement.

LIST

Lists information on a specified object.

Syntax

Where object-type is one of the object types listed below.

Description

The LIST command presents the following menu, if no object-type is specified:

                     Menu for List

 1. Databanks         8. Views               15. Shadows
 2. Domains           9. Modules             16. Collations
 3. Idents           10. Procedures          17. Methods
 4. Indexes          11. Functions           18. Specifications
 5. Objects          12. Triggers            19. Types
 6. Synonyms         13. Sequences           20. Statements
 7. Tables           14. Schemata             0. Exit

Choosing an item presents a submenu for choosing between different LIST functions - see the table that follows for details.

Entering an exclamation mark (!) in the Select field returns to the previous menu level. Entering a double exclamation mark (!!) returns two levels.

Giving an object type in the command executes the first menu choice for that type.

Selection numbers should not be used in batch files, because the may change in future versions.

Note: LIST is not available when connected to a Mimer SQL server of version 8.1 or older.

LIST COLLATIONS Options

LIST COLLATIONS
Result
ALL

Lists all collations in the database.

IN SCHEMA

Lists collations in the specified schema.

LIST DATABANKS Options

LIST DATABANK
Result
ALL

Lists all databanks in the database.

CREATED BY

Lists databanks created by a specified ident.

LIST DOMAINS Options

LIST DOMAINS
Result
ALL

Lists all domains in the database.

IN SCHEMA

Lists domains in the specified schema.

LIST FUNCTIONS Options

LIST FUNCTIONS
Result
ALL

Lists all the functions the current ident has execute privilege on.

IN SCHEMA

Lists functions in the specified schema.

LIST IDENTS Options

LIST IDENTS
Result
ALL

Lists all idents in the database.

CREATED BY

Lists idents created by a specified ident.

LIST INDEXES Options

LIST INDEXES
Result
ALL

Lists the secondary indexes in the database.

IN SCHEMA

Lists secondary indexes in the specified schema.

LIST MODULES Options

LIST MODULES
Result
ALL

Lists all the modules in the database that are visible to (i.e. created by) the current ident.

LIST OBJECTS Options

LIST OBJECTS
Result
ALL

Lists objects in the database.

CREATED BY

Lists objects created by a specified ident.

WITH TYPE

Lists objects of a specified type.

LIST PROCEDURES Options

LIST PROCEDURES
Result
ALL

Lists all the procedures the current ident has execute privilege on.

IN SCHEMA

Lists procedures in the specified schema.

LIST SCHEMATA Options

LIST SCHEMATA
Result
ALL

Lists schemata created by the current ident.

LIST SEQUENCES Options

LIST SEQUENCES
Result
ALL

Lists all the sequences the current ident has usage privilege on.

IN SCHEMA

Lists sequences in the specified schema.

LIST SHADOWS Options

LIST SHADOWS
Result
ALL

List shadows created on databanks created by the current ident or all shadows if the current ident has shadow privilege.

LIST STATEMENTS Options

LIST STATEMENTS
Result
ALL

List all the precompiled statements the current ident has usage privilege on.

IN SCHEMA

List all statements belonging to the defined schema.

LIST SYNONYMS Options

LIST SYNONYMS
Result
ALL

Lists synonyms in the database.

IN SCHEMA

Lists synonyms in the specified schema.

LIST TABLES Options

LIST TABLES
Result
ALL

Lists tables in the database.

IN SCHEMA

Lists tables in the specified schema.

LIST TRIGGERS Options

LIST TRIGGERS
Result
ALL

List triggers defined on tables accessible to current user.

IN SCHEMA

Lists triggers in the specified schema.

LIST VIEWS Options

LIST VIEWS
Result
ALL

Lists views in the database.

IN SCHEMA

Lists views in the specified schema.

LOG

Logs input, output or both to a specified sequential file.

Syntax

Description

All input, output or both will be logged in the specified sequential file.

If ON is specified a new file will always be created, otherwise the log data is appended to the file.

Logging is paused with the SET LOG OFF command and is resumed with the SET LOG ON command. Use CLOSE to stop logging permanently.

Using the AS option, you can set the file format to LATIN1, UTF8, UTF16, UTF16BE, UTF16LE, UTF32, UTF32BE or UTF32LE.

READ INPUT

Reads commands from a sequential file.

Syntax

Description

Commands and SQL statements are read from the specified file.

When READ COMMAND INPUT is specified, commands are read from the file while prompt answers are taken from the terminal (batch job, command procedure).

When READ ALL INPUT or READ INPUT is specified, both commands and prompt answers are read from the sequential file.

Using the AS option, you can set the file format to LATIN1, UTF8, UTF16, UTF16BE, UTF16LE, UTF32, UTF32BE or UTF32LE.

READLOG

Obtains information about logged operations.

Syntax

Description

READLOG is a Mimer SQL function which enables you to read the contents of LOGDB so that you can check logged operations performed on the database since the last backup copy or incremental backup was taken.

You can use READLOG as an audit trail or, in the event of a system failure, to determine which databanks need to be restored (i.e. which databanks have been altered since the last backup).

Functions

READLOG enables you to select information from LOGDB on the basis of time interval, ident performing the operation, and specified databanks or tables.

This is particularly useful in production systems where LOGDB can contain a large number of entries.

Authorization

To list the log for selected tables or all tables in a databank, you must have SELECT access on the tables in question.

To list the log for the entire database, you must have BACKUP privilege.

Using the READLOG Functionality

You control the READLOG functionality using a menu from which different listing options may be set before finally performing the read operation.

The different listing options are set by using menu selections 1-5. The menu is re-displayed after selecting any of these so that further options may be set for the listing.

When all the desired listing options have been set in this way, a listing is produced from the log by choosing menu selection 6, 7 or 8 from under List operations.

                    -- Read log --

List definitions    List restrictions   List operations
----------------    -----------------   ---------------
1. Log file         3. Time interval    6. Specified tables
2. File properties  4. Ident            7. Tables in databank
                    5. Databank         8. All (no data)
0. EXIT
List Definitions - Output Control
Log File

Choosing Log file allows you to specify the name of a sequential file into which the listing is to be placed. In systems where the terminal may be addressed by a logical file name, this may be given to display the listing on the terminal.

If this option is not selected, a sequential file with the default name RDLOGL will be used.

The following example sets the log file explicitly:

SQL>READLOG;

                    -- Read log --

List definitions    List restrictions   List operations
----------------    -----------------   ---------------
1. Output           3. Time interval    6. Specified tables
2. Properties       4. Ident            7. Tables in databank
                    5. Databank         8. All (no data)
0. EXIT

Select: 1
Output to file or terminal (F/T) [F]: F
Log list file: READLOG.DAT
List Properties

The file properties choice is used to set either the width of a report or the format for the log file. The file format can be one of the following encodings:

1. Default
2. Latin 1
3. UTF 8
4. UTF 16
5. UTF 16 big endian
6. UTF 16 little endian
7. UTF 32
8. UTF 32 big endian
9. UTF 32 litte endian

Default means the encoding specified by the locale settings for the client.

List Restrictions
Time interval

This option allows the listing to be restricted to a given time interval, specified as a starting time and a finishing time.

Times are given as a single parameter representing year, month, day, hour, minute and second in the format YYYYMMDDHHMMSS.

If an incomplete time specification is given (truncated from the right), the remaining parameters are taken as low for the starting time and high for the finishing time. Thus giving 200211 as both the starting and finishing time, lists the log from the beginning to the end of November 2002.

A default time value is assumed if no time interval is specified, or may be chosen for starting or finishing time by specifying a `blank' time.

If no start time is specified, the time at the beginning of the log is assumed. If no end time is specified, the time at the end of the log is assumed.

If neither a start time nor an end time is specified, the following message is displayed:

** No time restriction

A selected time interval applies for all subsequent list operations in the current session until the time interval is reset.

A time interval of two months has been selected in the following example:

Select: 3
Format   : YYYYMMDDHHMMSS
Starttime: 200311
Endtime  : 200312
Ident

Selecting an ident restricts the listing to operations performed by that ident. Only one ident may be selected for a given listing.

The default setting lists operations performed by all idents.

The default applies if no ident restriction is selected, or may be chosen by specifying a blank ident. If the default is chosen, the following message is displayed:

** No ident restriction

A selected ident applies for all subsequent list operations in the current session until a new ident is specified.

Example:

Select: 4
Identname: mimer_store
Databank

Selecting a databank restricts the listing to operations performed on that databank. This option must be specified if the list operation 7 (Tables in databank) is to be used. Only one databank may be selected for a given listing.

If no databank is specified, the list operation is done for all databanks. If this is the case, the following message is displayed:

** No databank restriction

A selected databank applies for all subsequent list operations in the current session until the databank is reset.

Example:

Select: 5
Databank: mimer_orders
List Operations
Specified Tables

This option activates listing of the log for selected tables in the database.

As many tables may be specified as are required, with the table name qualified, if necessary, by the name of the schema to which it belongs.

If no schema is specified, the schema with the same name as the current ident is assumed.

Databank restrictions selected with option 5 are ignored if specified tables are selected. However, any ident and time restrictions selected with options 3 and 4 are applied.

The ident running READLOG must have SELECT access on the requested tables, otherwise the following message is displayed for the table in question:

** No select access on table

If a non-existent table is requested, the following message is displayed:

** No such table

Errors of this type do not abort the listing if valid and invalid requests are mixed in the same operation.

The list operation is activated by giving a blank response to the prompt for a table name when all the required tables have been specified, as in the following example:

Select: 6
Table: HOTELADM.EMPLOYEE
Table: HOTELADM.STAFF
Table: HOTELADM.SALARY
Table:

Note:	The list operation can be interrupted by entering an exclamation mark !.
Tables in Databank

Operations on all tables in the databank specified under option 5 are listed. If no databank has been selected, the following message is displayed and the user must select a new option:

**Databank not entered

Time or ident restrictions selected with options 3 or 4 are applied.

Data is listed only for those tables to which the ident running READLOG has SELECT access.

Tables to which access is denied are indicated by the following message in the log list file:

Table <schema-name.table-name> - No select access
All (No Data)

This option lists logged operations without details of data records (see below). The ident running READLOG must have BACKUP privilege.

If the privilege is not held by the current ident the following error message is displayed:

**   AUTHORIZATION FAILURE
Output Format

The output from READLOG is divided into transactions, showing the date and time, the ident performing the transaction (with entered program idents where appropriate) and the number of database records read during the transaction.

Note: The output does not contain statements for reconstructing the logged operations - it is simply a documentary record of the transactions performed on the database

If list operations 6 or 7 (select by Specified tables or Tables in databank) are selected, the contents of the affected rows in the table are displayed. Insert and delete operations are listed as a single row. Update operations are recorded as the state of the row before and after the update.

If the list operation 8, All (no data), is selected the operations are listed without the data records.

SET ECHO

Controls whether or not lines read during READ INPUT are echoed.

Syntax

Description

When echo is set to ON, lines read during READ INPUT are echoed to the terminal or batch log file. When echo is set to OFF, these lines are not echoed. The default value is ON.

The setting has no effect on the output of responses to BSQL commands and statements.

SET EXECUTE

Activate or deactivate the execution of queries.

Syntax

Description

When execute is set to off no queries will be executed, only the syntax is checked. This can be useful when testing a script for correctness or when using the explain facility.

SET EXPLAIN

Activate or deactivate the explain facility.

Syntax

Description

When the explain facility is activated the execution plan for the query is shown. By default the query will be executed, to avoid this behavior the SET EXECUTE command can be used.

The execution plan will show different operations and the sequence in which these operations are performed. The following operations can be shown when the explain facility is active:

Operation
Description

Direct key lookup

Search primary key for exactly one record.

Direct index key lookup

Search unique index for exactly one record

Key lookup

Search primary key for range of values

Index key lookup

Search index for range of values

Key scan

Search primary key, not using leading columns

Index key scan

Search index, not using leading columns

Sequential read

Read all records from base table

Index sequential read

Read all records using index

Insert

Insert record in base table or temporary table

Insert, aggregate

Insert record in temporary table and update aggregate function result

Update

Update record in base table

Delete

Delete record from base table

Example:
SQL>CREATE TABLE expltab (c1 integer primary key, c2 character(10));
SQL>SET EXPLAIN ON;
SQL>SET EXECUTE OFF;
SQL>SELECT * FROM expltab ORDER BY c2;
Start of explain result

L1:
      Sequential read JAMES.EXPLTAB, end of table goto L2
      Insert into temporary table (1) (25 bytes)
      goto L1
L2:
      Sort temporary table (1)
L3:
      Sequential read temporary table (1), end of table goto end
      Record found, goto L3
end:

End of explain result

SQL>SET EXECUTE ON;
SQL>CREATE INDEX expltab_c2 ON expltab (c2);
SQL>SET EXECUTE OFF;
SQL>SELECT * FROM expltab ORDER BY c2;
Start of explain result

L1:
      Index sequential read using index JAMES.EXPLTAB_C2, end of table goto end
      Record found, goto L1
end:

End of explain result

SQL>

For more details, see the following article http://developer.mimer.com/howto/howto_46.htm

SET LINECOUNT

Sets the length of the terminal page.

Syntax

Description

The LINECOUNT value defines the length of the terminal page.

If LINECOUNT has a value greater than zero, terminal output will temporarily be stopped after the number of lines defined for the value.

After the Continue-prompt, the user will have the choice of either continuing with the display or terminating the output.

Answering `Y' (default) implies that the output will continue until the number of lines is reached again.

Answering `N' terminates the output. Answering `G' will ignore the line count and the output will continue until all data are displayed.

If LINECOUNT is zero, the output will continue until all data is displayed.

The value of LINECOUNT must either be zero or >= 10.

Default

If BSQL is run from a batch job, LINECOUNT is zero by default. For interactive operation, the default value is machine- and terminal-dependent.

SET LINESPACE

Sets the number of blank lines between each output record.

Syntax

Description

The LINESPACE value defines the number of blank lines to be written between each output record. This value is only used when printing the result of a SELECT statement.

The maximum value for LINESPACE is 9. The default value is 0.

SET LINEWIDTH

Specifies the width of the output.

Syntax

Description

The LINEWIDTH value defines the maximum line width for output to the terminal or batch log file.

The value for LINEWIDTH cannot be set to a value less than 20.

SET LOG

Stops or resumes logging input, output or both.

Syntax

Description

When SET LOG is set to OFF, logging of input, output or both in a sequential file is temporarily stopped.

Resume logging with the SET LOG ON command.

If no input/output log is specified, all active logs are stopped or resumed.

SET MAX_BINARY_LENGTH

Specifies the maximum display length for binary columns.

Syntax

Description

The MAX_BINARY_LENGTH value defines the number of elements that are displayed when selecting data that is defined as binary, binary varying or binary large object.

The default value is 15 000 and the value must be between 1 and 15 000.

As a binary string is shown as a hexadecimal string with two characters for each element the display length will be twice the value of MAX_BINARY_LENGTH.

SET MAX_CHARACTER_LENGTH

Specifies the maximum display length for character columns.

Syntax

Description:

The MAX_CHARACTER_LENGTH value defines the number of characters that are displayed when selecting data that is defined as any character data type.

The default value is 15 000 and the value must be between 1 and 15 000.

SET MESSAGE

Specifies whether or not messages should be displayed.

Syntax

Description

Specifies whether or not result messages such as One row found etc. are written to the terminal screen or batch log file.

The default setting is ON.

SET OUTPUT

Specifies whether or not output should be displayed.

Syntax

Description

When OUTPUT is set to ON, the output from BSQL is written to the terminal or batch log file. When it is set to OFF, the output does not appear.

The default value is ON.

SET PAGELENGTH

Specifies the page size of the output log file.

Syntax

Description

The PAGELENGTH value defines the page size of the file on which output is logged, i.e. at what interval a page break will be performed. A value of zero will result in no page breaks.

The PAGELENGTH value can either be set to zero or >= 10. The default value is machine-dependent.

SET PAGEWIDTH

Specifies the page width of the output log file.

Syntax

Description

The PAGEWIDTH value defines the page width of the output file. The value should be greater than 20. The default value is 132.

SHOW SETTINGS

Displays the current values of all set options.

Syntax

Description

Displays the current values for all set options, i.e. ECHO, EXECUTE, EXPLAIN, LINECOUNT, LINESPACE, LINEWIDTH, LOG, MB, MC, MESSAGE, OUTPUT, PAGELENGTH, PAGEWIDTH, TRANSACTION START, TRANSACTION ISOLATION LEVEL, TRANSACTION MODE (read only or read write).

Current server and connection names are also displayed.

TRANSACTIONS

Displays the menu for administrating distributed transactions.

Syntax

Description

You can use the TRANSACTIONS command to monitor distributed transactions that are in a prepared or heuristically completed state. Note that all transactions are uniquely identified by the XID string. Because those strings are somewhat long, BSQL assigns a small sequence number to each line to be used as a shorthand. This shorthand is only valid until the List transactions option is used again. Note that since transactions are normally short-lived, the same transaction may be assigned different sequence numbers each time the List transactions option is used.

Note: The TRANSACTIONS command should only be used in exceptional circumstances, such as when a transaction monitor has crashed or a network failure has occurred making it is impossible to establish contact with a transaction monitor.

The command will present the following menu:

                  Menu for handling distributed transactions

1. List transactions   2. Heuristic commit    3. Heuristic rollback
0. Exit

The List transactions option displays a list of all distributed transactions that are either in a prepared ore heuristically completed state. For example:

NUMBER STATUS     XID
====== ========== ===============================================================
     1 Prepared   34C6F6E675849446E616D6520
===
     2 Prepared   C6F6E675849446E616D6520
===

        2 transactions found

To heuristically commit or rollback a distributed transaction, you can choose option 2 or 3 in the menu. This will prompt for a transaction number which should correspond to a number in the listing. It is not possible to heuristically commit or rollback a distributed transaction without a prior listing.

When a distributed transaction is heuristically committed or rolled back it will remain in the list until it has been forgotten by the transaction monitor.

If the transaction with sequence number 1 (XID 34C6F6E675849446E616D6520) was heuristically committed a subsequent listing would look like this:

NUMBER STATUS     XID
====== ========== ===============================================================
     1 Prepared   C6F6E675849446E616D6520
===
     2 Committed  34C6F6E675849446E616D6520
===

        2 transactions found

More information on distributed transactions in general, can be found in the Point Display.

WHENEVER

Determines which actions should be taken in the event of an error or warning.

Syntax

Description

If an error or warning should occur in a file being run in batch, there are several action options that may be chosen to determine what should happen.

The actions can be broken down into two groups:

Execution Flow

EXIT

Leaves BSQL in batch mode. Returns to prompt if interactive mode. I.e. if interactive mode and file input mode, the remaining file input is ignored and a new prompt is received.

CONTINUE

Continues execution.


Variables in BSQL

Host variables are used in SQL statements to pass values between the database and an application program, see the Mimer SQL Programmer's Manual.

Host variable syntax is also supported in BSQL to facilitate interactive design and testing of SQL statements intended for use in SQL application programs.

In BSQL, host variables serve as parameter markers, and the user is prompted for parameter values when the statement is executed.

You can use host variables used to:

  • assign values to columns in the database (UPDATE and INSERT statements)
  • to manipulate information taken from the database or contained in other variables (in expressions)
  • to provide values for comparison predicates.

In all these contexts, the data type and length of the host variable must be compatible with that of any database values within the same syntax unit.

Writing Host Variables in SQL

Host variables are written in SQL as:

:host-identifier

or

:host-identifier :indicator-identifier

or

:host-identifier INDICATOR :indicator-identifier

In the first construction, the host identifier is the name of the main host variable.

In the second and third constructions, the main variable host-identifier is associated with an indicator variable indicator-identifier, used to signal the assignment of a NULL value to the main variable.

See the Mimer SQL Programmer's Manual for a description of the use of indicator variables.

Scope of Host Variables

The scope of host variables in BSQL is restricted to the individual usage instance in each statement.

Variables may not be used to pass values between separate statements, and the same variable name used more than once in a statement represents separate, independent variables.

Using Host Variables

When host variables are used in BSQL, BSQL prompts for the variable values, for example:

SQL>SELECT * FROM countries WHERE country = :COUNTRY;
COUNTRY: Spain

This corresponds to the statement:

SQL>SELECT * FROM countries WHERE country = 'Spain';

Note:	The entered variable is not enclosed between single quotation marks, in contrast to the corresponding string value.
Variables enclosed in single quotation marks will be interpreted as literal strings.
Including Indicator Variables

If an indicator variable is included, you will be prompted for whether to use a NULL value.

If you answer the prompt with No, you will then be prompted for a value.

If you answer Yes, the NULL value will be used. For example:

SQL>UPDATE currencies SET exchange_rate = :RATE:IND
SQL&  WHERE code = 'BND';
Null ?n
RATE: 1.34

SQL>UPDATE currencies SET exchange_rate = :RATE:IND
SQL&  WHERE code = 'BND';
Null ?y

In the first example above, the exchange_rate value is updated to 1.34. In the second example, the exchange_rate value is set to NULL.

Note: The prompts appear in the order in which the variables are used in the statement.


Transaction Handling in Mimer BSQL

Normal Mimer SQL transaction handling behavior applies in Mimer BSQL. The default transaction start setting of implicit means that, by default, a transaction is started whenever one is needed.

For a detailed description of transaction handling behavior in Mimer SQL, refer to the Point Display.

A special feature of BSQL is that all implicitly started transactions are automatically committed at the end of each statement, so that by default no attention needs to be paid to transaction handling at all in BSQL.

The START and COMMIT (or ROLLBACK) statements may be used together to group a number of statements into a single transaction when this is required.

Any transactions explicitly started using START will not be automatically committed by BSQL, so COMMIT or ROLLBACK must be used.


LOBs in BSQL

Although BSQL is not designed to handle Large OBjects (LOBs), it does provide limited LOB support for testing purposes.

A LOB is a column defined as being of type Character Large OBject (CLOB), National Character Large OBject (NCLOB) or Binary Large OBject (BLOB).

Columns defined as CLOB will, in all essentials, be treated as being the same as VARCHAR with a maximum length of 15 000.

Columns defined as NCLOB will, in all essentials, be treated as being the same as NCHAR VARYING with a maximum length of 5 000.

Columns defined as BLOB will be treated as BINARY VARYING with a maximum length of 15 000.

LOBs larger than these limits cannot be entered as input, and will be truncated as output.

For more information on LOB data types, see the Mimer SQL Reference Manual, Data Types in SQL Statements.


Errors in BSQL

Error messages are shown when you attempt to execute an erroneous SQL statement. There are two types of errors: semantic errors and syntax errors.

Semantic Errors

Semantic errors arise when SQL statements are formulated with correct syntax, but do not reflect the user's intentions.

For example, suppose that a user wishes to select the string constant Hotel: and the actual hotel name from the table HOTEL, but uses double quotation marks instead of single quotation marks around the string constant:

SELECT  "Hotel:",NAME
FROM    HOTEL;

Double quotation marks are used to delimit identifiers containing special characters, so that the statement is interpreted as a request to select two columns, called Hotel: and NAME, from the table. The first column does not exist.

This example will in fact lead to an execution error, and is easily detected. Other semantic mistakes can be more difficult to find, when the statement is executed but gives the `wrong' answer.

An example is the incorrect use of NULL in a search condition:

SELECT  RESERVATION FROM BOOK_GUEST
WHERE   CHECKOUT = CAST(NULL as DATE);

This will always give an empty result set, since NULL is not equal to anything.

The correct formulation would read WHERE CHECKOUT IS NULL.

Always check that the result of an SQL query looks reasonable, in particular if the query is complicated.

Syntax Errors

Syntax errors are constructions which break the rules for formulating SQL statements.

For example:

  • spelling errors in keywords

    • SLEECT (for SELECT)

  • incorrect or missing delimiters

    • DELETEFROM (for DELETE FROM)

    SELECT column1 column2 (for SELECT column1,column2)

  • incorrect clause ordering

    • UPDATE table WHERE condition SET values

    (for UPDATE table SET values WHERE condition)

Syntactically incorrect statements are not accepted and an appropriate error message is displayed.

The error must be corrected before the statement can be executed.

For syntax errors, BSQL analyzes the statement and makes an intelligent guess as to where the error lies. This guess is based upon the most likely syntax or appearance of the statement in question. The system then points out the error and lists an error message based on this analysis. The appearance of this pointer on your screen is machine dependent. In the examples shown in this chapter, the pointer appears as `^'. The messages are self-explanatory.

The statement analysis is however not completely foolproof and misleading error messages may arise. If the message seems to be inaccurate, check the statement construction against the syntax diagram in the Mimer SQL Reference Manual.

Error Examples

Some examples of errors and resulting error messages are listed below.

Incorrect statement:
SELECT  AVG(country) FROM countries;
Error message:
SELECT  AVG(country) FROM countries;
            ^
Invalid operand type, expected type is NUMERIC or INTERVAL
Incorrect statement:
SELECT  country FROM countries
WHERE   currency_code ON ('USD','GBP','SEK');
Error message:
SELECT  country FROM countries
WHERE   currency_code ON ('USD','GBP','SEK');
                      ^
Syntax error, 'ON' assumed to mean 'IN'
Incorrect statement:

In the following example, the error analysis is misleading:

SELECT  country FROM countries
WJERE   currency_code = 'USD';
Error message:
SELECT  country FROM countries
WJERE   currency_code = 'USD';
        ^
Syntax error, END-OF-QUERY assumed missing

The misspelled word WJERE is not recognized as an attempt to write WHERE, so that the second line is not interpreted as a selection condition.


Error Messages

Error messages from BSQL are shown when you enter an illegal BSQL command or attempt to execute an erroneous SQL statement.

The error messages for erroneous SQL statements are the same as the return codes found in the Mimer SQL Programmer's Manual.

Error messages that can be received for illegal BSQL commands are:

Code
Message
-1

String exceeds 256 characters which is not allowed

-2

File could not be opened

-3

Too many files have been opened

-5

Conflict. One of COMMIT or ROLLBACK and EXIT or CONTINUE

-100

Undefined command <%>

-101

Ambiguous command <%>

-102

<%> command not valid in this context

-103

Missing semicolon

-104

Missing statement terminator (@)

-201

Syntax error

-202

Undefined keyword

-203

String expected

-204

Filenames must be enclosed in apostrophes

-205

Invalid numerical literal

-206

Unexpected end of command

-207

Too many parameters

-300

Failed to read dictionary

-400

Record too large for one page (<%> lines required) Increase value of LC/
PL or set them to zero

-600

The number of host variables cannot exceed 20

-666

Space area exhausted

-700

Help databank not installed or inaccessible

-701

Help topic not found

-776

Maximum record length <%> exceeded

-777

Maximum header length exceeded

-800

Load/unload is not allowed within a transaction

-801

Pending transaction , Commit or Rollback

-802

Invalid transaction number, must be between 1 and <%>

-803

Server version and BSQL version must be the same when using READLOG

-804

The READLOG statement can not be used within a transaction

-805

Invalid string literal, missing delimiter (')

-806

Error when reading from terminal

-807

<%> logging has not been activated with the LOG command

-900

No buffer saved

-999

Too long statement

-1001

Syntax error in file name

-1002

File not found

-1003

File protection violation

-1004

File locked

-1005

Maximum number of opened files exceeded

-1006

Disk space exhausted

-1007

** Installation dependent **

-1008

** Installation dependent **

-1009

Unspecified file open error

-1101

Disk space exhausted

-1200

Previous perform file is not finished

-1300

Only select statements can be used with PRINT

-1400

Invalid numerical argument

-1500

Illegal value for <%>

-1600

The routine is overloaded. Use describe specific instead.


The Example Environment

This appendix describes the contents of the Mimer SQL example environment.

The complete environment is owned by a USER ident named MIMER_STORE.

The MIMER_STORE ident is granted the privilege to create databanks and idents.

The example environment is based on a store that sells music and books, available in various formats. The store also has a web site, through which users can place orders. Stored procedures (SQL/PSM) are used to take care of various aspects of the business logic.

The example environment forms as the basis for examples given in the Mimer SQL documentation set.

UNIX: On UNIX, the dbinstall utility, used to create the system databanks, provides an option to create the example environment.

Win: On Windows, the Mimer Administrator is used to create the system databanks. The wizard used for this provides an option to create the example environment.

The example environment can also be created manually by using the EXLOAD program (see below).

For more information about the example environment, see the feature description `The Example Database' on the Mimer SQL developer site, http://developer.mimer.com.


The EXLOAD program

By using the EXLOAD program the example environment can be created or removed. (The example environment can also be removed by dropping the MIMER_STORE ident from any SQL command tool).

At installation of the example environment, default values are provided for passwords and for databank file names. These default values can be changed by using the ALTER command after the installation is completed or by explicitly giving passwords and filenames as command line arguments to the EXLOAD command, see the syntax below.

Syntax

The EXLOAD syntax (expressed in Unix-style) is as follows:
usage: exload [-s|-m] [-f|-r] [-p SYSADM-password] [database]

-s: single, single-user mode database access
-m: multi, multi-user mode database access
-f: force, drop if already exists
-r: remove, drop if exists

When creating the example database it is possible to change the default
values for passwords and filenames. This is done by adding new values
after the 'database' parameter (that must be given in this case).
These optional parameters must be added in the following order:

[MIMER_STORE-password
[MIMER_ADM-password
[MIMER_USR-password
[MIMER_WEB-password
[MIMER_STORE-db-filename
[MIMER_ORDERS-db-filename
[MIMER_BLOBS-db-filename]]]]]]]

Command-line Arguments

UNIX-style
VMS-style
Function

-s

/SINGLE

Single-user mode database access

-m

/MULTI

Multi-user mode database access

-r

/REMOVE

Remove the example database

-f

/FORCE

Install the example database even if it already exists. The old environment is dropped.

-p SYSADM-password

/PASSWORD=
SYSADM-password

Password for SYSADM

database

database

Database name

MIMER_STORE-password

MIMER_STORE-password

Password for MIMER_STORE ident

MIMER_ADM-password

MIMER_ADM-password

Password for MIMER_ADM ident

MIMER_USR-password

MIMER_USR-password

Password for MIMER_USR ident

MIMER_WEB-password

MIMER_WEB-password

Password for MIMER_WEB ident

MIMER_STORE-db-filename

MIMER_STORE-db-filename

Filename for MIMER_STORE databank

MIMER_ORDERS-db-filename

MIMER_ORDERS-db-filename

Filename for MIMER_ORDERS databank

MIMER_BLOBS-db-filename

MIMER_BLOBS-db-filename

Filename for MIMER_BLOBS databank

All parameters to EXLOAD are optional. If no command line arguments are given, the following will happen:
  • A SYSADM password is needed and will be prompted for.
  • Access mode and database name will be taken from MIMER_MODE and MIMER_DATABASE, respectively.
  • If the example environment is already installed a question is prompted whether to continue or not
  • Default values are used for all example database passwords and filenames. The values used are displayed when the EXLOAD program is executed.

Exit Codes

The EXLOAD program returns an error status to the operating system when an error is encountered. This may be useful when running EXLOAD from scripts or in batch mode.

The following error codes are used:

UNIX/Windows
OpenVMS
Usage
0 (success)

1 (success)

This code is used when EXLOAD has executed successfully.

>1 (error)

2 (error)

This error code is used when EXLOAD failed to execute successfully.


The MIMER_STORE Schema

In this base schema most of the objects supported by Mimer SQL are introduced: collations, databanks, domains, idents, sequences, synonyms, tables and views. They are used to demonstrate some of the functionality that is available - constraints; primary, unique and foreign keys; secondary indices; NOT NULL; default values; and column and table check clauses. The schema also includes examples of PSM routines and triggers.

When an ident, other than a GROUP, is created, a schema of the same name is created by default. Objects in the MIMER_STORE schema have to be created before other objects can reference them.

Databanks

A databank is the physical file where a collection of tables is stored. There are three databanks defined in the example environment; the databank where the tables containing BLOBs will be stored is defined with the TRANSACTION option. The databanks have names that start with the prefix MIMER_.

Domains

There are a number of domains defined. In essence, a domain is a column data type specification that can be used in a number of table column definitions.

A number of different data types are introduced. The domains introduce the idea of consistency in the table definitions and demonstrate how to use check clauses, including the equivalent of NOT NULL.

Sequences

A sequence is an object that can provide a unique integer value. The sequences start with initial values other than the default to give the various sequences different number ranges; this is sometimes helpful when looking at data. The sequence names have a _SEQ suffix to help to clarify when they are being used.

Tables

Data in a relational database is logically organized in tables, which consist of horizontal rows and vertical columns. Columns are identified by a column-name.

A relational database is built up of several inter-dependent tables that can be joined together. Tables are joined by using related values that appear in one or more columns in each of the tables.

All the foundation tables are created under the MIMER_STORE schema; these tables provide a reasonably complete set for an introduction to SQL.

The tables CURRENCIES and COUNTRIES are used in many of the basic examples in this manual and contain entries for almost all countries and currencies, providing a moderate amount of data for any exercise.

Table name
Description

CURRENCIES

Holds currency details.

Introduces the use of domains (qualified with the schema name) and the decimal data type. Demonstrates that column constraints can include the primary key definition. Introduces the concept of NOT NULL in column definitions.

Uses the CHARACTER data type to show the difference compared to CHARACTER VARYING.

The table is created with the schema name explicitly included, after this example the default option is used.

Note the table is not defined in a specific databank, the system determines which databank to use; all other tables are created in a named databank.

Examples of creating comments on the table and columns follow this table creation; these are the only examples of comments.

COUNTRIES

Holds country details.

Introduces the concept of naming constraints; as when naming objects, a consistent approach will give benefits. Includes a unique constraint.

Introduces a foreign key definition linking the currency code to an entry in the CURRENCIES table. The column in the referenced table (CURRENCIES) is explicitly named; in future this is only done if the columns are not defined as the primary key.

CATEGORIES

Product categories, i.e. Music and Books. A category of `Video' is included for course work.

Shows the use of a collation in a column definition. A collation is a set of rules that determines how data is sorted and compared. Character data is sorted using collations that define the correct character sequence, with the capability to handle case-sensitivity and accents.

In this table create statement the primary, unique and foreign keys are defined as table constraints. This is the style that has been adopted in the rest of the schema, the previous examples were to show the syntax possibilities; again a consistent approach will make it easier for others.

FORMATS

Product formats, e.g. Paperback, Audio CD.

A domain could/should have been used for the CATEGORY_ID columns in the CATEGORIES and FORMATS table.

There are two unique constraints, both defined on two columns.

A DISPLAY_ORDER column has been included; the idea is that this can be used to display the different formats for a particular product in an order other than alphabetic.

PRODUCERS

Name of the record label or book publisher i.e. the organization that made the product.

Shows the use of a column default value that refers to a sequence.

PRODUCTS

Name of the product, i.e. name of the album or title of the book. It may be that the same product name is the title of a book and an album.

Until this point these referenced tables had been defined with the primary key as the identifier and the associated `name' as a unique key; this demonstrates the reverse.

This table holds a Soundex value of the name. As we shall see later the name is processed before the Soundex value is taken. A secondary index is defined on this Soundex value.

IMAGES

An item may have an image associated with it; for example the image could be an album or book jacket cover.

Note that this table is in the MIMER_BLOBS databank and therefore changes are not logged.

ITEMS

This forms a link between a product name and the different formats in which it is available. For example an album may appear in a number of different formats: Audio CD, Cassette, DVD Audio and Vinyl.

The table contains a number of attributes for the item, such as price (in euros) and stock level. There is also a reorder level but that is an extension for the classroom (i.e. a trigger can be used to place an order).

This table references a number of other tables and introduces the ability to specify what action to take when a referenced row is deleted. Both the PRODUCER_ID and IMAGE_ID permit a null value, which allows outer joins to be demonstrated.

A date data type is introduced in RELEASE_DATE. A secondary index is created on the date column allowing examples of how date ranges can be used.

The European Article Numbering (EAN) code is a useful example; it is the barcode that appears on everything these days. It is a unique key in its own right but the table has ITEM_ID as the primary key, making the EAN_CODE a candidate key. Obviously the EAN_CODE could have been used in referencing tables but in general it is a mistake to use external identifiers to join tables; this conflicts with the use of the International codes in the CURRENCIES and COUNTRIES tables but the data in these tables can be considered to be static.

A property of an EAN code is that it is a 13-digit number that incorporates a check digit. A PSM function to validate the check digit is included later in this schema; an ALTER TABLE statement then uses the PSM routine in a check clause to validate the EAN code.

CUSTOMERS

Personal details for store customers, whether they are on a mailing list or are Web users.

Shows the use of current_date as a default value against the REGISTERED column.

The idea is that when customers order through the Web-site they will be prompted for their e-mail address and password; the table check clause makes sure that the e-mail and password are either both defined or neither is defined.

Secondary indices are defined on the date of birth and post code columns, these are both useful when trying to identify a customer (e.g. when there are too many John Smiths to search through by name).

ORDERS

A record of when a customer placed an order.

ORDER_ITEMS

What items went into an order and the price at the time that the order was placed.

This is the first time that a multi-column primary key is used.

VALUE is an SQL reserved word; the definition shows how to force the use of a reserved word.

STOP_WORDS

Contains the 100 most common words in the English language.

Note that the CUSTOMERS, ORDERS and ORDER_ITEMS tables are grouped together in the MIMER_ORDERS databank.

It could be argued that the ITEMS table is not fully normalized - the PRODUCER_ID is repeated for each different format. But if you think about it you will realize that there is no benefit in introducing another table, only a penalty.

PSM Routines

The MIMER_STORE schema also introduces some fairly simple PSM routines (defined in a module named ROUTINES). These routines provide a basic introduction to functions and procedures; they demonstrate the general syntax of a number of PSM statements, including deterministic and access options.

Routines defined in a module cannot be modified without dropping the module, in which case all the routines in the module are dropped.

Note: The use of the `@' character which is used in BSQL to delimit SQL statements whose syntax involves use of the normal end-of-statement character `;' before the actual end of the statement. The `@' character may be used to delimit any statement; this is useful when dealing with large statements as the error reporting facility in BSQL shows more information in such cases.

You can debug PSM routines using Mimer SQL's Java-based graphic debugger. The debugger has support for watching variables, step-wise execution and setting breakpoints. You can debug procedures, functions and triggers.

Routine name
Description

AGE_OF_ADULT

Procedure that returns the age that a person is considered to be an adult.

A slightly contrived procedure to introduce the in and out parameter types; also includes an interval data type and a case statement.

CAPITALIZE

Function that processes a string making the first character of a word uppercase and the remainder of the word lowercase.

CAST_TO_DATE

Function that takes a character string (dd/mm/yy) and converts it to a date data type.

Note the use of the like comparison.

Introduce the concept of signaling, but this can be skipped over at this point.

EAN_CHECK_DIGIT

Function that returns the check digit for an EAN.

EXTRACT_DATETIME

Function to return just the variable component from a datetime or interval value. The value has to be cast to a character string in the call.

Uses `is not null'.

EXTRACT_DATE

Function to show how EXTRACT_DATETIME is used.

INDEX_TEXT

Function to process a character string, removing all punctuation characters. Also removes any of the words that are held in the table STOP_WORDS by using the exists predicate.

PRODUCT_SEARCH_CODE

Function to return the Soundex value of a character string after it has been processed by the INDEX_TEXT function.

RECIPIENT

Function to form the recipient details, e.g. for a letter address - Mr J Smith.

Note that the use of `is not null' is redundant.

SALUTATION

Function to create a formal or informal salutation based on the age of the person.

Performs simple date arithmetic using the AGE_OF_ADULT function.

STOP_WORDS

Database load procedure to extract the words from a character string and insert them into the STOP_WORDS table.

VALIDATE_EAN_CODE

Function to return the EAN code with the correct check digit.

Example of large number arithmetic.

The ITEMS table is altered to include a check clause using the VALIDATE_EAN_CODE function to ensure that only valid EAN codes can be entered.

After the definition of the views (see next section for details) there is another PSM routine, it shows how to create a standalone procedure.


Procedures

Procedure name
Description

COMING_SOON

Result set procedure that returns the products for a particular category that will be released in the next month.

Makes use of the PRODUCT_DETAILS view to demonstrate that a select against a view is treated the same as a table.

Shows how to define a result set procedure and one form of the row data type. Introduces the concepts of cursors.

The use of get diagnostics avoids any real need to cover condition handlers at this stage.


Views

There are a number of views in the MIMER_STORE schema to show various aspects of the functionality that can be supported.

View name
Description

CUSTOMER_DETAILS

Based on the single table CUSTOMERS.

Shows the simplest type of view - updatable if the user has the privilege. Allows a new customer to be added to the database.

SWEDISH_CUSTOMERS

Based on the previous view (i.e. views can be defined on views) but with a selection condition so that only Swedish customers are displayed.

Uses a `with check option' so that only Swedish customers can be operated on through this view.

CUSTOMER_ADDRESSES

Again based on the CUSTOMER_DETAILS view but also joined to the COUNTRIES table.

Shows how PSM functions can be used from standard SQL statements, including applying pre-defined functions on the result.

Demonstrates how to name the columns in a view definition. Introduces the coalesce expression.

The join is slightly more complicated than it needs to be because the column names are not consistent.

PRODUCT_DETAILS

Forms a view across a number of tables, including an outer join on the PRODUCERS table.

Note that the coalesce expression in the select list is named; all calculated columns in a view have to be named.


Triggers

There are examples of two very simple statement triggers in the MIMER_STORE schema, both defined against the PRODUCTS table. They are designed to ensure that the value of the PRODUCT_SEARCH column is based on the product name. The insert trigger unconditionally performs an update of the PRODUCT_SEARCH column (setting it to the default value), which will force the update trigger to fire.

The update trigger checks whether the value in the PRODUCT_SEARCH column is equal to the value returned by the PRODUCT_SEARCH_CODE function when applied to the product name. If the value of PRODUCT_SEARCH is inconsistent with the product name then an update is performed; this will cause a recursive call of the update trigger. Note that an update statement causes the trigger to fire even when no rows are updated, hence the use of 'if exists'.

The PRODUCT_SEARCH_CODE function returns the Soundex value of the product name after it has been processed by the INDEX_TEXT function, which basically involves stripping out any of the words that appear in the STOP_WORDS table (i.e. the 100 most common words in the English language). As an example, the album title `Same As It Ever Was' is reduced to `Same Ever' by the INDEX_TEXT function and the Soundex value returned by the PRODUCT_SEARCH_CODE function would then be `467900'. By using this function against a user input string, it is possible to allow a limited level of fuzzy matching.


Idents

There are a number of idents, both users and groups. All ident names have a MIMER_ prefix and group idents have a _GROUP suffix to aid with their identification.

The user ident MIMER_ADM can be thought of as an administrator, which is the reason why the user has been given the system privilege to create new idents and tables. Access and object privileges are not granted directly to the user but inherited through membership of MIMER_ADMIN_GROUP. This group of idents has a high level of privileges to manipulate the database tables and views but they do not have total uncontrolled access, only the MIMER_STORE ident has that. It should be possible to perform a lot of the course work from the MIMER_ADM ident.

The user MIMER_USR is granted membership of the MIMER_STORE_GROUP. As can be seen it is possible to grant a group membership of another group; in this case, the members of the MIMER_ADMIN_GROUP (now and in the future) are automatically granted whatever privileges are allocated to MIMER_STORE_GROUP. So when the execute privilege on the PSM procedure COMING_SOON is allocated to MIMER_STORE_GROUP it is automatically also granted to the members of MIMER_ADMIN_GROUP, which includes the user MIMER_ADM.


The MIMER_STORE_MUSIC Schema

Schemas have been used to group objects related to a specific area; note that all the schemas are owned by the ident MIMER_STORE.

It is possible to ignore the schemas other than MIMER_STORE; they can be viewed as a black box for the purposes of an introduction to SQL.

A CREATE SCHEMA statement is used to demonstrate that, within the statement, an object doesn't have to be created before it can be referenced.

The DURATION domain introduces the INTERVAL data type.

Tables

Table name
Description

ARTISTS

Holds artist names (e.g. Bruce Springsteen) with a Soundex value based on the name.

Shows that objects in schema other than the current have to be qualified with the owning schema name.

TITLES

Links an artist name with an item.

TRACKS

Holds track details, e.g. title, length.

SAMPLES

Holds samples from the tracks.

Views

View name
Description

DETAILS

This view includes the PRODUCT_DETAILS view and demonstrates a number of things:

Tables can be included from more than one schema.

The use of a correlation name in the PRODUCT_DETAILS table reference.

Restriction conditions can be applied so that different users would see different results, in this case the ident MIMER_WEB would not see vinyl albums.

One of the quirks in SQL - ITEM_ID has to be explicitly included in the select-list.

SEARCH

This view is based on DETAILS but includes additional selection restrictions.

Note that the AS clause is noise and may be omitted but it does add clarity. This view demonstrates both forms.

Note that the CREATE SCHEMA statement includes grant object privilege statements as well as object definitions.

PSM Routines

The names in the PSM routines for this schema have been capitalized rather than the parts separated by an underscore.

PSM routine name
Description

AddTitle

Procedure to insert the base details for an album into the database, updating a number of tables.

There are a number of checks on the input, for instance that the format is valid for the Music category and that the label exists in the PRODUCERS table - these introduce exception handlers.

Uses another form of the row data type.

Demonstrates the use of current_value of a sequence.

AddTrack

Procedure to insert the track details for an EAN code.

Demonstrates the use of SQLSTATE values in an exception handler.

Introduces the RESIGNAL statement and shows another option with GET DIAGNOSTICS.

ArtistName

Function to remove any leading definite or indefinite articles from a name.

ArtistSearchCode

Function to return the Soundex value of a character string after it has been processed by the ArtistName function.

Search

Result set procedure that searches the Music 'database' for matches based on the supplied title and artist details; a third parameter specifies the maximum number of rows to be returned (a value of zero suppresses this feature).

Makes use of the SEARCH view. Includes the use of the DISPLAY_ORDER column in the FORMATS table to present the data in an order other than alphabetic.

Each row is given a star rating that indicates the level of match (**** = exact). The artist 'Bruce Springsteen' provides a number of matches.

This procedure demonstrates the level of functionality that can be placed in the database.

TitleDetails

Result set procedure that returns music details for a given item identifier.

Given the result from a search this would allow the user to 'drill down' into the displayed information.

Illustrates how the compound statement label can be used to qualify a variable name. Shows that interval arithmetic can be performed.

TrackDetails

Result set procedure that returns any track details for a given item identifier.

Usage is the same as for TitleDetails.

Demonstrates the use of a user defined SQLSTATE.

Triggers

There are two statement triggers in the MIMER_STORE_MUSIC schema, both defined against the ARTISTS table. They are designed to ensure that the value of the ARTIST_SEARCH column is based on the artist name. The same technique as used in the MIMER_STORE schema to force an unconditional update is applied in the insert trigger.

The update trigger is written to show that it can contain the same level of functionality as any PSM routine; in this case it uses a cursor to process the updates.

Idents

Execute privilege on the PSM routines Search, TitleDetails and TrackDetails is granted to MIMER_STORE_GROUP.

Synonyms are created by MIMER_STORE for all tables and views in the MIMER_STORE_MUSIC schema. The synonyms are created with a MUSIC_ prefix for the DETAILS, SEARCH and TITLES tables/views.


The MIMER_STORE_BOOK Schema

This schema contains a table named TITLES, as does the MIMER_STORE_MUSIC schema.

Tables

Table name
Description

TITLES

Links an item with a list of authors and an ISBN.

A book may have more than one author, the names are held as a list in the column AUTHORS_LIST in the form: surname, forenames; surname, forenames; ...

AUTHORS

Links an item to an entry in the KEYWORDS table.

KEYWORDS

Holds each author in the form surname,[first initial].

An extension would be to categorize books and create a new table to form a link between an item and various categories, with the category being held in the KEYWORDS table.

Object privileges are granted on the MIMER_STORE_BOOK schema tables to MIMER_ADMIN_GROUP.

PSM Routines

PSM routine name
Description

VALIDATE_ISBN

Procedure to validate an ISBN.

Uses an INOUT parameter.

Shows how to CAST to an INTEGER and trap any error to validate that a string is numeric.

FORMAT_ISBN

Function to format an ISBN (e.g. insert hyphens to separate the country, group, publisher, title identifiers).

Demonstrates the use of CASE statements, including where there is not an ELSE. Uses a row data type to simplify coding.

AUTHORS

Function to return the first author from a list of authors; if there is more than one author then a mark of omission is included.

AUTHORS_NAME

Function to format an author's name into surname[,initial].

KEYWORD_ID

Function to insert a word into the KEYWORDS table and return the identifier with which the keyword is associated.

CATALOGUE_AUTHORS

Given the list of authors associated with a book, extracts each author, calls the AUTHORS_NAME function and then the KEYWORD_ID function. Finally, creates a link between each name and the book in the AUTHORS table.

ADD_TITLE

Procedure to insert the base details for a book into the database, updating a number of tables.

Inserts against the DETAILS view.

SEARCH

Result set procedure that searches the Book 'database' for matches based on the supplied title and author (surname, forename).

The author 'Christie, Agatha' will provide a number of matches.

Demonstrates a different method of searching.

TITLE_DETAILS

Result set procedure that returns book details for a given item identifier.

Given the result from a search this would allow the user to 'drill down' into the displayed information.

Views

View name
Description

DETAILS

This view includes the PRODUCT_DETAILS view and is the equivalent of the DETAILS view in the MIMER_STORE_MUSIC schema.

Triggers

There are two statement triggers defined against the TITLES table. They are designed to maintain the entries in the AUTHORS and KEYWORDS tables. The update trigger is written to show how to use the OLD and NEW table aliases.

There is also an INSTEAD OF trigger defined on the DETAILS view. This is used by the ADD_TITLE procedure to update the underlying tables on which the view is based. Note that the INSTEAD OF trigger has to be defined before an insert statement against the view can be included, otherwise the join is not considered to be updateable.

Idents

Access privileges on the view DETAILS are granted to MIMER_ADMIN_GROUP.

Execute privilege on the PSM routines SEARCH and TITLE_DETAILS is granted to MIMER_STORE_GROUP.

Synonyms are created by MIMER_STORE for all tables in the MIMER_STORE_BOOK schema. The synonyms are created with a BOOK_ prefix for the DETAILS view and TITLES table.


The MIMER_STORE Schema Revisited

A quick return to the base schema to include two PSM routines that are outside the scope of an introduction to SQL.

PSM Routines

PSM routine name
Description

ORDER_ITEM

Procedure to associate an order for a quantity of a particular item against an order identifier.

BARCODE

Result set procedure that returns the book or music details for a given EAN.

The intention would be to use this with a POS barcode reader.


The MIMER_STORE_WEB Schema

This schema provides some of the SQL functionality required to create a Web-based application to order items.

The ident-name in the AUTHORIZATION clause is currently restricted to be the name of the current ident.

The basic idea behind a Web application would be two tabs, one for Music and the other for Books. The relevant SEARCH routine provides a list of matches (one of the details returned is the ITEM_ID). The user would then have the ability to drill down to display further information (use MIMER_STORE_MUSIC.TitleDetails and MIMER_STORE_MUSIC.TrackDetails for music items and MIMER_STORE_BOOK.TITLE_DETAILS for book items.

If an item is selected for purchase (the quantity should be prompted for) and MIMER_STORE_WEB.ADD_TO_BASKET used to order the item (a blank SESSION_ID defines a new session). Once a session has been created the basket can be viewed using MIMER_STORE_WEB.VIEW_BASKET.

When an order has been completed the user needs to be identified by their e-mail and password (MIMER_STORE_WEB.VALIDATE_CUSTOMER) and then a call should be made to MIMER_STORE_WEB.PLACE_ORDER.

Tables

Table name
Description

SESSIONS

Maps an external session identifier with an internal order identifier.

Keeps track of the date/time that the 'basket' was last accessed.

PSM Routines

PSM routine name
Description

SESSION_EXPIRATION_PERIOD

Function that returns an interval data type that defines the period in which a basket should be accessed.

DELETE_BASKET

Procedure to delete a specified basket session; alternatively a session of '*' will delete all 'baskets' that have expired.

Note that the procedure deletes entries in the ORDERS table and relies on foreign key definitions in referencing tables to tidy up.

ORDER_ID

Function to return the order identifier associated with a specified session identifier.

Raises an exception if the 'basket' hasn't been used within the period specified by SESSION_EXPIRATION_PERIOD.

VALIDATE_BASKET

Function that uses the ORDER_ID function to validate that the basket is still active.

Uses an exception handler to catch any SESSION_INVALID exception raised by ORDER_ID. The exception handler will call DELETE_BASKET to remove a basket that has expired.

ADD_TO_BASKET

Function to place an order for a quantity of a specific item. If the session identifier is provided, then the order is placed against the relevant order identifier; if the session identifier is blank, then a new basket is created.

The function returns the current session identifier.

VIEW_BASKET

Result set procedure that lists the items ordered.

Uses a GROUP BY clause. Calls the BARCODE procedure, which is itself a result set procedure.

VALIDATE_CUSTOMER

Function to identify a customer by their e-mail address and password.

PLACE_ORDER

Procedure to order the contents of the basket. Procedure takes two in parameter: session identifier and customer identifier.

Returns an order number, total price in euros, the local currency for the customer and the price in that local currency.

Triggers

There is a statement trigger that will fire after an update to the SESSIONS table. The trigger is designed to prevent any changes to the values of the SESSION_NO and ORDER_ID columns. This demonstrates that a trigger can be used to abort an SQL update operation.

Idents

A synonym is created by MIMER_STORE for the SESSIONS tables in the MIMER_STORE_WEB schema.

A new user ident, MIMER_WEB, is created to allow Web-applications execute privilege on certain of the PSM routines.


Synonyms

A complete set of synonyms is created for the ident MIMER_ADM. This is to simply access to the tables in the classroom; the synonym can be used in place of the table-names that would have to be qualified with the schema-name.


Deprecated Features

This chapter discusses features and functionality that have been deprecated.


BSQL Commands

The following BSQL commands are deprecated but are supported for backward compatability.

LOAD

The LOAD command, used to load data from a sequential file into a target table, is now deprecated. Instead of LOAD, please see the Mimer SQL System Management Handbook, Loading and Unloading Data and Definitions.

UNLOAD

The UNLOAD command, used to unload data from a table into a sequential file, is now deprecated. Instead of UNLOAD, please see the Mimer SQL System Management Handbook, Loading and Unloading Data and Definitions.


Mimer SQL Engine
User's Manual
Version 9.3

Mimer SQL Engine User's Manual, Version 9.3, March 2008
© Copyright Mimer Information Technology AB.

The contents of this manual may be printed in limited quantities for use at a Mimer SQL installation site. No parts of the manual may be reproduced for sale to a third party.
Information in this document is subject to change without notice. All registered names, product names and trademarks of other companies mentioned in this documentation are used for identification purposes only and are acknowledged as the property of the respective company. Companies, names and data used in examples herein are fictitious unless otherwise noted.

Produced and published by Mimer Information Technology AB, Uppsala, Sweden.

P.O. Box 1713,
SE-751 47 Uppsala, Sweden.
Tel +46(0)18-780 92 00.
Fax +46(0)18-780 92 40.

Mimer Web Sites:
http://www.mimer.com
 http://developer.mimer.com