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 declare 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*129 SCHEMA
      CHARACTER*129 TABLE
      CHARACTER*21  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 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(1:29),TABLE(1:29),TYPE
 100     FORMAT (X,A,X,A,X,A)
         EXEC SQL FETCH MYCURSOR INTO :SCHEMA,:TABLE,:TYPE
      END DO

      EXEC SQL CLOSE MYCURSOR
      EXEC SQL COMMIT

      GOTO 9999

 9000 CONTINUE
      EXEC SQL WHENEVER SQLERROR CONTINUE
      CALL PSQLER
      EXEC SQL ROLLBACK

 9999 CONTINUE
      EXEC SQL WHENEVER SQLERROR CONTINUE
      EXEC SQL DISCONNECT ALL
      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