WAKECALL Program Using Dynamic SQL and a Result-set Procedure

The WAKECALL source code demonstrates how to embed a call to a result-set 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 WAKECALL Program


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


VMS: The following example illustrates how the C version of the WAKECALL program is compiled and linked under VMS. The programs written in the other languages can be linked in a similar manner. $ ESQL/C MIMEXAMPLES8:WAKECALL ! Preprocess source code$ CC WAKECALL ! Compile sample program$ LINK WAKECALL,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 WAKECALL Program

#include <stdio.h> #include <stdlib.h> #include <string.h> /* ** Example of Embedded Stored Procedure Calls. ** ** This program calls the procedure wake_up, which returns ** a list of those rooms which have requested wake up calls within ** the specified amount of time. */ 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 roomnr[30]; exec sql END DECLARE SECTION; exec sql DECLARE MYCURSOR CURSOR FOR CALL wake_up(interval '30' minute); exec sql WHENEVER SQLERROR goto error_exit; exec sql CONNECT TO ' ' USER 'HOTELADM' USING 'HOTELADM'; exec sql OPEN MYCURSOR; while (1) { exec sql FETCH MYCURSOR INTO :roomnr; if (strcmp(sqlstate, "02000") == 0) break; /* No more rows */ printf("Room %s\n", roomnr); } exec sql CLOSE MYCURSOR; exec sql commit; exec sql DISCONNECT DEFAULT; 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 WAKECALL Program

IDENTIFICATION DIVISION. PROGRAM-ID. WAKECALL. * ** Example of Embedded Stored Procedure Calls. ** ** This program calls the procedure wake_up, which returns ** a list of those rooms which have requested wake up calls ** within the specified amount of time. * DATA DIVISION. WORKING-STORAGE SECTION. EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 SQLSTATE PICTURE X(5). 01 ROOMNR PICTURE X(30). 01 EXCEPTIONS PIC S9(10) COMP. 01 ERROR-MESSAGE PIC X(254). 01 LINE-NUMBER PIC S9(10) COMP. EXEC SQL END DECLARE SECTION END-EXEC. PROCEDURE DIVISION. MAIN SECTION. DO-IT. EXEC SQL DECLARE MYCURSOR CURSOR FOR CALL wake_up(interval '30' minute) END-EXEC. EXEC SQL WHENEVER SQLERROR GO TO ERROR-EXIT END-EXEC. EXEC SQL CONNECT TO '' USER 'HOTELADM' USING 'HOTELADM' END-EXEC. EXEC SQL OPEN MYCURSOR END-EXEC. PERFORM FETCH-AND-DISPLAY UNTIL SQLSTATE = "02000". EXEC SQL CLOSE MYCURSOR END-EXEC. EXEC SQL COMMIT END-EXEC. EXEC SQL DISCONNECT DEFAULT END-EXEC. STOP RUN. FETCH-AND-DISPLAY. EXEC SQL FETCH MYCURSOR INTO :ROOMNR END-EXEC. IF SQLSTATE NOT = "02000", DISPLAY "Room " ROOMNR. 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 ROLLBACK END-EXEC. 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 WAKECALL.

FORTRAN Source Code for the WAKECALL Program

PROGRAM WAKECALL C C Example of Embedded Stored Procedure Calls. C C This program calls the procedure wake_up, which returns C a list of those rooms which have requested wake up calls within C the specified amount of time. C EXEC SQL BEGIN DECLARE SECTION CHARACTER*5 SQLSTATE CHARACTER*30 ROOMNO EXEC SQL END DECLARE SECTION EXEC SQL DECLARE MYCURSOR CURSOR FOR + CALL wake_up(interval '30' minute) EXEC SQL WHENEVER SQLERROR GOTO 9000 EXEC SQL CONNECT TO '' USER 'HOTELADM' USING 'HOTELADM' EXEC SQL OPEN MYCURSOR EXEC SQL FETCH MYCURSOR INTO :ROOMNO DO WHILE (SQLSTATE .NE. '02000') WRITE(6,100) 'Room ', ROOMNO 100 FORMAT (X,A,X,A) EXEC SQL FETCH MYCURSOR INTO :ROOMNO END DO EXEC SQL CLOSE MYCURSOR GOTO 9999 9000 CONTINUE CALL PSQLER 9999 CONTINUE EXEC SQL WHENEVER SQLERROR CONTINUE EXEC SQL COMMIT EXEC SQL DISCONNECT DEFAULT END SUBROUTINE PSQLER C C PSQLER 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