c******************************************************************************
c* filename:	demo2.f
c*
c* purpose:	to read in data from 2 classes/databases.  the databases/
c*		classes and hosts will be specified by the user.
c*
c* user input: (note: enter the following input in the file demo2.com)
c*
c*					max length	examples
c*
c*	1) filename 			16 characters	(demo2)
c*	2) first database name		20 chars	(demo)
c*	3) first database host name	30 chars	(aeolus.s2k.ucsd.edu)
c*	4) max no. of recs to output for 1st query	(20)
c*	5) first output file		30 chars	(demo2lks.dat)
c*	6) second database name		20 chars	(FNF)
c*	7) second database host name	30 chars	(arcadia.berkeley.edu)
c*	8) max no. of recs to output for 2nd query	(1200)
c*	9) second output file		30 chars	(demo2folsom.dat)
c*
c* directory:	/kidlat1/users/bainto/GDIDEMO/demo2.f
c*
c* date: 	8/17/93 -- ecv-b
c*******************************************************************************

	program demo2

      	include 	'/POSTGRES/gdi/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 ......................
      	
	real*4		fnfval(1200)

      	logical*4     	more_data

	integer*2	maxtemp(30), mintemp(30), precip(30), 
     &                  snowfall(30), evap(30),
     &          	fnfyr(1200), fnfmo(1200), fnfid(1200),
     &			fnfday(1200)

      	integer*4       maxrecs1, rows_retrieved, rows_affected, 
     &        		rows_left, maxrecs2, map_id, 
     &			date(30), intdate(1200)

	character	ncdcstn(30)*8, query1*300, query2*300, 
     &                  fout(2)*30, fnfname(1200)*16

	data fnfname/1200 * '                '/, fnfval/1200 * 0./,
     &       fnfyr/1200 * 0/, fnfmo/1200 * 0/, fnfid/1200 * 0/

c........................ b e g i n	p r o g r a m ..........................

c..... initialize some variables

      	vendor = 'postgres'
      	maxtxt = 80

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

      	na = ' '

c..... user input

 50	format(a)

	write(*,*) '  enter program name (max: 16 characters)'
	read(*,50) prgnam
	write(*,50) prgnam

	write(*,*) '  enter first database name (max: 20 characters)'
	read(*,50) database1
	write(*,50) database1

	write(*,*) '  enter first database host name (max: 30 chars)'
	read(*,50) dbhost1
	write(*,50) dbhost1

	write(*,*) '  max no. of records to output from first query'
	read(*,*)  maxrecs1
	write(*,*) maxrecs1

	write(*,*) '  output ascii file for first query (30 chars)'
	read(*,50) fout(1)
	write(*,*) fout(1)

	write(*,*) '  enter second database name (max: 20 characters)'
	read(*,50) database2
	write(*,50) database2

	write(*,*) '  enter second database host name (max: 30 chars)'        
	read(*,50) dbhost2
	write(*,50) dbhost2

	write(*,*) '  max no. of records to output from second query'
	read(*,*)  maxrecs2
	write(*,*) maxrecs2

	write(*,*) '  output ascii file for second query (30 chars)'
	read(*,50) fout(2)
	write(*,*) fout(2)

c..... open output files

	open (unit=21, file= fout(1), status='unknown',
     &        carriagecontrol='list')

	open (unit=22, file= fout(2), status='unknown',
     &        carriagecontrol='list')

C.................  Initialize the GDI. ..........................

      	status = gdi_init(prgnam)
       	if (status .ne. gdi_success) then
          write(*,*) 'GDI_INIT Failed.  Program exiting.'
          goto 999
      	endif

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(*,*) 'GDI_OPEN Failed: Error Code ', errcde
          write(*,*) errtxt
          goto 999
      	endif

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 (query1, 5000)
 5000 	format('retrieve (m.all) from m in ca10clim where m.ncdcstn =',        
     &         ' \"04471302\" and m.precip >= 600 sort by date')

C...........  Create a query mapping ...............................

       	map_id = gdi_open_map(dbconn)
      	if (map_id .eq. gdi_nomap) then
          goto 999
      	endif

C..... Map each attribute being retrieved to a FORTRAN variable
c..... or array (for multiple records)

      	STATUS = GDI_ADD_MAP_FIELD(DBCONN, MAP_ID, 
     +           'ncdcstn', ncdcstn, GDI_STRING, 8, 0)

      	STATUS = GDI_ADD_MAP_FIELD(DBCONN, MAP_ID, 
     +           'date', date, GDI_INT4, 0, 0)

      	STATUS = GDI_ADD_MAP_FIELD(DBCONN, MAP_ID, 
     +           'maxtemp', maxtemp, GDI_INT2, 0, 0)

      	STATUS = GDI_ADD_MAP_FIELD(DBCONN, MAP_ID, 
     +           'mintemp', mintemp, GDI_INT2, 0, 0)

      	STATUS = GDI_ADD_MAP_FIELD(DBCONN, MAP_ID, 
     +           'precip', precip, GDI_INT2, 0, 0)

      	STATUS = GDI_ADD_MAP_FIELD(DBCONN, MAP_ID, 
     +           'snowfall', snowfall, GDI_INT2, 0, 0)

      	STATUS = GDI_ADD_MAP_FIELD(DBCONN, MAP_ID, 
     +           'evap', evap, GDI_INT2, 0, 0)

c..... check status

      	if (status .ne. gdi_success) then
          goto 999
      	endif

      	call gdi_close_map(dbconn, map_id)

C................ Execute the query on the first database ...........

      	status = gdi_submit(dbconn, map_id, query1, maxrecs1,
     +           rows_retrieved, rows_affected, more_data)

        if (status .ne. gdi_success) then
          goto 999
      	endif

c................ Close down first connection

	status = gdi_close(dbconn)

C................ Print out the retrieved data. .....................

	ntot1 = rows_retrieved

      	write(*,*) '  query 1: found ', rows_retrieved,' records:'
      	write(21,*) '  query 1: found ', rows_retrieved,' records:'
	
      	do 10 i = 1, rows_retrieved
          write(21,210) ncdcstn(i), date(i), precip(i)
 210	  format(2x,a8,5x,i8,5x,i8)
 10   	continue
      
      	write(*,*) ' '
      	write(21,*) ' '

      	if (more_data) then
          rows_left = rows_affected - rows_retrieved
          write(*,*) rows_left, ' more rows are available.'
          write(21,*) rows_left, ' more rows are available.'
      	endif

c..... destroy first map

      	call gdi_destroy_map(dbconn, map_id)

c..... close first output file

	close (unit=21)

c+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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(*,*) 'GDI_OPEN Failed: Error Code ', errcde
          write(*,*) errtxt
          goto 999
      	endif

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 (query2, 100)
 100 	format('retrieve (m.name, m.id, m.year, m.month, m.value) '
     &         'from m in FNF where m.name = \"AMF\" and m.id= 1',        
     &         'sort by year')

C...........  Create a query mapping ...............................

       	map_id = gdi_open_map(dbconn)
      	if (map_id .eq. gdi_nomap) then
          goto 999
      	endif

C..... Map each attribute being retrieved to a FORTRAN variable
c..... or array (for multiple records). In this case, we don't need
c..... to map all the attributes since we are only retrieving selected ones.

      	STATUS = GDI_ADD_MAP_FIELD(DBCONN, MAP_ID, 
     +           'value', fnfval, GDI_real4, 0, 0)

      	STATUS = GDI_ADD_MAP_FIELD(DBCONN, MAP_ID, 
     +           'name', fnfname, GDI_STRING, 16, 0)		! 3-chars only

      	STATUS = GDI_ADD_MAP_FIELD(DBCONN, MAP_ID, 
     +           'id', fnfid, GDI_INT2, 0, 0)

      	STATUS = GDI_ADD_MAP_FIELD(DBCONN, MAP_ID, 
     +           'month', fnfmo, GDI_INT2, 0, 0)

      	STATUS = GDI_ADD_MAP_FIELD(DBCONN, MAP_ID, 
     +           'year', fnfyr, GDI_INT2, 0, 0)

c..... check status and close map

      	if (status .ne. gdi_success) then
          goto 999
      	endif

      	call gdi_close_map(dbconn, map_id)

C................ Execute the query on the second database ...........

      	status = gdi_submit(dbconn, map_id, query2, maxrecs2,
     +           rows_retrieved, rows_affected, more_data)

        if (status .ne. gdi_success) then
          goto 999
      	endif

C................ Print out the retrieved data. .....................

        ntot2 = rows_retrieved 

      	write(*,*) ' query 2: found ',rows_retrieved,' records:'
      	write(22,*) ' query 2: found ',rows_retrieved,' records:'

      	do 20 i = 1, rows_retrieved
          write(22,220) fnfname(i)(1:3), fnfid(i), fnfyr(i), 
     &                  fnfmo(i), fnfval(i)
 220	  format(2x,a3,5x,i3,5x,i5,5x,i2,5x,f10.3)
 20   	continue
      
      	write(*,*) ' '
      	write(22,*) ' '

      	if (more_data) then
          rows_left = rows_affected - rows_retrieved
          write(*,*) ROWS_LEFT, ' more rows are available.'
          write(22,*) ROWS_LEFT, ' more rows are available.'
      	endif

C................    Destroy query mapping.   ..........................

      	call gdi_destroy_map(dbconn, map_id)

c..... close connection to the database.

 999  	status = gdi_close(dbconn)
   
	stop
	end
