c NAME
c	two_connects
c 
c FILE 
c	two_connects.f
c
c SYNOPSIS
c       Test connecting to two postgres databases in one program.
c
c     

      common /sccsfilename/ sccsid
      character*80 sccsid
      data sccsid /'%W% %G%'/

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

C     define local variables

C     ==================  Connect to database ==================
      CHARACTER*20  VENDOR, DATABASE1, DATABASE2, NA
      CHARACTER*16  PRGNAM
      CHARACTER*30  DBHOST1, DBHOST2
      INTEGER       DBCONN

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

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

C     ==================  Output variables ============================
      CHARACTER*16  DBNAME(30)

      INTEGER       I

      VENDOR = 'postgres'
      PRGNAM = 'two_connects'

      DATABASE1 = 'jta'
      DBHOST1 = 'heel.s2k.berkeley.edu'
      DATABASE2 = 'jta2'
      DBHOST2 = 'arcadia.CS.Berkeley.EDU'

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

      MAXRECS = 30
      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 first database. ===================
      DBCONN = GDI_OPEN(VENDOR, NA, NA, DATABASE1, DBHOST1, 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 =========================
      WRITE (QUERY, 5000)
 5000 FORMAT('retrieve (pg_database.datname)')


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.
c     or array in this case.

      STATUS = GDI_ADD_MAP_FIELD(DBCONN, MAP_ID, 
     +     'datname', DBNAME, GDI_STRING, 16, 0)
      IF (STATUS .NE. GDI_SUCCESS) THEN
         GOTO 999
      END IF

      CALL GDI_CLOSE_MAP(DBCONN, MAP_ID)

C     ================ Execute the query on the first database ===========
      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     ================ Close down the first connection ===================
      STATUS = GDI_CLOSE(DBCONN)

C     ================ Print out the retrieved data. =====================
      WRITE (6,*) ' '
      WRITE (6,*) ' The following POSTGRES databases are on ', DBHOST1
      WRITE (6,*) ' '
      DO 10 I = 1, ROWS_RETRIEVED
         WRITE (6,*) '     ', DBNAME(I)

 10   CONTINUE
      
      WRITE (6,*) ' '

      IF (MORE_DATA) THEN
         ROWS_LEFT = ROWS_AFFECTED - ROWS_RETRIEVED
         WRITE (6,*) ROWS_LEFT, ' more rows are available.'
      END IF

C     ============ Open a connection to the second database. ===================
      DBCONN = GDI_OPEN(VENDOR, NA, NA, DATABASE2, DBHOST2, 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     ================ Execute the query on the second database ===========
      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,*) ' '
      WRITE (6,*) ' The following POSTGRES databases are on ', DBHOST2
      WRITE (6,*) ' '
      DO 20 I = 1, ROWS_RETRIEVED
         WRITE (6,*) '     ', DBNAME(I)

 20   CONTINUE
      
      WRITE (6,*) ' '

      IF (MORE_DATA) THEN
         ROWS_LEFT = ROWS_AFFECTED - ROWS_RETRIEVED
         WRITE (6,*) ROWS_LEFT, ' more rows are available.'
      END IF

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

 999  STATUS = GDI_CLOSE(DBCONN)
      
      END