Mimer SQL Engine Programmer's Manual

Introduction

Mimer SQL Engine is an advanced relational database management system (RDBMS) developed by Mimer Information Technology AB.

Mimer SQL Engine has a number of unique technical solutions to handle some of the more complicated functionality that a database management system must provide.

For example, Mimer SQL Engine provides a solution to the problem of allowing simultaneous access to the database without the danger of a deadlock occurring. This greatly simplifies database management and allows truly scalable performance, even during heavy system load.

Another significant technical innovation is the data storage mechanism, which is constantly optimized for the highest possible performance and ensures that no manual reorganization of the database is ever needed.

Mimer SQL Engine offers a uniquely scalable solution, including multi-processor support and, with its availability on all major UNIX/Linux, Windows Vista/XP/200x/NT, Mac OS X, and OpenVMS platforms, is ideally suited for open environments where interoperability is important. Mimer SQL Mobile, which is especially adapted for mobile environments like cellular phones and PDA's, is available on Symbian and Pocket PC.

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


About this Manual

The manual is intended for application developers working with Mimer SQL.

This manual describes the usage of SQL in application programs, and provides, together with the Mimer SQL Engine Reference Manual, the complete reference material for Mimer SQL.

For details on how to read the syntax diagrams that appear in this manual see the Mimer SQL Reference Manual, Reading SQL Syntax Diagrams.

This manual describes how SQL statements may be embedded in application programs written in conventional host languages. It also describes how to create and use stored procedures and triggers.

The information contained in this manual generally applies to all the platforms supported by Mimer SQL. From time to time platform-specific notes appear in the general description, presented as follows:

UNIX: Denotes information that applies specifically to UNIX platforms.

VMS: Denotes information that applies specifically to OpenVMS platforms.

Win: Denotes information that applies specifically to Windows platforms.


Database APIs

You can access Mimer SQL using the following native database application interfaces:

· ADO.NET

ADO.NET is the interface of choice when developing database applications in the Microsoft .NET framework and it is the natural successor for ADO (ActiveX Data Objects).

· JDBC

JDBCTM is the Java database API specified by Sun Microsystems Inc.

· ODBC

ODBC is a database independent interface. Through ODBC, Mimer SQL can support many of the tools available on the platforms supporting ODBC, e.g. Windows and UNIX.

· Embedded SQL (ESQL)

ESQL is used through a host programming language (C/C++, COBOL or FORTRAN as available on the host computer). SQL statements are included as part of the source code for an application program, which is compiled and linked with the appropriate language-specific facilities. The SQL statements are executed in the context of the application program.

In addition, Mimer SQL supports other interfaces such as PHP, Perl, QT and ADO. Please see http://developer.mimer.com/interfaces for details.

Prerequisites

Application developers using this manual are assumed to have a working acquaintance with the principles of the relational database model in general and of Mimer SQL in particular.

Knowledge of Mimer SQL is of course an advantage, although experience with other standard-compliant SQL implementations will suffice. Experience of Mimer SQL is best gained through interactive use of the BSQL facility, see the Mimer SQL User's Manual, Mimer BSQL.

Related Mimer SQL Publications

· Mimer SQL Engine Reference Manual

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

· Mimer SQL Engine User's Manual

contains a description of the BSQL facilities. A user-oriented guide to the SQL statements is also included, which may provide help for less experienced users in formulating statements correctly (particularly the SELECT statement, which can be quite complex).

· Mimer SQL Engine System Management Handbook

describes system administration functions, including export/import, backup/restore, databank shadowing and the statistics functionality.

The information in that manual is used primarily by the system administrator, and is not required by application program developers. The SQL statements that 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 release for which they are supplied.

· Mimer JDBC Driver Guide

is intended for Java application developers working with Mimer SQL. It covers all available Mimer JDBC drivers. The guide describes the usage of SQL in Java applications.

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, make 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.

SQL: 1999 - Understanding Relational Language Concepts, by Jim Melton, Alan R. Simon, and Jim Gray. ISBN: 1-55860-456-1. Explains SQL-99.

Advanced SQL: 1999 - Understanding Object-Relational and Other Advanced Features, by Jim Melton. ISBN: 1-55860-677-7. In-depth guide to SQL-99's practical application.

JDBC

JDBC information can be found on the internet at: http://java.sun.com/products/jdbc

For information on specific JDBC methods, please see the online documentation, which is normally included in the Java development environment.

JDBC API Tutorial and Reference, 2nd edition. ISBN: 0-201-43328-1. A useful book published by JavaSoft.

ODBC

Microsoft ODBC 3.0 Programmer's Reference and SDK Guide for Microsoft Windows and Windows NT. ISBN: 1-57231-516-4. This manual contains information about the Microsoft Open Database Connectivity (ODBC) interface, including a complete API reference.

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.

CAE Specification, Data Management: Structured Query Language (SQL), Version 2.
X/Open document number: C449. ISBN: 1-85912-151-9. This document contains the
X/Open-95 SQL specification.

Definitions, Terms and Trademarks

ANSI

American National Standards Institute, Inc.

API

Application Programming Interface

BSQL

The Mimer SQL facility for using SQL interactively or by running a command file

ESQL

The preprocessor for embedded Mimer SQL

IEC

International Electrotechnical Commission

ISO

International Standards Organization

JDBC

The Java database API specified by Sun Microsystems, Inc.

ODBC

Open Database Connectivity

PSM

Persistent Stored Modules, the term used by ISO/ANSI for stored procedures

SQL

Structured Query Language

X/Open

X/Open is a trademark of Open Group

All other trademarks are the property of their respective holders.


Mimer SQL and the ODBC API

ODBC enables your Mimer SQL database to become an integral part of an application. SQL statements can be incorporated into the application, allowing the application to retrieve and update values from a database. Values from the database can be placed in program variables for manipulation by the application. Conversely, values in program variables can be written to the database.

This chapter is not intended to be a complete guide to the functionality provided by ODBC. It is written to introduce you to accessing Mimer SQL through ODBC.

For a more detailed discussion of Mimer SQL, ODBC and other database APIs, please see: http://developer.mimer.com/interfaces


The Mimer ODBC Driver

Mimer SQL-specific versions of ODBC functions are implemented in the Mimer SQL native ODBC driver. The driver passes SQL statements to the Mimer SQL server and returns the results of the statements to the application.

The Mimer SQL setup process automatically installs the ODBC driver when the Mimer SQL client is installed on any Windows or UNIX platform.

Mimer SQL programs that are written using the ODBC API, communicate with Mimer SQL through C function calls.


Required Files

To make ODBC function calls, a C or C++ program must include the sqlext.h header file (a copy is supplied with the Mimer SQL distribution). By including this header file, sql.h is automatically included.

ODBC applications are linked with the ODBC Driver Manager when available, otherwise against the Mimer ODBC driver. On Windows, this is the file ODBC32.LIB, on other platforms you will need to check the documentation supplied with the ODBC Driver Manager.

To compile ODBC applications using Mimer specific functions and attributes, include the file mimodbc.h, which is included with the distribution.


Unicode and ANSI Interfaces

The Mimer ODBC driver is Unicode based. This allows applications to use both the ANSI and Unicode interfaces when using Mimer SQL. Unicode based applications can both store and retrieve Unicode data through SQL statements and/or Unicode host variables.

ANSI applications can use Unicode host variables, but are restricted to character (8-bit) characters when passing data with SQL statements. Please note that database objects still have to be named with the same character set as before.

The Unicode SQL data types in Mimer SQL are called NATIONAL CHARACTER (or NCHAR), NATIONAL CHARACTER VARYING (NCHAR VARYING), and NATIONAL CHARACTER LARGE OBJECT (NCLOB).

You can find more information about these data types in the Mimer SQL Reference Manual. The ODBC documentation contains specifics about the SQL_WCHAR database type and the SQL_C_WCHAR and SQL_C_WLONGVARCHAR host language types.


Examples in this Chapter

The ODBC function calls in the examples use ODBC 3.5 syntax, although they are not dependent on a Windows platform. It should be possible to use the examples as a basis for translation into other languages.

In the examples, there are various references to macros (e.g. SQL_ERROR), these are defined in the sql.h header file. SQL_NTS indicates a null terminated string; hopefully other names are self-explanatory.


Operating Systems

Mimer SQL supports ODBC as one of its native APIs for applications written in C, C++, Microsoft Visual Basic and a large number of other development tools.

UNIX: Information on the availability and use of Driver Managers for ODBC on UNIX platforms can be provided by your Mimer SQL distributor.

The library to specify as the ODBC driver when defining a Mimer ODBC data source is libmimer. An ODBC SDK (various versions available) is also useful in order to develop applications.

VMS: An ODBC driver is supplied with Mimer SQL on OpenVMS and client applications can link with it to use ODBC on OpenVMS platforms. The Driver Manager for ODBC on OpenVMS is not yet available. For further assistance, contact your Mimer SQL representative.

A Mimer SQL database server running on an OpenVMS node can always be accessed remotely by a client application using ODBC from another type of platform via the client/server interface.

Win: When a Mimer SQL client is installed on a Windows platform, the ODBC driver manager and other resources needed to use ODBC are also installed.

The ODBC SDK (available from Microsoft) is also required in order to develop applications.


Declarations

A C program that calls the ODBC API typically requires the following declarations:

#if defined(WIN32)
#include <windows.h>
#endif
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include "sqlext.h"
#include "mimodbc.h"

SQLHENV  henv;       // Environment handle for application
SQLHDBC  hdbc;       // Connection handle
SQLHSTMT hstmt;      // Statement handle

Handles identify a particular item; in ODBC this item can be an environment, connection, statement or descriptor. When the application calls SQLAllocHandle, the Driver Manager creates a new item of the specified type and returns the handle to the application. The application uses the handle to identify that item when calling ODBC functions.


Initializing the ODBC Environment

The first task for any ODBC application is to initialize the ODBC environment by allocating an environment handle (SQL_HANDLE_ENV):

/* Allocate environment handle */
if ( SQLAllocHandle( SQL_HANDLE_ENV,
                     SQL_NULL_HANDLE,
                     &henv ) == SQL_ERROR )
{
    printf( "Failed to allocate environment handle\n" );
    . . .
}

Before an application allocates a connection, it should declare the version of ODBC that it has been written for (this mainly affects SQLSTATE values and datetime data types), and then allocate a connection handle:

/* Set the ODBC version environment */
SQLSetEnvAttr( henv,
               SQL_ATTR_ODBC_VERSION,
               (SQLPOINTER)SQL_OV_ODBC3,
               SQL_IS_INTEGER );

/* Allocate connection handle */
if ( SQLAllocHandle( SQL_HANDLE_DBC,
                     henv,
                     &hdbc ) == SQL_ERROR )
{
    printf( "Failed to allocate connection handle\n" );
    . . .
}

Making a Connection

If an ODBC data source has been defined, ODBC applications can connect to Mimer SQL by using the data source name. Alternatively SQLDriverConnect can be used.

There are a number of mechanisms to get the information required to make a connection; some applications supply the connection details, others use the ODBC dialog box to allow the user to complete the information.

The simplest form of connection uses SQLConnect, which requires a data source name, user ID and password, for example:

SQLRETURN retcode;
. . .

/* Set connection timeout - 10 seconds */
SQLSetConnectAttr( hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)10, 0 );

/* Connect - DSN, User ID, Password */
retcode = SQLConnect( hdbc,
                      (SQLCHAR*) "EXAMPLEDB", SQL_NTS,
                      (SQLCHAR*) "MIMER_ADM", SQL_NTS,
                      (SQLCHAR*) "admin", SQL_NTS );
if ( retcode == SQL_SUCCESS
||   retcode == SQL_SUCCESS_WITH_INFO )
{
    /* User connected */

SQLDriverConnect allows the driver to connect by supplying the connection information as a number of keyword-value pairs:

"DSN=EXAMPLEDB;UID=MIMER_ADM;PWD=admin;"

There is an option for the Driver Manager to enter into a dialog with the user to complete any missing connection information (the handle of the parent window needs to be supplied to use this facility).

In the following example, the Driver Manager displays a window containing a combo box listing all the Mimer SQL database names and prompts for the user name and password:

SQLCHAR     OutConnectString[256];
SQLSMALLINT StringLength;

. . .

retcode = SQLDriverConnect( hdbc,
                            hwnd,
                            (SQLCHAR*) "DRIVER=Mimer;", SQL_NTS,
                            (SQLCHAR*)OutConnectString,
                            sizeof(OutConnectString),
                            &StringLength,
                            SQL_DRIVER_COMPLETE );
if (SQL_SUCCEEDED(retcode))
{
    /* User connected */
    printf( "connection string used: %s\n, OutConnectString );

Note:	The macro SQL_SUCCEEDED replaces the test against SQL_SUCCESS or SQL_SUCCESS_WITH_INFO.
Controlling Interaction with the User

You may wish to have more control over the interaction with the user, SQLDataSources provides a mechanism to get information about the data sources configured on the client:

SQLCHAR   DSNname[SQL_MAX_DSN_LENGTH+1];
SQLCHAR   driver[33];

. . .

/* Enumerate the system data source names */
retcode = SQLDataSources( henv,
                          SQL_FETCH_FIRST_SYSTEM,
                         (SQLCHAR*)DSNname,
                          sizeof(DSNname),
                          NULL,
                          SQLCHAR*)driver,
                          sizeof(driver),
                          NULL );

while (SQL_SUCCEEDED(retcode))
{
    printf( "%-32s     %s\n", DSNname, driver );  // Display details

    /* Fetch next */
    retcode = SQLDataSources( henv,
                              SQL_FETCH_NEXT,
                             (SQLCHAR*)DSNname,
                              sizeof(DSNname),
                              NULL,
                              (SQLCHAR*)driver,
                              sizeof(driver),
                              NULL );
}

Connecting Using a File Data Source

Another way of making a connection is to create a file data source. The file has a .dsn extension and contains the keyword-value pairs to make the connection.

Although it is possible to include the password, this would make the system insecure and therefore is not recommended:

[ODBC]
DSN=EXAMPLEDB
UID=MIMER_ADM

To make a connection using a file data source, use the option for the Driver Manager to enter into a dialog with the user to complete any missing connection information (again, the handle of the parent window needs to be supplied to use this facility):

retcode = SQLDriverConnect( hdbc,
                            hwnd,
                            (SQLCHAR*) "FILEDSN=example.dsn;",
                            SQL_NTS,
                            (SQLCHAR*)OutConnectString,
                            sizeof(OutConnectString),
                            &StringLength,
                            SQL_DRIVER_COMPLETE );
if (SQL_SUCCEEDED(retcode))
{
    /* User connected */
Determining Driver and Data Source Capabilities

After connection to the database, use SQLGetInfo to determine the capabilities of the driver and the data source associated with the connection:

/* Display DBMS version details */
SQLGetInfo( hdbc,
            SQL_DBMS_VER,
            (SQLPOINTER)&str_value,
            sizeof(str_value),
            &str_len );
printf( "%s\n", str_value );

/* Display SQL conformance level */
SQLGetInfo( hdbc,
            SQL_SQL_CONFORMANCE,
            (SQLPOINTER)&int_value,
            sizeof(int_value),
            NULL );
if (int_value & SQL_SC_SQL92_ENTRY)
   printf( "Entry level SQL-92\n" );
if (int_value & SQL_SC_FIPS127_2_TRANSITIONAL)
   printf( "FIPS 127-2 transitional level\n" );
if (int_value & SQL_SC_SQL92_INTERMEDIATE)
   printf( "Intermediate level SQL-92\n" );
if (int_value & SQL_SC_SQL92_FULL)
   printf( "Full level SQL-92\n" );´</pre>

Disconnecting

When the application has finished using a data source, it calls SQLDisconnect.

After disconnecting, the application should call SQLFreeHandle to release the connection handle and, if appropriate, to release the environment handle.


Error Handling

ODBC returns diagnostic information in two ways:

  • a return code indicating the success or failure of the ODBC function
  • diagnostics records, providing detailed information.

In general, program logic uses the return code to detect a failure and then the diagnostic records to detail the reason for the failure.

Retrieving Warning and Error Messages

If the ODBC driver returns a code indicating anything other than SQL_SUCCESS then the application can call SQLGetDiagRec to retrieve any warning or error messages:

SQLCHAR     msg[SQL_MAX_MESSAGE_LENGTH+1];
SQLCHAR     sqlstatus[6];
SQLSMALLINT msglen, msgno;
SQLINTEGER  nativeerror;

. . .

msgno = 1;
while (SQLGetDiagRec( SQL_HANDLE_DBC,
                      hdbc,
                      msgno++,
                      sqlstatus,
                      &nativeerror,
                      msg,
                      sizeof(msg),
                      &msglen) == SQL_SUCCESS)
{
    msg[msglen] = '\0';

    printf( "SQLSTATE:   %s\n", sqlstatus );
    printf( "Native:     %d\n", nativeerror );
    printf( "Message:    %s\n", msg );
    printf( "\n" );
}

Diagnostic records are associated with the ODBC handles: environment, connection, statement and descriptor. SQLGetDiagRec requires the handle type and the handle, making the coding of a general-purpose error handler more complex than other programming interfaces.

A warning is indicated by an SQLSTATE class value of '01' (e.g. '01000').


Transaction Processing

A transaction is an essential part of database programming. It defines the beginning and end of a series of database operations that are regarded as a single unit.

For example, to transfer money between two bank accounts, an amount is subtracted from one account and the same amount is added to the other account. It is essential that either both of these operations succeed or neither does.

Mimer SQL uses a method for transaction management called Optimistic Concurrency Control (OCC). OCC does not involve any locking of rows as such, and therefore cannot cause a deadlock.

Transactions in ODBC are usually managed at the connection level, although there is the option of applying a commit or rollback for all connections within an environment.

Transaction Management Mode

There are two modes for managing transactions within ODBC, Autocommit and Manual-commit. SQLSetConnectAttr is used to switch between the modes.

Autocommit Mode

Autocommit mode is the default transaction mode for ODBC; when a connection is made, it is in autocommit mode until SQLSetConnectAttr is used to switch to manual commit mode.

In autocommit mode each individual statement is automatically committed when it completes successfully, no explicit transaction management functions are necessary.

However, the return code from the function must still be checked as it is possible for the implicit transaction to fail.

Manual-commit Mode

When in manual commit mode, all executed statements are included in the same transaction until calling SQLEndTran specifically completes it.

When an application turns autocommit off, the next statement against the database starts a transaction. The transaction continues until SQLEndTran is called with either SQL_COMMIT or SQL_ROLLBACK. The next command sent to the database after that starts a new transaction.

Completing Transactions

Transactions are completed (either committed or rolled back) by use of the ODBC function SQLEndTran rather than using the SQL COMMIT or ROLLBACK statements.

Calling SQLEndTran with a request to rollback a transaction causes Mimer SQL to discard any changes made since the start of the transaction and to end the transaction.

Example Transaction

/* Disable transaction autocommit mode */
SQLSetConnectAttr( hdbc, SQL_ATTR_AUTOCOMMIT,
                   (SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0 );

SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt );

retry:
/* First statement against Mimer SQL starts a transaction */
SQLExecDirect( hstmt,
               "UPDATE mimer_store.currencies \
                   SET exchange_rate = exchange_rate * 1.05 \
                   WHERE code = 'USD'", SQL_NTS );

SQLExecDirect( hstmt,
               "UPDATE mimer_store.currencies \
                   SET exchange_rate = exchange_rate * 1.08 \
                   WHERE code = 'GBP'", SQL_NTS );

printf( "Commit transaction? : " );
scanf( "%s", ans );
if (ans[0] == 'Y'
|| ans[0] == 'y')
{
   retcode = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT );
   if (retcode == SQL_ERROR)
   {
      /* Check SQLSTATE for transaction conflict */
      SQLGetDiagField (hdbc,
                       1,
                       SQL_DIAG_SQLSTATE,
                       sqlstatus,
                       sizeof(sqlstatus),
                       &msglen );
      if (strcmp( sqlstatus, "40001" ) == 0) goto retry;
      goto display_error;
   }
}
else
{
   SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK );
}

Setting the Transaction Isolation Level

To set the transaction isolation level, use the SQL_ATTR_TXN_ISOLATION connection attribute.

The default isolation level for Mimer SQL is SQL_TXN_REPEATABLE_READ.


Executing a Command

The simplest way to execute a statement is to execute it directly using the SQLExecDirect function.

Each INSERT, UPDATE, and DELETE statement returns the number of rows affected by the operation, the function SQLRowCount returns this count.

For example:

SQLINTEGER rowcount;
. . .

/* Allocate statement handle */
SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt );
SQLExecDirect( hstmt,
               "UPDATE mimer_store.currencies \
                   SET exchange_rate = exchange_rate * 1.05 \
                   WHERE code = 'USD'", SQL_NTS );

SQLRowCount( hstmt, &rowcount );
printf( "%d rows have been updated\n", rowcount );


Repeating - Prepared Execution

Where an SQL statement will be repeatedly executed, it is more usual to use prepared execution, as a means to reduce the parsing and compilation overheads.

Mimer SQL versions 8.2 and later reduce the performance difference between direct and prepared execution by maintaining and re-using compiled statements on the server.

Prepared Statement Example

In this example each of the parameters in the prepared SQL statement (indicated by ?) are bound to a variable in the application before the statement is executed:

SQLFLOAT   increase;
SQLCHAR    code[4];
SQLINTEGER increaseInd, codeInd;
. . .

SQLPrepare( hstmt,
            "UPDATE mimer_store.currencies \
                SET exchange_rate = exchange_rate * ? \
                WHERE code = ?", SQL_NTS );

SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_DOUBLE,
                  SQL_REAL, 7, 0,
                  &increase, 0, &increaseInd );
SQLBindParameter( hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
                  SQL_CHAR, 4, 0,
                  code, sizeof(code), &codeInd );

/* Set parameter values and length/indicator */
increase = 1.05;
strcpy( code, "USD" );
codeInd = SQL_NTS;

SQLExecute( hstmt );
SQLRowCount( hstmt, &rowcount );
printf( "%d rows have been updated\n", rowcount );

Stored Procedure Example

Similarly, it is possible to prepare an SQL statement that calls a stored procedure:

SQLINTEGER  order_id, item_id;
SQLSMALLINT quantity;
SQLINTEGER  orderInd = 0, itemInd = 0, quantityInd = 0;
. . .

SQLPrepare( hstmt,
            "{CALL mimer_store.order_item( ?, ?, ? )}",
            SQL_NTS );

SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,
                  SQL_INTEGER, 0, 0,
                  &order_id, SQL_IS_INTEGER, &orderInd );
SQLBindParameter( hstmt, 2, SQL_PARAM_INPUT, SQL_C_SLONG,
                  SQL_INTEGER, 0, 0,
                  &item_id, SQL_IS_INTEGER, &itemInd );
SQLBindParameter( hstmt, 3, SQL_PARAM_INPUT, SQL_C_SSHORT,
                  SQL_INTEGER, 0, 0,
                  &quantity, SQL_IS_SMALLINT, &quantityInd );

/* Set parameter values */
order_id = 700001;
item_id = 60158;
quantity = 2;

SQLExecute( hstmt );

Parameters in Procedure Calls

Parameters in procedure calls can be input, input/output, or output. A more complicated example illustrates how to handle an output parameter:

SQLCHAR     country[3];
SQL_INTERVAL_STRUCT interval;
SQLINTEGER  countryInd, intervalInd;
SQLSMALLINT numparams;
. . .

SQLPrepare( hstmt,
            "{CALL mimer_store.age_of_adult( ?, ? )}",
            SQL_NTS );

SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
                  SQL_CHAR, 3, 0,
                  country, sizeof(country), &countryInd );
SQLBindParameter( hstmt, 2, SQL_PARAM_OUTPUT, SQL_C_INTERVAL_YEAR,
                  SQL_INTERVAL_YEAR, 0, 0,
                  &interval, sizeof(SQL_INTERVAL_STRUCT),
                  &intervalInd );

SQLNumParams( hstmt, &numparams );
printf( "statement contains %d parameters\n", numparams );

/* Set input parameter value and length */
strcpy( country, "US" );
countryInd = SQL_NTS;

SQLExecute( hstmt );
printf( "%d years\n", interval.intval.year_month.year );

A statement handle is released by calling SQLFreeHandle; however, it is more efficient to reuse statement handles rather than freeing them and allocate new ones. When SQLFreeHandle is called, the driver releases the associated structure. SQLDisconnect automatically frees all statements on a connection.


Result-set Processing

There are two ways of processing a result-set. One method uses SQLBindCol to bind applications variables to the columns of the result set. The second method of processing the result-set is to use SQLGetData.

Using SQLBindCOL

When each row of data is fetched, the column data is copied to the application variables. The following example also illustrates how to use the indicator variable; this either returns the length of character data (a negative length indicates that truncation has taken place), or SQL_NULL_DATA if the data is NULL:

SQLCHAR    code[4];
SQLCHAR    currency[33];
SQLINTEGER codeInd, currencyInd;
. . .
/* Allocate statement handle */
SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt );

SQLExecDirect( hstmt,
               "SELECT code, currency \
                   FROM mimer_store.currencies",
               SQL_NTS );

SQLBindCol( hstmt, 1, SQL_C_CHAR,
            code, sizeof(code), &codeInd );
SQLBindCol( hstmt, 2, SQL_C_CHAR,
            currency, sizeof(currency), &currencyInd );

while ((retcode = SQLFetch( hstmt )) != SQL_NO_DATA)
{
   printf( "%s  %s\n", code, currency );
}

/* Close the cursor */
SQLCloseCursor( hstmt );

Using SQLGetData

The second method of processing the result-set is to use SQLGetData; the equivalent of the previous example can be written:

SQLExecDirect( hstmt,
               "SELECT code, currency \
                   FROM mimer_store.currencies",
               SQL_NTS );

while ((retcode = SQLFetch( hstmt )) != SQL_NO_DATA)
{
   SQLGetData( hstmt, 1, SQL_C_CHAR,
               code, sizeof(code), &codeInd );
   SQLGetData( hstmt, 2, SQL_C_CHAR,
               currency, sizeof(currency), &currencyInd );

   printf( "%s  %s\n", code, currency );

Combining Result-set Processing Methods

SQLBindCol and SQLGetData can be combined. The previous two examples used forward-only cursors, which means that they only support fetching rows serially from the start to the end of the cursor.

In modern screen-based application, the user expects to be able to scroll backwards and forwards through the data. While it is possible to cache small result sets in memory on the client, this is not feasible when dealing with large result sets. Scrollable cursors provide the answer.

Scrollable Cursors

Scrollable cursors allow you to move forward and back to any row within the result-set. A statement attribute of SQL_SCROLLABLE specifies that the cursor be opened in scroll mode.

The function SQLFetchScroll supports fetching the next, prior, first and last rows, as well as absolute and relative positioning.

For example:

/* Allocate statement handle */
SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt );

/* Set cursor scrollable */
retcode = SQLSetStmtAttr( hstmt, SQL_ATTR_CURSOR_SCROLLABLE,
                          (SQLPOINTER)SQL_SCROLLABLE, 0 );
if (retcode == SQL_ERROR) goto error;

SQLExecDirect( hstmt,
               "SELECT code, currency \
                   FROM mimer_store.currencies \
                  WHERE code LIKE 'A%'",
               SQL_NTS );

SQLBindCol( hstmt, 1, SQL_C_CHAR,
            code, sizeof(code), &codeInd );
SQLBindCol( hstmt, 2, SQL_C_CHAR,
            currency, sizeof(currency), &currencyInd );

printf( "Original sort order\n" );
while ((SQLFetchScroll( hstmt, SQL_FETCH_NEXT, 0 )) != SQL_NO_DATA)
   printf( "%s  %s\n", code, currency );

printf( "\nReverse order\n" );
while ((SQLFetchScroll( hstmt, SQL_FETCH_RELATIVE, -1 ))!=SQL_NO_DATA)
   printf( "%s  %s\n", code, currency );

/* Close the cursor */
SQLCloseCursor( hstmt );


Updating Data

Applications can update data by executing the UPDATE, DELETE, and INSERT statements.

An alternative method is to position the cursor on a particular row and then use DELETE CURRENT, or UPDATE CURRENT statements.

The following example illustrates how this can be done by using two statement handles:

SQLHSTMT   cscroll, cupdate;
SQLCHAR    code[4];
SQLCHAR    currency[33];
SQLINTEGER codeInd, currencyInd;
. . .

/* Allocate statement handles */
SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &cscroll );
SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &cupdate );

/* Set scroll cursor attributes */
SQLSetStmtAttr( cscroll, SQL_ATTR_CURSOR_SCROLLABLE,
                (SQLPOINTER)SQL_SCROLLABLE, 0 );
SQLSetStmtAttr( cscroll, SQL_ATTR_CONCURRENCY,
                (SQLPOINTER)SQL_CONCUR_VALUES, 0 );

/* Name the cursor */
SQLSetCursorName( cscroll, "CRN", SQL_NTS );

SQLExecDirect( cscroll,
               "SELECT code, currency \
                   FROM mimer_store.currencies \
                   FOR UPDATE OF currency",
               SQL_NTS );

SQLBindCol( cscroll, 1, SQL_C_CHAR,
            code, sizeof(code), &codeInd );
SQLBindCol( cscroll, 2, SQL_C_CHAR,
            currency, sizeof(currency), &currencyInd );

/* Set the update cursor to use optimistic concurrency */
SQLSetStmtAttr( cupdate, SQL_ATTR_CONCURRENCY,
                (SQLPOINTER)SQL_CONCUR_VALUES, 0 );

/* Prepare the positioned update statement using scroll cursor name */
SQLPrepare( cupdate,
            "UPDATE mimer_store.currencies \
                SET currency = ? \
                WHERE CURRENT OF crn",
            SQL_NTS );

/* Bind the code parameter in the update statement */
SQLBindParameter( cupdate, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
                  SQL_CHAR, 33, 0,
                  currency, sizeof(currency), &currencyInd );

/* Position within the result-set on the scrolling cursor */
SQLFetchScroll( cscroll, SQL_FETCH_ABSOLUTE, 3 );
SQLSetPos( cscroll, 1, SQL_POSITION, SQL_LOCK_NO_CHANGE );

/* Update currency name using update statement handle */
if (strncmp( currency, "Leke", 4 ) == 0)
   strcpy( currency, "Albanian Leke" );
else
   strcpy( currency, "Leke" );
currencyInd = SQL_NTS;

SQLExecute( cupdate );


Mimer SQL and the JDBC API

JDBC is the de-facto standard for accessing relational database systems from the Java programming language. It defines a framework that provides a uniform interface to a number of different database connectivity modules.

Mimer SQL supports JDBC as one of its native application programming interfaces (API).

The Mimer JDBC Driver is a Type 4 - Native Protocol All-Java Driver, also known as a Java thin driver. The Type 4 architecture uses a message protocol that is specific to Mimer SQL; as this means that there is no need for any intervening processes or translation, this architecture is extremely efficient.

This chapter is not intended to be a complete guide to the functionality provided by JDBC. It is written to introduce you to accessing Mimer SQL through JDBC.

For a more detailed discussion of Mimer SQL, JDBC and other database APIs, please see: http://developer.mimer.com/interfaces


The Mimer JDBC Driver

The Mimer JDBC Driver is required in order to access a Mimer SQL database over
TCP/IP from a Java application.

You can use the Mimer JDBC driver on any client computer that has the Java runtime environment is installed.

Note: Only Mimer SQL database servers of version 8.2.2 or later support JDBC clients.

The driver is extremely small in size, so that it can be simply incorporated into an applet that can be downloaded over the Internet.

The Mimer JDBC driver is distributed with the Mimer SQL software. You can also download it from: http://developer.mimer.com/downloads. No other Mimer SQL software is required to be installed on the Java client, eliminating any need for configuration management on the client side.

To install Java, download the Java SDK. You can find the latest version:
http://java.sun.com/products/JDK/CurrentRelease

All further information about the Mimer JDBC drivers, including programming aspects, are found in the Mimer JDBC Driver Guide.


Embedded SQL

In previous chapters, we discussed the ODBC and JDBC APIs. This chapter discusses the scope, principles, processing and structure of embedded SQL (ESQL).

ESQL enables you to code SQL statements in a host program written in C/C++, COBOL or FORTRAN. You can specify SQL statements directly in the host program's source code. However, because the host language's compiler won't recognize the SQL statements as valid, a preprocessor is required.

The Scope of Embedded Mimer SQL

The following groups of SQL statements are common to ESQL and interactive SQL:

  • Data manipulation statements for reading or changing the content of the database and invoking stored routines. These are basically similar between interactive SQL and ESQL, but differ in certain details as a result of the different environments in which the statements are used.
  • Transaction control statements for grouping database operations in transactions (indivisible units of work).
  • Access control statements for allocating privileges and access rights to users of the system. These are identical between interactive SQL and ESQL.
  • Data definition statements for creating and altering objects in the database. These are identical between interactive SQL and ESQL.
  • Connection statements for identifying the current user of the system.
  • System administration statements for controlling the availability of the database and its physical components, managing backups and updating database statistics.

There are a number of commands provided for use with BSQL which are not included in the Mimer SQL interface, these are described in the Mimer SQL User's Manual, Mimer BSQL.

Note: In the Mimer SQL Reference Manual, Usage Modes, Mimer SQL statements are identified as valid for use in ESQL, for interactive use or both.


General Principles for Embedding SQL Statements

The following sections discuss host languages, preprocessors, identifying SQL statements, code, comments and recommendations.

Host Languages

You can embed Mimer SQL statements in application programs written in C/C++, COBOL or FORTRAN. The basic principles for writing ESQL programs are the same in all languages and all ESQL statements are embedded in the same way.

Information given in this manual applies to all languages unless otherwise explicitly stated. Language-specific information is detailed in Host Language Dependent Aspects.

ESQL Preprocessors

Because host language compilers do not recognize ESQL statements as valid, an ESQL preprocessor is required. An ESQL preprocessor processes the SQL statements embedded in a host language.

UNIX: Mimer SQL supports an ESQL preprocessor for the C/C++ host language on UNIX platforms. In addition FORTRAN is supported on Linux platforms.

VMS: Mimer SQL supports an ESQL preprocessor for the COBOL, FORTRAN and C/C++ host languages on OpenVMS platforms.

Win: Mimer SQL supports an ESQL preprocessor for the C/C++ host language on Windows platforms.

Identifying SQL Statements

SQL statements are included in the host language source code exactly as though they were ordinary host language statements (i.e. they follow the same rules of conditional execution, etc., which apply to the host language).

SQL statements are identified by the leading keywords EXEC SQL (in all host languages) and are terminated by a language-specific delimiter. Every separate SQL statement must be delimited in this way.

Blocks of several statements may not be written together within one set of delimiters. For instance, in COBOL, two consecutive DELETE statements must be written as:

EXEC SQL DELETE FROM countries END-EXEC.
EXEC SQL DELETE FROM producers END-EXEC.

and not

EXEC SQL DELETE FROM countries
    DELETE FROM producers END-EXEC.

Single SQL statements can however be split over several lines, following the host language rules for line continuation.

The following embedded statement is thus acceptable in a FORTRAN program (the continuation mark is a + in column 6 on the second line):

 EXEC SQL DELETE FROM countries
+    WHERE code = 'BA' END-EXEC.

The keywords EXEC SQL may not be split over more than one line.

Included Code

Any code which is included in the program by the host language compiler (as directed by host language INCLUDE statements) is not recognized by the ESQL preprocessor.

If external source code modules containing SQL statements are to be included in the program, the non-standard SQL INCLUDE statement must be used, for example:

EXEC SQL INCLUDE 'filename'

Files included in this way are physically integrated into the output from the preprocessor.

Comments

Comments may be written in the ESQL program according to the rules for writing comments in the host language. Thus comments may be written within an SQL statement if the host language accepts comments within host language statements.

The following statement is valid in C/C++:

exec sql DELETE FROM countries  /* Remove Bosnia and Herzegovina */
             WHERE code = 'BA';

Note:	The keywords EXEC and SQL may not be separated by a comment.

Recommendations

We recommend the following, when using ESQL:

  • Avoid variable names beginning with the letters SQL (except for SQLSTATE and SQLCODE, which should be used when appropriate).
  • Avoid subroutine or subprogram names ending with a number.

Language-specific restrictions are described in Host Language Dependent Aspects.


Processing ESQL

The following sections discuss preprocessing and processing ESQL.

Preprocessing - the ESQL Command

An application program containing ESQL statements must first be preprocessed using the ESQL command before it can be passed through the host language compiler, since the host language itself does not recognize the ESQL syntax.

Preprocessors are available for the host languages supported on each platform, see Host Languages.

The input to the preprocessor is thus a source code file containing host language statements and ESQL statements.

The output from the preprocessor is a source code file in the same host language, with the ESQL statements converted to source code data assignment statements and subroutine calls that pass the SQL statements to the Mimer SQL database manager.

The original ESQL statements are retained as comments in the output file, to help in understanding the program if a source code debugger is used.

The output from the preprocessor is human-readable source code, still retaining a large part of the structure and layout of the original program, which is used as input to the appropriate host language compiler to produce object code.

The default file extensions for preprocessor input and output files depend on the host language used and are shown in the table below:

Language
Input file extension
Output file extension

C

.ec

.c

COBOL

.eco

.cob

FORTRAN

.efo

.for

Invoking the ESQL Preprocessor

You invoke the ESQL preprocessor with the following command:

$ esql language [flagger] [options] infile [outfile]
Language

UNIX-style
VMS-style
Function
-c
--c

/C

Indicates that the input file is written using the C/C++ host language.

-b
--cobol

/COBOL

Indicates that the input file is written using the COBOL host language.

-f
--fortran

/FORTRAN

Indicates that the input file is written using the FORTRAN host language.

Flagger

UNIX-style
VMS-style
Function
-v level
--verify=level

/VERIFY=level

Ensures that all SQL statements which are beyond the bounds of the specified level of the SQL-92 standard will generate a preprocessor warning and will be flagged with a warning comment in the output file.

Possible level values are:
ENTRY
TRANSITIONAL
INTERMEDIATE
FULL

Options

UNIX-style
VMS-style
Function
-n
--nologo

/NOLOGO

Suppresses the display of the copyright message and input file name on the screen (warnings and errors are always displayed on the screen).

-l
--line

/LINE

Generates #line preprocessing directives for source written in the C language. These force the C compiler to produce diagnostic messages with line numbers relating to the input C source code rather than the code generated by the preprocessor (and thus compiled by the C compiler).

Input-file and Output-file

UNIX-style
VMS-style
Function
infile

infile

The input-file containing the source code to be preprocessed.

If no file extension is specified, the appropriate file extension for the source language is assumed (previously described in this section).

[outfile]

[outfile]

The output-file which will contain the compiler source code generated by the preprocessor.

If not specified, the output file will have the same name as the input file, but with the appropriate default output file extension (previously described in this section).

Note: As an application programmer, you should never attempt to directly modify the output from the preprocessor.

Any changes that may be required in a program should be introduced into the original ESQL source code. Mimer Information Technology AB cannot accept any responsibility for the consequences of modifications to the preprocessed code.

Example

The following example, on OpenVMS, shows how to preprocess the DSQLSAMP program:

$ ESQL/C MIMEXAMPLES9:DSQL

What Does the Preprocessor Do?

The preprocessor checks the syntax and to some extent the semantics of the ESQL statements, see Handling Errors and Exceptions for a more detailed discussion of how errors are handled). Syntactically invalid statements cannot be preprocessed and the source code must be corrected.

The preprocessor also places comments in the generated output to indicate the SQL2 standard conformance level for each SQL statement (entry-level, transitional, intermediate or full).

If a flagger option, see the table above, has been specified, then an SQL statement that is beyond the bounds of the specified conformance level is flagged with a warning comment in the preprocessor output and a warning on the screen. Mimer SQL extensions are always flagged with a warning comment and generate a preprocessor warning.

Processing ESQL - the Compiler

The output from the ESQL preprocessor is compiled in the usual way using the appropriate host language compiler, and linked with the appropriate routine libraries.

UNIX: On UNIX platforms, the vendors' common C compilers are supported. On Linux this is gcc.

VMS: The following compilers (all sold by Hewlett-Packard) are supported on the OpenVMS platform:

  • DEC C
  • DEC FORTRAN
  • DEC COBOL

    • Note: For COBOL, the source program must be formatted according to the ANSI rules. Use the /ANSI option when compiling the resulting COBOL program.

Win: On Windows platforms, the C compiler identified by the cc symbol in the file .\dev\makefile.mak below the installation directory is supported.

Note: Other compilers, from other software distributors, may or may not be able to compile the ESQL preprocessor output. Mimer Information Technology cannot guarantee the result of using a compiler that is not supported.

The SQL Compiler

At run-time, database management requests are passed to the SQL compiler responsible for implementing the SQL functions in the application program.

The SQL compiler performs two functions:

  • It checks SQL statements semantically against the data dictionary.
  • It optimizes operations performed against the database (i.e. internal routines determine the most efficient way to execute the SQL request, with regard to the existence of secondary indexes and the number of rows in the tables addressed by the statement). You, as a programmer, do not need to worry, for instance, about the order in which tables are addressed in a complex selection condition. This optimization process is completely transparent.

    • Note: Since all SQL statements are compiled at run-time, there can be no conflict between the state of the database at the times of compilation and execution. Moreover, the execution of SQL statements is always optimized with reference to the current state of the database.


Essential Program Structure

All application programs using embedded Mimer SQL must include certain basic components, summarized below in the order in which they appear in a program.

1 Host Variable Declarations

Any host variables used in SQL statements must be declared inside a so-called SQL DECLARE SECTION. This is described in more detail in Communicating with the Application Program.

2 The Status Information Variable: SQLSTATE

The status information variable SQLSTATE, if used, must be declared inside the SQL DECLARE SECTION. This variable provides the application with status information for the most recently executed SQL statement.

3 Executable SQL Statements

This is the body of the program, and performs the required operations on the database. Normally, these begin with connecting to Mimer SQL and performing the required transactions before finally disconnecting from Mimer SQL.

Summary of Functions for Manipulating Data

The following table summarizes the functions for data manipulation in interactive SQL and ESQL.

Operation
Interactive SQL
ESQL

Retrieve data

SELECT generates a result table directly.

SELECT is used to declare a cursor. The cursor must be opened and positioned. Data is retrieved into host variables one row at a time with FETCH.
Alternative: SELECT INTO retrieves a single-row result set directly into host variables.

Update data

UPDATE operates on a set of rows or columns.

UPDATE operates on a set of rows or columns.
UPDATE CURRENT operates on a single row through a cursor.

Insert data

INSERT inserts one or many rows at a time.

INSERT inserts one or many rows at a time.

Delete data

DELETE operates on a set of rows.

DELETE operates on a set of rows.
DELETE CURRENT operates on a single row through a cursor.

Invoke routine

CALL is used to execute all stored procedures, i.e. both result set and non-result set procedures are handled the same way.
Functions can be specified where an expression could be used and are invoked when an expression used in the same context would be evaluated.

Result set procedures are called by using the CALL clause in a cursor declaration and then using FETCH.
The CALL statement is used directly for non-result set procedures.
Functions can be specified where an expression could be used and are invoked when an expression used in the same context would be evaluated.

Many SQL statements (e.g. data definition statements) are simply embedded in their logical place in the application program and are executed without direct reference to other parts of the program.

Some features of ESQL however require special consideration, and are dealt with in detail in the chapters that follow:

  • Access authorization through the use of user and program idents.
  • Data manipulation statements which require the use of cursors (FETCH, UPDATE CURRENT, DELETE CURRENT). These together with cursor handling statements are probably the most commonly used statements in ESQL.
  • Transaction control, which is essential for a consistent database.
  • Dynamic SQL, which is a special set of statements allowing an application program to process SQL statements entered by the user at run-time.
  • Exception handling, which controls the action taken when, for instance, the end of a result set is reached.


Linking Applications

UNIX: The makefile, called ex_makefile, found in the installation examples directory, provides a verified example of the recommended way to build applications on UNIX platforms.

Applications built using the procedure contained in this makefile will reference the Mimer SQL shared library called libmimer.

VMS: All Mimer SQL applications should be linked with the options file MIMER.OPT, as shown in the following example:

$ LINK main,MIMLIB9:MIMER/OPT

The MIMER.OPT file includes the following:

  • MIMLIB9:LR.OLB (object library)
  • MIMLIB9:MDR.OLB (object library)
  • MIMLIB9:MIMDB9.EXE (shareable library)

If an image linked in this fashion is activated, it will translate the logical name MIMDB9 to get the name of the Mimer SQL shareable library to be used.

The logical name will have been defined by the MIMSETUP9 command procedure.

Win: The example makefile .\dev\makefile.mak in the installation directory should be copied and used in the recommended way to build applications on Windows platforms.

If applications are linked as recommended above to reference the Mimer SQL shared library, they will automatically use a new version of Mimer SQL when it is installed, without having to be re-linked.


Idents and Privileges

This chapter discusses Mimer SQL idents, privileges and database connections.


Mimer SQL Idents

An ident is an authorization-ID used to identify different kinds of users in a Mimer SQL database.

There are four kinds of ident in Mimer SQL: USER, OS_USER, PROGRAM and GROUP.

Idents connect to a database through the CONNECT statement and the ENTER statement is used to take up the privileges provided by a PROGRAM ident (see below).

Every USER and PROGRAM ident has a unique ident name and a private password which must be correctly supplied to the CONNECT or ENTER statement in application programs.

An OS_USER ident may access the database without explicitly providing a user name or password on condition that the user name for the user currently logged in to the operating system correspond to the definition of the OS_USER in the Mimer SQL database.

USER

USER idents are authorized to connect to a Mimer SQL database, by using the CONNECT statement in an application program or by entering the correct ident name and password in an interactive environment.

Any privileges a USER ident holds may be exercised once the ident has logged on. USER idents are generally associated with specific physical individuals authorized to connect to the database.

OS_USER

OS_USER is an ident type that allows the user currently logged in to the operating system to connect to a Mimer SQL database without providing a username or password.

If an OS_USER ident is defined with a password in Mimer SQL, the ident may connect to Mimer SQL in the same way as any other USER ident (i.e. by providing a user name and password). An OS_USER ident is subject to the same access restrictions as any other USER ident.

PROGRAM

PROGRAM idents provide specific privileges required when executing certain operations. PROGRAM idents may not initiate a connection to a Mimer SQL database, but may be entered from within an application program or interactive environment by using the ENTER statement.

A connection should have been established before the ENTER statement is used, see Connecting to a Database. Entering a PROGRAM ident is analogous to logging on as a USER ident, in that the PROGRAM ident gains access to the system and any privileges the ident holds become applicable.

PROGRAM idents are generally associated with specific functions within the system, not with physical individuals.

GROUP

GROUP idents are collective identities that provide common privileges for groups of users 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 one group can include any number of members.

GROUP idents provide a facility for organizing the privilege structure in the database system.


Database Privileges

The access of each ident to the database is defined by privileges granted within the system.

The privileges are grouped as follows:

  • System privileges
  • Object privileges
  • Access privileges.

System Privileges

Privilege
Explanation
BACKUP

gives the right to perform databank backup and restore operations.

DATABANK

gives the right to create databanks.

IDENT

gives the right to create idents.

SCHEMA

gives the right to create schemas.

SHADOW

gives the right to create and manage databank shadows.

STATISTICS

gives the right to execute the UPDATE STATISTICS statement.

Object Privileges

Privilege
Explanation
TABLE

gives the right to create tables in a specified databank.

SEQUENCE

gives the right to create sequences in a specified databank.

EXECUTE

gives the right to access a routine or to enter (connect to) a specified PROGRAM ident.

MEMBER

grants membership in a specified GROUP ident.

USAGE

gives 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 or collation.

Access Privileges

Privilege
Explanation
SELECT

gives the right to read the table contents.

INSERT

gives the right to add new rows to the table.

DELETE

gives the right to remove rows from the table.

UPDATE

gives the right to update the contents of the table.

REFERENCES

gives the right to use the primary key or unique keys of the table as a foreign key from another table.

About Privileges

System privileges are automatically granted to the system administrator at installation, and may be passed on to other idents.

Object and access privileges are initially granted only to the creator of an object. The creator may however grant the privileges on to other idents.

All privileges may be granted with or without GRANT OPTION, which controls the right of the receiving ident to grant the privilege on to another ident.

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. Similarly, privileges may only be revoked by their grantor.


Connecting to a Database

A database in Mimer SQL refers to the complete collection of databanks that may be accessed from one Mimer SQL system.

Mimer ESQL supports the ability to change between different connections (i.e. access different databases) from within the same application program. An application program may have several database connections open simultaneously, although only one is active at any one time.

Only idents of type USER or OS_USER are allowed to log on to Mimer SQL.


The CONNECT Statement

Logging on is requested from an application program with the CONNECT statement, see the Mimer SQL Reference Manual, CONNECT, for the syntax description.

The CONNECT statement establishes a connection between a user and a database. The user may be a USER ident (in which case the password must be provided) or an OS_USER (if such an ident has been created in the Mimer SQL database). To connect as the OS_USER ident with the same name as the current operating system user, provide an empty ident name string.

Example
EXEC SQL CONNECT TO 'db' AS 'con1' USER 'ident' USING 'pswd';
Local and Remote Databases

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, Creating a Mimer SQL Database, for details, by specifying the database by name or by using the keyword DEFAULT.

Default or Named Database

If the keyword DEFAULT is used, the OS_USER ident is used for the connection attempt. If the database name is given as an empty string, the DEFAULT database is used.

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

Implicit Connection

Normally, CONNECT should be the first executable SQL statement in an application program using ESQL. However, if an executable statement is issued before any connection has been established in the current application, an implicit connection will be attempted.

An implicit connection is made to the DEFAULT database using the current operating system user.

In order for the connect attempt to be successful, the current operating system user must be defined as an OS_USER ident in Mimer SQL and the DEFAULT database must be defined as a local database on the machine on which the current operating system user is defined.

If an implicit connection has previously been established in the application and there is no current connection, issuing an executable statement will result in a new attempt to make the same implicit connection. However, if an explicit connection has previously been established in the application and there is no current connection, issuing an executable statement will cause an error.

Changing Connection

A connection established by a successful CONNECT statement is automatically active.

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.

Only the most recent connection is active. Other connections are dormant, and may be made active by the SET CONNECTION statement. Resources such as cursors used by a connection are saved when the connection becomes dormant, and are restored by the appropriate SET CONNECTION statement.

The statement sequence below connects to a user-specific database as a specified ident name and to the DEFAULT database as OS_USER. The user-specific connection is initially active. Then the DEFAULT connection is activated. Finally the user-specific connection is activated again using SET CONNECTION.

EXEC SQL CONNECT TO 'db' AS 'con1' USER 'ident' USING 'pswd';
...
EXEC SQL CONNECT TO DEFAULT;
...
-- Set activate connection to CON1
EXEC SQL SET CONNECTION 'con1';

Note:	If different connections are made with different idents, the apparent access rights of the application program may change when the current connection is changed.

Disconnecting

The DISCONNECT statement breaks the connection between a user and a database and frees all resources allocated to that user for the specified connection (all cursors are closed and all compiled statements are dropped). The connection to be broken is specified as the connection name or as one of the keywords ALL, CURRENT or DEFAULT. (If a transaction is active when the DISCONNECT is executed, an error is raised and the connection remains open).

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 the 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 does not exist

con1 disconnected

con1 disconnected

SET CONNECTION con1

error - connection does not 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 application program by using the ENTER statement, see the Mimer SQL Reference Manual, ENTER for the syntax description. This statement must be issued in a context where a user is already connected as PROGRAM idents cannot connect directly to the system.

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. However, any cursors opened by the previous ident remain open.

PROGRAM idents are disconnected with the LEAVE statement. If LEAVE is requested with the optional keyword RETAIN, the full environment of the PROGRAM ident being left is kept.

Cursors left open by the PROGRAM ident are deactivated but not closed, and retain their positions in the respective result tables. The environment is restored if the PROGRAM ident is re-entered.

If LEAVE is requested without RETAIN, the environment of the PROGRAM ident being left is dropped. This means that all cursors and compiled statements are destroyed.

Note: The distinction between leaving a PROGRAM ident with the option RETAIN and entering a new PROGRAM ident is, while both operations save the environment of the PROGRAM ident, cursors left open at ENTER may still be used but those left open at LEAVE RETAIN are inaccessible until the program ident is re-entered.

The statements ENTER and LEAVE may not be issued within transactions, see Transaction Handling and Database Security.


Communicating with the Application Program

Information is transferred between the embedded SQL (ESQL) application program and the Mimer SQL database manager in four ways:

  • through host variables used in SQL statements
  • through the status variable SQLSTATE
  • through the diagnostics area, accessed by the SQL statement GET DIAGNOSTICS
  • through an SQL descriptor area (dynamic area).


Using Host Variables

Host variables are used in SQL statements to pass values between the database and the application program.

Declaring Host Variables

All variables used in SQL statements must be declared for the preprocessor, by enclosing the variable declarations between the SQL statements BEGIN DECLARE SECTION and END DECLARE SECTION.

Any variables declared outside the SQL DECLARE SECTION will not be recognized by the preprocessor.

Variables are declared within the section using the normal host language syntax.

For instance, the following example in C declares only the character variables user and passw for use in SQL statements:

int rc, pf, cnt;

exec sql BEGIN DECLARE SECTION;
   char user[129],
        passw[19];
exec sql END DECLARE SECTION;

Variables which are not used in SQL statements may also be declared in the SQL DECLARE SECTION. This will however extend the symbol table established by the preprocessor more than is necessary.

The use of array variables is currently not supported in embedded Mimer SQL (except for character string variables).

Using Variables in Statements

Host variables may be used:

  • to receive information from the database (SELECT INTO, FETCH, CALL and SET statements)
  • to assign values to columns in the database (CALL, INSERT and UPDATE statements)
  • to manipulate information taken from the database or contained in other variables (in expressions)
  • to get descriptor and diagnostics information (GET DESCRIPTOR, SET DESCRIPTOR and GET DIAGNOSTICS)
  • in dynamic SQL statements.

In all these contexts, the data type of the host variable or database column must be compatible with the data type of the corresponding database value or host variable. General considerations of data type compatibility may be found in the Mimer SQL Reference Manual. Host language specific aspects are described in Host Language Dependent Aspects of this manual.

If you have an INTEGER column containing values that do not fit into the largest integer variable allowed on your machine (remember that Mimer SQL supports INTEGER values with a precision of up to 45 digits), you can, for example, use a character string or float host variable for that column. In this case, Mimer SQL automatically performs the necessary conversions.

Host variables are preceded by a colon when used in SQL statements, see the Mimer SQL Reference Manual, Host Identifiers.

Note: The colon is not part of the host variable, and should not be used when the variable is referenced in host language statements.

Example:
EXEC SQL SELECT column
            INTO :VAR
            FROM table
            WHERE condition;

if VAR < LIMIT then ...

Indicator Variables

In ESQL, indicator variables associated with main variables are used to handle NULL values in database tables.

Indicator variables should be an exact numeric data type with scale zero and are declared in the same way as main variables in the SQL DECLARE SECTION.

See Declarations for a description of how main and indicator variables should be declared in the specific host languages.

Indicator variables are used in SQL statements by either specifying the name of the indicator variable, preceded by a colon, after the main variable name or by using the keyword INDICATOR, for example:

:main_variable :indicator_variable

or

:main_variable INDICATOR :indicator_variable
Transfer from Tables to Host Variables

When a NULL value is retrieved into a host variable by a FETCH, SELECT INTO, EXECUTE or CALL statement, the value of the main variable is undefined and the value of the indicator variable is set to -1.

An error occurs if the main variable is not associated with an indicator variable in the SQL statement. It is therefore recommended as a precaution that indicator variables are used for all columns which are not defined as NOT NULL in the database.

An indicator variable should always be used when a host variable is used for a routine parameter with mode OUT or INOUT because a NULL value can always be returned via a routine parameter.

When a non-null value is assigned to a main variable associated with an indicator variable, the indicator variable is set to zero or a positive value. A positive value indicates that the value assigned to a main character variable was truncated, and gives the length of the original value before truncation.

Transfer from Host Variables to Tables

When the host variable associated with an indicator variable is used to assign a value to a column, the value assigned is NULL if the value of the indicator variable is set to -1.

In such a case, the value of the main variable is irrelevant. If the indicator variable has a value of zero or a positive value, or if the main variable is not associated with an indicator variable, the value of the main variable itself is assigned to the column.


The SQLSTATE Variable

The SQLSTATE variable provides the application, in a standardized way, with return code information about the most recently executed SQL statement.

SQLSTATE must be declared between the BEGIN DECLARE SECTION and the END DECLARE SECTION (i.e. in the SQL declare section), as a 5 character long string (excluding any terminating null byte).

The return codes provided by SQLSTATE can contain digits and capital letters.

SQLSTATE consists of two fields. The first two characters of SQLSTATE indicates a class, and the following three characters indicates a subclass. Class codes are unique, but subclass codes are not. The meaning of a subclass code depends on the associated class code.

To determine the category of the result of an SQL statement, the application can test the class of SQLSTATE according to the following:

SQLSTATE Class
Result category

00

Success

01

Success with warning

02

No data

Other

Error

For a list of SQLSTATE values, see Return Codes.


The Diagnostics Area

The diagnostics area holds status information for the most recently executed SQL statement.

There is always one diagnostics area for an application, no matter how many connections the application holds.

Information from the diagnostics area is selected and retrieved by the GET DIAGNOSTICS statement. The syntax for GET DIAGNOSTICS (including a description of the diagnostics area) is in the Mimer SQL Reference Manual.

The GET DIAGNOSTICS statement does not change the contents of the diagnostics area, although it does set SQLSTATE.


The SQL Descriptor Area

An SQL descriptor area is used to hold data and descriptive information required for execution of dynamic SQL statements. SQL descriptor areas are allocated and maintained by ESQL statements, described in the Mimer SQL Reference Manual.

The SQL descriptor area is discussed in detail in SQL Descriptor Area.


Accessing Data

This chapter explains how embedded SQL applications retrieve data.


Retrieving Data Using Cursors

Data is retrieved from database tables with the FETCH statement, which fetches the values from an individual row in a result set into host variables.

The result set is defined by a SELECT construction or a result set procedure CALL, see Manipulating Data, used in a cursor declaration. A cursor may be thought of as a pointer which moves through the rows of the result set as successive FETCH statements are issued.

An exception is raised to indicate when the FETCH has reached the end of the result set.

Data retrieval involves several steps in the application program code, which are as follows:

  • declaration of host variables to hold data
  • declaration of a cursor with the appropriate SELECT conditions or result set procedure CALL
  • opening the cursor
  • performing the FETCH
  • closing the cursor.

General Framework

The steps in the previous section are built into the application program as shown in the general frameworks below (only SQL statements are shown in the frameworks).

For a SELECT:

EXEC SQL BEGIN DECLARE SECTION;
   ... VAR1, VAR2, ... VARn ...
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE cursor-name CURSOR FOR select-statement;

EXEC SQL OPEN cursor-name;

loop as required
   EXEC SQL FETCH cursor-name
                INTO :VAR1, :VAR2, ..., :VARn;
end loop;

EXEC SQL CLOSE cursor-name;

For a result set procedure CALL:

EXEC SQL BEGIN DECLARE SECTION;
   ... VAR1, VAR2, ... VARn ...
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE cursor-name CURSOR FOR CALL routine-invocation;

EXEC SQL OPEN cursor-name;

loop as required
    EXEC SQL FETCH cursor-name
                 INTO :VAR1, :VAR2, ..., :VARn;
end loop;

EXEC SQL CLOSE cursor-name;

Declaring Host Variables

All host variables used to hold data fetched from the database and used in selection conditions or as result set procedure parameters must be declared within an SQL DECLARE SECTION, see Communicating with the Application Program.

Indicator variables for columns that may contain NULL values must also be declared.

The same indicator variable may be associated with different main variables at different times, but declaration of a dedicated indicator variable for each main variable is recommended for clarity.

Declaring the Cursor

A cursor operates as a row pointer associated with a result set.

A cursor is defined by the DECLARE CURSOR statement and the set of rows addressed by the cursor is defined by the SELECT statement in the cursor declaration.

Cursors are local to the program in which they are declared. A cursor is given an identifying name when it is declared.

DECLARE CURSOR is a declarative statement that does not result in any implicit connection to a database, see Idents and Privileges for details on connecting to a database.

Preprocessing the statement generates a series of parameters used by the SQL compiler but does not generate any executable code; the query-expression or result set procedure call in the cursor declaration is not executed until the cursor is opened.

Holdable cursors can be declared using the WITH HOLD clause. An open cursor declared WITH HOLD remain open after COMMIT.

Cursors should normally be declared WITHOUT HOLD (default), because WITH HOLD cursors require more internal resources then ordinary cursors. In addition, long lasting WITH HOLD cursors can have negative performance effects just like long lasting transactions.

If the cursor declaration contains a CALL to a result set procedure, it is FETCH that actually executes the procedure.

The RETURN statement is used from within the result set procedure to return a row of the result set.

Each FETCH causes statements in the result set procedure to execute until a RETURN statement is executed, which will return the row data defined by it. Execution of the procedure is suspended at that point until the next FETCH.

If, during execution, the end of the procedure is encountered instead of a RETURN statement, the FETCH result is end-of-set. Result Set Procedures for a detailed description of result set procedures.

Note: It is advisable always to use an explicit list of items in the SELECT statement of the cursor declaration. The shorthand notations SELECT * and SELECT table.* are useful in interactive SQL, but can cause conflicts in the variable lists of FETCH statements if the table definition is changed.

Host Variables

The cursor declaration can use host variables in the WHERE or HAVING clause of the SELECT statement.

The result set addressed by the cursor is then determined by the values of these host variables at the time when the cursor is opened.

The same cursor declaration can thus address different result sets depending on when the cursor is opened, for example:

EXEC SQL DECLARE C1 CURSOR..; -- cursor with host variables
SET VARIABLES
EXEC SQL OPEN C1;             -- open one result set
...
EXEC SQL CLOSE C1;
SET VARIABLES
EXEC SQL OPEN C1;             -- open different result set

Scrollable cursors can be declared using the SCROLL keyword. When a cursor is declared as scrollable, records can be fetched using an orientation specification. This makes it possible to scroll through the result set with the cursor.

Cursors which are to be used only for retrieving data may be declared with a FOR READ ONLY clause in the SELECT statement. This can improve performance slightly in comparison with cursors that permit update and delete operations.

Opening the Cursor

A declared cursor must be opened with the OPEN statement before data can be retrieved from the database. The OPEN statement evaluates the cursor declaration in terms of

  • the privileges the current user holds on any tables and views accessed by the cursor
  • the values of any host variables used in the SELECT clause
  • for a cursor calling a result set procedure, whether the current user has the required EXECUTE privilege on the procedure and also the values of any IN parameters

When the OPEN statement has been executed, the cursor is positioned before the first row in the result set.

Retrieving Data

Once a cursor has been opened, data may be retrieved from the result set with FETCH statements, see the Mimer SQL Reference Manual, FETCH, for the syntax description.

Host variables in the variable list correspond in order to the column names specified in the SELECT clause of the cursor declaration. The number of variables in the FETCH statement may not be more than the number of columns selected. The number of variables may be less than the number of columns selected, but a success with warning -code is then returned in SQLSTATE.

A suitably declared record structure may be used in place of a variable list in host languages where this is supported, see Host Language Dependent Aspects.

Each FETCH statement moves the cursor to the specified row in the result set before retrieving data. In strict relational algebra, the ordering of tuples in a relation (the formal equivalent of rows in a table) is undefined. The SELECT statement in the cursor declaration may include an ORDER BY clause if the ordering of rows in the result set is important to the application.

Note: A cursor declared with an ORDER BY clause cannot be used for updating table contents.

If no ORDER BY clause is specified, the ordering of rows in the result set is unpredictable.

Note: The variables into which data is fetched are specified in the FETCH statement, not in the cursor declaration. In other words, data from different rows in the result set may be fetched into different variables.

When there are no more rows to fetch, the exception condition NOT FOUND will be raised.

The following construction thus fetches rows successively until the result set is exhausted:

EXEC SQL DECLARE C1 CURSOR FOR select-statement;
EXEC SQL OPEN C1;

EXEC SQL WHENEVER NOT FOUND GOTO done;
LOOP
    EXEC SQL FETCH C1 INTO :var1,:var2,...,:varn;
END LOOP

done:
EXEC SQL CLOSE C1;
Access Rights

The access rights for a user are checked when the cursor is opened and they remain unchanged for that cursor until the cursor is closed.

For example, if an application program declares and opens a cursor, then SELECT access on the table is revoked from the user running the program, data can still be fetched from the result set as long as the cursor remains open. Any subsequent attempt to open the same cursor will, however, fail.

Block Fetching

The Embedded SQL interface tries whenever possible to fetch rows in blocks to minimize server communications. The first fetch would normally issue a request to the server for a number of rows at once. In most situations, this will improve application performance.

In some situations, this is not the desired behavior. One such situation is queries searching through a huge number of rows without the help of indexes. The database server may only be able to return one row a second, and the entire query may take minutes, but the user is happy as long as he sees the first rows on screen. If this is important to the application, set the fetch size manually. An appropriate fetch size is the number of rows displayed at once. See Mimer SQL Reference Manual, SET SESSION FETCH SIZE for more information.

Closing a Cursor

An opened cursor remains open until it is closed with one of the statements CLOSE, COMMIT, ROLLBACK or DISCONNECT. CLOSE closes the specified cursor. ROLLBACK and DISCONNECT close all open cursors for the connection. COMMIT closes all open cursors, except cursors declared WITH HOLD, for the connection. Once a cursor is closed, the result set is no longer accessible. The cursor declaration remains valid, however, and a new cursor may be opened with the same declaration.

Note: The result set addressed by the new cursor may not be the same if the contents of the database or the values of variables used in the declaration have changed.

Normally, resources used by the cursor remain allocated when the cursor is closed and will be used again if the cursor is re-opened. The optional form CLOSE cursor-name RELEASE deallocates cursor resources. Use of CLOSE with the RELEASE option is recommended in application programs which open a large number of cursors, particularly where system resources are limited.

Note: The use of CLOSE with the RELEASE option may slow down performance if there is a following OPEN, since it requires that new resources are allocated at the next OPEN for that cursor. For this reason it should only be used when necessary.

Cursors are local to a connection and remain open but dormant when the connection is made dormant. The state of dormant cursors is fully restored (including result set addressed and position in the result set) when the connection is reactivated. Cursors are, however, closed and cursor resources are deallocated, when a connection is disconnected.

Note: Cursors opened in a program ident context are closed and resources deallocated when LEAVE is executed within the same connection, unless LEAVE RETAIN is specified.


Retrieving Single Rows

If the result of a SELECT statement is known to be a single row, the SELECT INTO statement may be used as an alternative to fetching data through a cursor.

This is a much simpler programming construction, since cursors are not used and the only requirement is that host variables used in the SELECT INTO statement are declared in the DECLARE SECTION.

However, there are two disadvantages associated with SELECT INTO:

  • An error occurs if the result set addressed by the search condition contains more than one row. In other words, SELECT INTO can only be reliably used when there is no possibility of a multi-row result set (essentially when the search condition includes the columns that form a UNIQUE or PRIMARY KEY column or returns just the result of a set function, e.g. COUNT(*)).
  • Execution of the SELECT INTO statement involves a check that the result set contains one single row, which may incur unnecessary overhead. Even if it is known that the result row is unique, a single FETCH operation through a cursor may be a more efficient implementation.

Use of a SELECT INTO statement is justified when the result set may contain several rows, but it is a condition for continued execution of the application program that the result row is unique. With a cursor, this would require a construction that checked that one and only one FETCH operation could be performed (alternatively, use a separate SELECT COUNT with the same search condition as the cursor). In such a case, a SELECT INTO statement with a check on the return code, see Handling Errors and Exceptions, is probably the preferred solution.

A CALL statement can be used to return information to the one or more host variables associated with the output parameter(s) of the procedure.

A SET statement can be used with a function invocation to return information to one host variable.


Retrieving Data from Multiple Tables

Data can be retrieved from multiple tables in ESQL by addressing several tables in the SELECT statement of the cursor declaration, in the same way as in interactive SQL. The preprocessor generates a SELECT statement addressing multiple tables, which is optimized by the SQL compiler when the cursor is opened.

For example:
EXEC SQL DECLARE c_1 CURSOR FOR SELECT ...
                                   FROM a, b
                                   WHERE a.x = b.y;
EXEC SQL OPEN c_1;
...

An alternative way to link information between tables could be to define the search condition for one cursor in terms of a variable fetched through another cursor:

EXEC SQL DECLARE c_1 CURSOR FOR SELECT x
                                   FROM a;
EXEC SQL DECLARE c_2 CURSOR FOR SELECT ...
                                   FROM b
                                   WHERE y = :HOSTX;

EXEC SQL OPEN c_1;
EXEC SQL FETCH c_1
            INTO :HOSTX;
EXEC SQL CLOSE c_1;

EXEC SQL OPEN c_2;
EXEC SQL FETCH c_2;
...

When considering the two alternatives, the first one is preferred. The reason for this is:

  • The SQL optimizer gets the full information about the query that it is supposed to return a result set for. In this way the optimizer can make more use of statistical information and it can thereby optimize the query to execute in a more efficient way.
  • The application will require less resources in the form of open cursors.
  • If the application is run in a client/server environment, the second alternative will cause more communication over the network, since it will send data over the net which is only used to determine which data from the second cursor that will be selected and is of no real interest to the application.
  • The application will be more compact as well as easier to understand and maintain.

The 'Parts explosion' Problem

A special case of data retrieval from multiple tables is the use of stacked cursors to fetch data from logical copies of the same table, in a manner that provides a solution to the so called "Parts explosion" problem.

A cursor can be defined as REOPENABLE and the same cursor may be opened several times in succession in the same application program, each previous instance of the cursor being saved on a stack and restored when the following instance is closed. A FETCH statement refers to the most recently opened instance of a cursor. Each instance of the cursor addresses an independent result set and the position of each cursor in its own result set is saved on the stack.

Note: Result sets addressed by different instances of a cursor may differ according to the conditions prevailing when the cursor instance was opened.

The state of the cursor stack needs to be controlled by the application. A counter can be used to indicate if there are more instances of the cursor remaining on the stack. See the example that follows.

Stacked cursors are typically used in application programs which traverse a tree structure stored in the database.

For example (this is a simplified variant of the "parts explosion" problem), traverse a tree structure and print out the leaf nodes:

procedure TRAVERSE;
integer CSTACK, LASTC;
EXEC SQL BEGIN DECLARE SECTION;
   integer PARENT, CHILD;
   string SQLSTATE(5);
EXEC SQL END DECLARE SECTION;
begin
   EXEC SQL DECLARE c_tree REOPENABLE CURSOR FOR
                           SELECT parent, child
                              FROM tree
                              WHERE parent = :PARENT;

   CSTACK := 1;
   LASTC := 1;
   PARENT := 1;                      -- Start at root node

   EXEC SQL OPEN CTREE;

   loop
      EXEC SQL FETCH c_tree
                  INTO :PARENT, :CHILD;

      if SQLSTATE = "02000" then     -- No more children
         EXEC SQL CLOSE c_tree;      -- Pop the parent
         CSTACK := CSTACK - 1;
         exit when CSTACK = 0;
         if CSTACK >= LASTC then
            print(PARENT);           -- Write leaf node
         end if;
         LASTC := CSTACK;
      else                           -- Step to next level
         PARENT := CHILD;
         EXEC SQL OPEN c_tree;       -- Stack the current parent
                                        and open new level
         CSTACK := CSTACK + 1;
      end if;
   end loop;
end TRAVERSE;

The counters CSTACK and LASTC keep track of the number of stacked cursor levels and the latest level in the tree hierarchy respectively.


Entering Data into Tables

The following sections explain how to perform cursor-independent operations and update and delete using cursors.

Cursor-independent Operations

The SQL statements CALL, INSERT, DELETE and UPDATE, as well user-defined function invocations, embedded in application programs operate on a set of rows in a table or view in exactly the same way as in interactive SQL.

Host variables may be used in the statements to supply values or set search conditions. Host variables may be used as routine parameters.

Examples:

EXEC SQL INSERT INTO mimer_store.items(item_id,
                                       product_id, format_id,
                                       release_date,
                                       price, stock, reorder_level,
                                       ean_code,
                                       producer_id)
                  VALUES (CURRENT_VALUE OF mimer_store.item_id_seq,
                          :product_id, :format_id,
                          mimer_store.cast_to_date(:book_release_date),
                          :book_price, :book_stock, :book_reorder_level,
                          (:ean * 10) + mimer_store.ean_check_digit(:ean),
                          producer_id);

From the standpoint of the application program, each statement is a single indivisible operation, regardless of how many columns and rows are affected.

Updating and Deleting Through Cursors

The UPDATE CURRENT and DELETE CURRENT statements, see the Mimer SQL Reference Manual, SQL Statement Descriptions, for the syntax description, allow update and delete operations respectively, to be controlled on a row-by-row basis from an application program. These statements operate through cursors, which are declared and opened as described above for FETCH.

These statements operate on the current row of the cursor referenced in the statement. If there is no current row, e.g. the cursor has been opened but not yet positioned with a FETCH statement, an error is raised.

UPDATE CURRENT changes the content of the current row according to the SET clause in the statement, but does not change the position of the cursor. Two consecutive UPDATE CURRENT statements will therefore update the same row twice.

DELETE CURRENT deletes the current row and does not move the cursor; after a DELETE CURRENT statement, the cursor is positioned between rows and there is no current row. The cursor must be moved to the next row with a FETCH statement before any other operation can be performed through the cursor.

For both UPDATE CURRENT and DELETE CURRENT statements, the table name as used in the statement must be exactly the same as the table name addressed in the cursor declaration. The cursor must also address an updatable result set.

If a FOR UPDATE OF clause is used to specify which fetched columns may be updated, only the columns specified may appear in the corresponding UPDATE statement.

UPDATE CURRENT and DELETE CURRENT changes for a particular cursor can be divided into several transactions if the cursor is a holdable cursor. A cursor declared WITH HOLD remains open when transactions are committed, which makes it possible to use the same cursor for fetch and update of additional rows after COMMIT. However, each row must still be fetched and updated (or deleted) in the same transaction.

Cursors are not updatable if the data retrieval statement in the cursor declaration contains any of the following features at the top level (i.e. not in a subselect) of the statement:

  • reference to more than one table in the FROM clause
  • reference to a read-only view in the FROM clause
  • the keyword DISTINCT
  • set-functions in the SELECT list (AVG, COUNT, MAX, MIN, SUM)
  • arithmetic or string concatenation expressions in the SELECT list
  • a GROUP BY clause
  • an ORDER BY clause
  • the UNION keyword
  • the result set of an explicit inner or outer JOIN
  • a CALL to a result set procedure
When to Use UPDATE CURRENT, DELETE CURRENT

UPDATE CURRENT and DELETE CURRENT statements are useful for manipulating single rows in interactive applications where rows are displayed, and the user decides which rows to delete or update.

The example below illustrates the program framework for such an operation (the construction is similar for a DELETE CURRENT operation):

...
EXEC SQL DECLARE c_1 CURSOR FOR ... ;
...
EXEC SQL OPEN c_1;
EXEC SQL WHENEVER NOT FOUND GOTO done;
loop
   EXEC SQL FETCH c_1
               INTO :VAR1, :VAR2, ..., :VARn;

   display VAR1, VAR2, ..., VARn;
   prompt "Update this row?";
   if ANSWER = "YES" then
      prompt "Give new values";
      EXEC SQL UPDATE tab
                  SET col1 = :NEWVAL1,
                      col2 = :NEWVAL2, ...
                  WHERE CURRENT OF c_1;
      display "Row updated";
   end if;

   prompt "Display next row?";
   exit when ANSWER = "NO";
end loop;

done:
EXEC SQL CLOSE c_1;

In situations where there is no requirement to interactively choose rows and where all the rows to be updated or deleted can be specified completely in terms of a WHERE clause, it is more efficient to do so rather than use a cursor.

An operation completely specified as a WHERE clause is executed as a single statement, rather than a series of statements (i.e. one for each FETCH etc.).


Transaction Handling and Database Security

This chapter explains transaction principles, transaction control statements, logging and protecting against data loss.


Transaction Principles

A transaction is an atomic operation which may not be divided into smaller operations.

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.

Read-only transactions have only two phases: build-up and prepare.

Transaction build-up may be started explicitly or implicitly, see Starting Transactions; 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.

Optimistic Concurrency Control

Since Mimer SQL uses optimistic concurrency control (OCC), deadlocks never occur, see Locking for a further discussion of deadlocks. How optimistic concurrency control works in Mimer SQL is described below.

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. Changes requested during transaction build-up become visible to other users when the transaction is successfully committed.

During build-up, a read-set records the state of the database as seen at the time of each operation (including intended changes). If the state of the database at commitment is inconsistent with the read-set, a conflict is reported and the transaction is rolled back (i.e. the write-set is erased and no changes are made to the database). This can happen if, for instance, a transaction updates a row which gets deleted by another user after build-up has started but before the transaction is committed. The application program is responsible for taking appropriate action if a transaction conflict occurs.

Concurrency Control Guidelines

Because of the nature of this concurrency control protocol, it is important that some of the implications are understood.

A transaction that exists for a long elapsed time has a greater chance of conflicting with changes made by other users than a transaction with a short elapsed time.

At the other extreme, an application that immediately commits every executed SQL statement will seldom meet any conflicts, but will incur unnecessary overhead.

In general:

  • keep transactions as short as is reasonably possible
  • keep interactive user dialogs outside of transactions

A common situation that can generate unnecessarily large read-sets is the following: an application program reads through the rows in a table in a loop construct, with a conditional exit to update a row on user intervention.

It is tempting to simply place a COMMIT after the update statement, for example:

EXEC SQL DECLARE c_1 CURSOR FOR SELECT...
loop
    EXEC SQL FETCH c_1
        INTO :VAR1, :VAR2, ..., :VARn;
    display VAR1, VAR2, ..., VARn;
    prompt "Update row?";
    exit when ANSWER = "YES";
end loop;
EXEC SQL UPDATE table
         SET ...
         WHERE CURRENT OF c_1;
EXEC SQL COMMIT;

However, the FETCH loop can create a large read-set while waiting for the user update request, risking transaction conflict at the UPDATE.

A tempting solution for this problem might be:

EXEC SQL DECLARE c_1 CURSOR FOR SELECT...
loop
    EXEC SQL FETCH c_1
        INTO :VAR1, :VAR2, ..., :VARn;
    display VAR1, VAR2, ..., VARn;
    prompt "Update row?";
    exit when ANSWER = "YES";
    EXEC SQL ROLLBACK;
end loop;
EXEC SQL UPDATE table
         SET ...
         WHERE CURRENT OF c_1;
EXEC SQL COMMIT;

But since ROLLBACK closes all cursors, this will not work.

Instead, something like the following is a better approach:

EXEC SQL DECLARE c_1 CURSOR FOR SELECT...
EXEC SQL SET TRANSACTION READ ONLY;
loop
    EXEC SQL FETCH c_1
        INTO :VAR1, :VAR2, ..., :VARn;
    display VAR1, VAR2, ..., VARn;
    prompt "Update row?";
    exit when ANSWER = "YES";
end loop;
EXEC SQL ROLLBACK;
EXEC SQL UPDATE table
         SET ...
         WHERE col1 = :VAR1,
               col2 = :VAR2, ...
EXEC SQL COMMIT;

The risk of a transaction conflict in the final transaction is minimal, because both the size and duration of the transaction is minimized. The use of a read-only transaction will can significantly improve the performance of the FETCH statements.

A number of changes are necessary if we want to loop over FETCH, UPDATE and COMMIT.

EXEC SQL DECLARE c_1 CURSOR WITH HOLD FOR SELECT...
loop
    EXEC SQL FETCH c_1
        INTO :VAR1, :VAR2, ..., :VARn;
    display VAR1, VAR2, ..., VARn;
    prompt "Update row?";
    if  ANSWER = "YES" then
        EXEC SQL COMMIT;
        EXEC SQL UPDATE table
                 SET ...
                 WHERE col1 = :VAR1,
                       col2 = :VAR2, ...
        EXEC SQL COMMIT;
    end if;
end loop;

The cursor is declared WITH HOLD in order to remain open and positioned after COMMIT.

COMMIT is used instead of ROLLBACK, since holdable cursors does not remain open after ROLLBACK.

The SET TRANSACTION statement is removed, because the existence of an open holdable cursor prohibits a change of transaction mode. The cursor cannot be accessed both in READ ONLY and in READ WRITE mode.

Locking

Deadlock situations, which can be relatively common in some database management systems where records are locked during transaction build-up, can not occur in Mimer SQL.

In Mimer SQL it is impossible for two processes to be waiting for a record locked by the other process. In some other database management systems this situation may require operator intervention to resolve the problem.

In any database system, at some stage in a transaction, the data records must be locked to prevent access by other processes and to ensure that the transaction is not interrupted. In the Mimer SQL system, no change is made to the database contents during the transaction build-up and no records are locked. This means that the database can be freely accessed (and updated) by any other process; the data accessed by the transaction is only locked during the commit phase. In this way, locks are held only for a very short period of time.

The problems associated with locking are further reduced since only those records that are actually to be updated are locked. Other data in the same table continues to be accessible to other transactions.


Transactions and Logging

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.

Options

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

· 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) that are interrupted will not be rolled back.

· READ ONLY

Only read operations are allowed, i.e no UPDATE, DELETE or INSERT can be performed.

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


Protecting Against Data Loss

The following sections discuss how system interruptions and hardware failure are handled.

System Interruptions

If a transaction build-up is interrupted by a system failure or a program termination (deliberate or otherwise) the transaction is aborted and none of the requested changes are made to the database.

Transactions which are interrupted after the request to commit, but before all operations in the transaction have been executed on the database, are completed by the automatic recovery functionality when the databank involved is next accessed. There is no possibility of transaction conflict in such an automatic completion, since no other process can access the affected data as long as an incomplete transaction is pending.

In the event of a system failure that interrupts one or more application programs, it may be necessary to manually examine the database contents to determine which transactions failed to commit before the interruption.

Hardware Failure

A databank that is damaged by hardware failure (e.g. a disk crash) may be recovered using back-up copies and the transaction log (LOGDB), provided that all write operations on the databank have been logged (and that the back-up copies and the LOGDB databank are intact).

Backup and restore facilities are described in the Mimer SQL Engine System Management Handbook.


Transaction Control Statements

The following sections explain how to start, end and optimize transactions. You can also read about consistency and exception diagnostics in transactions. Further, transaction options, cursors and error handling are discussed.

Starting Transactions

Transaction start may be set to EXPLICIT or IMPLICIT.

The default transaction start setting is IMPLICIT, which means a transaction will be started automatically whenever one is needed.

To set the transaction start mode, use the statements:
SET TRANSACTION START EXPLICIT;

SET TRANSACTION START IMPLICIT;

Different database connections can use different transaction start options.

The START statement can always be used to explicitly start a transaction. This is useful if a number of related updates are to be performed and it is desirable that all the updates succeed or fail together to maintain consistency.

You cannot start a transaction while a transaction is already active.

Explicit Transaction Start

With this setting, transactions are never automatically started. All transactions must be explicitly started by executing the START statement.

Any update operation (INSERT, UPDATE or DELETE) involving a table in a databank with the TRANS or LOG option must occur within a transaction. An error will be raised if such an update is attempted without first starting a transaction.

All the statements issued after the START statement and before the transaction is concluded are grouped together within that single transaction.

A transaction is concluded by executing a COMMIT or ROLLBACK statement.

Implicit Transaction Start

With this setting, a transaction is started automatically (if one is not already active) by a reference to an object stored in a databank with the TRANS or LOG option (i.e. if none of the objects referenced are stored in a databank with the TRANS or LOG option, no transaction is required and therefore one is not started).

The START statement may be used to explicitly start a transaction if required, typically to allow several updates to be grouped together within a single transaction for consistency, as already described.

An automatically started transaction is concluded by executing a COMMIT or ROLLBACK statement.

All the statements issued after the initiating update and before the concluding COMMIT or ROLLBACK statement are grouped together within that single transaction.

Ending Transactions

Transactions must be ended with the COMMIT or ROLLBACK statement.

· COMMIT

This statement requests that the operations in the write-set are executed on the database, making the changes permanent and visible to other users. The SQLSTATE value returned when a COMMIT statement is executed indicates either that the transaction commitment was successful (SQLSTATE = '00000') or that a transaction conflict occurred (SQLSTATE <> '00000').

· ROLLBACK

This statement abandons the transaction. The read-set and write-set are dropped and no changes are made to the database. ROLLBACK is always successful.

Note: A transaction in Mimer SQL is never physically rolled-back in the sense of undoing changes made to the database, since changes are not actually effected until a successful COMMIT is performed. However, the ROLLBACK statement may free internal resources.

Transactions that are not successfully committed due to a transaction conflict do not have to be explicitly rolled back. The ROLLBACK statement is most commonly used in exception routines for handling error situations that are detected by the application during transaction build-up.

If a connection or program is terminated without requesting a COMMIT or ROLLBACK for the current transaction, the system will abort the transaction. None of the changes requested during the transaction build-up will be made to the database.

Transaction handling in BSQL differs slightly from that described here - see the Mimer SQL User's Manual, Handling Transactions, for details.

Optimizing Transactions

The following SET TRANSACTION options are used to optimize transaction performance:

· READ ONLY

This setting should always be used for transactions that do not require update access to the database. Significant performance gains can be achieved, especially for queries retrieving large numbers of rows, when this setting is used in queries when there is no need for update access to the database.

· READ WRITE

This setting should only be used for transactions that require update access to the database. This is the default setting for a transaction.

The default option is READ WRITE, or the option defined to be the default for the current session by using the SET SESSION statement, see Setting Default Transaction Options.

The SET TRANSACTION READ command only affects the single next transaction started after it is used.

Consistency Within Transactions

The SET TRANSACTION ... ISOLATION LEVEL options can be used to control the degree to which the changes occurring within one transaction are affected by the changes occurring within other concurrently executing transactions.

The default option is REPEATABLE READ, or the option defined to be the default for the current session by using the SET SESSION statement, see Setting Default Transaction Options.

The SET TRANSACTION ... ISOLATION LEVEL command only affects the single next transaction started after it is used.

Options

The following options are available:

· SERIALIZABLE

This setting guarantees that the end result of the operations performed by two or more concurrent transactions will be the same as if the transactions had been executed in a serial fashion, where one executes to completion before the other starts.

· REPEATABLE READ

This setting offers the same consistency guarantee as serializable, except that the concurrency effect known as phantoms may be encountered (see below for a reference to the definition of this concurrency effect).

· READ COMMITTED

This setting offers the same consistency guarantee as repeatable read, except that the concurrency effect known as non-repeatable read may also be encountered (see below for a reference to the definition of this concurrency effect).

· READ UNCOMMITTED

This setting offers the same consistency guarantee as read committed, except that the concurrency effect known as dirty read may also be encountered (see below for a reference to the definition of this concurrency effect).

For a definition of the concurrency effects mentioned above (phantoms, non-repeatable read and dirty read) refer to the Mimer SQL Reference Manual, SET TRANSACTION.

All of the isolation level settings guarantee that each transaction will be executed completely or not at all and that no updates will be lost.

Exception Diagnostics Within Transactions

The SET TRANSACTION DIAGNOSTICS SIZE option allows the size of the diagnostics area to be defined. An unsigned integer value specifies how many exceptions can be stacked in the diagnostics area, and examined by GET DIAGNOSTICS, see the Mimer SQL Reference Manual, GET DIAGNOSTICS, in situations where repeated RESIGNAL operations have effectively been performed.

The SET TRANSACTION DIAGNOSTICS SIZE setting only affects the single next transaction to be started.

The default SET TRANSACTION DIAGNOSTICS SIZE setting (50 or whatever has been defined to be the default by using SET SESSION) applies unless an alternative is explicitly set before each transaction.

Setting Default Transaction Options

The SET SESSION statement can be used to define the default settings for the transaction options set by SET TRANSACTION READ, SET TRANSACTION ISOLATION LEVEL and SET TRANSACTION DIAGNOSTICS SIZE.

As these SET TRANSACTION commands only affect the single next transaction started after they are used, it is often convenient to define the desired default options for each of them.

A detailed description of the SET SESSION statement can be found in the Mimer SQL Reference Manual.

Statements in Transactions

The tables that follow summarize whether statements may or may not be used inside transactions.

Access Control Statements

Statements
Allowed
Comments

GRANT

REVOKE

Yes

Must be the only statement in a transaction.

Connection Statements

Statements
Allowed
Comments

CONNECT

SET CONNECTION

Yes

DISCONNECT

Yes

A ROLLBACK is performed on any active transaction.

ENTER

LEAVE (program ident)

No

Data Definition Statements

Statements
Allowed
Comments

ALTER

COMMENT

CREATE

DROP

Yes

Must be the only statement in a transaction.

Data Manipulation Statements

Statements
Allowed
Comments

SELECT EXPRESSION

SELECT INTO

FETCH

INSERT

DELETE

DELETE CURRENT

UPDATE

UPDATE CURRENT

Yes

OPEN

CLOSE

Yes

ROLLBACK closes all open cursors.

COMMIT closes all open non-holdable cursors.

Declarative Statements

Statements
Allowed
Comments

DECLARE CONDITION

DECLARE CURSOR

DECLARE HANDLER

DECLARE VARIABLE

Not applicable

Declarative statement

Diagnostic Statements

Statements
Allowed
Comments

GET DIAGNOSTICS

RESIGNAL

SIGNAL

Yes

Dynamic SQL Statements

Statements
Allowed
Comments

PREPARE

DESCRIBE

EXECUTE

EXECUTE IMMEDIATE

EXECUTE STATEMENT

ALLOCATE CURSOR

ALLOCATE DESCRIPTOR

DEALLOCATE DESCRIPTOR

DEALLOCATE PREPARE

GET DESCRIPTOR

SET DESCRIPTOR

Yes

See Dynamic SQL.

ESQL Control Statements

Statements
Allowed
Comments

DECLARE SECTION

WHENEVER

Not applicable

Declarative statement

Procedure Control Statements

Statements
Allowed
Comments

CALL

CASE

COMPOUND STATEMENT

IF

LEAVE

LOOP

REPEAT

RETURN

SET

WHILE

Yes

System Administration Statements

Statements
Allowed
Comments

ALTER DATABANK RESTORE

ALTER DATABASE

DELETE STATISTICS

CREATE BACKUP

SET DATABANK

SET DATABASE

SET SHADOW

UPDATE STATISTICS

No

These statements create internal transactions to ensure data dictionary consistency

Transaction Control Statements

Statements
Allowed
Comments

SET SESSION

SET TRANSACTION

START

No

These statements control transaction behavior

COMMIT

ROLLBACK

Yes

Cursors in Transactions

A cursor open by the current connection may be closed implicitly by one of the transaction terminating statements COMMIT and ROLLBACK. ROLLBACK closes all open cursors for the current connection. COMMIT closes all open cursors for the current connection, except cursors declared WITH HOLD. Holdable cursors remain open after COMMIT.

When a stacked cursor is closed, all instances of the cursor are closed.

Cursors are also closed implicitly by LEAVE and DISCONNECT. In SET TRANSACTION START EXPLICIT mode, cursors may be opened and used outside transactions. Such cursors remain accessible when an ENTER statement is issued, and remain open when a LEAVE statement is issued.

This is illustrated in the following statement sequence:

...
EXEC SQL SET TRANSACTION START EXPLICIT;

EXEC SQL DECLARE c_1 CURSOR FOR SELECT col1
                                   FROM tab1;
EXEC SQL DECLARE c_2 CURSOR FOR SELECT col2
                                   FROM tab2
                                   WHERE checkcol = :VAR1;

EXEC SQL OPEN c_1;

loop
   EXEC SQL FETCH c_1
               INTO :VAR1;     -- Fetch value from tab1

   EXEC SQL ENTER ... ;        -- Change current ident

      EXEC SQL OPEN c_2;
      EXEC SQL FETCH c_2
                  INTO ...;    -- Fetch row for c_2
      EXEC SQL CLOSE c_2;

   EXEC SQL LEAVE;
end loop;
...

In the above example, the value fetched for the cursor C1 is used to determine the set of rows addressed by cursor C2. Cursor C1 remains open and positioned during the ENTER ... LEAVE sequence.

Each time the loop is executed, a new value is fetched by C1 and a new set of rows is addressed by C2. The same behavior applies when LEAVE RETAIN is used to leave a PROGRAM ident but keep the environment for the ident.

A cursor opened and used outside a transaction may however not be used within a transaction. If the same cursor is required outside and inside a transaction, separate instances must be opened. Remember that separate instances of a cursor address separate result sets:

...
EXEC SQL SET TRANSACTION START EXPLICIT;

EXEC SQL DECLARE c_1 REOPENABLE CURSOR FOR SELECT col1
                                              FROM tab1;

EXEC SQL OPEN c_1;
EXEC SQL FETCH c_1
            INTO ...;       -- First row (outside transaction)
...
EXEC SQL START;
EXEC SQL OPEN c_1;          -- New instance of cursor
EXEC SQL FETCH c_1
            INTO ...;       -- First row again
...

Error Handling in Transactions

In general, errors and exception conditions are reported in SQLSTATE after each executable SQL statement.

The value of SQLSTATE indicates the outcome of the preceding statement, see SQLSTATE Return Codes for a list of SQLSTATE values.

GET DIAGNOSTICS can be used to get detailed status information after an SQL statement.

The value of SQLSTATE after a COMMIT statement indicates the success or failure of the request to commit the transaction, not the outcome of any data manipulations performed within the transaction.

About WHENEVER

Use of the general error handling statement WHENEVER, see the Mimer SQL Reference Manual, WHENEVER, for a description) in transactions requires some care:

  • Program control can be transferred to an exception routine in the event of an error. Make sure that the exception routine is designed to take care of uncompleted transactions.
    Most commonly, the first SQL statement in the exception routine should be GET DIAGNOSTICS. The exception routine should normally also execute a ROLLBACK statement. Remember that if the exception routine is used from a statement outside a transaction, any open cursors belonging to the current ident will be closed by the ROLLBACK statement. GET DIAGNOSTICS can be used to determine whether or not a transaction is active.
  • For transaction conflict, the SQLSTATE value returned from the COMMIT statement falls into the SQLERROR class. If the transaction is to be retried in the event of conflict, make sure that no WHENEVER SQLERROR GOTO exception statement is operative.

If WHENEVER error handling is used in an application program, a suitable program structure for COMMIT statements is:

EXEC SQL WHENEVER SQLERROR GOTO exception;
...
EXEC SQL WHENEVER SQLERROR GOTO retry;
EXEC SQL COMMIT;
EXEC SQL WHENEVER SQLERROR GOTO exception;
...


Distributed Transactions

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.

Support for distributed transactions is enabled by a special key in the Mimer license file. The evaluation key that is included in the distribution enables distributed transactions. In order to use distributed transactions with other licenses obtained from Mimer, make sure that the distributed transaction option is included.


Terms and Abbreviations

In the field of distributed transactions, a number of terms and abbreviations are used. Here are the most common ones:

Term/Abbreviation
Short for
Explanation

XA

eXtended Architecture

An Open Group (X/Open) standard for distributed transaction handling. For more information, visit:
http://www.opengroup.org

DTC

Microsoft Distributed Transaction Coordinator

The transaction manager used in Windows environments. For more information, visit:
http://msdn.microsoft.com/library

Application Server

N/A

A program that handles all application operations between users and an organization's business applications or databases.

MTS

Microsoft Transaction Server

The Microsoft application server

EJB

Enterprise JavaBeans

The framework for application servers in the Java environment.


How Does it Work?

Normally, an application that uses distributed transactions is a component in an application server. The application server environment takes care of all transaction processing. The application component just accesses one or several database servers (Resource Managers) using normal programming interfaces.

Note that the application server or Transaction Manager (TM) are not part of the Mimer database server. These components are obtained from other sources.

When the application server environment starts a new transaction, it contacts the Transaction Manager, which assigns a new transaction id (XID) to the transaction. All operations done by the application components are automatically assigned to this XID.

When the transaction is ready to commit, the TM executes the commit operations according to the two-phase commit protocol.

In phase 1, each participating RM is asked if it is ready to prepare for commit. By replying yes, the RM promises that it is able to commit the transaction and remember everything about the transaction. Although it can not commit the transaction yet, it must secure all information on disk to make sure that no information can be lost. If the RM determines that it can not commit the transaction at a later stage, it may answer no to the TM. In this case, the TM aborts its preparation phase. It contacts all RMs again and tells them to abort the transaction.

If the TM got a yes from all RMs in the first phase, phase 2 begins. Each RM is asked to commit the transactions.


Handling failures

The Transaction Manager is responsible for performing the two-phase commit protocol. It must maintain the current state of this protocol for every transaction it manages. It should also be able to deal with failures in any component, including itself.

A problematic situation occurs if the contact between the TM and the RM is lost when the protocol is between phase 1 and 2. In this case, the RM has promised to be able to commit a certain update, but it does not yet know whether it should actually do so. This is determined in phase 2. Because of this uncertainty, the RM does not know what value to return to other transactions that asks for the information that was updated. Should the old or the new value be returned? A Mimer database server will typically abort transactions that request data which was updated by a transaction that is in doubt.

The situation is automatically resolved when the contact between the TM and the RM is reestablished. Since both TM and RM save all information on disk, they may both crash between phase 1 and 2, and still be able to carry through with the two phase commit protocol.

However, if the TM somehow fails to reconnect to a Mimer database server that has prepared transactions in doubt, there is another option. The operator may perform a heuristic commit or a heuristic rollback. By doing this, the operator does the role that the TM normally does and resolves the state of the transaction that is in doubt. This can be done by using the TRANSACTIONS command, described in Mimer SQL User's Manual, TRANSACTIONS.

Note that if the TM has already instructed some RMs to (for example) commit, while the operator does a heuristic rollback on another RM, a transactional inconsistency has been introduced. This must be resolved manually. Because of this risk, heuristic operations should be used with due care.


Mimer SQL Support For Microsoft DTC on Windows

Mimer SQL Engine supports the complete distributed transaction model according to the Microsoft Distributed Transaction Coordinator (MSDTC). This allows transactions to span several Mimer SQL Engine databases. The database servers can be located on any type of hardware where Mimer SQL Engine 9.1 or later is supported. To use MSDTC, the client must be on a Windows platform.

It is also possible to have transactions over heterogeneous database systems. For example, a single transaction can be performed which updates data in both a Mimer SQL Engine database and a Microsoft SQL Server database.

The support allows code written for Microsoft Transaction Server and COM+ to be used with Mimer SQL Engine. The transaction can be managed automatically by the COM+ server. I.e. transactional components are fully supported and transactional attributes can be `Supported', `Required', or `Requires New'. Please see your COM+ documentation for a thorough discussion of these options.

There is no distributed transaction support for Mimer SQL Engine servers older than 9.1.


Mimer SQL Support for Java Enterprise Edition

Mimer SQL has full support for the distributed transactions in Java Enterprise Edition (Java EE) through the XA support in the Mimer JDBC Driver. With Java EE any XA compliant data source can take part in a distributed transaction. The distributed transactions are handled by the Java EE Application Server and specified by the application developer in a declarative manner. This way the application developer does not have to include any transaction handling logic in the application. Distributed transactions can be used in several different parts of the Java EE framework, for example in Enterprise Java Beans (EJB).

To be able to use Mimer SQL in a distributed transaction an XA data source must be created in the application server. How this is done differ between different application servers, consult your manual to see how it is done. When defining an XA data source for Mimer SQL, the com.mimer.jdbc.MimerXADataSource Java class should be used.

For more information about the Mimer JDBC Driver, see the Mimer JDBC Driver Guide.


Dynamic SQL

This chapter discusses the principles of dynamic SQL, processing dynamic SQL, the descriptor area, preparing statements, extended dynamic cursors and prepared statements.


Principles of Dynamic SQL

Dynamic SQL enables you to execute SQL statements placed in a string variable instead of explicitly writing the statements inside a program. This allows SQL statements to be constructed within an application program. These facilities are typically used in interactive environments, where SQL statements are submitted to the application program from the terminal.

An example of when dynamic SQL is needed would be a program for interactive SQL, where any correct SQL statement may be entered at the terminal and processed by the application. Limited dynamic facilities may however be provided by relatively simple application programs.

SQL Statements and Dynamic SQL

The following classes of SQL statements may be submitted to programs using dynamic SQL. Statements excluded from dynamic applications are declarations, diagnostic statements and dynamic SQL statements themselves.

· Access control statements:

ENTER

LEAVE

· Data definition statements:

CREATE

ALTER

COMMENT

DROP

· Security control statements:

GRANT

REVOKE

· Transaction control statements:

SET SESSION

SET TRANSACTION

START

COMMIT

ROLLBACK

· Data manipulation statements:

CALL

SELECT

SELECT INTO

INSERT

UPDATE

UPDATE CURRENT

DELETE

DELETE CURRENT

· System administration statements:

CREATE BACKUP

ALTER DATABANK RESTORE

SET DATABASE

SET DATABANK

SET SHADOW

UPDATE STATISTICS

Submitting Statements

Statements may be submitted to dynamic SQL applications in two forms:

  • Fully defined statements, written exactly as they would be submitted to interactive SQL. For example:
    • GRANT SELECT ON mimer_store_book.details TO mimer_admin_group

SELECT code, country
   FROM mimer_store.countries

SELECT price
   INTO :PRICE
   FROM mimer_store.items
   WHERE item_id = :ITEM_ID
  • Statements with parameter markers, which identify positions where the value of a host variable will be inserted when the statement is executed or the cursor is opened. A parameter marker is represented by a question mark ?. For example:
    • UPDATE mimer_store.currencies
   SET exchange_rate = ?
   WHERE code = ?

DELETE FROM countries
   WHERE code = ?

SELECT currency, exchange_rate * ?
   FROM mimer_store.currencies
   WHERE code IN (SELECT currency_code
                     FROM mimer_store.countries
                     WHERE code like ? || '%')

Statements submitted with parameter markers are equivalent to normal embedded statements using host variables, except that the statements are defined at run-time.


General Summary of Dynamic SQL Processing

The following statements are used when SQL statements are dynamically submitted:

Statement
Description
ALLOCATE CURSOR

Allocate extended cursor.

ALLOCATE DESCRIPTOR

Allocate SQL descriptor area.

CLOSE

Close an open cursor.

DEALLOCATE DESCRIPTOR

Deallocate SQL descriptor area.

DEALLOCATE PREPARE

Deallocate prepared SQL statement.

DECLARE CURSOR

Declare a cursor for a statement which will be dynamically submitted.

DESCRIBE

Examine the object form of the statement and assign values to the appropriate parameters in the SQL descriptor area.

EXECUTE

Execute a prepared statement (except result set generating statements).

EXECUTE IMMEDIATE

Shorthand form for PREPARE followed by EXECUTE. This form can only be used for fully-defined non-result set statements with no parameter markers.

FETCH

Fetch rows for a dynamic cursor.

GET DESCRIPTOR

Get values from the SQL descriptor area.

OPEN

Open a prepared cursor.

PREPARE

Compile an SQL source statement into an internal object form.

SET DESCRIPTOR

Set values in the SQL descriptor area.

All statements submitted to dynamic SQL programs must be prepared.

All prepared statements and singleton SELECT statements, where the result-set contains only one row, are executed with the EXECUTE statement.

All other SELECT statements and calls to result set procedures are executed using OPEN and FETCH for a cursor declared with the prepared statement.

The declaration of a cursor for a statement, DECLARE CURSOR, must always precede the PREPARE operation for the same statement in an application using dynamic SQL.


SQL Descriptor Area

The SQL descriptor area is used for managing input and output data in dynamically submitted SQL statements where the number and/or data type of the host variables required is not known at the time the program is written.

An SQL descriptor area is allocated with the ESQL statement ALLOCATE DESCRIPTOR and deallocated with DEALLOCATE DESCRIPTOR. See the Mimer SQL Reference Manual, SQL Statement Descriptions, for more information.

A program may allocate several separate descriptor areas, identified by different descriptor names. SQL descriptor areas are used for both result set list items, routine parameters and input values.

The following statement types can use information from SQL descriptor areas:

  • all SELECT statements and calls to result set procedures
  • INSERT, DELETE, UPDATE, SET and CALL statements using parameter markers
  • ENTER statements.

The following statement types do not use SQL descriptor areas:

  • all data definition statements, security control statements, access control statements (except ENTER) and transaction control statements
  • INSERT, DELETE, UPDATE and CALL statements using only constant expressions.

In practice, programs using dynamically submitted SQL statements are usually written as though all submitted statements use SQL descriptor areas (since the nature of the submitted statement is not known until run-time).

SQL descriptor areas can be left out of a program only if it is known in advance that they will not be needed (for instance in an application program which will handle only submitted data definition statements).

The Structure of the SQL Descriptor Area

The SQL descriptor area is a storage area holding information about the described statement. It is allocated and maintained with ESQL statements.

It consists of one COUNT field and one or more item descriptor areas, each one describing either a selected column or an input/output value:

COUNT

item descriptor area 1

item descriptor area 2

...

item descriptor area n

The COUNT field specifies how many of the item descriptor areas contain data.

The individual fields of the item descriptor area can be accessed with the GET DESCRIPTOR and SET DESCRIPTOR statements. See the Mimer SQL Reference Manual, SQL Statement Descriptions, for a description of these statements.


Preparing Statements

All statements submitted to dynamic SQL programs must be prepared. The simplest form of the operation uses a PREPARE statement, see the Mimer SQL Reference Manual, PREPARE, for the syntax description. The operation may also be combined with EXECUTE as a simple statement in the shorthand form EXECUTE IMMEDIATE.

The source form of the statement must be contained in a host variable, containing the statement string. (The statement string itself is not preceded by EXEC SQL nor terminated by the language-specific embedded delimiter.)

The prepared form of the statement is named by an SQL-identifier or a host variable, for extended statements, see Extended Dynamic Cursors.

In the following example the source form of the statement is given as a string constant for illustrative purposes, however, the statement would usually be read from some input source, e.g. the terminal, at run-time:

...
EXEC SQL BEGIN DECLARE SECTION;
   string SQL_TXT(255);
   ...
EXEC SQL END DECLARE SECTION;
...

SQL_TXT := "CREATE INDEX pdt_product_search
               ON products(product_search)";
EXEC SQL PREPARE OBJECT FROM :SQL_TXT;
...


Extended Dynamic Cursors

A typical cursor is identified by an SQL identifier. An extended cursor makes it possible to represent a dynamic cursor by a host variable or a literal. An extended cursor is allocated by the application with the ALLOCATE CURSOR statement, see the Mimer SQL Reference Manual, ALLOCATE CURSOR, for the syntax description.

When the application is finished with the processing of the SQL statement, the prepared statement may be destroyed by executing the DEALLOCATE PREPARE statement, see the Mimer SQL Reference Manual, DEALLOCATE PREPARE, for the syntax description. DEALLOCATE PREPARE also destroys any extended cursor that was associated with the statement.

Example of how extended cursors are used:

...
EXEC SQL BEGIN DECLARE SECTION;
   string SQL_TXT(255);
   string C1(128);
   string STM1(128);
   integer HOSTVAR1;
   string HOSTVAR2(10);
   ...
EXEC SQL END DECLARE SECTION;
...

SQL_TXT := "SELECT col1, col2
               FROM tab1";
STM1 := "STMT_1";
EXEC SQL PREPARE :STM1 FROM :SQL_TXT;

C1 := "CUR_1";
EXEC SQL ALLOCATE :C1 CURSOR FOR :STM1;
...

EXEC SQL ALLOCATE DESCRIPTOR 'SQLA' WITH MAX 50;
EXEC SQL DESCRIBE OUTPUT :STM1 USING SQL DESCRIPTOR 'SQLA';
...
EXEC SQL OPEN :C1;
EXEC SQL WHENEVER NOT FOUND GOTO done;

loop
   EXEC SQL FETCH :C1
               INTO SQL DESCRIPTOR 'SQLA';
   EXEC SQL GET DESCRIPTOR 'SQLA' VALUE 1 :HOSTVAR1 = DATA;
   EXEC SQL GET DESCRIPTOR 'SQLA' VALUE 2 :HOSTVAR2 = DATA;
   ...
   display HOSTVAR1, HOSTVAR2, ...;
end loop;

done:
EXEC SQL CLOSE :C1;
EXEC SQL DEALLOCATE DESCRIPTOR 'SQLA';
EXEC SQL DEALLOCATE PREPARE :STM1;
...


Describing Prepared Statements

Statements returning a result set and statements containing parameter markers can be described to obtain information about the number and data types of the parameters.

There are two forms of DESCRIBE:

  • DESCRIBE OUTPUT for result set values
  • DESCRIBE INPUT for input and output parameters.

Both forms of DESCRIBE use the object (prepared) form of the statement as an argument. The same statement may be described in both senses if necessary.

For example:

EXEC SQL BEGIN DECLARE SECTION;
   string SQLA1(128);
   integer MAXOCC;
   string SOURCE(255);
EXEC SQL END DECLARE SECTION;
...

MAXOCC := 15;
SQLA1 := "SQL_AREA_1";
EXEC SQL ALLOCATE DESCRIPTOR :SQLA1 WITH MAX 20;
EXEC SQL ALLOCATE DESCRIPTOR 'SQLA2' WITH MAX :MAXOCC;
...

EXEC SQL PREPARE 'OBJECT' FROM :SOURCE;
EXEC SQL DESCRIBE OUTPUT 'OBJECT' USING SQL DESCRIPTOR :SQLA1;
EXEC SQL DESCRIBE INPUT 'OBJECT' USING SQL DESCRIPTOR 'SQLA2';
...

DESCRIBE places information about the prepared statement in the SQL descriptor areas. See SQL Descriptor Area for a description of the SQL descriptor area.

The contents of the SQL descriptor area is read with the GET DESCRIPTOR statement and updated with the SET DESCRIPTOR statement.

Describing Output Variables

The items in the result set for a statement are described with the DESCRIBE OUTPUT statement. The keyword OUTPUT may be omitted.

The DESCRIBE OUTPUT statement shows:

  • whether the statement returns a result set or not. This is indicated by the value of the COUNT field of the SQL descriptor area which is set to zero for statements that do not return a result set. Statements that return a result set are calls to result set procedures, see Result Set Procedures, and query-expressions (refer to the Mimer SQL Reference Manual, SELECT).
  • dynamic SQL programs must test for this after each DESCRIBE operation because the treatment of statements that return result sets differs from the treatment of those that do not, see Handling Prepared Statements. If the statement returns a result set, the DESCRIBE statement will place information about the items in the result set in the fields of the descriptor area.
  • whether the current descriptor area allocation is sufficient or not. Insufficient area is indicated by the SQLSTATE variable set to a warning state and a value of COUNT (required number of items) greater than that specified in the WITH MAX ... clause of the ALLOCATE DESCRIPTOR statement, or greater than 100 if no WITH MAX ... clause was specified. If the area is insufficient, no items are described.

Describing Input Variables

The DESCRIBE INPUT statement is used to describe parameter markers.

The value of the COUNT field of the SQL descriptor area indicates the number of parameter markers in the statement (a value of zero indicates no input parameters). A value greater than that specified in WITH MAX ... indicates that the allocated SQL descriptor area is too small and the describe operation will not be performed. This situation is handled as described above for DESCRIBE OUTPUT.

Note: If the prepared statement is a call to a stored procedure that uses parameter markers, these will be described by the DESCRIBE INPUT statement. This is regardless of how the formal parameter is specified in the procedure definition. Whether the parameter is IN, INOUT or OUT can be seen from the PARAMETER_MODE field in the descriptor area.


Handling Prepared Statements

After PREPARE and DESCRIBE, the way in which submitted statements are handled differs according to whether the statement is executable or whether it returns a result set.

  • Executable statements are executed using the EXECUTE statement, with the object (prepared) form of the submitted statement as the argument.
  • Result set a cursor is used for these statements, associated with the object form of the prepared statement and are executed with OPEN and FETCH.

Executable Statements

Executable statements are identified by a value of zero in the COUNT field of the SQL descriptor area after a DESCRIBE OUTPUT statement. If the statement does not contain any parameter markers, it may be executed directly.

If, on the other hand, the statement contains parameter markers, the statement must be executed with an SQL descriptor area for input and output values.

Note: All parameter markers used in a call statement are described with the DESCRIBE INPUT statement, regardless of the mode of the formal parameter.

Parameter markers must be used for all INOUT or OUT parameters when a call statement is prepared dynamically.

The descriptor areas referenced in the EXECUTE statement may be replaced by explicit lists of host variables, provided that the number and data types of the user variables in the source statement are known when the program is written (so that variables can be declared and the appropriate variable list written into the EXECUTE statement).

This facility is of limited use, since the occasions when the user constructs freely chosen SQL statements with a predetermined number of user variables are rare.

The shorthand form EXECUTE IMMEDIATE combines the functions of PREPARE and EXECUTE. This form may only be used for executable statements with no parameter markers and is therefore of value only in contexts where the user is restricted to this type of statement. (Data definition and security control statements fall into this category, since user variables are not permitted in the syntax of these statements. EXECUTE IMMEDIATE can therefore be useful for application programs designed specifically to handle database definition statements).

Result Set Statements

Statements returning a result set are identified by a non-zero value in the COUNT field of the SQL descriptor area after DESCRIBE OUTPUT.

All dynamically submitted SELECT statements and calls to result set procedures must be handled through cursors. Cursors are declared or allocated for the object (prepared) form of submitted result set returning statements.

Note: A DECLARE CURSOR statement must precede the PREPARE statement in the program code. If ALLOCATE CURSOR is used instead of DECLARE CURSOR, the statement must have been prepared before the cursor can be allocated. The SQL statement must also be prepared before the cursor is opened.

If the source form of the result set returning statement contains parameter markers, these must be described before the cursor is opened and the OPEN statement must reference the relevant descriptor area. In the rare case where the number and data type of the user variables are known when the program is first written, the OPEN statement may reference an explicit variable list instead of a descriptor area.

The descriptor area used for the submitted result set returning statement is referenced when data is retrieved with the FETCH statement.

Example:
...
EXEC SQL ALLOCATE DESCRIPTOR 'SQLA1' WITH MAX 30;
EXEC SQL ALLOCATE DESCRIPTOR 'SQLA2' WITH MAX 30;
...

EXEC SQL PREPARE 'OBJECT' FROM :SOURCE;
...

EXEC SQL DESCRIBE OUTPUT 'OBJECT' USING SQL DESCRIPTOR 'SQLA1';
EXEC SQL GET DESCRIPTOR 'SQLA1' :NO_OUT = COUNT;
if NO_OUT = 0 then
   RESULT_SET := FALSE;
else
   EXEC SQL ALLOCATE 'C1' CURSOR FOR 'OBJECT';
   RESULT_SET := TRUE;
end if;
...

EXEC SQL DESCRIBE INPUT 'OBJECT' USING SQL DESCRIPTOR 'SQLA2';
EXEC SQL GET DESCRIPTOR 'SQLA2' :NO_IN = COUNT;
...

if RESULT_SET then
   EXEC SQL OPEN 'C1' USING SQL DESCRIPTOR 'SQLA2';
   ...
   EXEC SQL FETCH 'C1' INTO SQL DESCRIPTOR 'SQLA1';
   ...
else
   ...
end if;


Example Framework for Dynamic SQL Programs

This section gives a general framework (in pseudo code) for dynamic SQL programs designed to handle any valid SQL statement as input. The framework is largely a synthesis of the example fragments given earlier in this chapter.

The framework is written as a single sequential module to emphasize the order of operations.

Host variable declarations are omitted. Handling of values returned by FETCH is also omitted.

Example Framework

-- Allocate two SQL descriptor areas
EXEC SQL ALLOCATE DESCRIPTOR 'SQLA1' WITH MAX 50;
EXEC SQL ALLOCATE DESCRIPTOR 'SQLA2' WITH MAX 50;

-- read statement from terminal
read INPUT into SOURCE;

-- prepare statement
EXEC SQL PREPARE 'OBJECT' FROM :SOURCE;

-- describe statement and set type/parameter usage flags
EXEC SQL DESCRIBE OUTPUT 'OBJECT' USING SQL DESCRIPTOR 'SQLA1';
EXEC SQL GET DESCRIPTOR 'SQLA1' :NO_OUT = COUNT;
if NO_OUT = 0 then
   RESULT_SET := FALSE;
else
   -- allocate cursor for result set
   EXEC SQL ALLOCATE 'C1' CURSOR FOR 'OBJECT';
   RESULT_SET:= TRUE;
end if;

EXEC SQL DESCRIBE INPUT 'OBJECT' USING SQL DESCRIPTOR 'SQLA2';
EXEC SQL GET DESCRIPTOR 'SQLA2' :NO_IN = COUNT;
-- execute statement or open cursor and fetch after assigning
-- values to input variables
if RESULT_SET then
   EXEC SQL OPEN 'C1' USING SQL DESCRIPTOR 'SQLA2';
   loop
      EXEC SQL FETCH 'C1' INTO SQL DESCRIPTOR 'SQLA1';
      exit when NO_MORE_REQUIRED or SQLSTATE = "02000";
      ... -- process results of FETCH
   end loop;

   EXEC SQL CLOSE 'C1';
else
   EXEC SQL EXECUTE 'OBJECT' USING SQL DESCRIPTOR 'SQLA2';
end if;

EXEC SQL DEALLOCATE PREPARE 'OBJECT';

Note:	Features that are specific to real host languages are described in Host Language Dependent Aspects.


Mimer SQL Stored Procedures

In Mimer SQL, the term stored procedures refers to routines, i.e. functions and procedures.

Mimer SQL stored procedures conform to the SQL/PSM standard. The SQL/PSM standard consists of:

  • syntax and semantics for variable and cursor declarations
  • assignment of the results of expressions to variables and parameters
  • conditional statements
  • control statements for looping and branching
  • condition handling
  • getting diagnostics for status information and routine invocations.

Modules can be used to collect a number of routines together as a group.

Mimer SQL PSM Debugger

You can debug routines and triggers using Mimer SQL's Java-based graphic debugger for PSM routines. The debugger supports watching variables, step-wise execution and setting breakpoints. For more information, see The Mimer SQL PSM Debugger.


About Routines

A routine is either defined as a function or as a procedure. Essentially the same constructs may be used in both functions and procedures.

A routine can be created by declaring it in a module definition, see Modules, or be created on its own by executing the CREATE FUNCTION or CREATE PROCEDURE statement. A routine created on its own cannot be subsequently added to a module.

A routine belongs to the schema in which it was created and the routine name may be qualified in the normal way with the name of the schema. Only the ident with the same name as the schema to which a routine belongs may refer to it by its unqualified name, all other idents must use the fully qualified routine name.

It is possible to have multiple functions and procedures with the same name within a schema as long as they differ with regard to either the number of parameters or the data type for the parameters. This is called parameter overloading.

To distinguish between routines with the same name it is possible to give a specific name when creating a routine. This specific name can used when granting or revoking execute privilege for the routine or when dropping the routine.

It is possible for a function to have the same qualified name as a procedure, because the invocation of a function is distinct from that of a procedure.

In order to invoke a routine, the ident invoking it must have been granted EXECUTE privilege on the routine. Routines may be recursively invoked.

Note: When routines and modules are created, the CREATE statement must be executed as one single statement. For example, using BSQL, the create statement must be delimited by the @ character, see the Mimer SQL User's Manual, Creating Functions, Procedures, Triggers and Modules, for details and examples.

The following points should be noted for procedures:

  • they are invoked by using the CALL statement.
  • any result from a procedure must be returned via one of the output parameters, except in the special case of a result set procedure, which can return rows of a result set to a cursor, see Result Set Procedures.

    • The following points should be noted for functions:

  • they are invoked from an SQL statement where a value is required. Certain restrictions apply, see Invoking Functions. For example:
    • SET :isbn = mimer_store_book.format_isbn('1558604618');
  • the parameters of a function provide input only and the function result is returned as the value of the function invocation.

A routine essentially consists of static SQL source that is stored in the data dictionary and which may be invoked by name whenever it is to be executed.

The SQL source for a routine comprises a definition of various routine components, see Syntactic Components of a Routine Definition for details, followed by the routine body.

The routine body consists of a single executable SQL statement - typically a compound SQL statement, i.e. local declarations and a number of SQL statements delimited by a BEGIN and END. See Scope in Routines - the Compound SQL Statement.

Note: It is recommended that a compound SQL statement always be used for the body of a routine, as this offers the greatest flexibility and results in a consistent structure for all routines.

It is possible to declare exception handlers within a compound SQL statement to handle specific exceptions or classes of conditions, see Declaring Exception Handlers.

Functions

A function is invoked by specifying the function invocation where a value expression would normally be used. The parameters of a function are used to provide input only, values cannot be passed back to the calling environment through the parameters of a function.

A function always returns a single value and the data type of the return value is defined in the returns clause, which is specified after the parameter definition part of the function definition.

The function returns its value when a RETURN statement is executed within the body of the function. The data type of the value expression in the RETURN statement must be assignment-compatible with the data type specified in the returns clause of the function.

A function can be created with the same name as a predefined function (e.g. ABS and SIGN). If such a function also has the same number of parameters as the predefined function and each parameter has a data type that is assignment-compatible with the corresponding parameter of the predefined function, the invocations of the two functions cannot be distinguished. In this situation, any unqualified reference to the function name will be taken to be a reference to the predefined function.

Functions and SQL Statements

The SQL statements that apply to a function are:

Statement
Description
CREATE FUNCTION

creates a function that exists on its own, see the Mimer SQL Reference Manual, CREATE FUNCTION

DROP FUNCTION

drops a function that exists on its own, see the Mimer SQL Reference Manual, DROP

GRANT EXECUTE

grants the privilege to invoke a function, see the Mimer SQL Reference Manual, GRANT OBJECT PRIVILEGE

REVOKE EXECUTE

revokes the privilege to invoke a function, see the Mimer SQL Reference Manual, REVOKE OBJECT PRIVILEGE

COMMENT ON FUNCTION

defines a comment on a function, see the Mimer SQL Reference Manual, COMMENT.

Example 1
CREATE FUNCTION SQUARE_INTEGER(p_root INTEGER) RETURNS INTEGER
CONTAINS SQL
BEGIN
   RETURN p_root * p_root;
END
Example 2
CREATE FUNCTION mimer_store_web.session_expiration_period()
   RETURNS INTERVAL HOUR TO MINUTE
-- Defines the period that a session can be unused
DETERMINISTIC
RETURN INTERVAL '10' MINUTE(3);  -- Intentionally very short
Example 3
CREATE FUNCTION mimer_store_book.keyword_id(p_keyword VARCHAR(48))
   RETURNS INTEGER
-- Inserts a word in the KEYWORDS table
-- and returns the identifier with which the keyword is associated
MODIFIES SQL DATA
BEGIN
   DECLARE v_keyword_id INTEGER;

   DECLARE CONTINUE HANDLER FOR NOT FOUND
   BEGIN
      INSERT INTO mimer_store_book.keywords(keyword)
         VALUES (UPPER(TRIM(p_keyword)));

      SET v_keyword_id = CURRENT_VALUE OF mimer_store_book.keyword_id_seq;
   END;  -- of not found handler

   SELECT keyword_id
      INTO v_keyword_id
      FROM mimer_store_book.keywords
      WHERE keyword = TRIM(p_keyword);

   RETURN v_keyword_id;
END  -- of routine mimer_store_book.keyword_id

Procedures

A procedure is normally invoked explicitly by executing the CALL statement and does not return a value. The parameters of a procedure can be used to provide input and may be used to pass values back to the calling environment.

There is a special type of procedure, called a result set procedure, which returns rows of a result set to a cursor when it is invoked by executing the FETCH statement in that context.

A result set procedure is distinguished from a normal procedure by having a values clause specified after the parameter definition part of the procedure definition, see Result Set Procedures for a detailed description of result set procedures.

Procedures and SQL Statements

The SQL statements that apply to a procedure are:

Statement
Description
CREATE PROCEDURE

creates a procedure that exists on its own, see the Mimer SQL Reference Manual, CREATE PROCEDURE

DROP PROCEDURE

drops a procedure that exists on its own, see the Mimer SQL Reference Manual, DROP

GRANT EXECUTE

grants the privilege to invoke a procedure, see the Mimer SQL Reference Manual, GRANT OBJECT PRIVILEGE

REVOKE EXECUTE

revokes the privilege to invoke a procedure, see the Mimer SQL Reference Manual, REVOKE OBJECT PRIVILEGE

CALL

invokes a procedure, see the Mimer SQL Reference Manual, CALL

COMMENT ON PROCEDURE

defines a comment on a procedure, see the Mimer SQL Reference Manual, COMMENT.

Example 1
CREATE PROCEDURE mimer_store_web.delete_basket(p_session_no VARCHAR(16))
-- Deletes expired baskets
MODIFIES SQL DATA
BEGIN
   IF p_session_no = '*' THEN
      -- '*' indicates that all expired sessions should be deleted
      DELETE
         FROM mimer_store.orders
         WHERE order_id IN (SELECT order_id
                               FROM mimer_store_web.sessions
                               WHERE last_accessed < LOCALTIMESTAMP -
                                     mimer_store_web.session_expiration_period());
   ELSE
      -- Delete the specified session
      DELETE
         FROM mimer_store.orders
         WHERE order_id = (SELECT order_id
                              FROM mimer_store_web.sessions
                              WHERE session_no = p_session_no);
   END IF;
END  -- of routine mimer_store_web.delete_basket

CALL mimer_store_web.delete_basket( '*' );

COMMENT ON PROCEDURE mimer_store_web.delete_basket
        IS 'Deletes expired baskets';

DROP PROCEDURE mimer_store_web.delete_basket;
Example 2
CREATE PROCEDURE mimer_store_book.catalogue_authors(IN p_item_id INTEGER,
                                                 IN p_authors_list VARCHAR(128))
-- Stores author names as keywords and forms a link between a book
-- and the keywords
MODIFIES SQL DATA
BEGIN
   DECLARE v_author VARCHAR(50);
   DECLARE v_authors VARCHAR(130);
   DECLARE v_offset, v_length INTEGER;

   SET v_authors = REPLACE(' ' || p_authors_list || ' ', ' and ', ';');
   SET v_authors = REPLACE(v_authors, ' & ', ';');
   SET v_authors = TRIM(v_authors);

extract_authors:
   LOOP
      IF v_authors = '' THEN LEAVE extract_authors; END IF;

      SET v_offset = POSITION(';' IN v_authors);

      IF v_offset <> 1 THEN
         IF v_offset = 0
         OR v_offset > 49 THEN
            SET v_length = 48;
         ELSE
            SET v_length = v_offset - 1;
         END IF;

         SET v_author = mimer_store_book.authors_name(
                           SUBSTRING(v_authors FROM 1 FOR v_length));

         BEGIN
            DECLARE v_keyword_id INTEGER;

            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
            BEGIN
               -- Ignore all SQL errors
            END;  -- of sqlexception handler

            SET v_keyword_id = mimer_store_book.keyword_id(v_author);

            INSERT INTO mimer_store_book.authors(keyword_id, item_id)
               VALUES (v_keyword_id, p_item_id);
         END;
      END IF;

      IF v_offset = 0 THEN LEAVE extract_authors; END IF;

      SET v_authors = TRIM(SUBSTRING(v_authors FROM v_offset+1));
   END LOOP extract_authors;
END  -- of routine mimer_store_book.catalogue_authors


Syntactic Components of a Routine Definition

The following sections discuss parameters, language indicators, clauses, scope, variables and data types when working with routines.

Routine Parameters

A routine may have zero or more parameters and each parameter must have a name and a data type specified.

Each parameter of a procedure can have an optional mode specification (IN, OUT or INOUT - see CREATE PROCEDURE in the Mimer SQL Reference Manual for details). When the mode is not explicitly specified, IN is assumed by default.

It is not possible to specify the mode for the parameters of a function (they always have the default mode, IN).

A parameter name must be unique within the routine. A parameter can have the same name as a routine name, however this is not generally recommended. The parameter name can be up to 128 characters in length, see the Mimer SQL Reference Manual, Naming Objects, for further details about naming SQL objects.

The parameter data type can be any data type supported by Mimer SQL, except any of the large object types, see the Mimer SQL Reference Manual, Data Types in SQL Statements.

Note: A domain name cannot be specified for the data type of a routine parameter.

A parameter name may be referenced in an unqualified manner throughout a routine, at all scope levels - see Scope in Routines - the Compound SQL Statement for a discussion of scope in routines.

Examples:
CREATE FUNCTION onefunction(a INTEGER, b DECIMAL(5,2))
RETURNS DECIMAL(5,2)
BEGIN
   ...
END

CREATE PROCEDURE lookup(IN i INTEGER, OUT retval VARCHAR(20))
BEGIN
   ...
END
Parameter Overloading

Mimer SQL supports the possibility to define multiple functions or procedures with the same name as long as they differ with regard to the number of parameters or the data type of the parameter. It is not possible to have multiple functions that only differ with regard to the return data type.

As an example, it is possible to create two functions like

SQL>create function f(c1 char)returns int return 1;
SQL>create function f(c1 integer)returns int return 2;

and these can be used as

SQL>set ? = f('1');
          ?
===========
          1

SQL>set ? = f(1);
          ?
===========
          2

In this case there is no problem deciding which routine that should be invoked in the two cases since there can be no implicit conversion from a character type to integer or vice versa. Some interesting cases arise when there are parameter overloading and where there are implicit conversions between the parameter types. For instance, if we have these functions

SQL>create function f(p char varying(2))
SQL&returns int return 1;
SQL>create function f(p nchar varying(2))
SQL&returns int return 2;

Given the statement

SQL>set ? = f('a');

which routine should be invoked? The data type of the actual argument is char and there is no routine with a parameter list that matches the invocation exactly. In this case a type precedence list is used to determine the proper subject routine. For CHAR the type precedence list is CHAR, CHARACTER VARYING, NCHAR and NCHAR VARYING which means that the function returning 1 will be chosen in this case. If there are multiple parameters, the subject routine is determined by evaluating the type precedence list for each parameter, going from left to right.

The type precedence lists are found in Mimer SQL Reference Manual, Appendix H, Type Precedence Lists.

Specific Names and Parameter List

When specifying a routine in a drop, grant or revoke statement the routine must be uniquely identifiable. This is no problem as long as parameter overloading is not used. If there are multiple functions or procedures with the same name there are two ways of specifying a unique routine. The first is by using the specific name for the routine. A specific name can be defined when the routine is created. If no specific name is given, a unique name is generated automatically. This name can be seen in the view INFORMATION_SCHEMA.ROUTINES. As an example:

SQL>CREATE PROCEDURE p(p1 INT, p2 CHAR(20))
SQL&SPECIFIC p_int_char
SQL&...
SQL>GRANT EXECUTE ON SPECIFIC PROCEDURE p_int_char
SQL&TO public WITH GRANT OPTION;

The other way to distinguish between overloaded routines in DDL statements is to use a data type list. Given the above procedure definition, the grant statement can be written as

SQL>GRANT EXECUTE ON PROCEDURE p(INT,CHAR)
SQL&TO public WITH GRANT OPTION;

To specify a routine without parameters, the syntax is

SQL>GRANT EXECUTE ON PROCEDURE p()
SQL&TO public WITH GRANT OPTION;
Routine Parameters and Null Values

All parameters accept null values. Use CAST to invoke a routine with a null value as a parameter, as follows:

 CALL mimer_store_music.AddTrack(718751799622, CAST(NULL AS int), 'Null Set',
'3:53')

Routine Language Indicator

The language indicator specifies the language of the routine. Currently, the only language name supported is SQL.

If no language indicator is specified, LANGUAGE SQL is assumed by default.

Routine Deterministic Clause

The deterministic clause for a routine can specify NOT DETERMINISTIC or DETERMINISTIC. If a deterministic clause is not specified, NOT DETERMINISTIC is assumed by default.

A DETERMINISTIC routine is one that is guaranteed to produce the same result every time it is invoked with the same set of input values.

Therefore, a DETERMINISTIC routine may not contain a reference to: CURRENT_DATE, LOCALTIME or LOCALTIMESTAMP.

Specifying a routine to be DETERMINISTIC allows repeated invocations of it to be optimized.

Routine Access Clause

The access clause for a routine specifies which SQL statements are permitted within the routine.

The three different options for the routine access clause (CONTAINS SQL, READS SQL DATA and MODIFIES SQL DATA) are described under CREATE PROCEDURE in the Mimer SQL Reference Manual. If no routine access clause is specified, then CONTAINS SQL is assumed.

If the routine contains a SELECT statement, READS SQL DATA is required. (Or if a READS SQL DATA routine is called.)

If the routine contains a DELETE or an UPDATE statement, MODIFIES SQL DATA is required. (Or if a MODIFIES SQL DATA routine is called.)

Scope in Routines - the Compound SQL Statement

A compound SQL statement allows a sequence of procedural SQL statements to be considered as a single SQL statement, see COMPOUND STATEMENT in the Mimer SQL Reference Manual for a description of the syntax.

A routine body may contain only one executable SQL statement and the compound SQL statement allows a routine to be defined that can actually contain any number of SQL statements.

A compound SQL statement also defines a local scope in which variables, exception handlers, and cursors can be declared. Compound SQL statements may be nested, one within the other, and thus local scopes may be nested.

A compound SQL statement may be labeled, which effectively names the local scope defined by it. The label name can be used whenever the scope environment needs to be referred to explicitly, e.g. when qualifying the names of objects which have been declared in the compound SQL statement. The label name must not be the same as a routine name.

It is important to understand the effect of scoping on declared items, particularly with respect to: out-of-scope references to variables, see Declaring Variables, the scope within which an exception handler remains in effect and the flow of control effects following the use of different types of exception handler, see Declaring Exception Handlers.

The SQL statement LEAVE is specifically provided to give the programmer the ability to force the flow of control to exit from a labeled scope.

Example:
CREATE PROCEDURE some_procedure(INOUT y INTEGER)
CONTAINS SQL
s0:
BEGIN
   ...
s1:
   BEGIN
      IF y < 0 THEN
         SET y = 0;
         LEAVE s0;
      END IF;
      ...
   END s1;
   ...
END s0

In the example above, the effect of the LEAVE statement is to pass flow of control to the statement END S0, i.e. flow of control exits from the scope labeled S0.

All open cursors declared in a compound SQL statement are closed whenever flow of control leaves the compound SQL statement for any reason.

Note: A compound SQL statement may be preceded by a label which names the scope delimited by the BEGIN and END (this is called the beginning label). Specifying the label next to the END is optional. However, if a label is specified next to the END, the beginning label must be specified.

The ATOMIC Compound SQL Statement

The execution of any SQL statement, other than a procedure-control-statement, is atomic. See the Mimer SQL Reference Manual, Procedural SQL Statements, for a definition of a procedure-control-statement.

The execution of a compound SQL statement defined as ATOMIC is also atomic.

When the execution of an SQL statement is atomic, an atomic execution context becomes active while the statement, or any contained sub-query, is executing. While an atomic execution context is active, it is possible for another atomic execution context to become active within it.

While an atomic execution context is active the following is true:

  • It is not possible to explicitly terminate a transaction, thus all changes made within the atomic execution context occur within the same transaction.
  • If an SQL statement within the atomic execution context fails and no handler has been declared within it to handle the error, all the changes made within the atomic execution context will be cancelled and the associated exception will be propagated to the calling environment.
  • If an SQL statement within the atomic execution context fails and an UNDO handler has been declared within it to handle the error, all the changes made within the atomic execution context will be cancelled and the handler will be executed.

An atomic compound SQL statement is defined by specifying the keyword ATOMIC next to the BEGIN delimiter. The COMMIT and ROLLBACK statements cannot be used within an atomic compound SQL statement.

A compound SQL statement is explicitly defined as not being atomic by specifying NOT ATOMIC next to the BEGIN delimiter. If nothing is specified next to the BEGIN delimiter, NOT ATOMIC is assumed by default.

If the compound SQL statement contains a declaration for an UNDO exception handler, see Declaring Exception Handlers, the compound SQL statement must be ATOMIC.

Examples:
CREATE FUNCTION an_atomic_function(i INTEGER)
RETURNS INTEGER
BEGIN ATOMIC
   ...
   -- All statements executed between this BEGIN
   -- and END execute within the same active atomic
   -- execution context.
   -- UNDO exception handlers are permitted.
   -- No COMMIT or ROLLBACK allowed!
   ...
END

CREATE PROCEDURE a_non_atomic_procedure(i INTEGER)
BEGIN NOT ATOMIC
   ...
   -- This compound SQL statement is not atomic.
   -- COMMIT and ROLLBACK statements are permitted.
   -- No UNDO exception handlers allowed!
   ...
END

CREATE FUNCTION a_default_function(i INTEGER) RETURNS INTEGER
BEGIN
   ...
   -- This compound SQL statement is not atomic, by default.
   -- COMMIT and ROLLBACK statements are permitted.
   -- No UNDO exception handlers allowed!
   ...
END

Declaring Variables

It is possible to declare variables, cursors, condition names and exception handlers at the beginning of a compound SQL statement. These items can, therefore, be declared in a routine when a compound SQL statement is used for the routine body.

This section discusses the declaration of variables. Discussions about declaring the other items mentioned above can be found in the following sections:

  • cursors, see Using Cursors
  • condition names, see Declaring Condition Names
  • exception handlers, see Declaring Exception Handlers.

Variables of any data type supported by Mimer SQL may be declared. The name of a variable must be unique within the scope of its declaration and must not conflict with the name of any of the routine parameters.

Variable names can be a maximum of 128 characters in length and are case insensitive. See the Mimer SQL Reference Manual, Naming Objects, for further details.

Note: The data type for a variable must be specified explicitly, it is not possible to specify a domain.

More than one variable of the same type can be declared in a single variable declaration, see the examples below.

It is possible to specify an optional expression, which may be NULL, that defines the default expression for a variable declaration. The variable(s) created by the variable declaration are given the initial value derived from the default expression. If a default expression is not specified, the value NULL is assumed.

Examples:
DECLARE z INTEGER;
DECLARE x, y INTEGER DEFAULT 9;
DECLARE abx VARCHAR(50);
DECLARE a INTEGER DEFAULT NULL;

Note:	It is possible to declare a variable that has the same name as a column name in a table. All ambiguous references will be interpreted as a reference to a column name rather than a variable. It is therefore recommended that a suitable naming convention be adhered to that clearly distinguishes between the names of table columns and variables.

The name of a variable may be qualified in the normal way with the beginning label of the scope in which it has been declared.

Example:
CREATE PROCEDURE some_procedure(IN x INTEGER)
s0:
BEGIN
   DECLARE a, b INTEGER;
s1:
   BEGIN
      DECLARE b, c INTEGER;
      ...
   END s1;
s2:
   BEGIN
      DECLARE y INTEGER;
      ...
   END s2;
END s0

The qualified names for the variables in the preceding example are as follows:

S0.A, S0.B, S1.B, S1.C, and S2.Y.

The ROW Data Type

Mimer SQL supports a data type called the ROW data type. It can be used in a compound SQL statement in place of the data type specified when a variable is declared.

A variable that is declared as having the ROW data type implicitly defines a row value, which is a single construct that has a value that effectively represents a table row.

A row value is composed of a number of named values, each of which has its own data type and represents a column value in the overall row value.

A ROW data type can be defined either by explicitly specifying a number of field-name/data-type pairs or by specifying a number of table columns from which the unqualified names and data types are inherited.

ROW Data Type Syntax

The syntax for defining a ROW data type is:

The following points apply to the specification of a ROW data type:

  • A domain cannot be specified for data-type.
  • The value specified for data-type can be a ROW data type specification.
  • Two fields in the same ROW data type specification must not have the same name (this restriction applies equally to fields named by specifying a field-name value and those named by inheriting the unqualified name of a table column).
  • If table-name is specified without a list of column names, all the columns in the table are used to define fields in the ROW data type.

Using the ROW Data Type

A ROW variable field is referenced like this: variable-name.field-name.

A value may be assigned to one of the fields in a ROW variable in the same way as a value would be assigned to a variable declared with the same data type as the field. The data type of the field must be assignment compatible with the value being assigned to it.

If the declaration of a ROW variable does not include a DEFAULT clause, each field in the ROW variable is set to NULL initially.

The value of a field in a ROW variable may be used in the same way as any value of that type.

When a ROW data type is defined by specifying table columns, the names and data types of its fields are inherited from the columns in the table(s). Subsequently assigning values to the ROW variable will not affect the table(s) used to define the ROW data type.

A row value, which may be the value of a ROW variable, may be assigned to a ROW variable. The row value and the ROW variable are assignment-compatible if, and only if, both contain the same number of values and each value in the row value is assignment-compatible with the corresponding field in the ROW variable.

Two row values, one or both of which may be the value of a ROW variable, may be compared. The row values are comparison-compatible if, and only if, both contain the same number of values and each value in one is comparison-compatible with the corresponding value in the other.

A ROW variable may be used within a compound SQL statement in the following contexts:

  • As the only expression specified in a RETURN statement used in a result set procedure. The ROW variable must be assignment-compatible with the row value defined by the procedure VALUES clause.
  • As the only target variable specified in the INTO clause of a SELECT INTO statement. The row value selected must be assignment-compatible with the ROW variable and will be assigned to it.
  • As the only target variable specified in the INTO clause of a FETCH statement. The row value fetched must be assignment-compatible with the ROW variable and will be assigned to it.
  • As the procedure-variable or expression in a SET assignment statement (see the description above of assignment-compatibility involving ROW variables).
  • As an argument in a comparison (see the description above of comparison-compatibility involving ROW variables).

Row Value Expression

A row value expression is an expression that specifies a row value. The values that represent the column values of the row value expression are specified as value expressions in a comma-separated list that is delimited by parentheses.

A row value expression can be used in the following contexts:

  • As the only expression in a RETURN statement used in a result set procedure.
  • As the expression following the DEFAULT keyword in a DECLARE VARIABLE statement for a variable declared to have the ROW data type.
  • As a row value in a comparison or assignment operation.
Examples:
RETURN (24, 16, 'xyz', 11.3, x+4/9);

DECLARE rc ROW (a INTEGER, b INTEGER, s VARCHAR(10))
   DEFAULT (14, 27, 'hello');

IF rc = (14, 27, 'hello') THEN
   SET rc.s = 'bye';
END IF;

SET rc = (99, 105, 'new value');


Modules

A module is a collection of routines. All the routines in a module are created by declaring them when the module is created. Routines cannot be added to or removed from a module after the module has been created.

A module belongs to the schema in which it is created and all the routines contained in a module must belong to the same schema as the module.

The name of a routine in a module may be qualified in the normal way by using the name of the schema to which the routine belongs. The module name is never used to qualify the name of a routine.

Note: It is not possible to grant EXECUTE privilege on a module. In order to allow an ident to invoke a routine, whether it exists on its own or in a module, EXECUTE privilege on the routine must be granted to the ident.

When a module is dropped, all the routines in the module will be dropped as well. See Using DROP and REVOKE for a discussion of CASCADE effects on modules and routines.

The operations that may be performed on a module are:

  • CREATE MODULE
  • DROP MODULE
  • COMMENT ON MODULE

Refer to the Mimer SQL Reference Manual, SQL Statement Descriptions for a description of the SQL statements mentioned above, brief examples follow.

Examples:
CREATE MODULE module_1
   DECLARE PROCEDURE p1 ... ;
   DECLARE PROCEDURE p2 ... ;
   DECLARE FUNCTION f1 ... ;
   ...
END MODULE

COMMENT ON MODULE module_1 IS 'This is my example module';

DROP MODULE module_1 CASCADE;


SQL Constructs in Routines

The following SQL constructs are specifically provided for use in the body of a routine.

Assignment Using SET

The SET statement is used to assign a value to a variable declared in a routine or an output parameter of a procedure (i.e. a parameter with mode OUT or INOUT).

Examples:
SET a = 5;
SET x = NULL;
SET y = 11 + a;
SET d = CURRENT_DATE;
SET z = NEXT_VALUE OF Z_SEQUENCE;
SET x = CASE y WHEN 1 THEN y ELSE 0 END;

Conditional Execution Using IF

The IF statement provides a mechanism for conditional execution of SQL statements based on the truth value of a conditional expression.

Note: If the conditional expression includes (or equals) NULL, the conditional expression evaluates to false. Testing for the NULL value must be done by using IS NULL, see the Mimer SQL Reference Manual, The NULL Predicate.

A basic IF statement consists of a conditional expression followed by a list of one or more SQL statements in a THEN clause, which are executed if the conditional expression evaluates to true and, optionally, a list of one or more SQL statements in an ELSE clause which are executed if the conditional expression evaluates to false.

All of the predicates supported by Mimer SQL are allowed in the conditional expression of an IF statement - see the Mimer SQL Reference Manual, Predicates.

One or more IF statements can be nested, one within the other, by using an ELSEIF clause in place of the ELSE clause in the IF statement containing another.

The IF statement does not in any sense define a local scope, it is simply a mechanism for conditionally executing a sequence of SQL statements.

Once the SQL statements to be executed have been selected, they execute in the same way as any ordinary sequence of SQL statements. This point is particularly important when considering exception condition handling behavior, see Managing Exception Conditions.

Examples:
IF x > 50 THEN
   SET x = 50;
   SET y = 1;
ELSE
   SET y = 0;
END IF;

IF y IN (2,3,4) THEN
   ...
ELSE
   ...
END IF;

IF x > 50 THEN
   SET x = 50;
   SET y = 2;
ELSEIF x > 25 THEN
   SET y = 1;
ELSE
   SET y = 0;
END IF;

IF NOT EXISTS (SELECT *
                  FROM table_1) THEN
   ...
ELSE
   ...
END IF;

IF X > (SELECT c1
           FROM t1
           WHERE ... ) THEN
   ...
ELSE
   ...
END IF;

Conditional Execution - the CASE Statement

The CASE statement provides another mechanism for conditional execution of SQL statements. The CASE statement comes in two forms, a simple case and a searched case.

Simple Case

A simple case works by evaluating equality between one value expression and one or more alternatives of a second value expression. For example:

DECLARE y INTEGER;

CASE y
   WHEN 1 THEN ...
   WHEN 2 THEN ...
   WHEN 3 THEN ...
   ELSE ...
END CASE;
Searched Case

A searched case works by evaluating, for truth, a number of alternative search conditions. For example:

CASE
   WHEN EXISTS (SELECT *
                   FROM BILL) THEN ...
   WHEN x > 0 OR y = 1 THEN ...
   ELSE ...
END CASE;
About Case Statements

For both forms of the CASE statement the following is true:

  • A sequence of one or more SQL statements can follow the THEN clause for each of the conditional alternatives, in the same way as for an IF statement, even though only a single implied SQL statement is shown in the examples above.
  • Each alternative sequence of SQL statements in a CASE statement is treated in the same way, with respect to the behavior of exception handlers etc., as has already been described for sequences of SQL statements in an IF statement, see Conditional Execution Using IF.
  • Like the IF statement, the CASE statement simply provides a mechanism for selecting a sequence of SQL statements to execute. The CASE statement as a whole is not considered, in any sense, to be a single statement.
  • The conditional part of each WHEN clause is evaluated, working from the top of the CASE statement down. The SQL statements that are actually executed are those following the THEN clause of the first WHEN condition to evaluate to true. If none of the WHEN conditions evaluate to true, the SQL statements following the CASE statement ELSE clause are executed.

The presence of an ELSE clause in the CASE statement is optional and if it is not present (and none of the WHEN conditions evaluate to true) an exception condition is raised to indicate that a case was not found for the CASE statement.

Note: If it is desired that there be no operation performed and no exception condition raised if none of the WHEN conditions evaluate to true, then an ELSE clause should be specified as an empty compound SQL statement.

Only the single selected sequence of SQL statements that follow a THEN or the ELSE is executed before the CASE statement terminates. There is no potential fall-through to subsequent THEN sequences as is found in case statements in some other programming environments.

Note: The CASE statement is distinct from the CASE expression - see the Mimer SQL Reference Manual, CASE and Mimer SQL Reference Manual, CASE Expression.

Iteration

The following sections describe how you can use iteration.

Iterating through a result set - FOR loop

A for loop can be used to iterate through all records in a result set and perform some operations for each record. This is a vast simplification compared to using a cursor.

A simple example of a for loop is

FOR SELECT surname, forname FROM customers
    WHERE customer_id IN
    (SELECT customer_id FROM orders
     WHERE datetime BETWEEN DATE '2006-01-01' AND DATE '2006-06-31') DO
     CALL orderStat(surname,forname);
END FOR

I.e. call the orderStat routine for each record in the customers table that fulfil the where criteria. Within the body of the for statement it is possible to reference the columns values as ordinary variables. This also means that each item in the select list must have a name and that name must be unique within the select list.

The body of the for statement is an atomic statement, which means that it cannot contain statements such as start, commit and rollback.

It is possible to use a result procedure in a for loop

FOR CALL coming_soon('Blues') DO
    IF producer IN ('Bill Vernon','Bill Ham') THEN
       INSERT INTO stats(format,release_date,...)
           VALUES (format,release_date,...);
    END IF;
END FOR

In this case the correlation names in the as clause of the result procedure definition can be used as variable names in the body of the for loop.

The select or call statement in the for loop can be labelled and this label can be used to qualify variable references.

l1: BEGIN
    DECLARE forname CHAR(12);
    ...
    FOR l2 AS SELECT forname FROM customers DO
        IF l1.forname <> l2.forname THEN
            ...
        END if;
    END FOR;
END

The label used cannot be the same as any label of a compound statement enclosing the for loop.

Iteration Using LOOP

The LOOP statement may be preceded by a label that can be used as an argument to LEAVE in order to terminate the loop. The LOOP statement can contain a sequence of one or more SQL statements that are executed, in order, repeatedly.

The iteration is terminated by executing the LEAVE statement, or if an exception condition is raised.

Example:
s1:
LOOP
   IF ... LEAVE s1;
   ...
END LOOP s1;
Iteration Using WHILE

The WHILE statement may be preceded by a label that can be used as an argument to LEAVE in order to terminate the while loop. The WHILE statement can contain a sequence of one or more SQL statements that are executed, in order, repeatedly.

The WHILE statement includes a conditional expression and iteration continues as long as this expression evaluates to true. Iteration may also be terminated by executing the LEAVE statement, or if an exception condition is raised.

Example:
SET i = 0;
s1:
WHILE i <= 10 DO
   ...
   SET i = i + 1;
END WHILE s1;
Iteration Using REPEAT

The REPEAT statement may be preceded by a label that can be used as an argument to LEAVE in order to terminate the repeat loop. The REPEAT statement can contain a sequence of one or more SQL statements which are executed, in order, repeatedly.

The REPEAT statement includes an UNTIL clause, which specifies a conditional expression, and iteration continues until this expression evaluates to true. Iteration may also be terminated by executing the LEAVE statement, or if an exception condition is raised.

Example:
SET i = 0;
s1:
REPEAT
   ...
   SET i = i + 1;
UNTIL i > 10
END REPEAT s1;
Using ITERATE to Skip Statements

You can use an ITERATE statement to skip the remaining statements in an iteration as shown in the following examples:

SET x = 0:
s1:
REPEAT
   SET x = x + 1;
   ...
   IF x < 10 THEN
      ITERATE s1; -- execution continues at the beginning
                  -- of the repeat statement
   END IF;
   ...
UNTIL x = 20 END REPEAT s1;
Using ITERATE in all Iteration Statements

You can use ITERATE in all iteration statements in stored procedures. ITERATE is not restricted to the innermost statement. For example:

   SET x = 0;
s1:
   REPEAT
      SET x = x + 1;
s2:
      BEGIN
s3:
         LOOP
            ...
            IF x < 10 THEN
               ITERATE s1;
            ELSEIF x < 20 THEN
               ITERATE s3;
            END IF;
            ...
         END LOOP s3;
      END s2;
   UNTIL x = 20
   END REPEAT s1;

Note:	The statement ITERATE S1 will cause an implicit leave of the compound statement labeled S2.

Invoking Procedures and Functions

The following sections discuss invoking procedures and functions.

Invoking Procedures - CALL

The CALL statement is used to invoke a procedure. The name of the procedure may be qualified with the name of the schema to which it belongs. A value expression or target variable must be specified for each of the procedure's parameters, see the Mimer SQL Reference Manual, Target Variables, for the definition.

If the procedure parameter has mode OUT or INOUT, a target variable must be specified. For procedure parameters with mode IN, a value expression may be specified.

SQL/PSM is not strongly typed, so the expression specified for each procedure parameter need not have exactly the same data type as the parameter, however the expression must be assignment-compatible with the procedure parameter for which it is supplied, see the Mimer SQL Reference Manual, Assignments, for a discussion of assignment and implicit data type conversions.

Examples:
CALL PROC1( );

CALL PROC2(x, y);

CALL IDENT1.PROC7(CURRENT_DATE, x+3, z);
Invoking Functions

Functions are not invoked by calling them explicitly. A function is invoked, and it returns its value, when it is used in a procedure-control-statement or in an assignment where a value-expression would normally be used.

The name of the function may be qualified with the name of the schema to which it belongs.

The following restrictions apply:

  • If the function is defined as NOT DETERMINISTIC, it must not be used in the expression that follows the CASE keyword or the WHEN keyword in the simple case form of a CASE statement, see Conditional Execution - the CASE Statement.
  • If MODIFIES SQL DATA has been specified for the access-clause of the function, it must not be used in the expression following the DEFAULT keyword in a DECLARE VARIABLE statement.
Examples:
IF fn(x) > 70 THEN
   ...
ELSE
   ...
END IF;

SET v_Artist = Mimer_Store_Music.ArtistName(p_RecordedBy) || '%';

IF Mimer_Store.Index_Text(Data.Title) LIKE v_Title THEN
   ...
END IF;

Comments in Routines

Any text that occurs after -- and before end-of-line in a routine is taken to be a comment.

Example:
CREATE PROCEDURE tstproc(y INTEGER)
-- This is a comment: Note that Y has mode IN (default)
READS SQL DATA
BEGIN
   DECLARE b INTEGER;
   -- Here is another comment
   SET b = y + 22;  -- Y is input to the procedure
   ...
END

Restrictions

The following groups of SQL statements may not be used in a routine:

  • Access Control statements
  • Data Definition statements
  • Connection statements
  • ESQL Control statements
  • Security Control statements
  • Dynamic SQL statements
  • System Administration statements.

Refer to the Mimer SQL Reference Manual, SQL Statement Descriptions for a definition of the statement groups mentioned above.

Note: Any SQL statements used in a routine must be executable, so the usual restriction on the use of SELECT versus SELECT INTO applies (only the latter being considered executable - the former may, however, be used in a conditional expression, e.g. in an IF statement or a cursor declaration).

The following restrictions apply to result set procedures:

  • A COMMIT or ROLLBACK statement must not be executed in a result set procedure because it will interfere with the open cursor that will exist in the context from where the result set procedure is called.
  • A function or procedure that executes a COMMIT or ROLLBACK statement must not be invoked from within a result set procedure.
  • A function or procedure that has MODIFIES SQL DATA specified for its access clause must not be invoked from within a result set procedure.


Manipulating Data

The following sections discuss how to use write operations, cursors and SELECT INTO when manipulating data.

Write Operations

You can use INSERT, UPDATE and DELETE statements in a function or procedure provided MODIFIES SQL DATA has been specified for the access clause, see Routine Access Clause.

You can use routine parameters and variables in these statements wherever an expression can normally be used, as shown in the examples below.

Example:
CREATE PROCEDURE mimer_store_book.add_title(IN p_book_title VARCHAR(48),
                                            IN p_authors VARCHAR(128),
                                            IN p_published_by VARCHAR(48),
                                            IN p_format VARCHAR(20),
                                            IN p_isbn CHAR(18),
                                            IN p_date_released CHAR(10),
                                            IN p_price NUMERIC(7, 2),
                                            IN p_stock SMALLINT,
                                            IN p_reorder_level SMALLINT)
-- Add the details for a book entity; inserts against the join view which fires
-- the instead of trigger
MODIFIES SQL DATA
BEGIN
   -- Insert into join view
   INSERT INTO mimer_store_book.details(title, authors_list, publisher,
                                        format,isbn, release_date,
                                        price, stock, reorder_level)
      VALUES (p_book_title, p_authors, p_published_by, p_format,
              p_isbn, p_date_released, p_price, p_stock,
              p_reorder_level);
END  -- of routine mimer_store_book.add_title
ROW_COUNT Option

You can use the ROW_COUNT option of the GET DIAGNOSTICS statement may be used immediately after an INSERT, UPDATE, DELETE, SELECT INTO or FETCH statement to determine the number of rows affected by the preceding statement.

Example:
DECLARE v_rows INTEGER;
...
INSERT INTO mimer_store_book.details ...;
GET DIAGNOSTICS v_rows = ROW_COUNT;
IF v_rows > 0 THEN

Note:	All SQL statements except GET DIAGNOSTICS will overwrite the information in the diagnostics area.

Using Cursors

You can declare and use cursors in a compound SQL statement to receive a result set from a query-expression or from a result set procedure.

A cursor may not have the same name as another cursor declared in the same scope.

Cursors in a procedural usage context are used in much the same way, in terms of the SQL statements used, as cursors declared outside routines. It is possible to open cursors, fetch data into variables and use the statements UPDATE and DELETE WHERE CURRENT OF cursor.

Example 1
BEGIN
   DECLARE data ROW AS (mimer_store_book.details(title, authors_list, format,
                                                 price, item_id, product_id,
                                                 display_order));
   DECLARE c_1 CURSOR FOR SELECT title, authors_list, format,
                                 price, item_id, product_id,
                                 display_order
                             FROM mimer_store_book.details
                             NATURAL JOIN mimer_store_book.authors
                             NATURAL JOIN mimer_store_book.keywords
                             WHERE title_search LIKE TRIM(TRAILING '0' FROM
                                mimer_store.product_search_code(p_book_title))
                                || '%'
                             AND keyword LIKE REPLACE(
                                mimer_store_book.authors_name(p_author),
                                ',', '%,')
                                || '%'
                             ORDER BY title, authors_list, product_id,
                                      display_order;

   DECLARE EXIT HANDLER FOR NOT FOUND
      CLOSE c_1;
Example 2
DECLARE D DATE DEFAULT CURRENT_DATE;
DECLARE C1,C2 CHAR(5);
DECLARE Z SCROLL CURSOR FOR CALL PROC(1,D);
DECLARE I INTEGER;
...
OPEN Z;
...
FETCH FIRST FROM Z INTO C1;
...
FETCH ABSOLUTE I FROM Z INTO C2;

FETCH ABSOLUTE I FROM Z INTO C2;

The example 1 demonstrates detection of the NOT FOUND exception as a method of checking that a fetch statement does not return any data. If a NOT FOUND exception occurs in the example, an exit handler is invoked. After the exit handler has finished, the flow of control leaves the compound SQL statement labeled L1.

Alternatively, the GET DIAGNOSTICS statement can be used to retrieve the number of rows affected by the FETCH statement, as shown below.

Example:
DECLARE ROWS INTEGER;
L1:
LOOP
   FETCH X INTO I_CHARGE_CODE,I_AMOUNT;
   GET DIAGNOSTICS ROWCNT = ROW_COUNT;
   IF ROWCNT = 0 THEN
      LEAVE L1;
   END IF;
END LOOP;
CLOSE X;
Restrictions

The following specific restrictions apply to cursors used in routines:

  • no dynamic functions can be used (i.e. extended cursor names and the use of SQL descriptors)
  • REOPENABLE cursors are not allowed
  • the use of the keyword RELEASE with the CLOSE statement is not permitted.

Using FETCH to get result set data from a result set procedure may cause parts of the result set procedure to execute, see Result Set Procedures. The result set procedure will be in use until the associated cursor is closed.

SELECT INTO

Another way of fetching data is by using a SELECT INTO statement. This can only be used when one single record is fetched from the database. If more (or less) than one record fulfills the search criteria, an exception condition is raised.

Example:
SELECT currency, v_euros * exchange_rate
   INTO p_local_currency, p_local_price
   FROM mimer_store.customers
         JOIN mimer_store.countries AS cnt ON cnt.code = country_code
   JOIN mimer_store.currencies AS crn ON crn.code = currency_code
   WHERE customer_id = p_customer_id
   AND currency_code <> 'EUR'
   AND exchange_rate IS NOT NULL;

Transactions

It is possible to start and end transactions within a routine. A transaction is implicitly started when a routine that accesses the database is invoked.

It is also possible to explicitly start a transaction by using the START statement. When a transaction is ended, either by a COMMIT or ROLLBACK statement, all open cursors are closed.

Example:

START;
UPDATE table
   SET ...
   WHERE col = v_str, ...
...
COMMIT;

It is possible to affect the behavior of transactions by using the SET TRANSACTION and SET SESSION statements.

Note: If a compound SQL statement is defined as ATOMIC, a transaction cannot be terminated within it because execution of the COMMIT or ROLLBACK statements is not permitted.


Result Set Procedures

A result set procedure is a special type of procedure that allows a result set to be returned.

A result set procedure is called by specifying it in a cursor declaration and then using FETCH to get the result set data.

In interactive SQL, a result set procedure is called by using the CALL statement and the result set data is dealt with in the same way as a select.

Example (ESQL):
EXEC SQL DECLARE c_1 CURSOR FOR CALL result_proc(1, 5);

A result set procedure is distinguished when it is created or declared by a VALUES clause which follows the parameter part of the procedure definition.

The VALUES clause defines the data types of the columns in the result set and may contain an AS clause which names the columns.

Example
CREATE PROCEDURE barcode(IN p_ean BIGINT)
-- Result set procedure that returns book or music details for the given EAN
VALUES (VARCHAR(48), VARCHAR(48), VARCHAR(20), NUMERIC(7, 2), INTEGER)
AS (title, creator, format, price, item_id)
READS SQL DATA
BEGIN
   ...
END

All result set procedure parameters have mode IN, therefore, any data returned from a result set procedure is returned via the procedure's result set.

The option MODIFIES SQL DATA must not be specified for the access clause of a result set procedure, see Routine Access Clause.

Note: A function or procedure that has MODIFIES SQL DATA specified for its access clause must not be invoked from within a result set procedure.

A result set procedure must not execute a COMMIT or ROLLBACK statement, because this would close the cursor that is used in order to call the result set procedure.

Note: A function or procedure that executes a COMMIT or ROLLBACK statement must not be invoked from within a result set procedure.

A row in the result set of a result set procedure is returned by executing the RETURN statement. The arguments to a RETURN statement can be NULL, an expression or a variable which has the ROW data type.

When a FETCH is executed, the SQL statements in the body of the result set procedure are executed until a RETURN statement is executed.

The execution of the result set procedure is then suspended until the next FETCH statement is executed for the calling cursor, then flow of control within the result set procedure continues until the next RETURN statement is encountered, or until the end of the procedure is reached.

After flow of control has exited from the scope of a result set procedure the next attempt to FETCH more data into the calling cursor will flag end-of-set.

Thus, a result set procedure call can be used in place of the usual SELECT when declaring a cursor.

The following example, using ESQL, is intended to demonstrate how execution within the result set procedure proceeds, and is suspended, in response to FETCH statements being executed for the calling cursor:

EXEC SQL
   CREATE PROCEDURE result_proc(x INTEGER)
   VALUES (VARCHAR(10), INTEGER)
   AS (txt, xp)
   CONTAINS SQL
   BEGIN
      DECLARE xp INTEGER DEFAULT x;

      RETURN ('FIRST ROW', xp);

      SET xp = x * 2;
      RETURN ('SECOND ROW', xp);

      SET xp = x * 3;
      RETURN ('THIRD ROW', XP);
   END;

EXEC SQL DECLARE c_1 CURSOR FOR CALL result_proc(3);
EXEC SQL OPEN c_1;

EXEC SQL WHENEVER NOT FOUND GOTO done;

EXEC SQL FETCH c_1
            INTO :T, :X;
(This will fetch 'FIRST ROW', 3)
Result set procedure flow of control suspended at XP=X*2

EXEC SQL FETCH c_1
            INTO :T, :X;
(This will fetch 'SECOND ROW', 6)
Result set procedure flow of control suspended at XP=X*3

EXEC SQL FETCH c_1
            INTO :T, :X;
(This will fetch 'THIRD ROW', 9)
Result set procedure flow of control suspended at END;

EXEC SQL FETCH c_1
            INTO :T, :X;
Flow of control exits from procedure scope and the NOT FOUND exception is
raised.

done:
EXEC SQL CLOSE c_1;

More typically, a loop construct would be used in the result set procedure to deal with RETURN statements. It is also permissible to use a cursor within the result set procedure to get data to be returned via a SELECT.

Closing the cursor for a result set procedure will close any open cursors declared within it and no further execution of the procedure will occur.

Reopening the cursor will start execution of the result set procedure afresh from the beginning (i.e. no state information is saved between a close and reopen).


Managing Exception Conditions

An exception is raised if an error occurs when executing an SQL statement. Every exception is identified by an exception condition, expressed in terms of its SQLSTATE value.

About SQLSTATES

An SQLSTATE value is represented by the keyword SQLSTATE followed by a 5-character string containing only uppercase alphanumeric characters. The first two characters of the string identify the exception class and the last three the exception sub-class.

In Mimer SQL, the range of possible SQLSTATE values is divided into standard values and implementation-defined values. The implementation-defined values are those beginning with the characters J-R, T-Z, 5-6 and 8-9. For a list of the values, see Mimer SQL Reference Manual, Appendix E, SQLSTATE Return Codes.

Whenever an exception is raised, the exception condition is placed in the diagnostics area and the SQLSTATE value can be retrieved by using the RETURNED_SQLSTATE option of GET DIAGNOSTICS.

Condition Names

In addition to expressing an exception condition in terms of its SQLSTATE value, it is possible (within a compound SQL statement) to declare a condition name to represent it.

Whenever a condition name is used, it is immediately translated into the SQLSTATE value it represents. For more information, see Declaring Condition Names.

SIGNAL Statements

It is possible to raise an exception without an error occurring by using the SIGNAL statement. When the SIGNAL statement is used, the specified exception condition is placed in the cleared diagnostics area, expressed as its SQLSTATE value, and control proceeds as if an error had just occurred.

Example:
SIGNAL SQLSTATE 'UE456';

Exception Handlers and Actions

It is possible to declare exception handlers in a compound SQL statement that perform some action when exceptions are raised. The action defined by the exception handler is associated with one or more specific exception conditions, or one or more exception class groups, specified when the exception handler is declared. For more information, see Declaring Exception Handlers.

If there is an exception handler action defined for an exception condition that is raised, the exception handler action is performed and execution continues in the manner defined by the type of the exception handler.

If no exception handler action has been defined for an exception condition that is raised, the default error handling mechanism is invoked (which usually makes the exception condition visible to the calling environment).

If the exception NOT FOUND or an SQLWARNING is raised in an unhandled situation, execution will continue and the exception will be cleared by execution of the next statement in the procedure. The GET DIAGNOSTICS statement can be used to test for the NOT FOUND exception and an SQLWARNING.

RESIGNAL Statements

It may be necessary for an exception handler action to re-raise the current exception condition or to raise an alternative exception condition. The RESIGNAL statement is provided for this purpose and it may only be executed from within an exception handler.

If RESIGNAL is executed without specifying an exception condition, the current exception condition remains in the diagnostics area and the error handling mechanism proceeds to deal with the error as if the current exception handler action had not been found.

If an exception condition is specified (in the same way as for SIGNAL), this is pushed onto the top of the stack of exceptions in the diagnostics area, becoming the current SQLSTATE value, and the error handling mechanism proceeds as just described.

The size of the exceptions stack in the diagnostics area is set by using the SET TRANSACTION DIAGNOSTICS SIZE statement, see Exception Diagnostics Within Transactions.

Use of RESIGNAL is useful in situations where there are nested exception handler actions defined and it is required that an enclosing exception handler action be invoked from an inner one, or where the default error handling mechanism is to be allowed to proceed from some point within a defined exception handler action.

Examples:
RESIGNAL;

RESIGNAL SQLSTATE 'UE456';

Declaring Condition Names

As discussed in the previous section, exception conditions are identified by an SQLSTATE value. Whenever an exception is raised, the exception condition that identifies it is stored in the diagnostics area in the form of its SQLSTATE value.

It is always possible to specify an exception condition by using its SQLSTATE value, e.g. SQLSTATE VALUE 'S0700', however it is often desirable to declare a condition name that represents the SQLSTATE value in a way that more meaningfully describes the exception.

Condition names may be declared in a compound SQL statement, see the Mimer SQL Reference Manual, COMPOUND STATEMENT, for a detailed description.

Example:
DECLARE invalid_parameter CONDITION FOR SQLSTATE 'UE456';
...
SIGNAL invalid_parameter;

Following this declaration, the condition name INVALID_PARAMETER can be used instead of the SQLSTATE value SQLSTATE VALUE 'UE456' whenever there is a need to refer to this exception condition.

If a condition name is used in a signal statement the associated SQLSTATE value and the condition name is placed in the diagnostics area. If the condition does not have an associated SQLSTATE value, the SQLSTATE value 45000 is used. A condition is always local to a routine, i.e. consider the following example:

create procedure p2()
begin
    declare condition c1;
    ...
    signal c1;
end

create procedure p1()
begin
    declare condition c1;
    declare exit handler for c1
    begin
        ...
        call p2();
        ...
    end
end

In this case the exit handler in the procedure p1 will not be invoked when the statement signal c1 is executed.In order to catch a signaled condition the associated SQLSTATE must be used. The condition identifier can be propagated by using a RESIGNAL statement.

All SQLSTATE values in Mimer SQL that lie outside the range of standard values are treated as implementation-defined, so all SQLSTATE values are handled in the same way and may be specified explicitly in all situations.

Declaring Exception Handlers

Exception handlers may be declared in a compound SQL statement in order to define an action which will be executed if specified exceptions are raised within the scope of the exception handler.

The structure of the handler action is the same as the body of a routine, i.e. a single executable procedural SQL statement. The exceptions to which the handler action will respond may be specified as a list of exception conditions or by specifying one or more exception class groups.

The exception class groups are:

  • SQLWARNING covers SQLSTATE values beginning with 01.
  • NOT FOUND covers SQLSTATE values beginning with 02.
  • SQLEXCEPTION covers all other SQLSTATE values (including those in the implementation defined range), excluding those beginning with 00.

An exception handler that is declared to respond to one or more exception class groups is referred to as a general exception handler.

An exception condition may be specified by its SQLSTATE value or a condition name declared to represent it. An exception handler which is declared to respond to one or more specific exception conditions is referred to as a specific exception handler.

The same exception condition must not be specified more than once in the same exception handler declaration.

An exception handler can either be a general exception handler or a specific exception handler, i.e. an exception handler declaration cannot contain both exception class groups and specific exception conditions.

Exception handlers are declared in the local handler declaration list of a compound SQL statement and the scope of an exception handler is that compound SQL statement plus all the SQL statements contained within it except when another routine is invoked. When a user defined routine is invoked all exception handlers in the calling routine will get out of scope and they will get into scope again when the invoked routine has finished executing, e.g:

CREATE PROCEDURE innerMost(INT x)
BEGIN
    -- no handlers in this routine
    IF  x > 0 THEN
        SIGNAL SQLSTATE 'UE345';
    ELSE
        SIGNAL SQLSTATE 'UE543';
    END IF;
END

CREATE PROCEDURE outerMost()
BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE 'UE345' BEGIN END;
    DECLARE CONTINUE HANDLER FOR SQLSTATE 'UE543' BEGIN END;
    CALL innerMost(0);
    CALL innerMost(1);
    CALL innerMost(2);
END

When the signal statement with exception UE543 in the innerMost routine is executed, the execution of this routine will be stopped as there is no handlers declared. The exception will the be propagated to the outerMost routine which has a continue handler for this exception. This means that the execution will proceed with the next call statement. This will cause a new exception (UE345) being signalled. Again this exception will be propagated to the calling routine and the first exception handler will be invoked. As this is an exit handler the execution will continue after the end of the compound statement in the outerMost routine, i.e. the statement call innerMost(2) will never be executed.

The exception handler will be executed if one of the exceptions it is declared to respond to is raised within the scope of the handler.

A local handler declaration list can only contain one exception handler declared to respond to a particular exception condition or exception class group.

It is possible to declare a general and a specific exception handler, both of which cover the same scope, where an exception condition specified for the specific handler is in one of the exception class groups specified for the general handler. If the exception condition is raised in this situation, the specific handler is executed in preference to the general handler.

It is possible for the scope of two specific exception handlers, which respond to the same exception condition, to overlap. This will be the case if there are two nested compound SQL statements and each declares a specific exception handler for the same exception condition (this is permitted, provided the two exception handlers are not declared in the same local handler declaration list). In this situation the innermost exception handler action will be executed.

The same is true for two general exception handlers in this situation.

The RESIGNAL statement can be used in situations like this, in the inner exception handler action, to get the outer exception handler action to execute by propagating the exception out from the exception handler action which is currently executing.

Types of Exception Handlers

Exception handlers fall into the following types:

· Exit Handler

This type of exception handler will execute when the exception condition(s) that apply to it are raised. After the handler has executed, flow of control exits the scope of the compound SQL statement containing the exception handler declaration, by effectively performing a LEAVE, see Scope in Routines - the Compound SQL Statement.

· Continue Handler

This type of exception handler will execute when the exception condition(s) that apply to it are raised. After the handler has executed, flow of control continues by executing the SQL statement immediately following the SQL statement that raised the exception.

· Undo Handler

The execution of this type of handler will be initiated when the exception condition(s) that apply to it are raised. Before the handler action executes, all changes made by the executed SQL statements in the compound SQL statement, or by any SQL statements triggered by them, are canceled. The handler action is then executed and flow of control exits the scope of the compound SQL statement containing the exception handler declaration, by effectively performing a LEAVE, see Scope in Routines - the Compound SQL Statement.

Note: An UNDO exception handler can only be declared in a compound SQL statement that has been defined as ATOMIC, see The ATOMIC Compound SQL Statement.

Examples of Exception Handlers

s1:
    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            ...
        END;
        ...
    END s1;

s2:
    BEGIN
        DECLARE CONTINUE HANDLER FOR SQLSTATE 'S0700'
        BEGIN
            ...
        END;
        ...
    END s2;

s3:
    BEGIN
        DECLARE EXIT HANDLER FOR SQLSTATE 'S0700'
        BEGIN
            ...
        END;
        ...
s4:
        BEGIN ATOMIC
            DECLARE UNDO HANDLER FOR SQLSTATE 'S0700'
            BEGIN
                ...
            END;
            ...
        END s4;
        ...
    END s3;

Using the GET DIAGNOSTICS Statement

The GET DIAGNOSTICS statement can be used in a general exception handler to get the specific SQLSTATE value that provoked execution of the exception handler.

Example:
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
 BEGIN
     DECLARE v_state CHAR(5) DEFAULT '?????';
     GET DIAGNOSTICS EXCEPTION 1 v_state = RETURNED_SQLSTATE;
     CASE v_state
         WHEN '22003' THEN ...
         WHEN '20000' THEN ...
         ELSE RESIGNAL;
     END CASE;
 END;  -- of sqlexception handler

Note that GET DIAGNOSTICS must be the first statement in the exception handler as the diagnostics area always contains information about the latest statement.

The GET DIAGNOSTICS statement can also be used to get information about warnings and not found exceptions.

Example:
SELECT format into v_format FROM formats where category_id = v_category_id;
GET DIAGNOSTICS EXCEPTION 1 v_state = RETURNED_SQLSTATE;
IF  v_state = '02000' THEN
-- not found
ELSE
-- found
END IF;

It is not meaningful to use the GET DIAGNOSTICS statement to check for errors after a statement.

Example:
BEGIN
    DECLARE v_state CHAR(5);
    DECLARE EXIT HANDLER FOR sqlexception
    BEGIN
        ...
    END;
    INSERT INTO format(format,category_id) VALUES (v_format,v_category_id);
    GET DIAGNOSTICS EXCEPTION 1 v_state = RETURNED_SQLSTATE;
END

If an exception occurs in the insert statement this will be catched by the exception handler and as this is an exit handler the execution will resume after the compound statement. Thus, the diagnostics statements will never be invoked in this case. Even if it is a continue handler the get diagnostics statement is superfluous as the handler would clear the diagnostics information.


Access Rights

The ident creating a routine must, as is usual, have the appropriate access rights on the tables and other database objects referenced from the SQL statements in the routine. The creating ident must also have the right to create objects in the schema to which the routine is to belong (i.e. the ident must be the creator of the schema).

The right of the creator to access referenced database objects is verified when the CREATE FUNCTION, CREATE MODULE or the CREATE PROCEDURE statement is executed.

If an ident wishes to invoke a routine, that ident must have EXECUTE privilege on the routine.

Note: In order for the creator of a routine to grant EXECUTE privilege on the routine to another ident, the creator must have the WITH GRANT option in affect for all the access rights held on all the database objects referenced within the routine.

The above note is an important security point, because granting EXECUTE privilege on a routine is effectively granting appropriate access rights to the given ident on all the database objects referenced in the routine, therefore all those access rights must be held by the grantor with the WITH GRANT option.

An ident may be granted EXECUTE privilege on a routine with the WITH GRANT option and if this option is in affect, the ident may grant EXECUTE privilege on that routine to other idents.

Routines can be used as a security layer in the database. By having EXECUTE privilege on a routine granted, an ident only gets the right to perform the specific operations specified in the routine and not general access to the referenced database objects.

Note: It is not possible to grant EXECUTE privilege on a module, only on routines.


Using DROP and REVOKE

Care must be taken if database objects are dropped with the CASCADE option, and when REVOKE is performed, particularly with respect to routines and modules.

It is important to bear in mind the following points in connection with modules and routines:

  • Dropping an object referenced by an SQL statement in a routine will cause the routine to be dropped.
  • If the access rights on a database object are revoked from the creator of a routine that contains an SQL statement referencing the object, the routine will be dropped.
  • If a routine belonging to a module is dropped because of the effects of a cascade, the routine is effectively removed from the module (i.e. the module is not dropped).

If an ident attempts to drop a routine for which there is a compiled version currently being held by another ident, the DROP operation will fail because the routine is in use.

When a routine is invoked, it is compiled and the compiled version of the routine is held by the invoking ident. Any other idents invoking a routine while a compiled version of it exists will use the existing compiled version and this will be held by them as well.

A compiled version of a routine will generally be held by an ident until the ident disconnects. If the routine invocation is contained in a dynamic SQL statement, deallocating the statement will release the compiled version of the routine immediately without the need for a disconnect.


The Mimer SQL PSM Debugger

Using Mimer SQL's Java-based graphic PSM Debugger, you can select and run a stored function or stored procedure in an environment that enables you to:

  • view the stored source of the routine
  • watch the values of the input parameters and declared variables
  • observe the results of executing the routine.

You can interactively set breakpoints in the routine being debugged or in other routines or triggers invoked by this routine. When a breakpoint is set the execution will halt at this line, if encountered in the flow of execution.

You can execute a routine in step-wise fashion from the beginning or from the point at which a breakpoint halts execution.

When execution of a routine is interrupted or when a routine is executed in a step-wise fashion, an indicator next to the source line will show the current flow of control position.

Requirements

The PSM Debugger requires a Java 2 (version 1.2 or later) compatible Java runtime environment.

UNIX: The PSM Debugger is a Java-based program and must be used in an X-Windows environment.

VMS: If you are running an OpenVMS version older than 7.3, you can download a Java 2 kit from:
http://www.compaq.com/java/download

Win: You can download the Java runtime environment files from:
http://java.sun.com/products/jdk/CurrentRelease

Starting the PSM Debugger

To start the PSM Debugger:

UNIX: Enter the following command:

java -jar /usr/lib/psmdebug.jar

On Linux and Mac OS X, the PSM Debugger can also be started from the Start Menu.

VMS: Enter the following command:

$java -jar mimexe9:psmdebug.jar

Win: Click Start, navigate to where you installed Mimer SQL and select SQL-PSM Debugger.

The PSM Debugger window opens and the log in dialog box is displayed.

Logging In

Logging in to the Mimer SQL PSM Debugger establishes a connection to a database server and enables you to access the routines stored in the database.

To log in:
  1. In the login dialog box, enter the following details:

    2. Database

    The name of a database that is accessible from the node you are running on. The syntax for the database URL in the login dialog box is:

    hostname[:port]/database

    If the database resides on your local machine, specify localhost as the host name.

    For example:

    localhost/testdb

    Username

    The name of the ident you wish to use to access the database.

    Password

    The password to be used for the specified ident.

  2. Click OK to connect to the database, or Cancel to quit the Mimer SQL PSM Debugger.

    3. Once you have logged in to the Mimer SQL PSM Debugger, you will be accessing the database as the ident you specified in the log in dialog box and you can choose a routine to debug by selecting it from the Choose a procedure drop-down list.

Choosing a Routine

Choose a stored function or stored procedure to debug by selecting it from the drop-down list.

The list displays the routines for which you hold EXECUTE privilege. When you have selected a routine, you must specify values for all the input parameters.

Specifying the Input Parameters

When you select a routine with input parameters, a dialog box will list the name and data type of each input parameter and you must specify values for them.

When you have specified values for all the input parameters, click OK to view the source for the routine, or click Cancel to go back to selecting a routine from the drop-down list.

Viewing the Source Code for a Routine

When you have selected a routine and specified the input parameter values, the routine source is displayed.

It is possible to watch the values of declared variables and routine parameters declared as input or input/output by selecting them from the Variable details drop-down list.

You can set a breakpoint on a line of the routine source by clicking on the indicator to the left of the source line in the source window.

Watching Variables and Input Parameters

Once you have selected a variable or parameter from the drop-down list, its name and current value (when defined) are shown in the table below the list.

The watched values are updated in the table as the routine executes.

Setting Breakpoints

To set a breakpoint in a routine or trigger invoked by the current routine, you can choose a routine or a trigger from the list in the Breakpoints menu.

When you click on the indicator to left of a source line in the source window, the indicator will change color.

If the indicator is red, there is a breakpoint set on that source line which will halt execution of the routine when it is encountered.

If the indicator next to a source line is white, then no breakpoint is set on that line.

Executing a Routine

To execute a routine:
  • Click Go to execute the routine to the end or to the next breakpoint.
  • Click Step Into to execute the next line. If this line contains a call statement or a function invocation, the execution will halt at the first line in the invoked routine.
  • Click Step Over to execute the next line. This mode will not stop the execution if there is a routine invocation, unless there is a breakpoint set.
  • Click Cancel to force continuous routine execution to stop.

Whenever the value of a watched variable or parameter changes, the value shown for it in the table will be updated and will be displayed in red. Unchanged values will be displayed in black.

When execution of a routine is halted by a breakpoint, flow of control is positioned just before execution of the source line on which the breakpoint is set. An arrow to the left of the source line shows where execution has been halted.

To continue executing a routine that has been halted at a breakpoint, click Go, Step Into or Step Over.

Whenever execution of a routine is halted, because a breakpoint was encountered or during step-wise execution, the source line at which the routine is halted appears with an arrow to the left of it. The current flow of control position is just prior to execution of that source line.

The results of executing the routine are shown in the window below the routine selection drop-down list. The Mimer SQL PSM Debugger gives the same results feedback during execution of a routine as Mimer BSQL.


Triggers

This chapter discusses triggers: how to create them, execute them and drop them.

A trigger defines an SQL statement that is to be executed before, after or instead of a specified data manipulation operation on a named table or view.

The execution of the SQL statement can be made conditional on the evaluation of a search condition.

The SQL statement is typically a compound SQL statement, thus allowing a number of SQL statements to be executed by the trigger. The compound SQL statement must be defined as ATOMIC.

Thus, the body of a trigger is similar to the body of a routine and the same constructs may be used within it.


Creating a Trigger

A trigger is created by using the CREATE TRIGGER statement, see the Mimer SQL Reference Manual, CREATE TRIGGER.

Example:
CREATE TRIGGER products_after_insert AFTER INSERT ON products
REFERENCING NEW TABLE AS pdt
FOR EACH STATEMENT
BEGIN ATOMIC
   -- Force the update trigger to fire
   UPDATE products
      SET product_search = DEFAULT
      WHERE product_id IN (SELECT product_id
                              FROM pdt);
END -- of trigger products_after_insert

A trigger is created on a named table or view and the trigger must be created in the schema to which the table or view belongs. If the trigger is created on a base table, the table must be stored in a databank with the TRANS or LOG option.

The trigger name must follow the rules for naming private database objects, see the Mimer SQL Reference Manual, Naming Objects, and the name must be unique within the schema in which the trigger is created.

You can create any number of triggers on a named table, each of which may have the same trigger time, see Trigger Time, and trigger event, see Trigger Event, specified.

If two or more triggers exist on the same table with the same trigger time and trigger event, they will be executed in an order determined by their internal creation timestamp.

Note: When triggers are created using BSQL, the create statement must be delimited by the @ character.


Trigger Time

The trigger time specifies when, in relation to the execution of the triggering data manipulation statement, the trigger is executed.

The possible values for the trigger time are:

· AFTER

This specifies that the trigger will be executed following the execution of the triggering data manipulation statement.

· BEFORE

This specifies that the trigger will be executed prior to the execution of the triggering data manipulation statement.

· INSTEAD OF

This specifies that the trigger will execute when the triggering data manipulation statement would normally be executed. In this case the triggering data manipulation statement itself has no direct effect, it only causes the trigger to execute.

If the trigger time specified is AFTER or BEFORE, the table name must specify a base table which is located in a databank with the TRANS or LOG option.

If the trigger time specified is INSTEAD OF, the table name must specify a view.

Example:

Example of an instead of trigger, which can be used for handling join views.

CREATE TRIGGER book_details_instead_of_update
   INSTEAD OF UPDATE ON mimer_store_book.book_details
REFERENCING  NEW TABLE AS new_bd
BEGIN ATOMIC
--
-- Update one table with some of the data from the join view
--
   UPDATE titles
      SET authors_list = (SELECT authors_list
                            FROM new_bd
                           WHERE item_id = titles.item_id)
    WHERE item_id IN (SELECT item_id
                        FROM new_bd);
--
-- Update another table using another column from the join view
--
  UPDATE producers
     SET producer_name = (SELECT publisher
                            FROM new_bd
                           WHERE item_id = producers.producer_id)
   WHERE producer_id IN (SELECT item_id
                           FROM new_bd);
END


Trigger Event

The trigger event specifies the data manipulation statement that will cause the trigger to execute. The possible values for the trigger event are: INSERT, UPDATE and DELETE.

The trigger will be executed once each time the specified data manipulation statement is executed on the table on which the trigger was created.

Note: If the trigger time is INSTEAD OF, the trigger event itself has no effect on the table, it just causes the trigger to execute. The environment executing the trigger event behaves as if the data manipulation statement is actually being executed, even though no changes actually occur in the table(s) that would normally be affected. The only data manipulations possible in this case are those performed by the trigger action.


Trigger Action

The trigger action, like the body of a routine, consists of a single procedural SQL statement. In addition, the execution of the SQL statement can be made conditional on the evaluation of a search condition.

The search condition is specified in the optional WHEN clause of the CREATE TRIGGER statement.

As for routines, it is recommended that a compound SQL statement always be used for the trigger action.

Note: The entire trigger action must be executed in a single atomic execution context, therefore if a compound SQL statement is used, it must be defined as ATOMIC, see The ATOMIC Compound SQL Statement.

The SQL statement(s) of the trigger action are always executed within the transaction started for the trigger event. The normal restrictions on the use of certain procedural SQL statements within a transaction apply.

In addition, because the trigger action must be atomic, a COMMIT or ROLLBACK statement cannot be executed within it.

The creator of the trigger must hold the appropriate access rights, with grant option, for all the operations performed within the trigger action. This is checked when the CREATE TRIGGER statement is executed.

If the trigger time specified for the trigger is BEFORE, the following restrictions apply to the trigger action:

  • the trigger action must not contain any SQL statement that performs an update (i.e. DELETE, INSERT and UPDATE statements are not permitted)
  • a routine whose access clause is MODIFIES SQL DATA must not be invoked from within the trigger action.

If an exception is raised from the trigger action, it can be handled within the trigger by declaring a handler in the normal way for a compound SQL statement, see Declaring Exception Handlers.

If there is no handler declared in the trigger action to handle the exception, it will propagate to the environment executing the trigger event and will be dealt with appropriately there. The default behavior at that level will be to undo the effect of the trigger event and all the operations performed in the trigger action.

It is possible to explicitly raise an exception from within the trigger action, or from within an exception handler declared in it, by executing the SIGNAL statement.

New and Old Table Aliases

It is possible, by using the optional REFERENCING clause in the CREATE TRIGGER statement, to specify the existence of two special tables called the old table alias and the new table alias.

The name of each table is specified separately in the REFERENCING clause and they must not have the same name. Both tables are accessible from within the trigger action.

Each specified table is created just prior to the execution of the trigger and each is local to the entire scope of the trigger action, including the optional WHEN clause.

Note: A REFERENCING clause is not permitted in a CREATE TRIGGER statement which has BEFORE specified for the trigger time.

The old table alias and new table alias contain copies of those database table rows affected by the data manipulation operation that caused the execution of the trigger (i.e. the trigger event).

In the case of a trigger executing AFTER the trigger event, the alias tables show the database table rows actually affected by the trigger event.

In the case of a trigger executing INSTEAD OF the trigger event, the alias tables show the database table rows that would have been affected by the trigger event had its data manipulations actually been performed.

The old table alias shows each affected database table row in the state it was in before normal execution of the trigger event.

The new table alias shows how each affected table row appears (or would appear, in the case of INSTEAD OF triggers) after normal execution of the trigger event.

If the trigger event was an INSERT, the old table alias cannot be specified. If the trigger event was a DELETE, the new table alias cannot be specified.

The rows of the alias tables are created before the trigger executes and do not change during execution of the trigger.

If the trigger action contains data manipulation statements that attempt to alter the alias tables, an error will be raised when the CREATE TRIGGER statement is executed.

Altered Table Rows

When the rows of the database table on which the trigger was created are examined from within the trigger action, they will always reflect the actual data manipulations performed by the trigger event and the trigger action.

In the case of an AFTER trigger, all rows inserted by the trigger event will be visible, all rows deleted by the trigger event will not be found and all rows updated by the trigger event will appear in their altered state.

In the case of an INSTEAD OF trigger, none of the data manipulations specified by the trigger event will seen when the table is examined because the trigger event does not actually perform any of its data change operations.

The rows of the old table alias and the new table alias will always show the changes that were specified by the trigger event, even if these changes were not actually performed on the database table (as is the case for INSTEAD OF triggers).

Recursion

Any data manipulation statements occurring in a trigger action will be executed in the normal way. It is, therefore, possible that the execution of a data manipulation statement in the trigger action may lead to the execution of another trigger or the recursive execution of the current trigger.

In either case, the execution context of the current trigger action is preserved and newly-invoked trigger executes in the normal way, in its own execution context, with appropriate versions of any old table alias and new table alias, see New and Old Table Aliases.

Example:

The following trigger is called recursively. An update statement causes the trigger to fire even when no rows are updated, hence the presence of a when clause to avoid an infinite recursive invocation.

CREATE TRIGGER products_after_update
   AFTER UPDATE ON products
REFERENCING NEW TABLE AS pdt
WHEN ( EXISTS (SELECT * FROM pdt) )
BEGIN ATOMIC
   UPDATE products
      SET product_search = product_search_code(product),
          product = (SELECT capitalize(TRIM(product))
                       FROM pdt
                       WHERE product_id = products.product_id)
     WHERE product_id IN (SELECT product_id FROM pdt
                            WHERE product_search <>
                                      product_search_code(products.product)
                            OR product <> capitalize(TRIM(products.product));
END


Comments on Triggers

The COMMENT ON TRIGGER statement can be used to create a comment on a trigger.

Only the creator of the schema to which the trigger belongs may create a comment on the trigger.


Using DROP and REVOKE

The following points apply to triggers when using DROP and REVOKE with triggers:

  • A trigger can be dropped by using the DROP TRIGGER statement.
  • Only the creator of the trigger can drop it using the DROP TRIGGER statement.
  • When a trigger is dropped, the comments created on it are also dropped.
  • Dropping an object referenced from an SQL statement in a trigger action will cause the trigger to be dropped.
  • If the required privileges held on a database object are revoked from the creator of a trigger whose trigger action contains an SQL statement referencing the object, the trigger will be dropped.


Handling Errors and Exceptions

Errors may arise at three general levels in an embedded SQL (ESQL) program (not counting errors in the SQL-independent host language code). These are syntax, semantic and run-time errors.

See Managing Exception Conditions for information about managing exception conditions in routines and triggers.


Syntax Errors

Syntax errors are constructions that break the rules for formulating SQL statements. For example:

· Spelling errors in keywords:

SLEECT instead of SELECT

· Incorrect or missing delimiters:

DELETEFROM instead of DELETE FROM

SELECT column1;column2 instead of SELECT column1,column2

· Incorrect clause ordering

UPDATE ... WHERE ... SET instead of UPDATE ... SET ... WHERE

The preprocessor does not accept syntactically incorrect statements. The error must be corrected before the program can be successfully preprocessed.


Semantic Errors

Semantic errors arise when SQL statements are formulated in full accordance with the syntax rules, but do not reflect the programmer's intentions correctly.

Some semantic errors, e.g. incorrect references to database objects, are detected and reported by the ESQL preprocessor but other semantic errors will not become apparent until run-time.


Run-time Errors

Run-time errors and exception conditions (for example warnings) arising during execution of ESQL statements are signaled by the contents of the SQLSTATE status variable described in The SQLSTATE Variable. A list of possible SQLSTATE values is provided in SQLSTATE Return Codes.

The GET DIAGNOSTICS statement can be used to retrieve detailed information about an exception, see the Mimer SQL Reference Manual, GET DIAGNOSTICS, for the syntax description.

The NATIVE_ERROR and MESSAGE_TEXT fields of the diagnostics area retrieved by using GET DIAGNOSTICS are used to get the internal Mimer SQL return code and the descriptive text, respectively, relating to the exception, these are listed in Native Mimer SQL Return Codes.

Testing for Run-time Errors and Exception Conditions

The application program may test the outcome of a statement in one of two ways:

  • by explicitly testing the content of the SQLSTATE variable
  • by using the SQL statement WHENEVER, see the Mimer SQL Reference Manual, WHENEVER for the syntax description, which tests the class of the SQLSTATE variable.

An application program may contain any number of WHENEVER statements, and the statements may be placed anywhere in the program. A separate WHENEVER statement must be issued for each situation (NOT FOUND, SQLERROR or SQLWARNING) which is to be tested.

When an exception condition arises, action will be taken as specified in the WHENEVER statement most recently encountered in the code, for the respective condition.

WHENEVER statements are expanded by the preprocessor into explicit tests. These tests are placed after every subsequent SQL statement in that program until a new WHENEVER statement is issued for the same condition.

Two important consequences follow:

  • WHENEVER statements are preprocessed strictly in the order in which they appear in the source code, regardless of execution order or conditional execution that the source code might imply.

    • For instance, the WHENEVER statement in the following FORTRAN construction is expanded by the preprocessor, even though its execution is never actually requested:

         ...
      GOTO 1025
      EXEC SQL WHENEVER SQLERROR GOTO 1600
1025  CONTINUE
      EXEC SQL DELETE FROM MYTABLE
     ...
  • Mixing explicit tests and WHENEVER statements requires care. As a general rule, it is advisable to use either hand-written tests or WHENEVER statements in a program module, and to avoid mixing them.

    • The condition handling defined by a WHENEVER statement applies to the SQL statements that follow it in the source code. If a GOTO action is defined, the pre-processor inserts an exception test and action directly after each SQL statement affected by it and thus before any hand-written tests in the source code. The hand-written test in this situation would never be executed.

    If CONTINUE is specified in a WHENEVER statement, the pre-processor does not insert an exception test and action, thus no exception handling is defined by the WHENEVER statement. Any hand-written tests present in the source code will then take effect.

    The interchange between hand-written exception handling and the implicit exception handling inserted by the pre-processor (or not) can be confusing. It is therefore advisable to make a clear coding decision to use one method or the other.

Example:
void print_sqlerror()
/*
**  print_sqlerror prints an error message for the latest error.
*/
{
    exec sql BEGIN DECLARE SECTION;
    int i;
    int exceptions;
    varchar message[255];
    exec sql END DECLARE SECTION;

    exec sql GET DIAGNOSTICS :exceptions = NUMBER; /* How many exceptions? */
    for (i=1; i<=exceptions; i++) {
        exec sql GET DIAGNOSTICS EXCEPTION :i
                                           :message = MESSAGE_TEXT;
        printf("%s\n", message);
    }
}


Host Language Dependent Aspects

You can use embedded SQL (ESQL) statements in any of the following host languages:

  • C/C++
  • COBOL
  • FORTRAN

See Host Languages for list of the ESQL preprocessors available for a specific platform.

This appendix describes features of ESQL that differ between the respective host languages.

Note: It is not a complete description of the rules for writing ESQL programs. The programmer should use the main body of this manual as a guide to writing programs, and refer to this appendix for language-specific details.

The following topics are discussed for each language:

  • SQL statement format: delimiters, margins, line continuation, comments, special characters.
  • Restrictions.
  • Host variables - declarations, SQL data type correspondence, value assignment rules.
  • Preprocessor output format.
  • Scope rules.


ESQL in C/C++ Programs

Mimer SQL supports ESQL for C/C++ following the ISO/ANSI standard.

SQL Statement Format

The following sections discuss the SQL statement format.

Statement Delimiters

SQL statements are identified by the leading delimiter EXEC SQL and terminated by a semicolon ;, for example:

EXEC SQL DELETE FROM countries;
Line Continuation

Line continuation rules for SQL statements are the same as those for ordinary C statements.

For a string constant, a white-space character (ASCII HEX-values 09 - 0D, or 20, i.e. <TAB>, <LF>, <VT>, <FF>, <CR> or <SP>), can be used to join two or more sub-strings. Each substring must be separately enclosed in delimiters. For example:

EXEC SQL COMMENT ON TABLE currencies IS 'Holds currency'<CR>
                                        ' details';
Comments

Comments, from // to end-of-line or enclosed between the markers /* and */, may be written anywhere within SQL statements where a white-space is permitted, except between the keywords EXEC and SQL and within string constants. The comment may replace the white-space, for example:

EXEC SQL DELETE/* all rows */FROM countries;
Special Characters

The delimiters in SQL are single quotation marks ' for string constants and double quotation marks " " for delimited identifiers. This is contrary to the C string delimiter usage.

A white-space character separates keywords.

Host Variables in C/C++

The following sections discuss declarations, SQL data type correspondence and value assignments.

Declarations

Host variables used in SQL statements must be declared within the SQL DECLARE SECTION, delimited by the statements BEGIN DECLARE SECTION and END DECLARE SECTION.

Variables declared within the SQL DECLARE SECTION must conform to the following rules in order to be recognized by the SQL preprocessor:

  • host variables may be of AUTO, EXTERN or STATIC class
  • array variables are not permitted with the exception of character arrays
  • character arrays are interpreted as null terminated strings
  • the VARCHAR host variable data type is recognized by the ESQL/C preprocessor and should be used when variable-length character data is to be returned from SQL as a null terminated string without any blank padding (the VARCHAR host variable should be declared with a length one greater than the length of the column, because of the null termination).
  • the NCHAR host variable data type is recognized by the ESQL/C preprocessor and should be used when Unicode data is to be returned from SQL as a null terminated string with blank padding. The NCHAR host variable should be declared with a length one greater than the length of the column, because of the null termination.
  • the NCHAR VARYING host variable data type is recognized by the ESQL/C preprocessor and should be used when variable-length Unicode data is to be returned from SQL as a null terminated string without any blank padding. The NCHAR VARYING host variable should be declared with a length one greater than the length of the column, because of the null termination.
  • where binary data is stored in a character array, the size of the array must match the length of the binary data exactly because binary data is not terminated and therefore all array elements are significant
  • variable names are case significant
  • indicator variables should be declared as short or int
  • SQLSTATE should be declared as char[6] or VARCHAR[6]
  • Only data types CHAR, VARCHAR, NCHAR, NCHAR VARYING, BLOB, CLOB and NCLOB can be indexed.

When reading any character array host variable, declared as CHAR, VARCHAR, NCHAR or NCHAR VARYING, the contents of the variable must be null terminated. When a host variable declared as CHAR or NCHAR is read, its value is blank padded to the same length as the host variable. When a host variable declared as VARCHAR or NCHAR VARYING is read, no blank padding is performed.

When any type conversion is done when retrieving a numeric value to a fixed length character host variable, i.e. CHAR or NCHAR, the data will be right justified. When type conversion is done when retrieving a value to a variable length character type host variable, i.e. VARCHAR or NCHAR VARYING, the data will be left justified.

A syntax diagram showing the variable declarations recognized by the ESQL/C preprocessor is given below:

where character-declaration is:

and numeric-declaration is:

and lob-declaration is:

The following points should be noted:

  • In accordance with the syntax rules of C, keywords are case-sensitive and are given in the required case in the syntax diagram. This deviates from the general practice in Mimer SQL documentation of using upper-case to denote keywords
  • Index must be a number which is 1 or greater
SQL Data Type Correspondence

Valid host data types are listed below for each of the data types used in SQL statements.

SQL data type
C variable declaration
SMALLINT
INTEGER
BIGINT

short
int
long
long long

DECIMAL
NUMERIC

float
double
long double

FLOAT
REAL
DOUBLE PRECISION

float
double
long double

CHARACTER
VARCHAR
DATETIME
INTERVAL
BINARY
BINARY VARYING

char
varchar 1

NCHAR
NCHAR VARYING

nchar 2
nchar varying3

BLOB

sql type is blob 4

CLOB

sql type is clob 5

NCLOB

sql type is nclob 6
1The varchar host variable type is recognized by the ESQL/C preprocessor and converted to the char data type in C.
2The nchar host variable type is recognized by the ESQL/C preprocessor and converted to the wchar_t data type in C.
3The nchar varying host variable type is recognized by the ESQL/C preprocessor and converted to the wchar_t data type in C.
4The blob host variable type is recognized by the ESQL/C preprocessor and converted to:

4struct {
long hvn_reserved;
unsigned long hvn_length;
char hvn_data[L];
} hvn

4 Where L is the numeric value of the large object length and hvn is the host variable name as specified in the lob-declaration.
5The clob host variable type is recognized by the ESQL/C preprocessor and converted to:

5struct {
long hvn_reserved;
unsigned long hvn_length;
char hvn_data[L];
} hvn

5 Where L is the numeric value of the large object length and hvn is the host variable name as specified in the lob-declaration.
6The nclob host variable type is recognized by the ESQL/C preprocessor and converted to:

6struct {
long hvn_reserved;
unsigned long hvn_length;
wchar_t hvn_data[L];
} hvn

6 Where L is the numeric value of the large object length and hvn is the host variable name as specified in the nclob-declaration

Note: Your C compiler may not support all of these possible declarations.

Value Assignments

The general rules for conversion of values between compatible but different data types, see the Mimer SQL Reference Manual, SQL Syntax Elements, apply to the transfer of data between the database and host variables, with the data type correspondence as given in the table above.

When reading any character array host variable, the contents of the variable must be null terminated. When a fixed length character host variable (char, nchar) is read, its value is blank padded to the same length as the host variable. When a variable length character host variable (varchar, nchar varying) is read, no blank padding is performed.

When retrieving a value shorter than a fixed length character array host variable, the host variable will be padded with blanks (and null terminated). When retrieving a value to a variable length character host variable, the variable will not be blank padded, just null terminated.

When retrieving binary data into a character array there is no padding or termination of the binary string, so all the character array elements have significance. The character array must, therefore, be declared with exactly the same length as the binary data.

If a numeric type conversion is done when retrieving a value to a fixed length character host variable, the data will be right justified. When type conversion is done when retrieving a value to a variable length character host variable, the data will be left justified.

Example:
char cstr[9];
VARCHAR vstr[9];

retrieving the value 'abc ' will give the following result:

cstr = 'abc     ' /* blankpadded to eight characters */
vstr = 'abc '     /* the same length as the value */

retrieving the value 123, the values will be as:

cstr = '     123' /* right justified */
vstr = '123'      /* left justified */

See the Mimer SQL Reference Manual, Characters, for a further discussion of different character string assignments.

Preprocessor Output Format

Output from the ESQL/C preprocessor retains SQL statements from the original source code as comments. Comments on the same line as SQL statements are retained as `comments within comments', marked by the delimiters /+ and +/.

The preprocessed code is structured to reflect the structuring of the original source code.

The use of the #line directive will ensure that any information from the C compiler will correctly reference line numbers in the original source code. It will also help a debugger correctly coordinate display of source lines in the original source file and the generated C file. Refer to information on running ESQL for the platform you are using for details on how to get #line directives.

Scope Rules

Host variables follow the same scope rules as ordinary variables in C. SQL descriptor names, cursor names and statement names must be unique within the compilation unit. A compilation unit for C is the same as a file (including included files).


ESQL in COBOL Programs

Mimer SQL supports ESQL for COBOL following the COBOL-85 ANSI standard.

SQL Statement Format

The following sections discuss the SQL statement format.

Statement Delimiters

SQL statements are identified by the leading delimiter EXEC SQL and terminated by
END-EXEC.

SQL statements are treated exactly as ordinary COBOL statements with regard to the use of an ending period to mark the end of a COBOL sentence. Any valid COBOL punctuation may be placed after the END-EXEC terminator.

Examples:
EXEC SQL DELETE FROM countries END-EXEC.

IF SQLSTATE NOT = "02000" THEN
            EXEC SQL COMMIT END-EXEC
ELSE
            EXEC SQL ROLLBACK END-EXEC.
Margins

Statements (including delimiters) may be written anywhere between positions 8 and 72 inclusive.

Line Continuation

Line continuation rules for SQL statements are the same as those for ordinary COBOL statements.

If a string constant within an SQL statement is divided over several lines, the first non-blank character on the continuation line must be a string delimiter. There is no terminating string delimiter at the end of the line preceding the continuation line.

Example:
EXEC SQL SELECT CODE, CURRENCY
            FROM MIMER_STORE.CURRENCIES
            WHERE CODE LIKE :CURRENCY-CODE END-EXEC.
EXEC SQL COMMENT ON TABLE CURRENCIES IS
                    'Holds currency
-                   ' details' END-EXEC.

An alternative way to break a character string constant over several lines, is to use a white-space character (ASCII HEX-values 09 - 0D, or 20, i.e. <TAB>, <LF>, <VT>, <FF>, <CR> or <SP>), to join two or more substrings.

Each substring must be separately enclosed in delimiters.

EXEC SQL COMMENT ON TABLE CURRENCIES IS
                    'Holds currency'<CR>
                    ' details' END-EXEC.
Comments

Comment lines, marked by an asterisk (*) in position 7, may be written within SQL statements. The whole line following a comment mark is treated as a comment.

Debugging lines and page eject lines (marked by D and / respectively in position 7) are treated as comments by the preprocessor.

Special Characters

The delimiters in SQL are single quotation marks (') for string constants and double quotation marks (" ") for delimited identifiers. This is contrary to the default COBOL string delimiter usage.

Observe that the minus sign (-) is valid in variable names in COBOL. All arithmetic expressions using this operator should have at least one space separating the operands from the operator. For example:

:A - B means: variable called A minus column B

:A-B means: variable called A-B

Restrictions

The following restrictions apply specifically to COBOL:

  • END-EXEC is a keyword reserved to SQL.
  • COBOL figurative constants (such as ZERO and SPACE) may not be used as constants in SQL statements.

Host Variables in COBOL

The following sections discuss declarations, SQL data type correspondence, preprocessor output format and value assignments.

Declarations

Host variables used in SQL statements must be declared within the SQL DECLARE SECTION, delimited by the statements BEGIN DECLARE SECTION and END DECLARE SECTION.

Variables declared within the SQL DECLARE SECTION must conform to the following rules in order to be recognized by the SQL preprocessor:

  • variable names must begin with a letter. Within this restriction, any valid COBOL variable name may be used
  • host variable structures may not be used
  • the specifications JUSTIFIED, BLANK WITH ZERO, and OCCURS may not be used
  • the data type must be consistent with SQL data types as specified below
  • level number 01 or 77 should be used for all variable names that are used in SQL statements. Other levels may be used for program host variables, but they are not recognized by the preprocessor
  • FILLER entries are ignored for variables used in SQL statements
  • Indicator variables should be declared as PIC S9(4) COMP or PIC S9(9) COMP

A syntax diagram for COBOL variable declarations recognized by the ESQL/COBOL preprocessor is given below. Other declarations are ignored by the preprocessor:

Commas and semicolons may be used in accordance with standard COBOL practice.

The following abbreviations are accepted:

Abbreviation
Full term
PIC

PICTURE or PICTURE IS

USAGE

USAGE or USAGE IS

COMP

COMPUTATIONAL

SYNC

SYNCHRONIZED

Note: The PIC S9(n)9(m) formulation is not accepted.

SQL Data Type Correspondence

Valid host data types are listed below for each of the data types used in SQL statements.

Varying-length character string structures may be used in ESQL statements in COBOL programs. In assigning the value of such variables to columns, the current length of the string is used.

The variable name used in SQL statements is the name of the structure (level 01 declaration), not of the character string element (level 49).

SQL data type
COBOL data declaration
Comments
SMALLINT
INTEGER
BIGINT

01 name PIC S9(n) COMP.

1 £ n £ 9

DECIMAL
NUMERIC

01 name PIC S9(n)V9(m)
COMP-3.

1 £ n+m £ 15

FLOAT
DOUBLE PRECISION

01 name COMP-2.

REAL

01 name COMP-1.

CHARACTER
VARCHAR
DATETIME
INTERVAL
CLOB

01 name PIC X(n).

1 £ n

Value Assignments

The general rules for conversion of values between compatible but different data types, see the Mimer SQL Reference Manual, SQL Syntax Elements, apply to the transfer of data between the database and host variables, with the data type correspondence as given in the table above.

The first element in a varying-length character string structure is used to store the current length of the character string. When writing to the variable, the first element is updated with the current length of the variable. If the column value is longer than the variable, the value is truncated.

Preprocessor Output Format

Output from the ESQL/COBOL preprocessor retains SQL statements from the original source code as comments. Comments within SQL statements are retained exactly as written. The output follows the ANSI standard for record format, and should be compiled with a COBOL compiler set to accept ANSI standard.

Debugging lines and page eject lines (using D and / respectively in position 7) remain unchanged after preprocessing.

The preprocessed code is structured to reflect the structuring of the original source code.

Scope Rules

Host variables follows the same scope rules as ordinary variables in COBOL. SQL descriptor names, cursor names and statement names must be unique within the compilation unit. A compilation unit for COBOL is the same as a routine.


ESQL in FORTRAN Programs

Mimer SQL supports ESQL for ANSI FORTRAN-90 fixed format.

Source statements must be provided as fixed format, 80 byte records.

SQL Statement Format

The following sections discuss the SQL statement format.

Statement Delimiters

The leading delimiter EXEC SQL identifies SQL statements. The end of an SQL statement is marked by the end of the line when the following line does not begin with a continuation character. The FORTRAN-90 statement delimiter ; can also be used.

Example:
EXEC SQL DELETE FROM countries

Margins

Statements (including delimiters) may be written anywhere between positions 7 and 72 inclusive.

Line Continuation

Line continuation rules for SQL statements are the same as those for ordinary FORTRAN statements. The continuation character is any character except space and 0 (zero) in position 6. The FORTRAN limitation of a maximum of 19 continuation lines per statement does not apply within SQL statements.

For a string constant, a white-space character (ASCII HEX-values 09 - 0D, or 20, i.e. <TAB>, <LF>, <VT>, <FF>, <CR> or <SP>), can be used to join two or more substrings. Each substring must be separately enclosed in delimiters.

Examples:
     EXEC SQL SELECT CODE, CURRENCY
+                FROM MIMER_STORE.CURRENCIES
+                WHERE CODE LIKE :CODE

     EXEC SQL COMMENT ON TABLE CURRENCIES IS
+                        'Holds currency'<CR>
+                        ' details'
Statement Numbers

Any labeled SQL statement in the source code will generate a CONTINUE statement during preprocessing. Declarative SQL statements used before the first executable SQL statement should not be labeled.

Comments

Comment lines, marked by * or C in position 1, may be written within SQL statements. The whole line following a comment mark is treated as a comment, and the following line must either be another comment or follow the continuation rules given above.

Note: Lines that are completely blank are not treated as comments by the
ESQL/FORTRAN preprocessor. The absence of a continuation character indicates the end of the previous statement, and a completely blank line may be used to structure comments in the output from the preprocessor. See Preprocessor Output Format for details.

FORTRAN-90 style comments may also be used, marked by the ! character (the text between the ! and the end-of-line is treated as a comment).

Debugging lines (marked with a D in position 1) are treated as comments by the preprocessor.

Host Variables

The following sections discuss declarations, SQL data type correspondence, value assignments, preprocessor output format and scope.

Declarations

Host variables used in SQL statements must be declared within the SQL DECLARE SECTION, delimited by the statements BEGIN DECLARE SECTION and END DECLARE SECTION.

Variables declared within the SQL DECLARE SECTION must conform to the following rules in order to be recognized by the SQL preprocessor:

  • any valid FORTRAN variable name may be used.
  • variables must be scalar variables (i.e. they may not be elements of vectors or arrays).
  • implicit declaration by means of the IMPLICIT statement or default typing may not be used.
  • FORTRAN COMPLEX variables may not be used.
  • character variables must be declared with a fixed constant length. Expressions and variable length declarations (such as CHARACTER*(*)) may not be used.
  • indicator variables should be declared as INTEGER*2 or INTEGER*4.

A syntax diagram showing the variable declarations recognized by the ESQL/FORTRAN preprocessor is given below:

The data type declaration must be separated from the variable name by at least one space (which is not required in FORTRAN declarations outside the SQL DECLARE SECTION).

Thus the declaration:

    INTEGER*2A

is not recognized. The required formulation is:

    INTEGER*2 A

Lists of variables following a single default data type declaration are accepted. Any declarations in a list that are not valid in SQL contexts are ignored by the preprocessor. Thus, the following statement declares variables A and D as INTEGER*4 and B as INTEGER*2 for use in SQL statements, while the array C is ignored:

    INTEGER*4    A, B*2, C(10), D
SQL Data Type Correspondence

Valid host data types are listed below for each of the data types used in SQL statements.

SQL data type
FORTRAN data declaration
SMALLINT
INTEGER
BIGINT

INTEGER*2
INTEGER*4
INTEGER*8

DECIMAL
NUMERIC

REAL*4

FLOAT
DOUBLE PRECISION
REAL

REAL*8
DOUBLE PRECISION

CHARACTER
VARCHAR
DATETIME
INTERVAL
CLOB

CHARACTER*n

The following additional points should be noted:

  • FORTRAN does not support DECIMAL data types. A string of digits including a decimal point is interpreted as a REAL constant in FORTRAN. Exponential notation should always be used to specify floating point values in SQL statements.
  • DOUBLE PRECISION constants may be written with a D as the exponent marker in FORTRAN (e.g. 1.23D+02). The only permissible exponent marker within SQL statements is E (e.g. 1.23E+02).
Value Assignments

The general rules for conversion of values between compatible but different data types, see of the Mimer SQL Reference Manual, apply to the transfer of data between the database and host variables, with the data type correspondence as given in the table above.

Preprocessor Output Format

Output from the ESQL/FORTRAN preprocessor retains SQL statements from the original source code as comments. The output follows the ANSI standard for record format, and should be compiled with a FORTRAN compiler set to accept ANSI standard. Comments within SQL statements are retained exactly as written.

Completely blank lines between SQL statements and following comments cause the preprocessor to write the comments after the generated SQL call. Otherwise comments immediately following SQL statements are output before the generated call. Debugging lines (using D in position 1) remain unchanged after preprocessing.

The preprocessed code is structured to reflect the structuring of the original source code.

Scope Rules

Host variables follows the same scope rules as ordinary variables in FORTRAN.

SQL descriptor names, cursor names and statement names must be unique within the compilation unit.

A compilation unit for FORTRAN is the same as a routine.


Return Codes

Mimer SQL returns two kinds of return codes to an application, SQLSTATE and a native error code (aka. SQLCODE). The SQLSTATE variable returns a standardized, general error code, which gives a rough description of the status for the most recently executed SQL statement.

GET DIAGNOSTICS can be called to access the exception information stored in the diagnostics area that applies to the most recently executed SQL statement, see Run-time Errors.

The symbol <%> in the text of error messages listed in this chapter indicates the location of an identifier inserted at run-time


SQLSTATE Return Codes

SQLSTATE contains a 5-character long return code string that indicates the status of an SQL statement. These return codes are standardized following the established standards. Observe that not all standardized SQLSTATE return codes are used by Mimer SQL.

SQLSTATE values consists of two fields. The class field, which is the first two characters of the string, and the subclass field, which is the terminating three characters of the string.

List of SQLSTATE Values

:

Class
Subclass
Meaning

00

000

Successful completion

01

000

Warning

01

002

- disconnect error

01

003

- null value eliminated in set function

01

004

- string data, right truncation

01

005

- insufficient item descriptor areas

01

006

- privilege not revoked

01

007

- privilege not granted

01

008

- implicit zero-bit padding

01

S01

- error in row

01

S02

- option value changed

01

S05

- cancel treated as close

01

S06

- attempt to fetch before the result set returned the first rowset

01

S07

- fractional truncation

02

000

No data

07

000

Dynamic SQL error

07

001

- using clause does not match dynamic parameter specifications

07

002

- using clause does not match target specifications

07

003

- cursor specification cannot be executed

07

004

- using clause required for dynamic parameters

07

005

- prepared statement is not a cursor specification

07

006

- restricted data type attribute violation

07

007

- using clause required for result fields

07

008

- invalid descriptor count

07

009

- invalid descriptor index

07

00F

- invalid DATETIME_INTERVAL_CODE

08

000

Connection exception

08

001

- client unable to establish connection

08

002

- connection name in use

08

003

- connection does not exist

08

004

- server rejected the connection

08

006

- connection failure

08

S01

- communication link failure

09

000

Triggered action exception

0A

000

Feature not supported

0B

000

Invalid transaction initiation

0K

000

Resignal when handler not active

0W

000

Prohibited statement encountered during trigger execution

21

000

Cardinality violation

21

S01

- insert value list does not match column list

21

S02

- degree of derived table does not match column list

22

000

Data exception

22

001

- string data, right truncation

22

002

- null value, no indicator parameter

22

003

- numeric value out of range

22

005

- error in assignment

22

006

- invalid interval format

22

007

- invalid datetime format

22

008

- datetime field overflow

22

011

- substring error

22

012

- division by zero

22

015

- interval field overflow

22

018

- invalid character value for cast

22

019

- invalid escape character

22

023

- invalid parameter value

22

024

- unterminated C string

22

025

- invalid escape sequence

22

026

- string data, length mismatch

22

027

- trim error

22

029

- noncharacter in UCS string

23

000

Integrity constraint violation

24

000

Invalid cursor state

25

000

Invalid transaction state

25

S03

- transaction is rolled back

26

000

Invalid SQL statement name

27

000

Triggered data change violation

28

000

Invalid authorization specification

2E

000

Invalid connection name

2F

000

SQL routine exception

2F

003

- prohibited SQL-statement attempted

2F

005

- function executed no return statement

33

000

Invalid SQL descriptor name

34

000

Invalid cursor name

35

000

Invalid condition number

37

000

Syntax error or access violation
in PREPARE or EXECUTE IMMEDIATE *

3C

000

Ambiguous cursor name

40

000

- transaction rollback

40

001

- serialization failure

42

000

Syntax error or access rule violation

42

S01

- base table or view already exists

42

S02

- base table or view not found

42

S11

- index already exists

42

S21

- column already exists

42

S22

- column not found

44

000

WITH CHECK OPTION violation

45

000

Unhandled user-defined exception

HY

000

General error

HY

001

- memory allocation error

HY

003

- invalid application buffer type

HY

004

- invalid SQL data type

HY

007

- associated statement is not prepared

HY

008

- operation canceled

HY

009

- invalid use of null pointer

HY

010

- function sequence error

HY

011

- attribute cannot be set now

HY

012

- invalid transaction operation code

HY

013

- memory management error

HY

014

- limit on the number of handles exceeded

HY

015

- no cursor name available

HY

016

- cannot modify an implementation row descriptor

HY

017

- invalid use of an automatically allocated descriptor handle

HY

018

- server declined cancel request SQLCancel

HY

019

- non-character and non-binary data sent in pieces

HY

020

- attempt to concatenate a null value

HY

021

- inconsistent descriptor information

HY

024

- invalid attribute value

HY

090

- invalid string or buffer length

HY

091

- invalid descriptor field identifier

HY

092

- invalid attribute/option identifier

HY

093

- invalid parameter number

HY

095

- function type out of range

HY

096

- invalid information type

HY

097

- column type out of range

HY

098

- scope type out of range

HY

099

- nullable type out of range

HY

100

- uniqueness option type out of range

HY

101

- accuracy option type out of range

HY

102

- table type out of range

HY

103

- invalid retrieval code

HY

104

- invalid precision or scale value

HY

105

- invalid parameter type

HY

106

- fetch type out of range

HY

107

- row value out of range

HY

108

- concurrency option out of range

HY

109

- invalid cursor position

HY

110

- invalid driver completion

HY

111

- invalid bookmark value

HY

C00

- optional feature not implemented

HY

T00

- timeout expired

HY

T01

- connection timeout expired

IM

000

ODBC specific return codes

IM

001

- driver does not support this function

IM

008

- dialog failed

S0

000

ODBC 2.0 codes *

S0

001

- base table or view already exists *

S0

002

- base table not found *

S0

011

- index already exists *

S0

012

- index not found *

S0

021

- column already exists *

S0

022

- column not found *

S1

000

General error

S1

001

- memory allocation failure

S1

002

- invalid column number

S1

003

- program type out of range

S1

004

- SQL data type out of range

S1

008

- operation canceled

S1

009

- invalid argument value

S1

010

- function sequence error

S1

012

- invalid transaction operation code specified

S1

090

- invalid string or buffer length

S1

091

- descriptor type out of range

S1

092

- option type out of range

S1

093

- invalid parameter number

S1

095

- function type out of range

S1

096

- information type out of range

S1

097

- column type out of range

S1

098

- scope type out of range

S1

099

- nullable type out of range

S1

100

- uniqueness option out of range

S1

101

- accuracy option type out of range

S1

102

- table type out of range

S1

105

- direction option out of range

S1

106

- fetch type out of range

S1

107

- row value out of range

S1

108

- concurrency option out of range

S1

109

- invalid cursor position

S1

C00

- driver not capable

S1

T00

- timeout expired

* Return code will only be returned to ODBC applications executing in ODBC 2.0 mode.


Native Mimer SQL Return Codes

Here the native Mimer SQL return codes are listed together with the associated text message. See Run-time Errors for details on how to retrieve this information after an exception has been raised.

Sometimes the deprecated term SQLCODE is used when referring to the native return code.

The codes are grouped according to function as follows:

Code numbers
Functional group

> 0

Warnings and Messages

= 0

Success

100

No data

-100 to -999

ODBC Errors and Warnings

-10000 to -10999

Data-dependent Errors

-11000 to -11999

Limits Exceeded

-12000 to -12999

SQL Statement Errors

-14000 to -14999

Program-dependent Errors

-16000 to -16999

Databank and Table Errors

-18000 to -18999

Miscellaneous Errors

-19000 to -19999

Internal Errors

-21000 to -21999

Communication Errors

-22000 to -22999

JDBC Errors

-23000 to -23999

Mimload Errors

Corrective action is given in general terms for each group of codes. When reporting errors to Mimer support, make sure you include the native Mimer SQL return code.

Warnings and Messages

No corrective action is normally required for internal Mimer SQL return code values greater than zero.

Number
Explanation

54

Character string was truncated

53

Null values eliminated in set function

55

Insufficient item descriptor areas

56

Privilege not revoked

57

Privilege not granted

58

Zero bits were added to the binary string

90

Login failure

91

Soft enter performed

92

No cursor state was saved on stack

94

Message text not found

100

Row not found for FETCH, UPDATE or DELETE, or the result of a query is an empty table

100

No data - Item number is greater than the value of count

ODBC Errors and Warnings

These errors occur when ODBC calls to Mimer SQL fail for some reason.

Number
Explanation

-100

Illegal sequence

-101

Out of memory

-102

Option out of range

-103

Function not supported

-104

Connection not open

-105

Connection in use

-106

Invalid argument value

-107

Invalid transaction operation code

-108

Internal network buffer overflow

-109

Invalid C data type

-110

Invalid SQL data type

-111

Bad address

-112

Function already active

-113

Operation canceled

-114

Wrong number of parameters

-115

Use ODBC function SQLEndTran (or SQLTransact for ODBC 2 applications) to commit or rollback transaction

-116

Statement is not in a prepared state

-117

Invalid transaction state

-118

Unknown statement type

-119

Server data type not supported by client

-120

Unknown data type

-121

Invalid buffer length

-122

String data truncated

-123

Numeric data truncated

-124

Numeric value out of range

-125

Invalid numeric value

-126

Bad parameter passed to numeric package

-127

Invalid column number

-128

Database name mandatory

-129

Connect dialog failed

-130

Data truncated

-131

Invalid connection string attribute

-132

Invalid cursor state

-133

Invalid parameter number

-134

Descriptor type out of range

-135

Invalid type passed to DICOA3

-136

Function type out of range

-137

Invalid cursor name

-138

Duplicate cursor name

-139

Cursor hash table corrupt

-140

ODBC database control block chain corrupt

-141

Option type out of range

-142

Option value not supported

-143

Option not supported

-144

Invalid row or keyset size

-145

Invalid concurrency option

-146

Invalid fetch type

-147

Not a scrollable cursor

-148

Row position out of range

-149

Only one SQLPutData for fixed length parameter

-150

SQLPutData does not support block cursors

-151

Driver not capable

-152

Table type out of range

-153

Invalid string length

-154

Data type out of range

-155

Syntax error found in escape clause

-156

DDO buffer overflow

-157

Uniqueness option type out of range

-158

Accuracy option type out of range

-159

Column type out of range

-160

Scope type out of range

-161

Nullable type out of range

-162

Internal type mismatch

-163

Conversion between data types not supported

-164

Invalid date, time, or timestamp

-165

Restricted data type attribute violation

-166

Date, time, or timestamp data truncated

-167

Database has not been configured. Run Configure MIMER 7.1

-168

Translated native SQL string was truncated

-169

ODBC extension DATE, TIME or TIMESTAMP is not supported

-170

ODBC extension OUTER JOIN is not supported

-171

ODBC extension for procedure invocation is not supported

-172

Unrecognized first word in escape clause, expected 'CALL','FN','OJ','D','T' or 'TS'

-173

This server version does not support the used scalar function

-174

Unrecognized scalar function found in escape clause

-175

Argument missing in scalar function

-176

Too many arguments in scalar function

-177

Syntax error, incomplete escape clause

-178

Syntax error, unmatched apostrophe in string literal

-179

Syntax error, unmatched quote in delimited identifier

-180

Invalid data type specified in scalar function CONVERT

-181

Information type out of range

-182

Parameter type may only be used with procedures

-183

Parameter type out of range

-184

Update and delete where current fully supported (not simulated)

-185

Option value changed

-186

Static scrollable cursor used instead of keyset or dynamic cursor

-187

Error in row, please check next error code

-188

Cancel treated as FreeStatement/CLOSE

-189

Attempt to fetch before the result set returned the first rowset

-190

Invalid cursor position

-191

Unknown first parameter in scalar function TIMESTAMPADD

-192

Unknown first parameter in scalar function TIMESTAMPDIFF

-193

Bad parameter passed to datetime package

-194

Out of critical section objects

-196

Invalid attribute option identifier

-197

Attribute cannot be set now

-198

General error

-199

Invalid use of an automatically allocated descriptor

-200

Invalid descriptor field identifier

-201

Invalid descriptor index

-202

Associated statement not prepared

-203

Interval second fraction truncated

-204

Interval truncation error

-205

Interval convert error

-206

Year to month interval cannot be converted to a numeric value because it is not a single field

-207

Interval cannot be converted to a numeric value because it is not a single field

-208

Invalid interval literal

-209

Interval leading field truncation error

-210

Interval trailing field truncated

-211

Binary data truncated

-212

Binary truncation error

-213

Binary length invalid

-214

Binary data invalid

-215

Binary not supported

-216

Inconsistent descriptor information

-217

Cannot modify IRD

-218

Invalid use of null pointer

-219

Character data not hexadecimal

-220

Internal error

-221

Significant parts of datetime/interval string truncated

-222

Interval field truncation

-223

String data truncated

-224

Binary data truncated

-225

String data truncated

-226

Binary data truncated

-227

Internal error - Must put blob separately

-228

UNICODE string data conversion error

-229

LOB data larger than specified in SQL_LEN_DATA_AT_EXEC(x)

-230

Distributed transaction mode is active, but no transaction enlist has been performed. May be due to early transaction abort or illegal sequence of calls in application.

-232

Wide character data types not supported by server

-233

SQL_LEN_DATA_AT_EXEC(n) only allowed for long data types

-235

Maximum escape clause nesting depth reached

-236

The character string '%' could not be casted to an %

-237

An SQL % interval cannot be casted to a %

-238

A non-fractional part of a TIME or a TIMESTAMP was truncated

-300

Failed to read dictionary

-804

Unable to translate MSDTC transaction id to an XA transaction id (XID)

-805

Unable to perform distributed transaction enlist

-806

Invalid sequence of calls within ODBC to MSDTC interface routines

Data-dependent Errors

These errors arise when an SQL statement cannot be executed correctly because of the data content of variables, expressions, and so on in the statement. The appropriate corrective action is determined by the nature of the error and the specific context in the application program.

Number
Explanation

-10001

Transaction aborted due to conflict with other transaction

-10002

Transaction aborted due to conflict with in-doubt transaction Do not retry transaction until in-doubt state resolved

-10100

PRIMARY KEY constraint violated, attempt to insert NULL value in table <%>, column <%>

-10101

PRIMARY KEY constraint violated, attempt to insert duplicate key in table <%>

-10102

Domain constraint <%> violated for table <%>, column <%>

-10103

Table constraint <%> violated for table <%>

-10104

View constraint violation

-10105

Referential constraint <%> violated INSERT/UPDATE operation not valid for table <%>

-10106

INSERT/UPDATE operation not valid for table <%> UPDATE/DELETE operation not valid for table <%>

-10107

The result of a subquery or select into is more than one value

-10108

Result of SELECT INTO or EXECUTE INTO statement is a table of more than one row

-10109

Data type constraint violation (numeric value out of range)

-10110

UNIQUE constraint <%> violated for table <%>

-10111

Domain constraint <%> violated for CAST

-10199

Host variable type packed decimal is not supported

-10200

Reserved numeric operand found during data type conversion

-10201

Length error or incorrect value found during data type conversion

-10202

Division by zero attempted

-10203

Negative overflow occurred during data type conversion

-10204

Positive overflow occurred during data type conversion

-10205

Loss of significance occurred during data type conversion

-10207

Undefined value found during data type conversion

-10208

Restricted data type attribute violation

-10210

Error in assignment

-10211

Undefined value found during data type conversion

-10212

Overflow occurred during data type conversion

-10221

The NULL value cannot be assigned to a host variable because no indicator variable is specified

-10222

NULL not allowed for item descriptor area

-10250

Data type not supported

-10301

Loss of significance occurred in arithmetic operation <%>

-10302

Positive overflow occurred in arithmetic operation <%>

-10303

Negative overflow occurred in arithmetic operation <%>

-10304

Division by zero attempted

-10305

Bad parameter encountered in arithmetic operation <%>

-10306

Invalid input for numeric function

-10307

A corrupt numeric value was encountered

-10308

Binary data truncated

-10309

The binary strings are of unequal length

-10310

Invalid character value for CAST

-10311

String data truncated

-10312

Numeric value out of range

-10313

Illegal (negative) substring length

-10314

Like pattern escape character not followed by underscore or percent character

-10315

Length of like pattern escape character is not equal to 1

-10316

Data truncated

-10317

Invalid hexadecimal string

-10321

Datetime loss of significance

-10322

Datetime positive overflow

-10323

Datetime negative overflow

-10325

Bad parameter encountered in datetime operation

-10326

Datetime illegal operand

-10327

Invalid datetime value

-10328

Datetime subtype mismatch

-10329

Invalid datetime format

-10331

Interval loss of significance

-10332

Interval positive overflow

-10333

Interval negative overflow

-10335

Bad parameter encountered in interval operation

-10336

Interval illegal operand

-10337

Invalid interval value

-10338

Interval subtype mismatch

-10339

Invalid interval format

-10340

Length error for large object

-10341

Can not set attribute value for UDT instance as it is null

-10351

Character string contains non-ascii character

-10352

Character string literal contains illegal UNICODE character

-10354

Bad parameter encountered in UNICODE conversion routine

-10355

Bad length parameter encountered in UNICODE conversion routine

-10356

Character string not aligned on proper byte boundary

-10361

The used collation can not be loaded

-10401

Illegal character found when converting from UNICODE to ASCII

-10402

Illegal character found when converting from ASCII to UNICODE

-10403

The argument to the UNICODE_CHAR function is an invalid UNICODE value

-10451

Character or binary string too long

-10601

Invalid value for field of item descriptor area

-10602

Invalid datetime or interval code

-10603

PRECISION field of SQL descriptor cannot be set on non-numeric data types

Limits Exceeded

These errors arise when internal limits in the Mimer SQL system are exceeded. Some of the limitations are determined by installation-specific parameters, while others are fixed by Mimer SQL. In general, errors of this nature require either re-installation of the system with extended limitations or modification of the application program to reduce the system demands.

Contact your Mimer representative if you have difficulty avoiding errors of this nature.

Number
Explanation

-11001

Dynamic storage area exhausted in host level interface (DYNDE3)

-11002

Internal DB dynamic storage area (SQLPOOL) exhausted

-11011

Internal storage (SQLPOOL) for like pattern exhausted

-11012

Transaction list exhausted

-11013

Too many databanks referenced in statement (max 30)

-11014

Too many databanks active in transaction

-11047

The maximum number of recursive invocations has been exceeded

-11100

Internal limit exceeded : query stack

-11101

Internal limit exceeded : scan stack

-11102

Internal limit exceeded : generation stack

-11103

Internal limit exceeded : table descriptor list

-11105

Internal limit exceeded : patch table

-11106

Internal limit exceeded : label table

-11107

Internal limit exceeded : traversal stack

-11108

Internal limit exceeded : sco list

-11109

Internal limit exceeded : boolean stack

-11111

Internal limit exceeded : semantic stack

-11113

Internal limit exceeded : statement too complex

-11114

Required temporary table row length is <%>, only <%> is possible

-11115

Internal limit exceeded : restriction group pool

-11118

Internal limit exceeded : scan queue

SQL Statement Errors

These errors arise from syntactic or semantic errors in SQL statements. In general, syntactic errors in ESQL programs are detected by the preprocessor, so errors cannot arise at run-time.

Dynamically submitted SQL statements are however parsed at run-time, and the syntax error codes are returned after attempting PREPARE for a syntactically incorrect source statement.

Semantic errors can arise at run-time from both dynamic and static SQL statements.

Number

Explanation

-12001

Too many errors, error collection terminated

-12101

Syntax error, <%> assumed missing

-12102

Syntax error, <%> ignored

-12103

Syntax error, <%> assumed to mean <%>

-12104

Invalid construction

-12106

Internal parser error, analysis aborted

-12107

Syntax analysis resumed here

-12108

Multiple statements not allowed

-12109

Character string literal contains non-ascii character, use nchar literal instead (i.e. N'text')

-12110

Delimited identifier contains non-ascii character

-12120

Table name too long

-12121

String literal too long

-12122

Numeric literal too long

-12123

Invalid password string

-12124

<%> is an invalid hexadecimal literal

-12125

Reserved word may not be used as an identifier

-12126

Invalid name

-12127

Character string literal contains illegal UNICODE character

-12128

Result of concatenation too long

-12129

Table definition does not include any column specification

-12131

Table definition includes more than one PRIMARY KEY specification

-12132

Only one column allowed in column list

-12152

<%> not allowed in EXECUTE mode

-12154

User name too long

-12156

Column name too long

-12157

Synonym name too long

-12158

Correlation name too long

-12159

Cursor name too long

-12160

Databank name too long

-12161

Shadow name too long

-12162

Host Variable or Parameter Marker name too long

-12163

File name too long

-12164

Label name too long

-12165

Index name too long

-12166

Object name too long

-12167

View name too long

-12168

Domain name too long

-12169

Too many identifier names given

-12174

Syntax error in escape clause, expecting comma before PRODUCT or CONFORMANCE specification

-12175

Syntax error in escape clause, invalid CONFORMANCE specification

-12176

Syntax error in escape clause, invalid YEAR specification

-12177

Syntax error in escape clause, invalid PRODUCT specification

-12178

Syntax error in escape clause, invalid VENDOR specification

-12179

Syntax error in escape clause, expecting VENDOR or YEAR after '--(*'

-12180

Syntax error, unexpected token '*)--'

-12181

Syntax error in escape clause, terminating '*)--' missing

-12182

Syntax error in escape clause

-12200

Table <%> not found, table does not exist or no access privilege

-12201

Table reference <%> is ambiguous

-12202

<%> is not a column of an inserted table, updated table or any table identified in a FROM clause

-12203

<%> is neither an object table of an INSERT, UPDATE or DELETE statement, nor specified in a FROM clause

-12204

Column reference <%> ambiguous

-12205

Column <%> not referenced in GROUP BY clause

-12207

DISTINCT specified more than once in a subselect

-12208

SELECT clause of a subquery specifies more than one column

-12209

Column <%> identified in HAVING clause but not included in GROUP BY clause

-12210

Operand of set function includes a set function

-12211

NEXT_VALUE of sequence not allowed in ORDER BY clause

-12212

Operand of set function includes a correlated reference specified in an expression

-12213

Set function not specified in a SELECT clause or HAVING clause

-12214

Invalid operand type, expected type is <%>

-12215

Operand not of <%> type

-12216

Operands are not comparable

-12217

Set function containing DISTINCT may not be specified within an expression

-12218

Constant expression not allowed in ORDER BY clause

-12219

The length of the trim character expression is not one

-12220

Expression must be a column

-12221

SELECT clause contains both column expressions and set function expressions

-12222

ORDER BY expression contains no valid column reference

-12223

ORDER BY clause invalid because it includes a column name that is not part of the result table

-12224

ORDER BY clause invalid because it includes an integer which does not identify a column of the result table

-12225

The ORDER BY clause is invalid because it includes an ambiguous column reference

-12226

Set function argument not bound in HAVING context

-12227

Duplicate column reference in FOR UPDATE OF clause

-12228

ORDER BY clause must contain only SELECT list items when DISTINCT, GROUP BY or UNION is specified

-12229

Set function not allowed in ORDER BY clause

-12230

Invalid numeric literal

-12231

Update or insert value is NULL, but the object column <%> cannot contain NULL values

-12232

Insert value must be a constant expression or NULL

-12233

The number of insert values is not the same as the number of object columns

-12234

Update or insert value not compatible with the data type of the object column <%>

-12235

Subquery not allowed in ORDER BY clause

-12236

Column name <%> does not identify a unique column of the result table

-12237

User defined type <%> not found, does not exist or no usage privilege

-12238

Column <%> cannot be updated because it is derived from a set function or expression

-12239

The use of NULL in a SELECT clause is only allowed in a UNION

-12240

Statement contains too many table references

-12241

Domains not allowed in parameter list

-12242

The corresponding columns of the operands of a UNION do not have compatible column descriptions

-12243

Result table contains a column for which the type cannot be determined

-12244

Operands of a UNION do not have the same number of columns

-12245

FOR UPDATE clause may not be specified because the result table cannot be modified

-12246

Column <%> in the FOR UPDATE clause is not part of the identified table or view

-12250

A host variable or parameter marker is not allowed in a view definition

-12251

CREATE VIEW statement must include a column list because the SELECT clause contains an expression

-12252

CREATE VIEW statement must include a column list because the SELECT clause contains duplicate column names

-12253

The number of columns specified for the view is not the same as specified by the SELECT clause

-12254

WITH CHECK OPTION cannot be used for the specified view because it cannot be modified

-12258

<%> operation not permitted because the view cannot be modified

-12259

<%> operation not permitted because the joined table cannot be modified

-12260

LOAD operation not permitted, the table contains a trigger specification

-12261

INSERT statement not permitted because the object column <%> is derived from an expression

-12262

The type of the parameter marker cannot be determined

-12263

Parameter markers and host variables not allowed in EXECUTE IMMEDIATE environment

-12264

Parameter markers may not be specified in SELECT clause

-12265

Literal or computed value overflow

-12266

Decimal divide operation invalid because the result would have a negative scale

-12267

Duplicate column reference in INSERT column list

-12268

Duplicate column reference in UPDATE set clauses

-12269

Duplicate column reference in View Parameter

-12270

<%> does not have <%> privilege on object <%>

-12271

Duplicate column reference in GROUP BY clause

-12273

The types of the results of a CASE expression are not type compatible

-12274

The type of the CASE expression result cannot be determined

-12275

At least one result in a CASE expression must be non-null

-12277

Invalid CAST data type specification

-12278

Invalid EXTRACT field specification

-12280

<%> is an invalid datetime literal

-12281

<%> is an invalid interval literal

-12282

Invalid interval qualifier

-12283

Invalid use of interval qualifier

-12300

Syntax error in escape clause

-12301

Translated native SQL string was truncated

-12302

The function <%> is not supported

-12303

Unrecognized first word, <%> in escape clause expected 'CALL', 'FN','OJ', 'D', 'T' or 'TS'

-12304

Unrecognized scalar function <%>

-12305

Invalid data type <%> in function CONVERT

-12306

Syntax error, incomplete escape clause

-12307

Syntax error, unmatched apostrophe in string literal

-12308

Syntax error, unmatched quote in delimited identifier

-12309

Unknown first parameter <%> in scalar function <%>

-12310

Argument missing in scalar function <%>

-12311

Too many arguments in scalar function <%>

-12500

A databank named <%> already exists (or filename already used)

-12501

Table <%> does not exist

-12502

<%> does not have <%> privilege

-12503

<%> does not have <%> privilege on object <%>

-12504

Statement not allowed within transaction

-12505

<%> is not a USER or PROGRAM ident

-12506

No privilege

-12507

<%> does not have any databank available (see CREATE DATABANK)

-12509

An ident cannot REVOKE a privilege from itself

-12510

An ident cannot GRANT a privilege to itself

-12511

Duplicate column specification

-12512

Invalid type description

-12513

The cascade option would cause a drop of the last column <%> for table <%> in schema <%>

-12514

The value for <%> must be between <%> and <%>

-12515

The value of <%> must be <%> than or equal to the value of <%>

-12516

Qualified column name required

-12517

Object <%> does not exist

-12518

Circular grant of membership between groups not permitted

-12520

An ident cannot GRANT a privilege to itself

-12521

Create databank option <%> specified more than once

-12522

Alter databank option <%> specified more than once

-12523

Databank <%> does not exist

-12524

READ ONLY option only available for ALTER DATABANK

-12525

Alter table option <%> specified more than once

-12526

Default value for NOT NULL column <%> missing

-12527

Constraints and unique indexes must be specified WITHOUT CHECK when the database is set to AUTOUPGRADE

-12528

It is not allowed to create a shadow when the database is set to AUTOUPGRADE

-12529

The database can not be set to AUTOUPGRADE when there are shadows defined

-12530

Operand not of type <%>

-12531

Operands not comparable

-12533

Literal or computed value overflow

-12534

Invalid numeric literal

-12535

Invalid identifier, keyword VALUE expected

-12536

Name <%> in PRIMARY KEY specification not recognized as a column name of current table definition

-12537

<%> must be unqualified

-12538

Default value not compatible with domain definition

-12539

Host variable construction illegal in this context

-12540

<%> is not a column of the specified table(s)

-12541

Data pagesize too small for table record length

-12542

Default value is outside the range specified by domain definition

-12543

Index pagesize too small for table key length

-12544

Too many columns specified in <%> statement

-12545

Primary key column <%> may not be updated because the table is in a WORK databank

-12546

Column <%> is not type compatible with the corresponding column of the referenced table

-12547

Number of columns specified in the foreign key is not the same as the number of columns in the primary key of the referenced table

-12549

Databank option may not be changed to WORK since <%> contains tables with FOREIGN KEY, REFERENCE, UNIQUE or trigger specifications

-12550

Table <%> includes a FOREIGN KEY or UNIQUE constraint and may therefore not be created in a databank with WORK option

-12551

Table <%> is in a databank with WORK option and may therefore not be used as referential constraint

-12552

A UNIQUE index can not be created for table <%> as it is located in a databank with WORK option

-12553

Explicit grant membership on PUBLIC is not permitted

-12554

PUBLIC cannot be member of another group

-12555

The option can not be changed to READ ONLY since <%> contains a table using a current collation

-12556

<%> cannot be shadowed because it is a WORK databank

-12557

Shadow named <%> already exists

-12558

Ident named <%> already exists

-12559

Index named <%> already exists

-12560

Table, View or Synonym named <%> already exists

-12561

Domain <%> not found, domain does not exist or no usage privilege

-12563

Shadow <%> does not exist

-12564

Ident <%> does not exist

-12565

Maximum row length exceeded by index or key table

-12566

Maximum row length exceeded by base table

-12568

EXISTS construction illegal in this context

-12569

ALL or ANY construction illegal in this context

-12570

Set function construction illegal in this context

-12571

Subquery construction illegal in this context

-12572

Too many columns given in FOREIGN KEY clause

-12573

Name <%> in CHECK clause not recognized as a column name of current table definition

-12574

Name <%> in column constraint not recognized as current column name

-12577

Default value not compatible with column specification

-12579

No such unique constraint in referenced table

-12581

Too many columns given in UNIQUE constraint

-12582

UNIQUE constraint equivalent to PRIMARY KEY constraint

-12583

UNIQUE constraint equivalent to previously given UNIQUE constraint

-12585

Name <%> in FOREIGN KEY clause not recognized as a column name of current table definition

-12586

Data pagesize not supported

-12587

Index pagesize not supported

-12588

Compression <%> not supported

-12589

An ident or a schema owning any collation cannot be dropped. Drop collation first

-12590

Table contains too many columns

-12591

Cannot create unique index

-12592

Dependencies exist, RESTRICT specified

-12593

Column <%> does not exist

-12594

Column <%> cannot be dropped as it is the only column in table

-12595

Column <%> cannot be dropped, dependencies exist

-12596

Default value for column <%> does not exist

-12597

Change of data type is not allowed for a column included in a key or index

-12598

The datatype for a column can not be changed to/from a domain or user defined type

-12599

The proposed data type change is not supported

-12600

Change of data type is not supported for a column used by a view, routine or trigger

-12601

Statement does not support backup of <%>

-12602

The same file name is given for backup and incremental backup

-12603

Database is already OFFLINE

-12604

Database is already ONLINE

-12605

Cannot RESET LOG, because database is ONLINE

-12606

Databank <%> is already OFFLINE

-12607

Databank <%> is already ONLINE

-12608

Cannot RESET LOG, because databank <%> is ONLINE

-12609

Shadow <%> is already OFFLINE

-12610

Shadow <%> is already ONLINE

-12611

Cannot RESET LOG, because shadow <%> is ONLINE

-12612

Shadow <%> is already specified

-12613

Cannot set more than one shadow OFFLINE for databank having shadow <%>

-12614

Statistics cannot be updated for <%> because it is a view

-12615

Filename already used by databank or shadow

-12616

Cannot SET DATABASE OFFLINE, because another user is connected

-12617

Cannot SET DATABANK <%> OFFLINE, because the databank is in use

-12618

INCREMENTAL backups can only be used in conjunction with EXCLUSIVE

-12620

A domain or user-defined type named <%> already exists

-12621

A schema named <%> already exists

-12622

The schema name for the index table must be the same as the schema name for the table

-12623

A PRIMARY KEY constraint for this table has already been defined

-12624

The added PRIMARY KEY or UNIQUE constraint cannot be created as there are duplicates

-12625

The added referential constraint is not fulfilled by existing records

-12626

The added check constraint criteria is not fulfilled by existing records

-12627

One or more specified columns does not exist in table

-12628

Referenced table or column not found

-12629

FOREIGN KEY not referencing a compatible UNIQUE or PRIMARY KEY

-12630

Constraint <%> cannot be dropped, dependencies exist

-12631

PRIMARY KEY or UNIQUE column constraint not valid when records exist

-12632

Collation <%> does not exist

-12633

A collation named <%> already exists

-12634

Invalid collation definition string

-12635

Collation <%> cannot be dropped, used by domain or column

-12636

A collate clause can only be specified for character types

-12637

Expressions with different collating sequences can not be compared

-12638

Collation specified in ORDER BY must be the same as used in DISTINCT/UNION

-12639

Column of type LARGE OBJECT not allowed in PRIMARY KEY, UNIQUE, FOREIGN KEY or INDEX

-12640

Precompiled statement <%> does not exist or no execute privilege

-12641

A precompiled statement named <%> already exists

-12642

Invalid SQL construction used in CREATE STATEMENT

-12643

Column of datatype LARGE OBJECT not allowed in this context

-12644

SQL syntax error, only EXECUTE STATEMENT allowed

-12645

Column of datatype LARGE OBJECT not allowed in a databank having shadow

-12646

Precompiled statement is not scrollable

-12647

Precompiled statement is scroll only

-12648

Not allowed to change table as it is used by a precompiled statement

-12650

Only the creator of a precompiled statement may alter it

-12651

Invalid syntax in statement invocation

-12660

No column result set

-12661

The datatype list does not match any routine

-12662

There is no matching routine with the specified number of parameters

-12670

Support for user defined types is not included in this version

-12671

Support for read only databanks is not included in this version

-12672

Support for this diagnostic item is not included in this version

-12673

CURRENT_COLLATION not allowed for column in unique constraint

-12674

CURRENT_COLLATION not allowed for column in primary key

-12675

CURRENT_COLLATION not allowed for column in unique index

-12676

Collation <%> may not be used as base since it is binary

-12678

INSERT columns must be specified when CREATE STATEMENT, i.e. INSERT INTO table (column-list) ...

-12679

SELECT * not allowed when CREATE STATEMENT, specify selected columns (at top level)

-12701

<%> is a reserved word, and cannot be used as the name for a symbol

-12702

<%> is a global variable, and cannot be used as the name for a variable or parameter

-12703

The class <%> is already present in the handler declaration

-12704

The SQLSTATE <%> is already present in the handler declaration

-12705

The condition identifier <%> is already present in the handler declaration

-12706

The condition identifier <%> is already used in another handler in this scope

-12707

A condition identifier for the SQLSTATE <%> has already been defined in this scope

-12708

<%> is not a valid SQLSTATE value

-12709

The SQLSTATE <%> associated with the condition identifier <%> is already present in the handler declaration

-12710

An exception handler for the state (<%>) associated with the condition identifier <%> has already been defined in this scope

-12711

An exception handler for the state <%> has already been defined in this scope

-12712

An exception handler for the class <%> has already been defined in this scope

-12713

The default literal is too large for this data type

-12714

The literal value is too large for this data type

-12715

The type of the default value is not compatible with the type of the variable

-12717

Invalid declaration. The maximum precision for this data type is <%>

-12718

The scale cannot exceed the precision

-12720

The number of correlation names for <%>, does not match number of result types

-12721

The parameter <%> must be declared as IN as it is defined in a function, method or result set procedure

-12722

The parameter <%> is declared as IN, and cannot be assigned

-12723

The parameter <%> is declared as OUT, and cannot be used in expressions

-12724

<%> cannot be assigned a value directly

-12725

Result set procedures can only be used in cursor declarations

-12726

The formal argument of the routine is IN but the actual argument is OUT

-12727

The formal argument of the routine is OUT but the actual argument is IN

-12728

Literals or expressions cannot be used for OUT parameters

-12729

Return statements are only allowed in result set procedures or functions

-12730

Recursive call to <%>, not allowed

-12731

<%> statement not allowed in result procedure

-12732

The procedure <%> does not return a result set and cannot be used in a declare cursor for call

-12733

RESIGNAL statement only allowed in exception

-12734

Wrong number of items in result clause, <%> expected

-12735

Wrong number of parameters, <%> expected

-12736

Invalid type for argument, <%> expected

-12737

Too long name <%>

-12738

Duplicate declaration <%>

-12739

x.y.z names not allowed

-12740

Syntax error, <%> assumed to mean <%>

-12741

The label or routine <%> is not defined

-12742

The label <%> is not defined

-12743

Procedure <%> does not exist or no execute privilege

-12744

The variable <%> is not defined

-12745

The condition identifier <%> is not defined

-12746

The cursor <%> is not defined

-12747

The use of a domain (<%>) is invalid in this context

-12748

Operands are incompatible

-12749

The SQL module <%> already exists

-12750

The procedure <%> already exists

-12751

Duplicate parameter <%>

-12752

The procedure <%> is declared in an SQL module, and cannot be dropped directly

-12753

Failed to read data dictionary

-12754

The length of a host variable cannot exceed 32

-12755

Host variables cannot be used within a procedure

-12756

The data type for the parameter marker cannot be determined

-12757

The number of items in the into clause is less than the number of items in the select list

-12758

The number of items in the into clause is greater than the number of items in the select list

-12759

The number of items in the fetch into clause is less than the number of items in the cursor declaration

-12760

The number of items in the fetch into clause is greater than the number of items in the cursor declaration

-12761

A fetch direction other than next, can only be defined for a scrollable cursor

-12762

The value in a fetch absolute or relative must be an integer

-12763

The argument to fetch absolute must be larger than 0

-12764

A cursor for call is read only and cannot be used in an update or delete where current statement

-12765

The cursor is declared as read only and cannot be used in an update or delete where current statement

-12766

The table name in the <%> statement is not the same as the name used in the cursor declaration

-12767

The column <%> is not specified in the for update list of the cursor declaration

-12768

The size for a data type must be larger than zero

-12769

It is not allowed to declare exception handlers or condition identifiers for the SQLSTATE successful completion ('00000')

-12770

The formal argument of the procedure is declared as INOUT but the actual argument is <%>

-12771

A handler declaration cannot contain both an exception class and SQLSTATE values or condition identifiers

-12772

The procedure does not return a result set and therefore cannot be used with a scroll cursor

-12773

<%> is not a column and is not declared as a variable or parameter

-12774

The label <%> has already been declared

-12775

The statement requires <%> access

-12776

<%> statement is only allowed if the access indication is MODIFIES SQL DATA or READS SQL DATA

-12777

<%> statements are only allowed if the access indication is MODIFIES SQL DATA

-12778

The access indication for a result set procedure must be READS SQL DATA or CONTAINS SQL

-12779

An UNDO exception handler can only be specified in an atomic compound statement

-12781

Only assignment and comparison operations allowed

-12782

The column name <%> has already occurred in this row declaration

-12783

The field <%> is not defined in the row data type

-12784

The row data types do not have the same number of fields

-12785

A row data type variable may not be used as a parameter or result type nor in a DML statement

-12800

Functionality not supported: <%>

-12801

Referencing OLD TABLE is not allowed if trigger event is INSERT

-12802

Referencing NEW TABLE is not allowed if trigger event is DELETE

-12803

The compound statement in a triggered action must be atomic

-12804

Referencing OLD or NEW ROW may only be used if FOR EACH ROW is specified

-12805

A column list can only be specified if trigger event is UPDATE

-12806

Duplicate column name in OF list

-12807

It is not allowed to modify the <%> table

-12808

The trigger time for a view must be INSTEAD OF

-12809

The trigger time for a base table can not be INSTEAD OF

-12810

Only the creator of a table can create a trigger for the table

-12811

It is not allowed to create triggers for tables, located in databanks with WORK option

-12812

Referencing OLD or NEW table is not allowed in FOR EACH ROW triggers

-12813

A trigger named <%> already exists

-12830

A function named <%> already exists

-12831

Function <%> does not exist or no execute privilege

-12832

The result of the expression is not deterministic while the routine is declared as deterministic

-12833

All privileges used in a trigger must be held with grant option

-12834

Sequence <%> does not exist

-12835

A sequence named <%> already exists

-12836

The keyword NULL cannot be used <%>

-12837

The operands to a overlaps predicate must be of a row data type with two elements

-12838

The two elements in an operand to the overlaps predicate must either be of the same type or otherwise it shall be possible to add the second value to first value

-12839

<%> is not allowed in a before trigger

-12840

The simple value specification for a get diagnostics statement must be of integer type

-12841

<%> is not allowed in a trigger

-12842

The increment for a sequence must be non zero

-12843

Invalid values for sequence attributes

-12844

Schema <%> does not exist or no privilege

-12845

The schema name for routines in a module definition must be the same as the schema name for the module

-12846

The value for diagnostics size must be positive

-12847

The ident name in an authorization clause must be the same as the current user

-12848

A constraint named <%> already exists

-12849

The function <%> MODIFIES SQL DATA and can thus not be used in a DML statement

-12850

A trigger must be located in the same schema as the table

-12851

A constraint must have the same schema name as the object to which it belongs

-12852

The schema name for a routine is not the same as the schema name for the module

-12853

Ident name not allowed as a schema with the same name exists

-12854

A non-deterministic expression is not allowed in a check clause

-12855

Default values with a reference to a sequence combined with a check clause is not allowed in an alter table statement

-12856

References to LOB columns in the NEW table in an instead of trigger is not allowed

-12857

The label <%> can not be used for an iterate statement

-12858

The record <%> is not compatible with the <%>

-12860

No type or domain named <%> is defined

-12861

The attribute name <%> is already specified

-12862

Instantiability can not be specified for a distinct type

-12863

A distinct type must be FINAL

-12864

A type can not be FINAL and NOT INSTANTIABLE

-12865

The default value for the attribute is invalid

-12866

There already exists a routine with specific name <%>

-12867

Parameters for functions and methods may not be specified as IN explicitly

-12868

A parameter name for the routine is missing

-12869

The type attribute <%> specified multiple times

-12870

No specification for the method <%> was found

-12871

The parameter type does not match method specification for <%>

-12872

The parameter name <%> does not match method specification

-12873

A type name must be specified

-12874

A method must be created in the same schema as the type

-12875

A type binding can only be specified for methods

-12876

A structured type must be declared as FINAL or NOT FINAL

-12877

An attribute in a structured user defined type may not use that type as the data type

-12878

No <%> method specification exists

-12879

The method does not have the same return data type as the method specification

-12880

SELF is not allowed as parameter or variable name in an instance method

-12881

A method named <%> is already defined for this type

-12882

A method specification named <%> with equal parameters is already defined for this type

-12883

The type for the parameter <%> does not match the method specification

-12884

Type <%> does not exist or no usage privilege

-12885

As the type <%> is declared as NOT INSTANTIABLE it can not be used when defining columns or variables

-12886

The method specification <%> does not have a returns clause

-12887

The schema name for the type <%> and the method specification are not the same

-12888

<%> is a static method and must be invoked with the syntax typename::method

-12889

<%> is a instance method and must be invoked with the syntax object.method

-12890

There is no method <%> defined for the type, or no execute privilege

-12891

The attribute <%> does not exist in this type

-12892

The subject routine for <%> can not be determined, there exists multiple routines with the same name. Provide more specific data types

-12893

The method specification for <%> can not be determined, there exists multiple method specifications with the same number of parameters but none matching the data types for this method definition

-12894

The distinct type can not be created since there exists a function with the name <%> and one parameter having the same type

-12895

The object <%> has been created implicitly and can not be dropped

-12896

A constructor method can only be defined for a structured user defined type

-12897

A constructor method must have the same name as the user defined type

-12898

A constructor method must have the same return type as the user defined type, <%> does not fulfill this requirement

-12899

<%> is a constructor method and must be invoked using the new operator

-12900

The attribute name is the same as an existing routine

-12901

Source as distinct is only valid for distinct types

-12902

Multiple source as distinct or distinct as source clauses not allowed

-12903

xxx

-12904

Order full not allowed for state orderings

-12905

No EQUALS ordering defined for the type <%> comparison not allowed

-12906

Not allowed to create or drop ordering for distinct type

-12907

The type does not have any ordering

-12908

There is already an ordering defined for this type

-12909

<%> orderings are not supported

-12910

A function named EQUALS with the same parameter types already exists

-12911

No FULL ordering defined for the type <%> comparison not allowed

-12912

Distinct as source is only valid for distinct types

-12913

Only method specifications can be dropped with the alter type statement

-12914

CASCADE not allowed for alter type

-12915

Cast to and from structured types not allowed

-12916

Not possible to add or drop attributes for a distinct type

-12917

Only the creator of a user defined type may alter it

-12918

Not allowed to add or drop attribute to this type as it is used as the data type for a column

-12919

The addition of the attribute would cause a circular dependency

-12920

<%> is the last attribute for the type and can not be dropped

-12921

The cursor <%> is declared in a for statement and can not be used explicitly

-12922

All items in the select list in a for statement must be named

-12923

Duplicate name <%> in select list in for statement

-12924

All result items for a result set procedure must have a name when used in a for statement

-12925

Duplicate result item name <%> for result set procedure which is not allowed when used in a for statement

Program-dependent Errors

These errors arise as a result of incorrect program construction. In general, corrective action requires revision of the program source code.

Number
Explanation

-14001

Invalid sequence of SQL statements

-14002

SQL statement invalid because the user is not connected

-14003

CONNECT statement invalid because the user is already connected

-14004

System already closed down

-14005

Cannot perform DISCONNECT in a transaction

-14006

Login failure

-14011

Transaction already started

-14012

Transaction handling required

-14013

No transaction started

-14014

Cannot perform write operations as transaction status is read-only

-14015

Commit or rollback statements are not allowed in an atomic execution context

-14016

Select for update is not allowed for a read-only cursor

-14017

Mixing DDL and DML statements in a transaction is not allowed

-14018

Incremental backup not allowed in BACKUP transaction

-14019

Operation not allowed in BACKUP transaction

-14020

START BACKUP command required to perform an online backup

-14021

Cannot perform ENTER or LEAVE in a transaction

-14022

Cannot perform ENTER operation because program level is already active

-14023

No program level entered, cannot perform leave operation

-14024

Session statements are not allowed in a trigger

-14025

START LOAD command required to perform a load operation

-14026

The table name specified in the COMMIT/ROLLBACK LOAD statement does not match the table name (<%>) specified in the START LOAD statement

-14027

Invalid transaction state, held cursor requires same isolation level

-14031

DESCRIBE statement does not identify a prepared statement

-14032

EXECUTE statement does not identify a prepared statement

-14033

PREPARE statement identifies a SELECT statement of an opened cursor

-14034

The cursor is not in a prepared state

-14035

The cursor identified in a FETCH or CLOSE statement is not open

-14036

The cursor cannot be used because its statement name does not identify a prepared SELECT statement

-14037

The cursor identified in the UPDATE or DELETE statement is not open

-14038

UPDATE or DELETE CURRENT statement not allowed for a cursor of a prepared SELECT statement

-14039

Cursor is not scrollable

-14041

The cursor identified in the UPDATE statement is not positioned on a row

-14042

The cursor identified in the DELETE statement is not positioned on a row

-14043

Routine signaled SQLSTATE

-14044

Routine signaled a condition

-14045

Prepared statement not a cursor specification

-14046

The statement RESIGNAL was used outside a exception handler

-14047

Duplicate transaction identifier

-14048

Current user not owner of transaction

-14049

Already associated with transaction

-14050

The specified transaction identifier could not be found

-14051

Invalid distributed transaction state

-14052

It is not possible to mix local and distributed transactions

-14053

The transaction identifier is in use by other user

-14054

The requested operation cannot be performed in a distributed transaction

-14101

Invalid statement identifier

-14201

Compilation did not yield an executable program

-14202

The cursor identified in an OPEN statement is already open, but not declared as REOPENABLE

-14203

Statement position cannot be saved when temporary tables are used in the query

-14210

Cursor for result set procedure may not be reopenable

-14211

WITH HOLD option is only available for SELECT statements

-14301

SQLDA contains an invalid data address or indicator variable address

-14302

Invalid address of username or password

-14303

Invalid address

-14311

Illegal statement length given for SQL statement

-14312

Input character string too long

-14313

Like pattern string too long

-14314

Username or password too long

-14315

Illegal byte length of floating point number

-14316

Unterminated C string, null byte not found

-14321

Illegal host variable type

-14322

Illegal host variable type in like pattern string

-14323

Username and password must be fixed length character strings

-14324

Illegal indicator variable type

-14331

The number of provided host variables does not match the number of parameters

-14332

Using clause required for dynamic parameters

-14333

Using clause required for result fields

-14401

Column cannot be updated because it is not identified in the UPDATE clause of the SELECT statement of the cursor

-14402

The table identified in the UPDATE or DELETE statement is not the same as that designated by the cursor

-14403

Cannot describe statement without naming information

-14404

Cannot update table <%> because the declare statement is read-only

-14405

Cannot delete from table <%> because the declare statement is read-only

-14406

Unexpected statement type encountered in an UPDATE or DELETE statement

-14501

Database connection is not open

-14601

Invalid cursor state

-14611

Using clause does not match dynamic parameters

-14612

Using clause or Into clause does not match target specifications

-14621

Cursor not found

-14622

Ambiguous cursor name

-14623

Invalid cursor name

-14624

Cursor already allocated for statement

-14631

Invalid SQL statement name

-14641

Invalid SQL descriptor name

-14642

Invalid descriptor index

-14643

Invalid descriptor count

-14651

Invalid exception number

-14652

The xa_info string passed to xa_open or xa_close has an improper syntax

-14653

Invalid flags argument passed to XA routine

-14654

Asynchronous operations are not supported by XA routines

-14700

No return statement was encountered when executing a function or method

-14701

The statement is not allowed in an atomic execution context

-14702

The same column has been updated to different values when executing a trigger

-14703

An exception occurred during the execution of a trigger

-14720

The maximum_value for the sequence <%> has been reached

-14721

There is no current_value for the sequence <%> because the next_value function has not been invoked

-14722

Sequence <%> locked by another user

-14723

Update of attributes for the sequence <%> failed

-14724

Not possible to allocate multiple sequence values

-14725

WITH HOLD cursors can not be used with result procedures

Databank and Table Errors

These errors are associated with problems of physical access to databanks and tables. Locking errors should not result from transaction conflicts, but generally indicate either locking at the operating system level or malfunction of the internal Mimer SQL routines.

Many of the errors in this class are corrected by action taken at the operating system level. If errors persist in spite of corrective action, contact your Mimer representative.

Number
Explanation

-16001

Table <%> locked by another user

-16002

Table <%> locked by another cursor

-16003

Table <%> in referential constraint definition locked by another user

-16004

Table <%> in referential constraint definition locked by another cursor

-16005

Log locked by another user

-16006

Backup unit log file locked

-16007

Databank <%> is read only, data modifications are not allowed

-16008

Sequence <%> is located in a read only databank, new values can not be generated

-16009

Sequence <%> is located in a databank which is offline, new values can not be generated

-16101

No databank <%> found in dictionary

-16135

Record no longer exists

-16141

Syntax error in filename for databank <%>

-16142

Cannot open databank <%>, file <%> not found

-16143

File protection violation for databank <%>, file <%>

-16144

Cannot open databank <%>, file <%> locked by other user

-16145

Too many databanks open concurrently (direct access I/O limit)

-16146

File create error, disk full

-16147

File create error (quota exceeded) for databank <%>, file <%>

-16148

Device or network connection not ready, databank <%>, file <%>

-16149

Cannot open databank <%> in file <%>

-16150

Tried to access databank <%> on node which is inaccessible because TRANSDB is OFFLINE

-16151

Too many databanks open concurrently

-16152

Tried to open a non-MIMER databank

-16153

Data no longer available, storage device has been removed

-16154

Table control area exhausted

-16155

Incompatible version of databank <%>

-16156

Databank <%> belongs to another system databank

-16157

Tried to open a read-only databank with write access

-16159

Old version of the databank <%> cannot be accessed without using the ALTER DATABANK. RESTORE statement

-16160

Cannot set TRANSDB shadow OFFLINE on the same node as the master TRANSDB

-16161

Databank <%> disk space exhausted

-16162

Databank LOGDB disk space exhausted

-16163

Databank TRANSDB disk space exhausted

-16164

Databank SQLDB disk space exhausted

-16172

Databank <%> locked by another MIMER/DB user

-16182

Databank <%> corrupt

-16183

Bad parameter

-16184

I/O error

-16185

Internal databank identifier invalid

-16186

Internal table identifier invalid

-16187

Shadow <%> in file <%> has illegal sequence number

-16189

Corrupt bitmap page

-16190

Table root entry not found

-16191

Exclusive access to databank required for attempted operation

-16192

Load not allowed in databank with TRANS or LOG option

-16193

TRANSDB and/or LOGDB not open

-16194

Error occurred in transaction commit phase

-16195

Internal inconsistency detected

-16196

No end of table mark found for tableid

-16197

Shadow <%> is already OFFLINE

-16198

Shadow <%> is already ONLINE

-16199

Result of bitmap page I/O undefined

-16200

Result of index page I/O undefined

-16201

Result of root page I/O undefined

-16202

Result of data page I/O undefined

-16203

Corrupt index page

-16204

Corrupt root page

-16205

Corrupt data page

-16206

Write set corrupt

-16207

Table <%> has invalid record length

-16208

Unable to open databank <%>. SHADOW license required

-16209

Unable to open databank <%>. NETIO license required

-16210

Cleanup control area exhausted

-16211

Not properly closed, dbcheck initiated

-16212

TRANSDB restart directory corrupt

-16213

Error when closing databank file. Please consult MIMER log file

-16214

Blockdata DKBLK1 missing

-16215

Error accessing remote TRANSDB, node will not be accessed further

-16216

Blocksize not supported

-16217

Error when generating internal key

-16218

Operation not allowed. Configured number of users exceeded

-16219

Too many MIMER databases started

-16220

Unable to retrieve limit on number of allowed users

-16221

Lost contact with peer

-16222

Record length from update before is invalid

-16224

Transaction state table entries exhausted

-16225

Transaction state identifier invalid

-16226

Invalid function code

-16227

Commit set corrupt

-16228

Restart set corrupt

-16229

Cancel requested

-16230

Transaction cache inconsistent

-16231

Shadow <%> is inaccessible due to incomplete CREATE SHADOW or SET ONLINE operation

-16232

Database upgrade required

-16233

Operation not allowed. Licensed number of users exceeded

-16234

Execution interrupted by scheduler

-16235

There are pending in-doubt transactions

-16239

Databank <%> maximum size reached, unable to insert more data

-16240

The option for databank <%> is not supported

-16243

Disk full will be reached for databank <%>, unable to insert more data

-16401

Routine cannot be dropped because it is in use

-16402

Routine cannot be used because it is being dropped

Miscellaneous Errors

These errors arise from miscellaneous problems that do not fall into the other classes. If the corrective action is not indicated by the error description, contact your Mimer representative for help.

Number
Explanation

-18001

Blockdata BLKDS2 not included

-18002

Cannot log in, error in SYSDB initialization

-18003

No privilege to open log file

-18004

Databank LOGDB already opened by another MIMER/DB user. Could not be opened exclusively to drop log records

-18005

Unknown language

-18006

Language not properly installed

-18007

Unable to fetch message text

-18008

Restore in wrong sequence attempted

-18009

Mismatching version of Embedded SQL and MIMER/DB

-18010

Invalid log record found during restore operation

-18011

Mismatching version of MIMER/DB and utilities

-18012

The transformation of a TRANSDB shadow to master was interrupted before completion. Please login to BSQL to complete the transformation

-18013

MIMER/DB started from SYSDB shadow. Transform SYSDB shadow to master with BSQL, or restart system from master SYSDB

-18014

Alter shadow not allowed in SQL for system databanks. Use utility program instead.

-18015

Open with hold is not possible when temporary tables are used for evaluation of the query

-18016

Cursor could not be opened with hold as it is not prepared with hold

-18017

With hold functionality not supported

-18018

The network server version does not support scroll

-18019

Bad parameters passed to DBAPI4

-18020

Unknown information code = <%> used

-18021

Only SELECT, INSERT, UPDATE, and DELETE operations (excluding where current forms) may be compiled together in a single statement

-18022

Distributed transactions not supported by server

-18023

Requested DTC function not supported by server

-18024

Failed to enlist transaction in distributed transaction

-18025

Unable to retrieve transaction manager's whereabouts

-18026

Failed to import transaction identifier

-18027

Statement already active in other transaction

-18028

Cannot initiate a new PSM debugger session, because the number of request threads is insufficient

-18029

Execution interrupted by debugger

-18041

Update of primary key columns for a table located in a databank, with WORK option is not allowed

-18042

Primary key columns may not be updated by Level2 applications

-18043

The rowid column may not be updated

-18044

Inaccessible shadow found at LOAD operation

-18045

The table specified in the preceding START LOAD statement can only be referenced using single value inserts while the LOAD operation is active

-18046

WITH HOLD cursors cannot be used in XA transactions

-18101

Operation not allowed. SHADOW license required

-18102

Operation not allowed. MIMER/DB license required

-18103

Operation not allowed. MIMER/DB Level2 license required

-18104

Operation not allowed. MIMER/NET Client license required

-18105

Operation not allowed. MIMER/NET Server license required

-18106

Operation not allowed. MIMER/NET PC-Server license required

-18107

Operation not allowed. Beta test version of MIMER requires BETA license.

-18108

Error opening MIMER keyfile

-18109

Operation not allowed. XA, distributed transaction license required

-18121

Operation not allowed. VAR specific MIMER license required

-18122

Authorization failure. Invalid attempt to connect

-18201

SYSDB cannot be backed up using CREATE BACKUP without an ONLINE shadow

-18231

<%> records dropped from LOGDB

-18232

Shadow <%> is OFFLINE

-18233

Unable to access databank <%>, because it is OFFLINE

-18234

Error <%> occurred when trying to access shadow <%>

-18235

Error <%> occurred when trying to access databank <%>

-18236

Statistics updated for <%> tables

-18237

Databank <%> does not have LOG option

-18238

<%> records copied to incremental backup

-18239

Unable to CONNECT, because database is OFFLINE

-18240

Unable to CONNECT, because database is OFFLINE and one connection already exists

-18241

Unable to CONNECT, because SYSDB is OFFLINE

-18242

Unable to CONNECT, because SYSDB is OFFLINE and one connection already exists

-18243

Unable to access databank <%>, because database is OFFLINE

-18244

Could not get exclusive access to the database because one or more connections already exists

-18245

Could not connect to database <%>, a system administrator is executing a statement that requires exclusive access to the database

-18500

Database <%> not found in SQLHOSTS file

-18501

Database <%> unknown on remote node <%>

-18502

Handshake message invalid, incompatible protocol <%>

-18503

Only remote databases are allowed, specify database which is not local

-18504

The network server version of database <%> is not compatible

-18505

Local memory pool in network server exhausted (SQLPOOL)

-18506

In the current version only one local (and several remote) databases can be connected at a time

-18507

Unknown connection name <%> specified

-18508

Already connected to database <%>

-18509

Database name <%> invalid, too long or contains invalid characters

-18510

Connection name invalid, too long or contains invalid characters

-18512

Illegal reentrant call

-18513

Use another TCP/IP port number

-18514

Too deep address indirection

-18515

Cannot get value of thread-local variable

-18516

The network server version does not support Level2

-18518

Catalog version from beta test. See Release Notes, how to upgrade

-18519

Erroneous contents in SQLHOSTS

-18521

Error opening SQLHOSTS, filename syntax error

-18522

Error opening SQLHOSTS, file not found

-18523

Error opening SQLHOSTS, file protection violation

-18524

Error opening SQLHOSTS, file locked

-18525

Error opening SQLHOSTS, too many opened files

-18526

Error opening SQLHOSTS, file create error, disk space exhausted

-18527

Error opening SQLHOSTS, other error (-7)

-18528

Error opening SQLHOSTS, other error (-8)

-18529

Error opening SQLHOSTS, other error (-9)

-18530

Error opening SQLHOSTS, illegal access options

-18550

Invalid network package format

-18551

Unknown request code in network package (<%>)

-18552

Network package longer than expected

-18553

Internal data structures corrupt (DSNEE4)

-18554

The UTILITY program does not have client/server support

-18594

Query timeout period expired

-18595

Network partner disconnected

-18601

Could not connect to database <%>, unknown node '<%>'

-18602

Could not connect to database <%>, unknown protocol '<%>'

-18603

Could not connect to database <%>, unknown interface '<%>'

-18604

Could not connect to database <%>, unknown service '<%>'

-18605

Could not connect to database <%>, chosen protocol not supported on ALPHA/VMS '<%>'

-18606

Could not connect to database <%>, network type not supported '<%>'

-18607

Could not connect to database <%>, remote node is unreachable '<%>'

-18608

Bad parameter NETID=<%> passed to network package

-18609

Invalid parameter RECLEN=<%> passed to network package

-18610

Invalid parameter BUFFER=<%> passed to network package

-18611

Too many concurrent network connections

-18612

Connection refused '<%>'

-18613

Unexpected network event '<%>'

-18614

The underlying network protocol does not have enough capabilities '<%>'

-18615

Network service busy '<%>'

-18616

Local or remote system resources are insufficient '<%>'

-18617

Connection timed out '<%>'

-18618

Insufficient privileges for attempted network operation '<%>'

-18619

Unexpected network error '<%>'

-18620

Network operation would block (asynch mode)

-18621

Could not load network library

-18622

Required routines missing from network library

-18901

Multi-user system not started

-18902

MIMER logins are currently disabled, try again later

-18903

Access denied to MIMER multi-user system

-18904

Unable to attach to multi-user system, no response

-18905

Operation not allowed. Licensed number of users exceeded

-18906

Invalid database path

-18920

Machine dependent error-18920

-18921

Machine dependent error-18921

-18922

Machine dependent error-18922

-18923

Machine dependent error-18923

-18924

Machine dependent error-18924

-18925

Machine dependent error-18925

-18926

Machine dependent error-18926

-18927

Machine dependent error-18927

-18928

Machine dependent error-18928

-18929

Machine dependent error-18929

Internal Errors

These errors arise from malfunction in internal Mimer SQL routines. Contact your Mimer representative for help.

Number
Explanation

-19001

Program level list corrupt

-19002

No program level found

-19003

Statement list corrupt

-19004

Output parameter list corrupt

-19005

Table list corrupt

-19006

Unable to find log file, LOGDB corrupt

-19007

Inconsistency detected when trying to update dictionary

-19008

Unable to open SYSTEM base tables

-19009

Dictionary table SYSTEM.USERS corrupt

-19010

Unable to extract correct information from SYSTEM.DATABANKS

-19011

Unable to extract correct information from SYSTEM.TABLE_CONSTRAINTS

-19012

Dictionary mismatch found for table with TABLE_SYSID = <%>

-19015

Sysid record in SYSTEM.OBJECTS not found

-19016

Function not supported <%>

-19017

Invalid MAE program

-19018

Invalid operation code <%> at PC=<%>

-19019

Missing IMC instruction

-19020

Invalid function code passed to instruction <%>

-19021

No databank control block found for <%>

-19022

Bad function code passed to DSCDB3

-19023

Invalid pointer to naming structure

-19024

Severity message program corrupt

-19025

Invalid table descriptor

-19026

Invalid table descriptor, log status invalid

-19027

Base table must be opened before index tables

-19028

Table root entry not found

-19029

Unable to change position on write set because no mark is set

-19030

Invalid length for allocation of program space

-19031

Invalid table type

-19032

No table control block found

-19033

Cannot delete databank file outside transaction

-19034

Bad function code passed to DSCRD2

-19035

Invalid index descriptor

-19036

Error detected when closing table, hash chain corrupt

-19037

Invalid internal table type encountered

-19038

Write set inconsistency encountered

-19039

Invalid internal statement identifier

-19040

Invalid internal system identifier

-19041

Invalid internal user identifier

-19042

The static statement cannot be compiled because it is already identified with some other statement

-19043

The statement cannot be prepared because it is already identified with a static statement

-19044

Transaction control block chain corrupt

-19045

Shadow <%> cannot be transformed because it is OFFLINE

-19046

Databank <%> is referenced but not opened

-19047

Table has not been opened with sufficient access to allow current operation

-19048

Databank <%>, no shadow is found in dictionary with sequence number = <%>

-19049

The internal update operation has not been prepared with the old record

-19050

Catalog version not compatible with server

-19051

Compiled LIKE pattern corrupt

-19052

Could not store lookup path, inconsistency detected

-19053

Output descriptor overflow

-19054

Unable to initialize database system

-19055

Unable to generate an internal key

-19056

Inconsistent user identifier (not logged in)

-19057

Scroll program corrupt

-19058

Extended name not supported in static SQL

-19059

Invalid error message descriptor

-19061

Loss of significance for VARCHAR length

-19062

Positive overflow for VARCHAR length

-19063

Negative overflow for VARCHAR length

-19065

Bad parameter for VARCHAR length

-19066

Illegal operand for VARCHAR length

-19067

Bad record number

-19068

No matching record

-19069

Corrupt cancel state

-19071

Unrecognized data types for conversion

-19072

Invalid read set record

-19073

Insufficient internal descriptor area

-19074

Internal inconsistency encountered in TCACHE

-19075

Invalid internal DDU identifier

-19076

Internal inconsistency encountered in table list

-19077

Internal inconsistency encountered in DU1

-19078

Invalid parameter encountered in MOS

-19079

Internal inconsistency encountered in DSETH3

-19080

Thread initialization failure

-19081

Hash table missing in system control block

-19082

Internal inconsistency encountered in DSGSH2

-19083

Internal inconsistency encountered in MCOMEM

-19084

Runtime assertion failed

-19085

Invalid descriptor encountered on server

-19086

Invalid statement status encountered on server

-19087

PSM debugger resources already allocated

-19088

PSM debugger resources already deallocated

-19089

Invalid parameter encountered in DDU

-19091

Invalid function code passed to routine

-19092

Invalid function code 2 passed to routine

-19093

Invalid function code 3 passed to routine

-19101

Not valid conversion of data types

-19102

Not supported data type conversion

-19103

Initialisation failure for data type conversion

-19111

STMs for called statements remaining after disconnect

-19112

Release of statements terminated due to error <%>

-19113

Nonexistent parameter specified

-19114

Specified collation not found

-19115

LOB identification not found in directory table

-19116

No LOB column in parameter list

-19117

LOB start position out of bounds

-19118

Client and server disagreed on LOB length

-19119

Internal truncation error encountered in Mimer client driver

-19122

Failed to load name information package for system id=<%>

-19123

Failed to load table information package for table id=<%>

-19124

Failed to load domain information package for domain id=<%>

-19125

Cannot commit transaction for WITH HOLD

-19126

WITH HOLD resource mismatch

-19127

Not allowed to use CURRENT_COLLATION

-19201

System error : <%>- Area outside MAE data storage

-19202

System error : <%>- Attempt to qqwsal() in closed area

-19203

System error : <%>- Cost value out of range

-19204

System error : <%>- Error converting into Mimer format

-19205

System error : <%>- Error from MDRCCI call

-19206

System error : <%>- Error when reading databank option

-19207

System error : <%>- Expression switch case not recognized

-19208

System error : <%>- Factor was left unused

-19209

System error : <%>- Failed to get a slave RST

-19210

System error : <%>- Generation stack underflow

-19211

System error : <%>- Group is not allocated

-19212

System error : <%>- Host variable not defined

-19213

System error : <%>- Host variable number mismatch

-19214

System error : <%>- Illegal Set Func. mode switch case

-19215

System error : <%>- Illegal Status switch case <%>

-19216

System error : <%>- Index table not found

-19217

System error : <%>- Invalid base pointer

-19218

System error : <%>- Invalid object type

-19219

System error : <%>- Invalid pointer

-19220

System error : <%>- Main switch case not recognized

-19221

System error : <%>- Multiple offset assignment

-19222

System error : <%>- Multiple restriction groups

-19223

System error : <%>- No area opened

-19224

System error : <%>- Nonexistent member

-19225

System error : <%>- No Tbl_desc for SCO

-19226

System error : <%>- NOT stack overflow

-19227

System error : <%>- NOT stack underflow

-19228

System error : <%>- Offset outside MAE data storage

-19229

System error : <%>- qqcbix() with illegal operator

-19230

System error : <%>- qqcunx() with illegal operator

-19231

System error : <%>- Error from MDRCFC call

-19232

System error : <%>- qqrlst() with NULL list

-19233

System error : <%>- qqwlst() with NULL list

-19234

System error : <%>- Query result stack underflow

-19235

System error : <%>- Query stack underflow

-19236

System error : <%>- Rule matrix index out of range

-19237

System error : <%>- Scan kind not implemented

-19238

System error : <%>- Scan stack underflow

-19239

System error : <%>- Selectivity factor value out of range

-19240

System error : <%>- Semantic stack underflow

-19241

System error : <%>- Set range violation

-19242

System error : <%>- Set size incompatibility

-19243

System error : <%>- Stack underflow

-19244

System error : <%>- Statement switch case not recognized

-19245

System error : <%>- Switch case not recognized

-19246

System error : <%>- Too complicated UNION query

-19247

System error : <%>- Too many nested subqueries

-19248

System error : <%>- Traversal stack underflow

-19249

System error : <%>- Unexpected EXPRESSION in HOST variables

-19250

System error : <%>- Unexpected expression operand

-19251

System error : <%>- Unexpected expression subtype

-19252

System error : <%>- Unexpected node class

-19253

System error : <%>- Unexpected SELECT ITEM

-19254

System error : <%>- Unexpected statement subclass

-19255

System error : <%>- Unexpected DD return code <%>

-19256

System error : <%>- Unknown Host Variable type

-19257

System error : <%>- Unknown statement type

-19258

System error : <%>- WS stack overflow

-19259

System error : <%>- X stack overflow

-19260

System error : <%>- X stack underflow

-19261

System error : <%>- Error logging is not enabled

-19262

System error : <%>- Source position line or column is negative

-19263

System error : <%>- Message insert string too long

-19264

System error : <%>- Error logging is already enabled

-19265

System error : <%>- MAE constant storage overflow

-19266

System error : <%>- Selectivity rule number out of range

-19267

System error : <%>- No entry for index id

-19268

System error : <%>- qqcfnx() with illegal operator

-19269

Unexpected duplicate row found in temporary table

-19270

System error : <%>- Scan queue underflow

-19271

System error : CPL - PSM depth overflow

-19280

System error : <%>- Error from MDRTDC call

-19290

Out of memory

-19291

Invalid attribute type

-19292

Error when trying to store procedure in dictionary

-19293

Error when trying to share program

-19300

<%> unhandled production

-19301

Internal inconsistency detected in PSM

-19901

Function not yet implemented

Communication Errors

When an application has received a communication error, the connection will become unusable. The only valid operation on that connection will be DISCONNECT.

Error codes from the communication kernel layer (network routines):

Number
Explanation

-21001

Already listening on service <%>

-21002

Error trying to ASSIGN channel for TCP/IP communication

-21003

Error when creating socket

-21004

Error when binding socket address for service <%>

-21005

Error when getting port number for service <%>

-21006

Illegal protocol specified: <%>

-21007

Error when looking up host name: <%>

-21008

Error when connecting to database <%> on <%> using <%> to service <%>

-21009

Illegal channel id specified

-21010

Error when reading data from network channel

-21011

Error when writing data to network channel

-21012

Channel is not open

-21013

Channel is not accessible from this process

-21014

Error when creating mailbox

-21015

Error when declaring network object for service <%>

-21016

Unimplemented feature

-21017

Error when accepting new channel

-21018

Error when doing local listen for database <%> on path <%>

-21019

Too many channels used

-21020

Multiple read requests on channel

-21021

Multiple write requests on channel

-21022

Local write when not owning buffer

-21023

Cancel request illegal on channel

-21024

No available channel id number

-21025

Tried to open too many local servers

-21026

Database server for database <%> not running

-21027

Incompatible buffer versions

-21028

Failed to do a LOCAL connection to the server for database <%>

-21029

Illegal reentrant request on channel

-21030

Network request would block caller

-21031

Too large network I/O requested

-21032

Could not find DSINI4 in single-user library

-21033

Could not find DSHND4 in single-user library

-21034

Could not find DSGMD4 in single-user library

-21035

Could not find DSUMP4 in single-user library

-21036

The channel is closed

-21037

The specified network interface is not supported

-21038

Could not lock communication buffers in memory

-21039

Error trying to ASSIGN channel for DECNET communication

-21040

Could not map library for single-user mode

-21041

Could not initialize CK package

-21042

Error when performing initial communication with database server

-21043

Server rejected connection to database <%> on <%> using <%> to service <%>

-21044

Server rejected named pipe connection to database <%>

-21045

The address family for network protocol was unknown

-21046

Error when creating named pipe server objects

-21047

Error when setting up TCP server objects

-21048

Unexpected communication error

-21049

Error when reading/writing data to/from network channel

-21050

Error when closing communication with database server

-21051

All local communication slots are in use

-21052

Database server request failed

-21053

Database or network service not started Error when connecting to database <%> on <%> using <%> to service <%>

-21054

Database server for database <%> not started

-21055

The MIMER network service on <%> for <%> does not currently accept new connections. Try again later

-21056

Local communication has been disabled for database server <%>

-21057

Named pipe communication has not been enabled for database server <%>

-21058

TCP/IP communication has not been enabled for database server <%>

-21100

Command timed out

-21101

Error mapping MCS (MIMER Control Storage)

-21102

Error when doing system communication through the MCS

-21103

MCS communication area is busy. Try again later

-21104

Server for database <%> is already started

-21105

Illegal directory specified for the SYSDB file

-21106

Error in parameter file

-21107

Error when starting database server process

-21108

Error when looking up database name

-21109

Error when creating memory pool in database server

-21110

Could not allocate space from SQLPool

-21111

Error when initiating the ENQ/DEQ package

-21112

Error when attaching a thread to the ENQDEQ area

-21113

Error when initiating server I/O package

-21114

Error when setting default directory for database server I/O package

-21115

Could not start database server thread

-21116

Protocol error- received new request before completion of last request

-21117

Could not create proper execution environment

-21118

Database server not operational

-21119

Notification thread failed. Server can no longer respond to mimcontrol

-21120

Illegal directory path

-21121

Could not create new directory

-21122

Channel closed by administrator

-21123

Invalid channel number specified

-21124

Error when initiating request (rq) queue

-21125

Could not lock the bufferpool in memory

-21126

Database server halted. Failed to generate automatic database dumps

-21127

Database server halted. Dump files from the failed database are placed under <%>

-21128

Error when stopping database server process

-21129

Error when deleting memory pool in database server

-21130

Error getting database server parameters

-21131

Must be superuser to perform this function

-21132

The environment variable MIMER_HOME must point to the MIMER distribution

-21133

An illegal combination of command switches was specified

-21134

The database parameter must be specified

-21135

Permission denied

-21180

Error opening SQLHOSTS file

-21181

Error opening SQLHOSTS file - file name syntax error

-21182

Error opening SQLHOSTS file - file not found

-21183

Error opening SQLHOSTS file - file protection violation

-21184

Error opening SQLHOSTS file - file is locked

-21185

Error opening SQLHOSTS file - too many files are opened

-21186

Error opening SQLHOSTS file - file creation error (diskspace exhausted)

-21187

Error opening SQLHOSTS file - machine dependent code -7

-21188

Error opening SQLHOSTS file - machine dependent code -8

-21189

Error opening SQLHOSTS file - other error

-21190

Error opening SQLHOSTS file - illegal access option

-21191

Could not find a local definition for the specified database name

Error codes used by the server when creating database dumps. These codes are never returned to application programs:

Number
Explanation

-21200

Error when creating dump file

-21201

Error when writing dump file

Error codes reflecting problems in the layer that creates and interprets network packets:

Number
Explanation

-21300

Too large network buffer requested on client side

Other file management error codes:

Number
Explanation

-21400

Illegal file name

-21401

File not found

-21402

File protection violation

-21403

File was locked

-21404

The file could not be opened since a system resource was exhausted

-21405

The disk space is exhausted

-21406

The file is not open

-21407

Read not allowed on file

-21408

Write not allowed on file

-21410

Illegal argument

-21411

Illegal character

-21412

Memory allocation error

-21499

Unspecified error

JDBC Errors

These errors arise when the Mimer JDBC Driver fails for some reason. The error codes are in the range -22000 to -22999. When using Java, the error message is always included in the exception that is thrown.

To get the complete and accurate list of error codes, execute the following command:

$ java com.mimer.jdbc.Driver -errors

Mimload Errors

The following error codes are used by the Mimload application.

Number
Explanation

-23001

Unexpected DB error

-23003

Load/Unload is not allowed within transaction

-23004

Syntax error

-23005

Out of memory

-23006

Schema not found

-23007

Databank not found

-23008

Table not found

-23009

No results were created for statement

-23010

Statement has no parameters

-23011

Output parameters are not allowed

-23012

Could not open file

-23013

Could not read from file

-23014

Could not write to file

-23015

Could not close file

-23016

Syntax error in data descriptor

-23017

Could not open log file

-23018

Could not write to log file

-23019

Could not close log file

-23021

Statement failed, see next error


Deprecated Features

Some non-standard features in earlier versions of Mimer SQL are deprecated, but retained for backward compatibility.

Where these features have equivalents in the standard implementation, only the standard form is documented in the main body of this manual.

Use of the standard forms is strongly recommended.


INCLUDE SQLCA

The SQLCA communication area is deprecated feature in Mimer SQL.

Applications should now use the SQLSTATE variable and the GET DIAGNOSTICS statement to get all the information previously obtained from SQLCA.

See Communicating with the Application Program for a description of SQLSTATE and GET DIAGNOSTICS.

For backward compatibility reasons the use of the SQLCA is still supported.

Win: On Windows, SQLCA is no longer available. Applications using SQLCA has to be modified to use SQLSTATE or SQLCODE instead.


SQLCODE

The use of SQLCODE to retrieve the internal Mimer SQL return code for an exception has been replaced by the use of SQLSTATE and GET DIAGNOSTICS with the NATIVE_ERROR option, see Run-time Errors, in Mimer SQL.

For compatibility reasons, return codes can still be retrieved in SQLCODE, as in earlier versions of Mimer SQL. Mimer SQL will assume the existence of an SQLCODE variable in the application if no INCLUDE SQLCA statement is found and neither SQLSTATE nor SQLCODE is declared between BEGIN DECLARE SECTION and END DECLARE SECTION.

The values of SQLCODE are the same as the values for the native Mimer SQL return codes, see Native Mimer SQL Return Codes.


SQLDA

The SQLDA area, which was used in earlier versions of Mimer SQL, has now been replaced by a standardized SQL descriptor area. The SQLDA area was allocated and maintained by constructions in the host language. The new SQL descriptor area is allocated and maintained by standardized ESQL statements.

The SQLDA is still supported in Mimer SQL for compatibility reasons.

Win: On Windows, SQLDA is no longer available. Applications using SQLDA has to be modified to use SQL descriptor areas instead.


Parameter Marker Representation

In earlier versions of Mimer SQL it was documented that a parameter marker in a dynamic SQL statement could be represented by either a question mark or a parameter name preceded by a colon.

The non-standard representation of a parameter name preceded with a colon is now deprecated, but it is still supported for compatibility reasons.


VARCHAR(size)

In earlier versions of Mimer SQL a VARCHAR structure was documented, which was used in the C language for handling variable-length character strings.

This VARCHAR structure was defined as:

#define VARCHAR(x)
        struct {
            short  len;
            char   text[x];
        }

where the character string is stored in text, and the length of the text is stored in len.


SET TRANSACTION

Following the introduction of the SET TRANSACTION READ and SET TRANSACTION ISOLATION LEVEL options, the SET TRANSACTION CHANGES options no longer apply.

The following options are supported for backward compatibility only:


DBERM4

The DBERM4 routine, which could be used to retrieve the internal Mimer SQL return code and error message text for an exception, is now deprecated.

The GET DIAGNOSTICS statement should now be used to retrieve these exception information items, see Run-time Errors.


Mimer SQL Engine
Programmer's Manual
Version 9.3

Mimer SQL Engine Programmer'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