Mimer SQL Programmer's Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


EXAMPLE Program


This simple EXAMPLE program is written according to international SQL standards and it should be possible to use it on any standard-compliant database management system without modification.

The program logs in to the default database with the user ident SYSADM and password SYSADM (change the program as appropriate). The program will then select and print all the rows of the X/Open standard system catalog view INFORMATION_SCHEMA.TABLES, i.e. lists all tables in the system.

Building the EXAMPLE Program

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

VMS: The following example illustrates how the FORTRAN version of the EXAMPLE program is compiled and linked under VMS. The programs written in the other languages can be linked in a similar manner.
 $ ESQL/FORTRAN MIMEXAMPLES8:EXAMPLE  ! Preprocess source code$ FORTRAN EXAMPLE ! Compile preprocessed code$ LINK EXAMPLE,MIMLIB8:MIMER/OPT     ! Link an 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 EXAMPLE Program

 #include <stdio.h>
 #include <stdlib.h>
 #include <string.h>
 
 /*
 **  Example of X/Open CAE SQL (1992) compliant embedded program.
 **
 **  The program tries to connect to the DEFAULT database as SYSADM
 **  with password SYSADM.
 **  It will then declare a cursor for the standard view
 **  INFORMATION_SCHEMA.TABLES, and fetch all records from it.
 **
 **  If any error occurs, the print_sqlerror routine will be called
 **  to print an error message.
 */
 
 
 /*
 **  X/Open does not allow the same variable to be defined in several
 **  DECLARE SECTIONs, so we declare SQLSTATE here so that all routines
 **  in the file can use the same variable.
 */
 exec sql BEGIN DECLARE SECTION;
 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;
 
 /* 
 **  How many exceptions? 
 */
     exec sql GET DIAGNOSTICS :exceptions = NUMBER; 
     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;
     VARCHAR schema[129];
     VARCHAR table[129];
     char type[21];
     exec sql END DECLARE SECTION;
 
     exec sql DECLARE MYCURSOR CURSOR FOR
 	SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
          FROM INFORMATION_SCHEMA.TABLES;
 
 /* 
 **  Connect to DEFAULT 
 */
     exec sql WHENEVER SQLERROR goto error_exit;
 
     exec sql CONNECT TO '' USER 'SYSADM' USING 'SYSADM'; 
 
     exec sql OPEN MYCURSOR;
 
     while (1) {
 	    exec sql FETCH MYCURSOR INTO :schema,:table,:type;
 	    if (strcmp(SQLSTATE, "02000") == 0) break; /* No more rows */
 	    printf("%s %s %s\n", schema, table, type);
     }
 
     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 DEFAULT;
     exit(0);
     return 0;
 }
 

COBOL Source Code for the EXAMPLE Program

        IDENTIFICATION DIVISION.
        IDENTIFICATION DIVISION.
        PROGRAM-ID. EXAMPLE.
       *
       **  Example of X/Open CAE SQL (1992) compliant embedded program.
       **
       **  The program tries to connect to the DEFAULT database as 
       **  SYSADM with password SYSADM.
       **  It will then declare a cursor for the standard view
       **  INFORMATION_SCHEMA.TABLES, and fetch all records from it.
       *
 
        DATA DIVISION.
        WORKING-STORAGE SECTION.
            EXEC SQL BEGIN DECLARE SECTION END-EXEC.
        01 SQLSTATE PICTURE X(5).
        01 TABLE-SCHEMA PICTURE X(128).
        01 TABLE-NAME PICTURE X(128).
        01 TABLE-TYPE PICTURE X(20).
 
        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
                SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
                FROM INFORMATION_SCHEMA.TABLES END-EXEC.
 
            EXEC SQL WHENEVER SQLERROR GO TO ERROR-EXIT END-EXEC.
 
       **   CONNECT TO DEFAULT DATABASE
            EXEC SQL CONNECT TO '' USER 'SYSADM' USING 'SYSADM' 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 :TABLE-SCHEMA,
                                         :TABLE-NAME,
                                         :TABLE-TYPE     END-EXEC.
 
            IF SQLSTATE NOT = "02000",
                DISPLAY TABLE-SCHEMA, TABLE-NAME, TABLE-TYPE.
 
 
        ERROR-EXIT.
            EXEC SQL ROLLBACK END-EXEC.
            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 EXAMPLE.
 

FORTRAN Source Code for the EXAMPLE Program

       PROGRAM SQLEX
 C
 C  Example of SQL-92 compliant embedded FORTRAN program.
 C
 C  The program tries to connect to the DEFAULT database as SYSADM
 C  with  password SYSADM.
 C  It will then create a cursor to the standard view
 C  INFORMATION_SCHEMA.TABLES, and fetch all records from it.
 C
 C  If any error occurs, the PSQLER routine will be called to
 C  print an error message.
 C
       EXEC SQL BEGIN DECLARE SECTION
       CHARACTER*5  SQLSTATE
       CHARACTER*128 SCHEMA
       CHARACTER*128 TABLE
       CHARACTER*20 TYPE
       EXEC SQL END DECLARE SECTION
 
       EXEC SQL DECLARE MYCURSOR CURSOR FOR
      +     SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
      +     FROM INFORMATION_SCHEMA.TABLES
 
       EXEC SQL WHENEVER SQLERROR GOTO 9000
 
 C     Connect to the default database
       EXEC SQL CONNECT TO '' USER 'SYSADM' USING 'SYSADM'
 
       EXEC SQL OPEN MYCURSOR
 
       EXEC SQL FETCH MYCURSOR INTO :SCHEMA,:TABLE,:TYPE
       DO WHILE (SQLSTATE .NE. '02000')
          WRITE(6,100) SCHEMA,TABLE,TYPE
  100     FORMAT (X,A,X,A,X,A)
          EXEC SQL FETCH MYCURSOR INTO :SCHEMA,:TABLE,:TYPE
       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
 


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