http://developer.mimer.com

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. See
• 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.

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se