IBM DB2 for i: Code 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;                    
                               

}

Contact IBM

Browse Power Systems

Next generation applications for big data and analytics and cognitive computing are providing unprecedented insights into opportunities, threats and efficiencies. IBM Power Systems is at the forefront of delivering solutions to gain faster insights from analyzing both structured information and unstructured big data. With the secure, flexible and open platform of IBM Power Systems plus solutions and software, organizations can outpace their competitors by delivering faster services, providing differentiated offerings and turning operational cost into investment opportunity.

To draw insights and make better decisions, businesses rely on the secure, flexible and open platform of IBM Power Systems. Built with the first processor designed for big data workloads, the design of Power Systems combines the computing power, memory bandwidth and I/O in ways that are easier to consume and manage, building on strong resiliency, availability and security.

IBM Power Systems deliver flexibility and choice of operating systems to enable your business to support the next generation applications for big data and analytics and cognitive computing that are transforming how organizations work today. Whether running 1, 2, or all 3 - coupled with PowerVM, they maximize the benefit of Power Systems in your business.

Transform your business with Systems Software that enables virtualization, high availability, flexibility, security and compliance on Power Systems™. IBM’s integrated approach to developing Systems and Systems Software together delivers optimized results with Power Systems.

IBM and IBM Business Partner solutions exploit key benefits in IBM Power Systems’ unique design combined with systems software to provide support for next generation applications for big data and analytics and cognitive computing to providing unprecedented insights into opportunities, threats and efficiencies to your organization.

Over the last five years thousands of clients have migrated to IBM Power Systems. Learn how Power Systems has helped them support next generation applications for big data and analytics and cognitive computing on an open platform for choice while improving business performance, reducing risk, and establishing a platform for growth.

Additional information