Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


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:


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX