IBM DB2 for i: Code example

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

}