/******************************************************************************
**
** Source File Name = outcli.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.
**
** There are two parts to this program:
** 1. the outcli executable (placed on the client)
** 2. the outsrv library (placed on the server)
**
** There are two different ways to invoke from outcli:
** 1. EXEC SQL CALL :procname (:sal:salind)
** 2. EXEC SQL CALL :procname USING DESCRIPTOR :*inout_sqlda
**
** When the CALL USING and sqleproc API are used,
** the outcli routine will allocate and initialize a one
** variable SQLDA to point to the data.
** This SQLDA may be used for both input and output,
** although in this program it is used for output only.
**
** When the CALL with Host Variable is used,
** the precompiler allocates and initialize an internal one
** variable SQLDA, again for both input and output purpose.
**
** Any of the above 2 ways will call the outsrv routine stored
** in the outsrv library.
**
** The outsrv routine will obtain the median salary of
** employees in the "staff" table of the "sample" database.
** This value will be placed in the output SQLDA and
** returned to the outcli routine. The outcli routine will
** then print out the median salary.
**
** NOTE: All variables on the EXEC SQL CALL statements are assummed to
** be used for both input and output. One technique to minimize
** network flow is for the client program to provide indicator
** variables with all its host variables.
** - The client program should set the "output-only" variable's
** indicator value to -1 before issuing the CALL.
** - The server program should set the "input-only" variable's
** indicator value to -128 before returning.
**
** This will cause the transmission of just the indicator value
** instead of the host variable itself. This will be worthwhile
** when dealing with fairly long character data.
**
** An external function "check_error" is contained in the file "util.c"
** which must be compiled along with this file.
**
** 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 <sql.h> /* :rk.1:erk. */
#include <sqlda.h>
#include <sqlca.h>
#include <string.h>
#include "util.h"
#ifdef DB268K
/* Need to include ASLM for 68K applications */
#include <LibraryManager.h>
#endif
#define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1;
int main(int argc, char *argv[]) {
EXEC SQL BEGIN DECLARE SECTION;
char database[9];
char userid[9];
char passwd[19];
/* Declare a Local Variable for Holding the Procedure's Name */
char procname[255] = "outsrv";
/* Declare Local Variables for Holding Returned Data */
double sal = 0.0; /* :rk.3:erk. */
short salind = 0;
EXEC SQL END DECLARE SECTION;
/* Declare the output SQLDA */
struct sqlda *inout_sqlda = (struct sqlda *)
malloc(SQLDASIZE(1)); /* :rk.2:erk. */
/* Declare the SQLCA */
struct sqlca sqlca;
char eBuffer[1024]; /* error message buffer */
#ifdef DB268K
/* Before making any API calls for 68K environment,
need to initial the Library Manager */
InitLibraryManager(0,kCurrentZone,kNormalMemory);
atexit(CleanupLibraryManager);
#endif
if (argc != 4) {
printf ("\nUSAGE: outcli remote_database userid passwd\n\n");
return 1;
}
strcpy (database, argv[1]);
strcpy (userid, argv[2]);
strcpy (passwd, argv[3]);
/* Connect to Remote Database */
printf("CONNECT TO Remote Database.\n");
EXEC SQL CONNECT TO :database USER :userid USING :passwd; /* :rk.5:erk. */
CHECKERR ("CONNECT TO RSAMPLE");
/********************************************************\
* Call the Remote Procedure via CALL with Host Variables *
\********************************************************/
printf("Use CALL with Host Variable to invoke the Server Procedure "
"named outsrv\n");
salind = -1; /* Sal has no input, so set to null */
EXEC SQL CALL :procname (:sal:salind); /* :rk.6a:erk. */
/* COMMIT or ROLLBACK the transaction */
if (SQLCODE == 0)
{ EXEC SQL COMMIT;
printf("Server Procedure Complete.\n");
}
else
{ /* print the error message, roll back the transaction and return */
sqlaintp (eBuffer, 1024, 80, &sqlca);
printf("\n%s\n", eBuffer);
EXEC SQL ROLLBACK;
printf("Server Procedure Transaction Rolled Back.\n\n");
return 1;
}
/* Print Salary Returned in The Host Variables */
printf("Median Salary = %.2f\n\n", sal );
/***********************************************\
* Call the Remote Procedure via CALL with SQLDA *
\***********************************************/
/* Initialize the output SQLDA */ /* :rk.4:erk. */
inout_sqlda->sqln = 1;
inout_sqlda->sqld = 1;
inout_sqlda->sqlvar[0].sqltype = SQL_TYP_NFLOAT;
inout_sqlda->sqlvar[0].sqllen = sizeof( double );
inout_sqlda->sqlvar[0].sqldata = (char *)&sal;
inout_sqlda->sqlvar[0].sqlind = (short *)&salind;
printf("Use CALL with SQLDA to invoke the Server Procedure "
"named outsrv\n");
salind = -1; /* Sal has no input, so set to null */
EXEC SQL CALL :procname USING DESCRIPTOR :*inout_sqlda; /* :rk.6b:erk. */
/* COMMIT or ROLLBACK the transaction */
if (SQLCODE == 0)
{ EXEC SQL COMMIT;
printf("Server Procedure Complete.\n");
}
else
{ /* print the error message, roll back the transaction and return */
sqlaintp (eBuffer, 1024, 80, &sqlca);
printf("\n%s\n", eBuffer);
EXEC SQL ROLLBACK;
printf("Server Procedure Transaction Rolled Back.\n\n");
return 1;
}
/* Print Salary Returned in The Host Variables */
printf("Median Salary = %.2f\n\n", sal );
/* Free allocated memory */
free( inout_sqlda );
/* Disconnect from Remote Database */
EXEC SQL CONNECT RESET; /* :rk.7:erk. */
CHECKERR ("CONNECT RESET");
return 0;
}
/* end of program : outcli.sqc */