Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


General Principles for SQL Modules


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

Host languages

You can call the C code that comes out of the MSQL pre-processor from any host language that supports the used data types. Mimer MSQL officially supports C/C++, COBOL, Fortran and Pascal.

Information given in this manual applies to all languages unless otherwise explicitly stated. Language-specific information is detailed in Host Language Dependent Aspects.

Writing an SQL module

An SQL module is a list of SQL statements, where each SQL statement is included in a procedure. Each procedure contains only one SQL statement. The MSQL syntax is case insensitive. Routine, cursor and parameter names (except SQLCODE and SQLSTATE) are case sensitive, as they will follow to the generated C code which is case sensitive.

Module declaration

The syntax for declaring an SQL module is as follows:

 MODULE module-name
 LANGUAGE (C|COBOL|FORTRAN|PASCAL)
 

The LANGUAGE clause tells the module which host language it will be called from and adapts some data types to the given host language. For a full list of how a host application should handle all Mimer SQL data types, see Host Language Dependent Aspects.

LANGAUGE C
LANGUAGE FORTRAN
LANGUAGE COBOL
LANGUAGE PASCAL

The SQLCODE parameter is always translated to a pointer to an integer, no matter the chosen module language.

Cursor declarations

All cursors that are to be used in the module's procedures need to be declared before any procedure is declared. A cursor is declared using the corresponding SQL syntax as follows:

 DECLARE cursor-name CURSOR FOR cursor-statement
 

A module can contain multiple cursor declarations. They all need to be declared before the module procedures, and they are delimited by new rows. The cursor declaration can be split on more than one row wherever a white-space is permitted.

 DECLARE cursor-name1 CURSOR FOR
 cursor-statement1
 DECLARE cursor-name2
 CURSOR FOR
 cursor-statement2

Module procedures

Following the module and cursor declarations, the procedures that execute SQL statements are declared. An SQL module language procedure has a name, parameter declarations, and an executable SQL statement.

The syntax for declaring an SQL module procedure is as follows:

 PROCEDURE procedure-name
     [parameter-declaration
     [, parameter-declaration];
 SQL statement;
 

The syntax for the parameter declaration is as follows:

 :parameter-name datatype
 

or

 SQLSTATE
 

or

 SQLCODE
Example:
 PROCEDURE fetch_data
     :param_1 INTEGER
     :param_2 SMALLINT
     SQLSTATE;
 FETCH data_cursor INTO
     :param_1,
     :param_2;
 

The parameters may be input parameters, output parameters or both.

Parameters are separated by commas or whites paces and ended with semicolon.

SQLSTATE and SQLCODE parameters are status parameters through which errors are passed. Use either, not both, in the same SQL module. Using both in the same SQL module causes undefined behavior.

Comments

You can comment your SQL module file with double dashes, as follows:

 -- A very useful cursor.
 DECLARE cursor-name CURSOR FOR cursor-statement
 

Comments are not forwarded to the embedded C and C code.

Recommendations

We recommend the following when writing MSQL:

Calling an SQL module

In the host program, you call an SQL procedure at whatever point in the host program you want to execute the SQL statement in that procedure. You call the SQL procedure as if it was a subprogram in C.

The following SQL module procedure:

 PROCEDURE fetch_data
     :param_1 INTEGER
     :param_2 SMALLINT
     SQLSTATE;
 FETCH data_cursor INTO
     :param_1,
     :param_2;
 

will be processed into the following C function:

 void fetch_data(int* param_1, short* param_2, char sqlstate[6])
 

When calling this function from your host program, the parameters you send need to be compatible with the C data types in the C function. Input char arrays might need to be null-terminated, as per C standard, see Writing an SQL module - Module declaration and Host Language Dependent Aspects for more details.


Mimer
Mimer Information Technology AB
Phone: +46 18 780 92 00
info@mimer.com
Mimer SQL Documentation TOC PREV NEXT INDEX