Introduction

Using an SQL module is a way to separate SQL code from the host application, allowing SQL statements to be reused and separately maintained, while still providing a straightforward way to access databases similar to Embedded SQL.

An SQL module is simply a list of SQL statements, put into procedures. At any point you like in the host program, you can explicitly call such an SQL procedure to execute the SQL statement in that procedure. You call the SQL procedure as if it were a subprogram in the host language. For more information and references on Module SQL, see the Links section down below.

A C compiler is needed to compile an SQL module for Mimer.

Module SQL is available from Mimer version 11.0.3A.

Function

The MSQL preprocessor processes the SQL statements in an SQL module into embedded SQL in C, which is then sent to the ESQL preprocessor to be processed into a C file that can be compiled and linked together with a calling host application.

Every SQL statement in an SQL module is put into a procedure which corresponds to a C function. Host variables are used as input or output (or both) variables to the procedure. A host application can then call the SQL statement procedure as an external C function.

You can find detailed information on Module SQL in the Programmer’s Manual part of the Mimer SQL Documentation Set, found in the Documentation page.

Example

The following is an example SQL module and C application that connects to the database defined as the default. All rows are then fetched from the table SYSTEM.TYPE_INFO.

Example applications in Fortran, Cobol and Pascal are available as well, named simple_prg.for, simple_prg.cob and simple_prg.pas. Please note that if the Fortran, Cobol or Pascal examples are to be used, the LANGUAGE clause of the SQL module below needs to be changed to LANGUAGE FORTRAN, LANGUAGE COBOL or LANGUAGE PASCAL accordingly, before running it through the MSQL preprocessor.

The SQL module

The example SQL module is stored in a provided file called simple.msq:

MODULE SIMPLE
LANGUAGE C

DECLARE MYCURSOR CURSOR FOR
   SELECT TYPE_NAME, COLUMN_SIZE
   FROM SYSTEM.TYPE_INFO
   ORDER BY COLUMN_SIZE

PROCEDURE connect_to_default_database
   SQLCODE;
CONNECT TO '' USER 'SYSADM' USING 'SYSADM';

PROCEDURE open_mycursor
   SQLCODE;
OPEN MYCURSOR;

PROCEDURE fetch_mycursor_into
   :type_name     CHAR(30),
   :column_size   INT,
   SQLCODE;
FETCH MYCURSOR INTO :type_name, :column_size;

PROCEDURE close_mycursor
   SQLCODE;
CLOSE MYCURSOR;

PROCEDURE commit_transaction
   SQLCODE;
COMMIT;

PROCEDURE disconnect_all
   SQLCODE;
DISCONNECT ALL;

The module SQL file is preprocessed as follows:

msql simple.msq

The resulting files, simple.ec and simple.eh, are preprocessed by the ESQL preprocessor into the files simple.c and simple.h. The source file is then compiled using a C compiler into simple.o.

The main program

The example C application is stored in a file called simple_prg.c, and should be compiled using a C compiler into simple_prg.o:

#include <stdio.h>
#include "simple.h"

int main()
{
   int     sqlcode;
   int     column_size;
   char    type_name[31];

   /* Connect to default database */

   connect_to_default_database(&sqlcode);
   open_mycursor(&sqlcode);
   fetch_mycursor_into(type_name, &column_size, &sqlcode);
   while (sqlcode == 0) {
       printf("%s %d\n", type_name, column_size);
       fetch_mycursor_into(type_name, &column_size, &sqlcode);
   }
   close_mycursor(&sqlcode);
   commit_transaction(&sqlcode);
   disconnect_all(&sqlcode);
   return 0;
}

Linking and execution

The main program object file, simple_prg.o is then linked with the module SQL object file, simple.o, and the Mimer SQL shared library or DLL providing the database API. Usually, as an aid to this step, an example makefile or a build procedure is available in the Mimer SQL software distribution package.

When executing the resulting program, a list of the items selected will be displayed.

Benefits

Module SQL lets you keep your SQL statements separate from your host application. This makes debugging the host application easier, and you can distribute development and maintenance of SQL code and host application code into different resources.

Links

In the Mimer SQL software distribution package you can find other and more complete examples on Module SQL. For example, see the SQL module example.msq file, used with the main program files; example_prg.c (C), example_prg.for (Fortran), example_prg.cob (Cobol) and example_prg.pas (Pascal). This example is the same as the simple one described in this article, but with a complete error handling. All example files used in this article, and other ones covering more detailed functionality, are available in the Mimer SQL software distribution packages found at the Download page.

For details on MSQL and ESQL, please turn to the Programmer’s Manual part of the Mimer SQL Documentation Set, found in the Documentation page.

This feature is included in the SQL standard, and can be read about in the SQL document called “ISO_IEC_9075-2(E)_Foundation”.  The SQL feature ID is E182, named “Host language binding”, with the subheading “SQL-client modules” (this feature was previously called “Module language”).