|
DB2 Example: Procedure Source Code
/*********************************************************************/
/* SQL Parameter Style Example */
/* -Source code for External Stored Procudure using SQL Parm style */
/* */
/* Compile Steps: */
/* CRTSQLCI OBJ(KMTEST/CALLPGM2) SRCFILE(KMTEST/SRC) COMMIT(*NONE) */
/* OUTPUT(*PRINT) OPTION(*GEN *SQL) DBGVIEW(*SOURCE) */
/* */
/* CRTPGM PGM(KMTEST/CALLPGM2) MODULE(*PGM) DETAIL(*FULL) */
/* COMMIT(*NONE) */
/* */
/*********************************************************************/
#include
#include /* standard library */
#include /* incl. strg fnctns. */
int SQLCODE;
char SQLSTATE[5];
main(int argc,char *argv[])
{
/* Data Structure for stored procedure parameters */
unsigned char inputVar[7]; /* For parameter - input */
unsigned char outputVar[7]; /* For parameter - out */
short int inpIndicator; /* Indicator parameter-input */
short int outIndicator; /* Indicator parameter-out */
unsigned char stateVar[5]; /* SQL state parameter */
struct procname {
short int length;
unsigned char data[139];
} procname_var; /* For procedure name parm */
struct specname {
short int length;
unsigned char data[128];
} specname_var; /* For specific name parm */
struct msgtxt {
short int length;
unsigned char data[70];
} msgtxt_var; /* For message text parm */
/* declaration for message being returned */
struct outmsgtxt {
short int length;
unsigned char data[70];
} outmsgtxt_var = {28, "Possible Problem Encountered"};
/********************************************************************/
/* */
/* Receive the parameters into local variables */
/* */
/********************************************************************/
strcpy(inputVar,argv[1]); /* Get input char value */
strcpy(outputVar, argv[2]); /* Get output parm value */
inpIndicator = *(short int *) argv[3]; /* Get indicator parameters */
outIndicator = *(short int *) argv[4];
strncpy(stateVar,argv[5],5); /* Get SQLSTATE parameter */
procname_var = *(struct procname *) argv[6];
/* Get procedure name param */
/* in local variable */
specname_var = *(struct specname *) argv[7];
/* Get specific name param */
/* in local variable */
msgtxt_var = *(struct msgtxt *) argv[8];
/* Get message text param */
/* in local variable */
/********************************************************************/
/* Verify the Parameter count */
/********************************************************************/
if (argc!=9) /* Should be 9 parms */
{
strcpy(outputVar,"ERROR1");
}
/********************************************************************/
/* Verify the Input Parameter */
/********************************************************************/
if (!strcmp(inputVar,"INPVAL")) /* Validate parameter */
{
/* Pass back input value as the output parameter value */
strcpy(outputVar,inputVar);
}
else
{
strcpy(outputVar,"ERROR2");
}
/********************************************************************/
/* Verify the Indicator Parameter */
/********************************************************************/
if (inpIndicator != 0 )
{
strcpy(outputVar,"ERROR3");
}
/********************************************************************/
/* Verify the SQLSTATE Parameter */
/********************************************************************/
if (strncmp(stateVar,"00000",5))
{
strcpy(outputVar,"ERROR4");
}
/********************************************************************/
/* Verify the PROCNAME Parameter */
/********************************************************************/
if ((procname_var.length == 15 ) & /* Procedure name parameter */
(strncmp(procname_var.data,"SPTEST.STYLESQL",
procname_var.length))) /* Change *******************/
/* - change if lib or*/
/* test name changes */
{
strcpy(outputVar,"ERROR5");
}
/********************************************************************/
/* Specific name will be the same as the proc name for this test */
/********************************************************************/
/********************************************************************/
/* Verify the Message Text Parameter */
/********************************************************************/
if (msgtxt_var.length != 0) /* Message text length = 0 */
{
strcpy(outputVar,"ERROR6");
}
/* Set Output parameter value */
strcpy(argv[2],outputVar);
/*******************************************************************/
/* Execute the following code if you want the stored procedure to */
/* set the SQLSTATE and Message Text parameters */
/* --Invoker has to access the value of these parameters */
/* via the SQLCA */
/* --Message text parm can only be set when the SQLSTATE parm */
/* value is 38xxx */
/*******************************************************************/
strncpy(stateVar, "38999", 5);
strncpy(argv[5], stateVar, 5);
*(struct outmsgtxt*)argv[8] = outmsgtxt_var;
}
| 
