Module SQL

This chapter discusses the scope, principles, processing and structure of Module SQL (MSQL).

MSQL enables you to call SQL statements in a host program written in C/C++, COBOL, Fortran or Pascal, without embedding the actual SQL statements in the host program. The SQL statements are explicitly put into a separate SQL module, that is written in the Module language and maintained separately from the host program.

The Scope of Mimer Module SQL

The Mimer MSQL files are pre-processed through the Mimer Module SQL pre-processor into Embedded C code. The resulting Embedded C code can then be processed through the Mimer Embedded SQL pre-processor into C code which can be compiled, and then linked together with the host application.

 

The SQL statements supported are therefore limited to the SQL statements supported by Embedded SQL, which are described in Embedded SQL.

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

•The CHARACTER, VARCHAR, BOOLEAN, DATETIME and INTERVAL SQL data types are handled like a null-terminated C char array in the generated MSQL C code. When calling module procedures, all host languages need to make room for and apply null-termination. The Fortran CHARACTER data type cannot be used. This includes SQLSTATE.

•The CLOB SQL data type is handled like a C struct specified according to the SQL standard in the generated MSQL C code. When calling module procedures, all host languages need to use corresponding data structures.

•The DECIMAL SQL data type is handled like a C double in the generated MSQL C code. When calling module procedures, all host languages need to use corresponding data types. The COBOL PACKED DECIMAL (COMP-3) data type cannot be used.

LANGUAGE FORTRAN

•The CHARACTER, VARCHAR, BOOLEAN, DATETIME and INTERVAL SQL data types are handled like a fixed length Fortran CHARACTER array in the generated MSQL C code, without null termination. This includes SQLSTATE.

•The CLOB SQL data type is handled like a fixed length Fortran CHARACTER array in the generated MSQL C code, without null termination.

•The DECIMAL SQL data type is handled like a C double in the generated MSQL C code. When calling module procedures, the Fortran application needs to use the corresponding data type.

LANGUAGE COBOL

•The CHARACTER, VARCHAR, BOOLEAN, DATETIME and INTERVAL SQL data types are handled like a fixed length COBOL PICTURE X array in the generated MSQL C code, without null termination. This includes SQLSTATE.

•The CLOB SQL data type is handled like a fixed length COBOL PICTURE X array in the generated MSQL C code, without null termination.

•The DECIMAL SQL data type is handled like a COBOL PACKED DECIMAL (COMP-3) data type of the expected precision and scale in the generated MSQL C code.

LANGUAGE PASCAL

•The CHARACTER, VARCHAR, BOOLEAN, DATETIME and INTERVAL SQL data types are handled like a fixed length Pascal PACKED ARRAY OF CHAR in the generated MSQL C code, without null termination. This includes SQLSTATE.

•The CLOB SQL data type is handled like a fixed length COBOL Pascal PACKED ARRAY OF CHAR in the generated MSQL C code, without null termination.

•The DECIMAL SQL data type is handled like a C double in the generated MSQL C code. When calling module procedures, the Pascal application needs to use the corresponding data type.

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:

•Avoid variable names beginning with the letters SQL (except for SQLSTATE and SQLCODE, which should be used when appropriate).

•Avoid procedure names ending with a number.

•Avoid separating identifiers (e.g. procedure or parameter names) with casing only. Even though the generated C code might be valid, the calling host language might be case insensitive.

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.

Processing MSQL

The following sections discuss pre-processing and processing MSQL.

Pre-processing - the MSQL command

An SQL module file must first be pre-processed using the MSQL command, then pre-processed using the ESQL command (see Processing ESQL), before it is compiled with the C compiler.

The input to the pre-processor is thus an SQL module written in Module language.

The output from the preprocessor is always an Embedded C source code file, containing both C and ESQL statements. If the host language is C, an Embedded C header file is also generated.

The default file extensions for preprocessor input and output files are shown in the table below:

Invoking the MSQL Preprocessor

The MSQL preprocessor has the following command line syntax:

msql [-n] [-i enc] [-o enc] [-b yes|no] [-u yes|no] infile     [outfile [headerfile]]

 

msql [--nologo] [--inencoding=enc] [--outencoding=enc] [--inbom=yes|no]

    [--outbom=yes|no] infile [outfile [headerfile]]

 

msql [-v|--version] | [-?|--help]

Options

 

Note:As an application programmer, you should never attempt to directly modify the output from the preprocessor.

Any changes that may be required in an SQL module should be introduced into the original SQL module code. Mimer Information Technology AB cannot accept any responsibility for the consequences of modifications to the pre-processed code.

What Does the Preprocessor Do?

The preprocessor checks the syntax (See Handling errors and exceptions for a more detailed discussion of how errors are handled.) Syntactically invalid statements cannot be pre-processed and the source code must be corrected.

Processing MSQL

Compiling

The output from the MSQL and ESQL preprocessors is compiled in the usual way using the appropriate C compiler and linked with the appropriate routine libraries.

 

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

Linking

Linking the processed SQL module with a host application is done in the same way as user-written embedded C code, see Linking Applications.

The SQL Compiler

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:

•It checks SQL statements semantically against the data dictionary.

•It optimizes operations performed against the database (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). You, as a programmer, do 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.

Connecting to a Database

Connecting to a database is done much in the same way as in embedded programs, see Connecting to a Database. Every connect attempt is a separate SQL statement that needs its own module procedure.

Examples

Example SQL module:

PROCEDURE connect_ident

   :ident VARCHAR(100)

   :pswd VARCHAR(100)

   SQLSTATE;

CONNECT TO 'the_database' USER :ident USING :pswd;

Example host application in C:

#include "module_name.h"

 

int main()

{

   char sqlstate[6];

   char ident[101] = "SYSADM";

   char pswd[101] = "SYSADM";

 

   connect_ident(ident, pswd, &sqlcode);

Example host application in Fortran:

     CHARACTER*5 SQLSTATE

     CHARACTER*100 IDENT

     CHARACTER*100 PSWD

 

      IDENT = 'SYSADM'

      PSWD = 'SYSADM'

     CALL CONNECT_IDENT(IDENT, PSWD, SQLSTATE)

Example host application in Cobol:

      DATA DIVISION.

      WORKING-STORAGE SECTION.

      01 SQLSTATE PIC X(5).

      01 IDENT PIC X(100).

      01 PSWD PIC X(100).

 

      PROCEDURE DIVISION.

      HEAD SECTION.

 

      MAIN.

 

          MOVE "SYSADM" TO IDENT.

          MOVE "SYSADM" TO PSWD.

 

          CALL "CONNECT_IDENT" USING IDENT, PSWD, SQLSTATE.

Example host application in Pascal:

type chararray = packed array [1..100] of char;

type sqlstatearray = packed array [1..5] of char;

 

var

sqlstate : sqlstatearray;

ident : chararray;

pswd : chararray;

 

procedure connect_ident(var ident : chararray, var pswd : chararray,

  var sqlstate: sqlstatearray); external;

 

begin

   ident := 'SYSADM';

   pswd := 'SYSADM';

   connect_ident(ident, pswd, sqlstate);

Communicating with the Application Program

Information is transferred between the host application program and the Mimer SQL database manager through an SQL module much in the same way as in embedded SQL programs, see Communicating with the Application Program.

Indicator variables

In MSQL, as in ESQL, indicator variables associated with main variables are used to handle null values in database tables. Indicator variables are declared in the parameter list to a module procedure like the main parameters, with the SQL data type SMALLINT. It is translated to the C type short. The host language then provides the variable in the parameter list in the same way as the main variables.

PROCEDURE fetch_name

   :name VARCHAR(20)

   :name_indicator SMALLINT

   SQLSTATE;

FETCH name_cursor INTO :name:name_indicator;

Accessing data

This section explains how SQL modules retrieve data.

Retrieving data using cursors

Data is retrieved much in the same way as in ESQL, see Accessing Data. The host application will have to check the value of SQLCODE or SQLSTATE in order to find out when the cursor has reached the end of the result set.

Examples

Example SQL module:

DECLARE currencies_cursor CURSOR FOR

SELECT code FROM mimer_store.currencies

 

PROCEDURE open_currencies_cursor

   SQLSTATE;

OPEN currencies_cursor;

 

PROCEDURE fetch_currency_code

   :code CHARACTER(3)

   SQLSTATE;

FETCH currencies_cursor INTO :code;

 

PROCEDURE close_currencies_cursor

   SQLSTATE;

CLOSE currencies_cursor;

Example host application in C:

#include "module_name.h"

 

int main()

{

   int sqlcode;

   char code[4];

   open_currencies_cursor(&sqlcode);

 

   while (sqlcode == 0)

   {

       fetch_currency_code(code, &sqlcode);

   }

 

   close_currencies_cursor(&sqlcode);

Example host application in Fortran:

     INTEGER*4 SQLCODE

     CHARACTER*3 CODE

 

     CALL OPEN_CURRENCIES_CURSOR(SQLCODE)

 

     DO WHILE (SQLCODE .EQ. 0) THEN

         CALL FETCH_CURRENCY_CODE(CODE, SQLCODE)

     END DO

 

     CALL CLOSE_CURRENCIES_CURSOR(SQLCODE)

Example host application in Cobol:

      DATA DIVISION.

      WORKING-STORAGE SECTION.

      01 SQLCODE PIC S9(9) USAGE IS BINARY.

      01 CODE PIC X(3).

 

      PROCEDURE DIVISION.

      HEAD SECTION.

 

      MAIN.

 

          CALL "OPEN_CURRENCIES_CURSOR" USING SQLCODE.

 

          PERFORM UNTIL SQLCODE IS NOT ZERO

              CALL "FETCH_CURRENCY_CODE" USING NAME, SQLCODE.

          END-PERFORM.

 

          CALL "CLOSE_CURRENCIES_CURSOR" USING SQLCODE.

Example host application in Pascal:

type currency_code_type = packed array [1..3] of char;

 

var

sqlcode : integer;

code : currency_code_type;

 

procedure open_currencies_cursor(var sqlcode : integer); external;

procedure fetch_currency_code(var code : currency_code_type,

  var sqlcode : integer); external;

procedure close_currencies_cursor(var sqlcode : integer); external;

 

begin

  open_currencies_cursor(sqlcode);

 

  while sqlcode = 0 do

  begin

     fetch_currency_code(code, sqlcode);

  end

 

  close_currencies_cursor(sqlcode);

Retrieving single rows

See Accessing Data for detailed information on how retrieving single rows work. It is done in an SQL module by declaring a procedure for the specific statement.

Dynamic SQL

Dynamic SQL can be used in the same way as in ESQL, see Dynamic SQL. The PREPARE and EXECUTE operations are performed through module procedures.

PROCEDURE prepare_stmnt

   :stmnt VARCHAR(300)

   SQLCODE;

PREPARE statement1 FROM :stmnt;

    

PROCEDURE execute_prepared

   SQLCODE;

EXECUTE statement1;

Handling errors and exceptions

Errors may arise at three general levels in an SQL module. These are syntax, semantic and run-time errors.

Syntax Errors

Syntax errors are constructions that break the rules for formulating SQL statements. For example:

•Spelling errors in keywords:

SLEECT instead of SELECT

•Incorrect or missing delimiters:

DELETEFROM instead of DELETE FROM

SELECT column1;column2 instead of SELECT column1,column2

•Incorrect clause ordering

UPDATE … WHERE … SET instead of UPDATE … SET … WHERE

The preprocessor does not accept syntactically incorrect statements. The error must be corrected before the program can be successfully preprocessed.

Semantic Errors

Semantic errors arise when SQL statements are formulated in full accordance with the syntax rules, but do not reflect the programmer's intentions correctly. Semantic errors are not detected by the MSQL preprocessor.

Run-time Errors

Run-time errors and exception conditions (for example warnings) arising during execution of SQL module procedures are signaled in the same was as in ESQL, see Handling Errors and Exceptions.

The errors cannot be caught with WHENEVER statements that are used in ESQL, the host application has to rely on the values of SQLCODE and SQLSTATE.

Examples

Example SQL module:

PROCEDURE connect_sysadm

   SQLCODE;

CONNECT TO '' USER 'SYSADM' USING 'SYSADM';

 

PROCEDURE get_diagn

   :errcode INT

   :errmsg  VARCHAR(300)

   SQLCODE;

GET DIAGNOSTICS EXCEPTION 1

   :errcode = native_error,

   :errmsg  = message_text;

Example host application in C:

void connect_sysadm(int* sqlcode);

void get_diagn(int* errcode, char errmsg[301], int* sqlcode);

 

int main()

{

   int sqlcode;

   int errcode;

   char errmsg[301];

 

   connect_sysadm(&sqlcode);

   if (sqlcode != 0)

   {

printf("Failed to connect SYSADM.\n");

 

       get_diagn(&errcode, errmsg, &sqlcode);

       if (sqlcode != 0)

       {

printf("Failed to get diagnostics message.\n");

       }

       else

       {

printf("errcode: %d\n", errcode);

printf("errmsg: %s\n", errmsg);

       }

   }

Example host application in Fortran:

     INTEGER*4 SQLCODE

     INTEGER*4 ERRCODE

     CHARACTER*300 ERRMSG

 

     CALL CONNECT_SYSADM(SQLCODE)

     IF (SQLCODE .NE. 0) THEN

          PRINT *, 'Failed to connect SYSADM.'

 

         IF (SQLCODE .NE. 0) THEN

              PRINT *, 'Failed to get diagnostics message.'

         ELSE

              PRINT *, 'errcode: ', ERRCODE

              WRITE(*, '(X,72A)') 'errmsg: ', ERRMSG

         END IF

     END IF

Example host application in Cobol:

      DATA DIVISION.

      WORKING-STORAGE SECTION.

      01 SQLCODE PIC S9(9) USAGE IS BINARY.

      01 ERRCODE PIC S9(9) USAGE IS BINARY.

      01 ERRMSG PICTURE X(300).

 

      PROCEDURE DIVISION.

      HEAD SECTION.

 

      MAIN.

 

          CALL "CONNECT_SYSADM" USING SQLCODE.

          IF SQLCODE IS NOT ZERO

              DISPLAY "Failed to connect SYSADM."

 

              CALL "GET_DIAGN" USING ERRCODE, ERRMSG, SQLCODE.

              IF SQLCODE IS NOT ZERO

                  DISPLAY "Failed to get diagnostics message."

              ELSE

                  DISPLAY "errcode: " ERRCODE

                  DISPLAY "errmsg: " ERRMSG

              END-IF.

          END-IF.

Example host application in Pascal:

type message = packed array [1..300] of char;

 

var

sqlcode : integer;

errcode : integer;

errmsg : message;

 

procedure connect_sysadm(var sqlcode : integer); external;

procedure get_diagn(var errcode : integer, var errmsg : message,

  var sqlcode : integer); external;

 

begin

  connect_sysadm(sqlcode);

  if sqlcode <> 0 then

  begin

     writeln('Failed to connect SYSADM.');

 

     get_diagn(errcode, errmsg, sqlcode);

     if sqlcode <> 0 then

     begin

        writeln('Failed to get diagnostics message.');

     end;

     else

     begin

        writeln('errcode: ', errcode)

        writeln('errmsg: ', errmsg);

     end;

Host Language Dependent Aspects

You can call SQL modules (MSQL) in any host language that supports the C data types generated by the MSQL preprocessor. The following host languages are supported:

•C/C++

•COBOL

•Fortran

•Pascal

Note:This is not a complete description of the rules for writing SQL modules. The programmer should use the main body of this manual as a guide to writing programs and refer to this appendix for language-specific details.

This section describes the recommended data types to use in each of the above mentioned host languages when calling an SQL module procedure.

The SQL data types INT(n>18), FLOAT, BINARY and VARBINARY are handled by Module SQL as null-terminated C char arrays, no matter the calling host language. The host application needs to apply null termination to any input, and take precautions to null termination appearing in any output.

The SQL data types DATE, TIME, TIMESTAMP, INTERVAL, CHAR, VARCHAR, CLOB and BOOLEAN are handled by Module SQL as character arrays of the given host language, see Module declaration for more details.

C header files

When the LANGUAGE clause in an SQL module is set to C, a C header file containing embedded SQL statements will be generated by the MSQL preprocessor, by default with the .eh file extension. The embedded SQL header file needs to be pre-processed using the ESQL command into a pure C header file (the header file name and extension has to be specified to ESQL.) It can then be included in the C host application to provide the routine headers for the routines generated in the source code file.

Example

The SQL module currencies.msq has the LANGUAGE clause set to C. It is pre-processed using the MSQL command.

msql currencies.msq

 

The embedded SQL files currencies.ec and currencies.eh are generated. They are pre-processed using the ESQL commands.

esql --c currencies.ec

esql --header currencies.eh

 

or

esql /C currencies.ec

esql /HEADER currencies.eh

 

The C source file currencies.c is generated, containing the source code that calls the Mimer database. The C header file currencies.h is generated for inclusion in the C host application program.

#include "currencies.h"

 

int main

{

   int sqlcode;

   open_currencies_cursor(&sqlcode);

C data types

Note:The stdint types int16_t, int32_t and int64_t can be used in the host application if desired. The SQL module does not use them.

COBOL data types

Fortran data types

Pascal data types