/******************************************************************************
**
** Source File Name = advsql.sqc  
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 2000 
** 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:
**                 SELECT LASTNAME, WORKDEPT FROM EMPLOYEE\n");
**                    WHERE CASE
**                       WHEN BONUS+COMM = 0 THEN NULL
**                       ELSE SALARY/(BONUS+COMM)
**                    END > 10
**
**    STRUCTURES USED :
**       sqlca
**       sqlda
**
**    APIs USED :
**       none
**
**    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.
**
**    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 <sqlda.h>
#include "utilemb.h"


EXEC SQL INCLUDE SQLCA;


int main(int argc, char *argv[]) {
   int counter = 0;

   EXEC SQL BEGIN DECLARE SECTION;
      char  userid[9];
      char  passwd[19];
      char  lname[16];
      char  wdept[4];
   EXEC SQL END DECLARE SECTION;


   if (argc == 1) {
      EXEC SQL CONNECT TO sample;     /* :rk.5:erk. */
      EMB_SQL_CHECK("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. */
      EMB_SQL_CHECK("CONNECT TO SAMPLE");  /* :rk.6:erk. */
   }
   else {
      printf ("\nUSAGE: advsql [userid passwd]\n\n");
      return 1;
   } /* endif */

   printf( "\nSample C program:  ADVSQL\n");
   printf ("\nthe statement to be executed:\n\n");
   printf ("SELECT LASTNAME, WORKDEPT FROM EMPLOYEE\n");
   printf ("   WHERE CASE\n");
   printf ("      WHEN BONUS+COMM = 0 THEN NULL\n");
   printf ("      ELSE SALARY/(BONUS+COMM)\n");
   printf ("   END > 10\n\n");

   /* Declare the cursor for the advanced SQL statement. */
   EXEC SQL DECLARE c1 CURSOR FOR
            SELECT LASTNAME, WORKDEPT FROM employee
            WHERE CASE
               WHEN BONUS+COMM = 0 THEN NULL
               ELSE SALARY/(BONUS+COMM)
            END > 10;

   EXEC SQL OPEN c1;
   EMB_SQL_CHECK("OPEN c1");

   printf ("LASTNAME        WORKDEPT\n");

   do {
      EXEC SQL FETCH c1 INTO :lname, :wdept;
      if (SQLCODE != 0) break;

      printf ("%-15.15s %-3.3s\n", lname, wdept);

      counter++;
   } while ( 1 );

   EXEC SQL CONNECT RESET;
   EMB_SQL_CHECK("CONNECT RESET");
   return 0;
}
/* end of program : advsql.sqc */