www.mimer.com/developer

Using Embedded Mimer SQL

---

The following sections discuss the scope, principles, processing and structure of embedded SQL.

The Scope of Embedded Mimer SQL

The following groups of SQL statements are common to embedded SQL 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 embedded SQL, but differ in certain details as a result of the different environments in which the statements are used.
• Procedure control statements for performing operations that occur specifically within stored routines and triggers.
• 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 embedded SQL.
• Data definition statements for creating and altering objects in the database. These are identical between interactive SQL and embedded SQL.
• 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.
• Declarations for variables, conditions, cursors and handlers occurring within stored routines or triggers.

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.

Note: In the Mimer SQL Reference Manual, Mimer SQL statements are identified as valid for use in embedded SQL, for interactive use or both. This is specified in the "Usage" section of the statement description.

General Principles for Embedding SQL Statements

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

Host Languages

Statements in Mimer SQL may be embedded in application programs written in C/C++, COBOL or FORTRAN. The basic principles for writing embedded SQL programs are the same in all languages and all embedded SQL 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 Apects.

The ESQL preprocessor is used to process SQL statements embedded in a host language.

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

VMS: Mimer SQL supports an ESQL preprocessor for the COBOL, FORTRAN and C/C++ host languages on VMS 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, two consecutive DELETE statements must be written (in COBOL) as:

             EXEC SQL DELETE FROM HOTEL   END-EXEC.
             EXEC SQL DELETE FROM ROOMS   END-EXEC.

and not

             EXEC SQL DELETE FROM HOTEL
                      DELETE FROM ROOMS   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 HOTEL
       +          WHERE CITY = 'SAN FRANCISCO'
 

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 SQL 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:

 EXEC SQL INCLUDE filename
 

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

Note: The file name must be enclosed in SQL string delimiters if it contains any non-alphanumeric characters.

Comments

Comments may be written in the embedded SQL 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 HOTEL /* Hotel was closed */
                WHERE CITY = 'SAN FRANCISCO';
 

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

Recommendations

The following recommendations are imposed by the use of embedded SQL:

• Variable names beginning with the letters "SQL" should be avoided (except for SQLSTATE and SQLCODE, which should be used when appropriate).
• Subroutine or subprogram names ending with a number should be avoided.

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

Processing Embedded SQL

The following sections discuss preprocessing and processing embedded SQL.

Preprocessing - the ESQL Command

An application program using embedded SQL 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 embedded SQL 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 embedded SQL statements.

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

The original embedded SQL 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

The preprocessor is invoked by the following command:

 $ esql language [flagger] [options] input-file [output-file]
 

Language Flags

UNIX-style	VMS-style	Function
-c	/C	Indicates that the input file is written using the C/C++ host language.
-cob(ol)	/COBOL	Indicates that the input file is written using the COBOL host language.
-for(tran)	/FORTRAN	Indicates that the input file is written using the FORTRAN host language.

Flagger Flags

UNIX-style	VMS-style	Function
-e(ntry)	/ENTRY	Ensures that all SQL statements which are beyond the bounds of the entry level SQL2 standard will generate a preprocessor warning and will be flagged with a warning comment in the output file.
-t(ransitional)	/TRANSITIONAL	Ensures that all SQL statements which are beyond the bounds of the transitional SQL2 standard will generate a preprocessor warning and will be flagged with a warning comment in the output file.
-i(ntermediate)	/INTERMEDIATE	Ensures that all SQL statements which are beyond the bounds of the intermediate level SQL2 standard will generate a preprocessor warning and will be flagged with a warning comment in the output file.
-f(ull)	/FULL	Ensures that all SQL statements which are beyond the bounds of the full level SQL2 standard will generate a preprocessor warning and will be flagged with a warning comment in the output file.

Options Flags

UNIX-style	VMS-style	Function
-s(ilent)	/SILENT	Suppresses the display of the copyright message and input file name on the screen (warnings and errors are always displayed on the screen).
-l(ine)	/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 Flags

UNIX-style	VMS-style	Function
filename	filename	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).
filename	/OUTPUT=filename	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: The application programmer should never attempt to directly modify the output from the preprocessor. Any changes which may be required in a program should be introduced into the original embedded SQL source code. Upright Database Technology AB cannot accept any responsibility for the consequences of modifications to the preprocessed code.

The preprocessor checks the syntax and to some extent the semantics of the embedded SQL statements, see Handling Errors and Exception Conditions 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 Embedded SQL - 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 C compiler defined for the MIM_CC symbol in the makeopt file found in the installation examples directory is supported.

VMS: The following compilers (all sold by Compaq) are supported on the VMS 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.

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

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:

• SQL statements are checked semantically against the data dictionary.
• Operations performed against the database are optimized (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). The programmer does 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.

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

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

Operation	Interactive SQL	Embedded SQL
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 embedded SQL 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 embedded SQL.
• 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