/*******************************************************************************
**
** Source File Name = mrspsrv.c  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 a CLI 'output' stored procedure
**       that returns a result set.
**
**       There are two parts to this program:
**           - the mrspcli executable (placed on the client)
**           - the mrspsrv library (placed on the server)
**
**       Refer to the mrspcli.c program for more details on how
**       this program is invoked as the mrspsrv routine
**       in the mrspsrv library by the SQL CALL statement.
**
**       The mrspsrv routine will do two things:
**         1) Obtain the median salary of employees in the "staff" table of
**            the "sample" database. This value will be placed in the
**            input/output SQLDA and returned to the mrspcli routine.
**         2) It will also leave open a cursor in the "staff" table
**            positioned to return all the employees with salaries
**            greater than the median.
**       The mrspcli sample will then print out the median salary, and the
**       list of employees with salaries greater than the median.
**
**
** 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 <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcli1.h>
#include "samputil.h"          /* Header file for CLI sample code */

/*--> SQLL1X58.SCRIPT */
int SQL_API_FN mrspsrv(
               void *reserved1,
               void *reserved2,
               struct sqlda   *output_sqlda,
               struct sqlca   *sqlca)
/*<-- */
{
   /* Delare CLI Variables */
   SQLHANDLE henv, hdbc, hstmt1, hstmt2 ;
   SQLRETURN rc ;

   SQLSMALLINT  num_records;
   SQLINTEGER   indicator;
/*--> */

   SQLCHAR * stmt1 = ( SQLCHAR * ) "SELECT ID, NAME, SALARY FROM STAFF ORDER BY salary" ;
   SQLCHAR * stmt2 = ( SQLCHAR * ) "SELECT count(*) FROM STAFF" ;

   SQLINTEGER   counter = 0;

   /*-----------------------------------------------------------------*/
   /* Setup CLI required environment                                  */
   /*-----------------------------------------------------------------*/

   rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
   if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
   rc = SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbc ) ;
   if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;

   /*-----------------------------------------------------------------*/
   /* Issue NULL Connect, since in CLI we need a statement handle     */
   /* and thus a connection handle and environment handle.            */
   /* A connection is not established, rather the current             */
   /* connection from the calling application is used                 */
   /*-----------------------------------------------------------------*/

   SQLConnect( hdbc, NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS ) ;
   SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt1 ) ;
   SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt2 ) ;

   /* Execute a Statement to Obtain and Order all Salaries */
   rc = SQLExecDirect(hstmt1, stmt1, SQL_NTS);
   if (rc != SQL_SUCCESS) goto ext;

   /* Execute a Statement to */
   /* determine the Total Number of Records */
   rc = SQLExecDirect(hstmt2, stmt2, SQL_NTS);
   if (rc != SQL_SUCCESS) goto ext;

   rc = SQLFetch(hstmt2);
   if (rc != SQL_SUCCESS) goto ext;

   rc = SQLGetData(hstmt2, 1, SQL_C_SHORT, &num_records,
              0, NULL);
   if (rc != SQL_SUCCESS) goto ext;

   /* Fetch Salaries until the Median Salary is Obtained */
   while ( counter++ < num_records/2 + 1 )
   {  rc = SQLFetch(hstmt1);
      if (rc == SQL_ERROR) goto ext;
   }

   /* Return the median salary */
   rc = SQLGetData(hstmt1, 3, SQL_C_DOUBLE, output_sqlda->sqlvar[0].sqldata,
              0, &indicator);
   *(output_sqlda->sqlvar[0].sqlind) = indicator;

   if (rc != SQL_SUCCESS) goto ext;

   /*-----------------------------------------------------------------*/
   /* Return to caller                                                */
   /*-----------------------------------------------------------------*/

ext:
   SQLGetSQLCA(henv, hdbc, hstmt1, sqlca);

   /* Leave hstmt1 allocated, with cursor open to return all rows
      with salaries greater than the median, unless rc == SQL_ERORR
   */
   if( rc == SQL_ERROR)
   {
       rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt1 ) ;
       CHECK_HANDLE( SQL_HANDLE_STMT, hstmt1, rc ) ;
   }
   rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt2 ) ;
   CHECK_HANDLE( SQL_HANDLE_STMT, hstmt2, rc ) ;

    rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;

    rc = SQLDisconnect( hdbc ) ;
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;

    rc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc ) ;
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;

    rc = SQLFreeHandle( SQL_HANDLE_ENV,  henv ) ;
    if ( rc != SQL_SUCCESS )
        return( terminate( henv, rc ) ) ;

    return(1);  /* Return SQLZ_DISCONNECT_PROC */
}
/*<-- */