Mimer SQL Programmer's Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


FREQCALL Program Using Dynamic SQL and a Stored Procedure


The FREQCALL source code demonstrates how to embed a call to a stored procedure. The procedure used is included in the example database, described in the Mimer SQL System Management Handbook, which must be installed in order to execute the programs successfully.

Building the FREQCALL Program

UNIX: The following example assumes Mimer SQL is installed in the directory /opt/mimer821A and illustrates how the C version of the FREQCALL program is compiled and built by using the distributed example makefile:
 $ mkdir freqcall# Do everything in a sub directory
 $ cd freqcall
 $ cp /opt/mimer821A/examples/ex_makefile ./makefile
 $ cp /opt/mimer821A/examples/freqcall.ec .
 
Take a copy of the makefile and update it so that the MYPROG symbol is set to "freqcall", as the name of the program to be created (avoid trailing spaces).
 MYPROG = freqcall
Then make the freqcall program:
 $ export MIMER_HOME=/opt/mimer821A
 $ make
 .
 .

VMS: The following example illustrates how the C version of the FREQCALL program is compiled and linked under VMS. The programs written in the other languages can be linked in a similar manner.
 $ ESQL/C MIMEXAMPLES8:FREQCALL     ! Preprocess source code$ CC FREQCALL                      ! Compile sample program$ LINK FREQCALL,MIMLIB8:MIMER/OPT  ! Link executable program

Win: On Windows platforms, the examples files are installed in the dev directory which is located below the installation directory. The dev directory contains a makefile called makefile.mak, which will build all the example programs at the same time. The examples have been tested using Microsoft Visual C.
These instructions assume that Microsoft Visual C is used and that the make command is being executed from the dev directory, appropriate adjustments must be made if this is not the case.
$ nmake -f makefile.mak

C Source Code for the FREQCALL Program

 #include <stdio.h>
 #include <stdlib.h>
 #include <string.h>
 
 /*
 **  Example of Embedded Stored Procedure Calls.
 **
 **  This program calls the procedure freeq, to find out the number of
 **  vacant rooms at a given hotel.
 */
 
 exec sql BEGIN DECLARE SECTION;
 static char sqlstate[6];
 exec sql END DECLARE SECTION;
 
 void print_sqlerror()
 /*
 **  print_sqlerror prints an error message for the latest error.
 **  Programmed according to the X/Open CAE specification.
 */
 {
     exec sql BEGIN DECLARE SECTION;
     int i;
     int exceptions;
     VARCHAR message[255];
     exec sql END DECLARE SECTION;
 
     exec sql GET DIAGNOSTICS :exceptions = NUMBER; /* How many exceptions? */
     for (i=1; i<=exceptions; i++) {
         exec sql GET DIAGNOSTICS EXCEPTION :i
                                            :message = MESSAGE_TEXT;
         printf("%s\n", message);
     }
 }
 
 main()
 {
     exec sql BEGIN DECLARE SECTION;
     char query[200] = "call freeq(?,?,?,?,?)";
     char hotelcode[30];
     char roomtype[30];
     char arrive[30], depart[30];
     int  rooms;
     exec sql END DECLARE SECTION;
 
     exec sql WHENEVER SQLERROR goto error_exit;
 
     exec sql CONNECT TO ' ' USER 'HOTELADM' USING 'HOTELADM';
 
     /*
      * Make it easy for us. Ask for free rooms on the SKY hotel,
      * roomtype SSGLS during new years eve of the new millennium.
      */
     strcpy(hotelcode,"SKY");
     strcpy(roomtype,"SSGLS");
     strcpy(arrive,"1999-12-31");
     strcpy(depart,"2000-01-01");
 
     exec sql PREPARE CALL FROM :query;
     exec sql EXECUTE CALL INTO :rooms
                  USING :hotelcode, :roomtype, :arrive, :depart;
     printf("%d available rooms.\n",rooms);
 
     exec sql commit;
     exec sql DISCONNECT;
     exit(0);
 
 error_exit:
     print_sqlerror();
 
     exec sql WHENEVER SQLERROR CONTINUE;
     exec sql rollback;
     exec sql DISCONNECT;
     exit(0);
     return 0;
 }
 

COBOL Source Code for the FREQCALL Program

        IDENTIFICATION DIVISION.
        PROGRAM-ID. FREQCALL.
       *
       * Example of Embedded Stored Procedure Calls.
       *
       * This program calls the procedure freeq, to find out the number 
       * of vacant rooms at a given hotel.
       *
 
        DATA DIVISION.
        WORKING-STORAGE SECTION.
            EXEC SQL BEGIN DECLARE SECTION END-EXEC.
        01 SQLSTATE PICTURE X(5).
        01 QUERY PICTURE X(200).
        01 HOTELCODE PICTURE X(30).
        01 ROOMTYPE PICTURE X(30).
        01 ARRIVE PICTURE X(30).
        01 DEPART PICTURE X(30).
        01 ROOMS PIC S9(10) COMP.
        01 EXCEPTIONS PIC S9(10) COMP.
        01 LINE-NUMBER PIC S9(10) COMP.
        01 ERROR-MESSAGE PICTURE X(254).
            EXEC SQL END DECLARE SECTION END-EXEC.
 
        PROCEDURE DIVISION.
        BIG.
 
            EXEC SQL WHENEVER SQLERROR GO TO ERROR-EXIT END-EXEC.
 
            EXEC SQL CONNECT TO '' USER 'HOTELADM' USING 'HOTELADM'
                END-EXEC.
 
       *
       *  Make it easy for us. Ask for free rooms on the SKY hotel,
       *  roomtype SSGLS during new years eve of the new millennium.
       *
 
            MOVE 'SKY' TO HOTELCODE.
            MOVE 'SSGLS' TO ROOMTYPE.
            MOVE '1999-12-31' TO ARRIVE.
            MOVE '2000-01-01' TO DEPART.
 
            MOVE 'call freeq(?,?,?,?,?)' TO QUERY.
 
            EXEC SQL PREPARE CALL FROM :QUERY END-EXEC.
            EXEC SQL EXECUTE CALL INTO :ROOMS
                         USING :HOTELCODE, :ROOMTYPE,
                               :ARRIVE, :DEPART END-EXEC.
 
            DISPLAY ROOMS WITH CONVERSION " available rooms.".
 
            EXEC SQL COMMIT END-EXEC.
            EXEC SQL DISCONNECT DEFAULT END-EXEC.
            STOP RUN.
 
        ERROR-EXIT.
            EXEC SQL WHENEVER SQLERROR CONTINUE  END-EXEC.
            EXEC SQL GET DIAGNOSTICS :EXCEPTIONS = NUMBER  END-EXEC.
            PERFORM DISPLAY-ERROR-LINE VARYING LINE-NUMBER
                       FROM 1 BY 1
                       UNTIL LINE-NUMBER IS GREATER THAN EXCEPTIONS.
            EXEC SQL DISCONNECT DEFAULT END-EXEC.
            STOP RUN.
 
        DISPLAY-ERROR-LINE.
            EXEC SQL GET DIAGNOSTICS EXCEPTION :LINE-NUMBER
                         :ERROR-MESSAGE = MESSAGE_TEXT END-EXEC.
            DISPLAY ERROR-MESSAGE.
 
        END PROGRAM FREQCALL.
 

FORTRAN Source Code for the FREQCALL Program

       PROGRAM FREQCALL
 C
 C  Example of Embedded Stored Procedure Calls.
 C
 C  This program calls the procedure freeq, to find out the number of
 C  vacant rooms at a given hotel.
 C
       EXEC SQL BEGIN DECLARE SECTION
       CHARACTER*5   SQLSTATE
       CHARACTER*200 QUERY
       CHARACTER*30  HOTELCODE
       CHARACTER*30  ROOMTYPE
       CHARACTER*30  ARRIVE
       CHARACTER*30  DEPART
       INTEGER       ROOMS
       EXEC SQL END DECLARE SECTION
 
       EXEC SQL WHENEVER SQLERROR GOTO 9000
 
       EXEC SQL CONNECT TO '' USER 'HOTELADM' USING 'HOTELADM'
 
 C
 C  Make it easy for us. Ask for free rooms on the SKY hotel,
 C  roomtype SSGLS during new years eve of the new millennium.
 C
 
       HOTELCODE = 'SKY'
       ROOMTYPE = 'SSGLS'
       ARRIVE = '1999-12-31'
       DEPART = '2000-01-01'
 
       QUERY = 'call freeq(?,?,?,?,?)'
 
       EXEC SQL PREPARE CALL FROM :QUERY
       EXEC SQL EXECUTE CALL INTO :ROOMS
      +           USING :HOTELCODE, :ROOMTYPE, :ARRIVE, :DEPART
 
       WRITE(6,100) ROOMS,' available rooms.'
  100  FORMAT (I,A,X,A)
 
       GOTO 9999
 
  9000 CONTINUE
       CALL PRINT_SQLERROR
 
  9999 CONTINUE
       EXEC SQL WHENEVER SQLERROR CONTINUE
       EXEC SQL COMMIT
       EXEC SQL DISCONNECT DEFAULT
       END
 
 
       SUBROUTINE PRINT_SQLERROR
 C
 C  PRINT_SQLERROR prints an error message for the latest error.
 C  Programmed according to the X/Open CAE specification.
 C
 
       EXEC SQL BEGIN DECLARE SECTION
       CHARACTER*5   SQLSTATE
       INTEGER       I,ERRORS,MSGLEN
       CHARACTER*254 MESSAGE
       EXEC SQL END DECLARE SECTION
 
       EXEC SQL GET DIAGNOSTICS :ERRORS = NUMBER
       DO 10 I=1,ERRORS
          EXEC SQL GET DIAGNOSTICS EXCEPTION :I
      +        :MESSAGE = MESSAGE_TEXT,
      +        :MSGLEN  = MESSAGE_LENGTH
          WRITE (6,100) MESSAGE(:MSGLEN)
  100     FORMAT(X,A)
  10   CONTINUE
 
       RETURN
       END
 


Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Programmer's Manual TOC PREV NEXT INDEX