/******************************************************************************
**
** Source File Name = tblcli.sqc
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1999
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
**    PURPOSE :
**       This program is an example of how table UDF is implemented in
**       order to execute selection query.
**
**       Use with tblsrv.c UDF
**
**    STRUCTURES USED :
**       sqlca
**       sqlda
**
**    APIs USED :
**
**    FUNCTIONS DECLARED :
**       'C' COMPILER LIBRARY :
**          stdio.h  -  printf
**          string.h -  fgets, strncpy
**
**       DBMS LIBRARY :
**         sqlenv.h
**         sqludf.h
**
**    Other Function(s) used:
**          external : [in the file util.c]
**             check_error :     Checks for SQLCODE error, and prints out any
**                               related information available.
**             init_da:            Initialiaze the memory space for an SQLDA.
**             alloc_host_vars:    Allocate memory to be filled with SQLDA
**                                 data.
**             free_da:            Frees up memory which has been allocated
**                                 as an SQLDA structure.
**             display_col_titles: Display column titles.
**             display_da:         Display row information.
**
**    EXTERNAL DEPENDENCIES :
**       - Ensure existence of database for precompile purposes.
**       - Precompile with the SQL precompiler (PREP in DB2)
**       - Bind to a database (BIND in DB2)
**       - Compile and link with the IBM Cset++ compiler (AIX and OS/2)
**         or the Microsoft Visual C++ compiler (Windows) 
**         or the compiler supported on your platform.
**
** For more information about these samples see the README file.
**
** For more information on programming in C, see the:
**   -  "Programming in C and C++" section of the Application Development Guide
** For more information on Building C Applications, see the:
**   -  "Building C Applications" section of the Application Building Guide.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqludf.h>
#include "util.h"

#ifdef DB268K
/* Need to include ASLM for 68K applications */
#include <LibraryManager.h>
#endif

EXEC SQL INCLUDE SQLCA ;

#define CHECKERR(CE_STR) if (check_error(CE_STR, &sqlca)!=0) return 1;

int output_statement (char *);

int main(int argc, char *argv[]) {

   struct sqlda * sqlda_ptr ;
   int counter = 0 ;
   EXEC SQL BEGIN DECLARE SECTION ;
      char database[9] ;
      char userid[9] ;
      char passwd[19] ;
      char statement[255] ;
   EXEC SQL END DECLARE SECTION ;

#ifdef DB268K
   /* Before making any API calls for 68K environment,
      need to initial the Library Manager */
	InitLibraryManager(0,kCurrentZone,kNormalMemory);
	atexit(CleanupLibraryManager);
#endif

   if ( argc != 4 ) {
      printf ( "\nUSAGE: tblcli database userid password\n\n" ) ;
      return( 1 ) ;
   }
   strcpy( database, argv[1] ) ;
   strcpy( userid, argv[2] ) ;
   strcpy( passwd, argv[3] ) ;
   /* Connect to Database */
   printf( "CONNECT TO Database %s.\n", database ) ;
   EXEC SQL CONNECT TO :database USER :userid USING :passwd ;
   CHECKERR ( "CONNECT TO DATABASE" ) ;

   printf( "\nSample C program:  TBLCLI\n" ) ;

   EXEC SQL DROP FUNCTION tfweather_u ;
   
   EXEC SQL CREATE FUNCTION tfweather_u()
               RETURNS TABLE (CITY          VARCHAR(30),
                              TEMP_IN_F     INTEGER,
                              HUMIDITY      INTEGER,
                              WIND          VARCHAR(5),
                              WIND_VELOCITY INTEGER,
                              BAROMETER     FLOAT,
                              FORECAST      VARCHAR(25))
			   SPECIFIC tfweather_u
			   DISALLOW PARALLEL
			   NOT FENCED
			   NOT VARIANT
			   NO SQL
			   NO EXTERNAL ACTION
			   SCRATCHPAD
			   FINAL CALL
			   LANGUAGE C
			   PARAMETER STYLE DB2SQL
			   EXTERNAL NAME 'tblsrv!weather' ;
			   
   CHECKERR ("CREATE FUNCTION tfweather_u") ;

   memset( statement, '\0', 255) ;

   strcpy( statement,
           "SELECT w.city, w.temp_in_f, w.forecast "
           "FROM TABLE( tfweather_u() ) AS w "
           "WHERE w.barometer > 30.0" ) ;

   init_da( &sqlda_ptr, 1 ) ;
   EXEC SQL PREPARE dyn_statmnt from :statement ;
   CHECKERR ("PREPARE");

   /* obtain the initial SQLDA, to get the number of columns */
   EXEC SQL DESCRIBE dyn_statmnt INTO :*sqlda_ptr ;

   /* need to reassign the SQLDA with the correct number of columns to
      the SQL statement */
   init_da( &sqlda_ptr, sqlda_ptr->sqld ) ;

   EXEC SQL DESCRIBE dyn_statmnt INTO :*sqlda_ptr ;
   CHECKERR ("DESCRIBE") ;

   /* allocating the proper amount of memory space needed for the
      variables */
   alloc_host_vars( sqlda_ptr ) ;

   EXEC SQL DECLARE pcurs CURSOR FOR dyn_statmnt ;
   EXEC SQL OPEN pcurs ;
   CHECKERR ("OPEN") ;

   EXEC SQL FETCH pcurs USING DESCRIPTOR :*sqlda_ptr ;
   CHECKERR ("FETCH") ;
   /* if the FETCH is successful, obtain data from SQLDA */
   /* display the column titles */
   display_col_titles( sqlda_ptr ) ;
   /* display the rows that are fetched */
   while ( SQLCODE == 0 ) {
      counter++ ;
      display_da( sqlda_ptr ) ;
      EXEC SQL FETCH pcurs USING DESCRIPTOR :*sqlda_ptr ;
   } /* endwhile */
   EXEC SQL CLOSE pcurs ;
   CHECKERR ( "CLOSE CURSOR") ;
   printf ( "\n  %d record(s) selected\n\n", counter ) ;

   free_da( sqlda_ptr ) ;
   
   EXEC SQL CONNECT RESET ;
   CHECKERR ( "CONNECT RESET" ) ;
   return( 0 ) ;
}
/* end of program : tblcli.sqc */