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_
