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.