c NAME
c	hall_test
c 
c FILE 
c	hall_test.f
c
c SYNOPSIS
c       test Norm Hall's killer query
c
c     ===============  Sample POSTGRES program =====================

      common /sccsfilename/ sccsid
      character*80 sccsid
      data sccsid /'@(#)gdi_f77_pg_test.f       1.5     7/8/93'/

      include '../../include/gdi_f77_refdefs.h'

C     define local variables

C     ==================  Connect to database ==================
      CHARACTER*10  VENDOR, DATABASE, NA
      CHARACTER*16  PRGNAM
      INTEGER       DBCONN

C     ==================  Error Handling Variables =============
      CHARACTER*80  ERRTXT
      INTEGER       MAXTXT, STATUS, SEVERITY, ERRCDE

C     ==================  Query variables ======================
      INTEGER*4     MAP_ID
      CHARACTER*400 QUERY
      INTEGER       MAXRECS, ROWS_RETRIEVED, ROWS_AFFECTED
      LOGICAL*4     MORE_DATA

C     ==================  Output variables ============================
      INTEGER*4     OID(20)
      INTEGER*4     TEMP(20)
      INTEGER*2     DEPTH(20)
      INTEGER*4     SAL(20)
      INTEGER*4     I

      VENDOR = 'postgres'
      DATABASE = 'hall'
      PRGNAM = 'hall_test'

C     Some GDI_OPEN arguments are Not Applicable (NA) to POSTGRES
      NA = ' '

      MAXRECS = 20
      MAXTXT = 80

C     =================  Initialize the GDI. ==========================
      STATUS = GDI_INIT(PRGNAM)
      IF (STATUS .NE. GDI_SUCCESS) THEN
         WRITE (6,*) 'GDI_INIT Failed.  Program exiting.'
         GOTO 999
      END IF

C     ============ Open a connection to the database. ===================
      DBCONN = GDI_OPEN(VENDOR, NA, NA, DATABASE, NA, PRGNAM)
      IF (DBCONN .EQ. GDI_NOCONN) THEN
         CALL GDI_ERROR_GET(DBCONN, ERRCDE, ERRTXT, MAXTXT, 
     +        STATUS, SEVERITY)
         WRITE (6,*) 'GDI_OPEN Failed: Error Code ', ERRCDE
         WRITE (6,*) ERRTXT
         GOTO 999
      END IF

C     Set debug and threshold flags. GDI_DEBUG_ON prints errors to the screen.

      CALL GDI_ERROR_INIT(DBCONN, GDI_DEBUG_ON, GDI_WARNING, 
     +   RESERVED1, RESERVED2)

C     ===========  Build a query ========================================
      QUERY = 'retrieve (a.oid, b.depth, b.temp, b.sal)'//
     +    ' from a in ngots_raw_hdr, b in ngots_dts'//
     +    ' where ( (a.quad = ''1'' and a.lon >= 11000'//
     +    ' and a.lat >= 2000 and a.lon < 18000 and'//
     +    ' a.lat <= 6000) or'//
     +    ' (a.quad = ''7'' and a.lon >= 10000 and a.lat >= 2000 and'//
     +    ' a.lon < 18000 and a.lat <= 6000)) and a.oid = b.hoid and'//
     +    ' a.date >= 609999 and a.date <= 619999'

C     ===========  Create a query mapping ===============================
      MAP_ID = GDI_OPEN_MAP(DBCONN)
      IF (MAP_ID .EQ. GDI_NOMAP) THEN
         GOTO 999
      END IF

C     Map each attribute being retrieved to a FORTRAN variable.

      STATUS = GDI_ADD_MAP_FIELD(DBCONN, MAP_ID, 
     +     'oid', OID, GDI_INT4, 0, 0)
      IF (STATUS .NE. GDI_SUCCESS) THEN
         GOTO 999
      END IF

      STATUS = GDI_ADD_MAP_FIELD(DBCONN, MAP_ID, 
     +     'depth', DEPTH, GDI_INT2, 0, 0)
      IF (STATUS .NE. GDI_SUCCESS) THEN
         GOTO 999
      END IF

      STATUS = GDI_ADD_MAP_FIELD(DBCONN, MAP_ID, 
     +     'temp', TEMP, GDI_INT4, 0, 0)
      IF (STATUS .NE. GDI_SUCCESS) THEN
         GOTO 999
      END IF

      STATUS = GDI_ADD_MAP_FIELD(DBCONN, MAP_ID, 
     +     'sal', SAL, GDI_INT4, 0, 0)
      IF (STATUS .NE. GDI_SUCCESS) THEN
         GOTO 999
      END IF

      CALL GDI_CLOSE_MAP(DBCONN, MAP_ID)

C     ================ Execute the query =================================
      STATUS = GDI_SUBMIT(DBCONN, MAP_ID, QUERY, MAXRECS,
     +     ROWS_RETRIEVED, ROWS_AFFECTED, MORE_DATA)

      IF (STATUS .NE. GDI_SUCCESS) THEN
         GOTO 999
      END IF

C     ================ Print out the retrieved data. =====================
      WRITE (6,*) ROWS_RETRIEVED, ' rows were retrieved.'
      WRITE (6,*) ' '
      DO 10 I = 1, ROWS_RETRIEVED
         WRITE (6,*) OID(I), DEPTH(I), TEMP(I), SAL(I)
 10   CONTINUE

      WRITE (6,*) ' '
      IF (MORE_DATA) THEN
         WRITE (6,*) 'More rows are available.'
      ELSE
         WRITE (6,*) 'No more data exists in the database.'
      END IF

C     Destroy query mapping.
      CALL GDI_DESTROY_MAP(DBCONN, MAP_ID)

 999  STATUS = GDI_CLOSE(DBCONN)
      
      END