Mimer SQL Programmer's Manual TOC PREV NEXT INDEX

Mimer Developer Site

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:

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:

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:

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.

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:



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Programmer's Manual TOC PREV NEXT INDEX