/******************************************************************************
**
** Source File Name = mrspsrv2.sqc  1.4            
**
** 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:
**          This sample program demonstrates stored procedures
**          returning a result set using embedded SQL.
**
**          There are two parts to this program:
**              - the mrspcli2 executable (placed on the client)
**              - the mrspsrv2 library (placed on the server)
**
**          Refer to the mrspcli2.sqc program for more details on how
**          this program is invoked as the mrspsrv2 routine
**          in the mrspsrv library by the EXEC SQL CALL statement.
**
**          The mrspsrv2 routine will obtain the median salary of
**          employees in the "staff" table of the "sample" database,
**          and return all employees with a salary higher than the
**          median.
**          The median value will be placed in the input/output SQLDA and
**          returned to the outcli routine.
**
**    EXTERNAL DEPENDENCIES :
**       - Existing database for precompile purposes.
**       - Precompile with the SQL precompiler (PREP in DB2)
**       - Binding to a database (BIND in DB2)
**       - Compiling and linking 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 CLI see the:
**     - "Building CLI Applications" section of the Application Building Guide, and the
**     - CLI Guide and Reference.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/

#include <malloc.h>
#include <memory.h>
#include <string.h>
#include <sqlenv.h>    /* :rk.1:erk. */
#include <sql.h>
#include <sqlda.h>

SQL_API_RC SQL_API_FN mrspsrv2 (   /* :rk.2:erk. */
    void *reserved1,
    void *reserved2,
    struct sqlda   *inout_sqlda,
    struct sqlca   *ca) {

   /* Declare a local SQLCA */
   EXEC SQL INCLUDE SQLCA;

   /* Declare Host Variables */
   EXEC SQL BEGIN DECLARE SECTION;
      short num_records;
      char  stmt[512];
   EXEC SQL END DECLARE SECTION;

   /* Declare local sqlda pointer, and sqlvar variables */
   struct sqlda   *local_sqlda;
   short    id;
   short    ind_id;
   char     name[10];
   short    ind_name;
   short    declen;
   double   salary;
   short    ind_salary;

   /* Declare Miscellaneous Variables */
   int counter = 0;
   short precision;
   EXEC SQL WHENEVER SQLError   GOTO error_exit;
   EXEC SQL WHENEVER SQLWARNING CONTINUE;
   EXEC SQL DECLARE c1 CURSOR WITH HOLD FOR s1;

   /* Alocoate storage for a local sqlda with 3 sqlvars */
   local_sqlda = (struct sqlda *) malloc( SQLDASIZE(3) );

   /* Setup local_sqlda, (see init_da() in util.c for a generic example */
   strncpy(local_sqlda->sqldaid, "SQLDA   ", sizeof (local_sqlda->sqldaid));
   local_sqlda->sqldabc = (long)SQLDASIZE(3);
   local_sqlda->sqln = 3;
   local_sqlda->sqld = 3;

   local_sqlda->sqlvar[0].sqltype = SQL_TYP_NSMALL;
   local_sqlda->sqlvar[0].sqllen = 2;
   local_sqlda->sqlvar[0].sqldata = (char *)&id;
   local_sqlda->sqlvar[0].sqlind = &ind_id;

   local_sqlda->sqlvar[1].sqltype = SQL_TYP_NCHAR;
   local_sqlda->sqlvar[1].sqllen = 9;
   local_sqlda->sqlvar[1].sqldata = (char *)&name;
   local_sqlda->sqlvar[1].sqlind = &ind_name;

   local_sqlda->sqlvar[2].sqltype = SQL_TYP_NFLOAT;
   local_sqlda->sqlvar[2].sqllen = sizeof(double);
   local_sqlda->sqlvar[2].sqldata = (char *) &salary;
   local_sqlda->sqlvar[2].sqlind =  &ind_salary;


   /* Prepare a Statement to Obtain and Order all Salaries */
   strcpy( stmt, "SELECT id, name, salary FROM STAFF ORDER BY salary" );
   EXEC SQL PREPARE s1 FROM :stmt;
   /*Determine the Total Number of Records */
   EXEC SQL SELECT COUNT(*) INTO :num_records FROM STAFF; /* :rk.3:erk. */

   /* Fetch Salaries until the Median Salary is Obtained */
   EXEC SQL OPEN c1;
   while ( counter++ < num_records/2 + 1 )  /* :rk.4:erk. */
   {
       EXEC SQL FETCH c1 USING DESCRIPTOR :*local_sqlda;
   }
   /* Do NOT close cursor, return remaining result set */
   /*  EXEC SQL CLOSE c1;  */

   /* Cursor is delacred WITH Hold, cursor will remain open after COMMIT */
   EXEC SQL COMMIT;

   /* Precsion for a DECIMAL is 1st byte of short.
      Size of a packed decimal is precision/2 + 1;
   */
   precision = ((char *)&(local_sqlda->sqlvar[2].sqllen))[0];

   /* Copy double from local sqlda to inout sqlda */
   memcpy( inout_sqlda->sqlvar[0].sqldata, local_sqlda->sqlvar[2].sqldata,
           sizeof(double) );

   /* Copy length  */
   inout_sqlda->sqlvar[0].sqllen = local_sqlda->sqlvar[2].sqllen;

   /* Copy Indicator */
   if( local_sqlda->sqlvar[2].sqlind != NULL )
   {
       *inout_sqlda->sqlvar[0].sqlind = *local_sqlda->sqlvar[2].sqlind;
   }

   /* Return the SQLCA to the Calling Program */ /* :rk.5:erk. */
   memcpy( ca, &sqlca, sizeof( struct sqlca ) );
   return(SQLZ_DISCONNECT_PROC);

error_exit:
   /* An Error has occurred -- ROLLBACK and return to Calling Program */
   memcpy( ca, &sqlca, sizeof( struct sqlca ) );
   EXEC SQL ROLLBACK;

   return(SQLZ_DISCONNECT_PROC);
}