/******************************************************************************
**
** Source File Name = tabsql.sqc  1.5
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 1999 
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
**    PURPOSE : an example of using advanced SQL statements.
**              The advanced SQL statement to be executed and displayed is:
**              The following example is described thoroughly in the
**              "Common Table Expressions" of "Using SQL - Advanced"
**      WITH
**      PAYLEVEL AS (SELECT EMPNO, YEAR(HIREDATE) AS HIREYEAR, EDLEVEL,
**         SALARY+BONUS+COMM AS TOTAL_PAY FROM EMPLOYEE
**         WHERE EDLEVEL > 16),
**      PAYBYED (EDUC_LEVEL, YEAR_OF_HIRE, AVG_TOTAL_PAY) AS
**         (SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY) FROM PAYLEVEL
**         GROUP BY EDLEVEL, HIREYEAR)
**      SELECT EMPNO, EDLEVEL, YEAR_OF_HIRE, TOTAL_PAY, AVG_TOTAL_PAY
**         FROM PAYLEVEL, PAYBYED
**         WHERE EDLEVEL=EDUC_LEVEL
**            AND HIREYEAR = YEAR_OF_HIRE
**            AND TOTAL_PAY < AVG_TOTAL_PAY;
**
**               The second example is described in the "Nested Table
**               Expressions" section of "Using SQL - Advanced".
**      SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY)
**         FROM (
**            SELECT EMPNO, YEAR(HIREDATE) AS HIREYEAR, EDLEVEL,
**               SALARY+BONUS+COMM AS TOTAL_PAY FROM EMPLOYEE
**               WHERE EDLEVEL > 16
**            ) AS PAY_LEVEL
**         GROUP BY EDLEVEL, HIREYEAR
**
**   
**    STRUCTURES USED :
**       sqlca
**       sqlda
**
**    APIs USED :
**
**    FUNCTIONS DECLARED :
**       'C' COMPILER LIBRARY :
**          stdio.h  -  printf
**          string.h -  strncpy
**
**       OTHER :
**          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 DEPENDANCIES :
**       - 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 <sqlda.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 [1000]);

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

   EXEC SQL BEGIN DECLARE SECTION;
      char  userid[9];
      char  passwd[19];
   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

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

   if (argc == 1) {
      EXEC SQL CONNECT TO sample;     /* :rk.5:erk. */
      CHECKERR ("CONNECT TO SAMPLE"); /* :rk.6:erk. */
   }
   else if (argc == 3) { 
      strcpy (userid, argv[1]);
      strcpy (passwd, argv[2]);
      EXEC SQL CONNECT TO sample USER :userid USING :passwd;  /* :rk.5:erk. */
      CHECKERR ("CONNECT TO SAMPLE");  /* :rk.6:erk. */
   }
   else {
      printf ("\nUSAGE: tabsql [userid passwd]\n\n");
      return 1;
   } /* endif */
   
   printf ("\nCOMMON TABLE EXPRESSIONS EXAMPLE\n");
   printf ("^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n");
   strcpy (statement, "WITH "
      "PAYLEVEL AS (SELECT EMPNO, YEAR(HIREDATE) AS HIREYEAR, EDLEVEL, "
         "(SALARY+BONUS+COMM) AS TOTAL_PAY FROM EMPLOYEE "
         "WHERE EDLEVEL > 16), "
      "PAYBYED (EDUC_LEVEL, YEAR_OF_HIRE, AVG_TOTAL_PAY) AS "
         "(SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY) FROM PAYLEVEL "
         "GROUP BY EDLEVEL, HIREYEAR) "
      "SELECT EMPNO, EDLEVEL, YEAR_OF_HIRE, TOTAL_PAY, AVG_TOTAL_PAY "
         "FROM PAYLEVEL, PAYBYED "
         "WHERE EDLEVEL=EDUC_LEVEL "
            "AND HIREYEAR = YEAR_OF_HIRE "
            "AND TOTAL_PAY < AVG_TOTAL_PAY");
   output_statement (statement);

   printf ("\nNESTED TABLE EXPRESSIONS EXAMPLE\n");
   printf ("^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n");
   printf ("QUESTION: What is the average total pay, by education level \n");
   printf ("          and year of hire, for those with an education \n");
   printf ("          level greater than 16?\nANSWER:\n");
   strcpy (statement, "SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY) "
      "AS AVG_TOTAL_PAY "
      "FROM ( "
         "SELECT EMPNO, YEAR(HIREDATE) AS HIREYEAR, EDLEVEL, "
         "SALARY+BONUS+COMM AS TOTAL_PAY FROM EMPLOYEE "
         "WHERE EDLEVEL > 16 "
      ") AS PAY_LEVEL "
      "GROUP BY EDLEVEL, HIREYEAR");
   output_statement (statement);

   EXEC SQL CONNECT RESET;
   CHECKERR ("CONNECT RESET");
   return 0;
}


int output_statement (char sqlInput[1000]) {
   int counter = 0;
   struct sqlda *sqldaPointer;
   EXEC SQL BEGIN DECLARE SECTION;
      char sqlStatement[1000];
   EXEC SQL END DECLARE SECTION;

   strcpy (sqlStatement, sqlInput);

   init_da (&sqldaPointer, 1);
   EXEC SQL PREPARE statement1 from :sqlStatement;
   CHECKERR ("PREPARE");

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

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

   EXEC SQL DESCRIBE statement1 INTO :*sqldaPointer;
   CHECKERR ("DESCRIBE");

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

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

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

   free_da(sqldaPointer);
}
/* end of program : tabsql.sqc */