/*******************************************************************************
**                                                                        
** Source File Name = outsrv2.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.
**
**       There are two parts to this program:
**           - the outcli2 executable (placed on the client)
**           - the outsrv2 library (placed on the server)
**
**       Refer to the outcli2.c program for more details on how
**       this program is invoked as the outsrv2 routine
**       in the outsrv2 library by the SQL CALL statement.
**
**       The outsrv2 routine will 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 outcli2 routine.  The outcli2 sample will
**       then print out the median salary.
**                                                                        
** 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 */

/*--> SQLL1X45.SCRIPT */
int SQL_API_FN outsrv2(
               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 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 */
   SQLExecDirect(hstmt2, stmt2, SQL_NTS);
   SQLFetch(hstmt2);

   rc = SQLGetData(hstmt2, 1, SQL_C_SHORT, &num_records,
              0, NULL);

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

   rc = SQLGetData(hstmt1, 1, 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:
   if ( rc != SQL_SUCCESS) printf("RC = %d\n", rc);
   SQLGetSQLCA(henv, hdbc, hstmt1, sqlca);

   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 */
}
/*<-- */