Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


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:
 void open_currencies_cursor(int* sqlcode);
 void fetch_currency_code(char code[4], int* sqlcode);
 void close_currencies_cursor(int* sqlcode);
 
 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.


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