Example C stored procedure with a GENERAL WITH NULLS linkage convention

You can call a stored procedure that uses the GENERAL WITH NULLS linkage convention from a C program.

This example stored procedure does the following:

Searches the Db2 catalog table SYSROUTINES for a row that matches the input parameters from the client program. The two input parameters contain values for NAME and SCHEMA.
Searches the Db2 catalog table SYSTABLES for all tables in which the value of CREATOR matches the value of input parameter SCHEMA. The stored procedure uses a cursor to return the table names.

The linkage convention for this stored procedure is GENERAL WITH NULLS.

The output parameters from this stored procedure contain the SQLCODE from the SELECT operation, and the value of the RUNOPTS column retrieved from the SYSROUTINES table.

The CREATE PROCEDURE statement for this stored procedure might look like this:

CREATE PROCEDURE GETPRML(PROCNM CHAR(18) IN, SCHEMA CHAR(8) IN,
  OUTCODE INTEGER OUT, PARMLST VARCHAR(254) OUT)
  LANGUAGE C
  DETERMINISTIC
  READS SQL DATA
  EXTERNAL NAME "GETPRML"
  COLLID GETPRML
  ASUTIME NO LIMIT
  PARAMETER STYLE GENERAL WITH NULLS
  STAY RESIDENT NO
  RUN OPTIONS "MSGFILE(OUTFILE),RPTSTG(ON),RPTOPTS(ON)"
  WLM ENVIRONMENT SAMPPROG
  PROGRAM TYPE MAIN
  SECURITY DB2
  RESULT SETS 2
  COMMIT ON RETURN NO;

The following example is a C stored procedure with linkage convention GENERAL WITH NULLS.

#pragma runopts(plist(os))
#include <stdlib.h>
 
EXEC SQL INCLUDE SQLCA;
 
/***************************************************************/
/* Declare C variables used for SQL operations on the          */
/* parameters. These are local variables to the C program,     */
/* which you must copy to and from the parameter list provided */
/* to the stored procedure.                                    */
/***************************************************************/
EXEC SQL BEGIN DECLARE SECTION;
char PROCNM[19];
char SCHEMA[9];
char PARMLST[255];
struct INDICATORS {
  short int PROCNM_IND;
  short int SCHEMA_IND;
  short int OUT_CODE_IND;
  short int PARMLST_IND;
 } PARM_IND;
EXEC SQL END DECLARE SECTION;
 
/***************************************************************/
/* Declare cursors for returning result sets to the caller.    */
/***************************************************************/
  EXEC SQL DECLARE C1 CURSOR WITH RETURN FOR
    SELECT NAME
    FROM SYSIBM.SYSTABLES
    WHERE CREATOR=:SCHEMA;
 
main(argc,argv)
  int argc;
  char *argv[];
 {
 
       /********************************************************/
       /* Copy the input parameters into the area reserved in  */
       /* the local program for SQL processing.                */
       /********************************************************/
  strcpy(PROCNM, argv[1]);
  strcpy(SCHEMA, argv[2]);
 
       /********************************************************/
       /* Copy null indicator values for the parameter list.   */
       /********************************************************/
  memcpy(&PARM_IND,(struct INDICATORS *) argv[5],
    sizeof(PARM_IND));

       /********************************************************/
       /* If any input parameter is NULL, return an error      */
       /* return code and assign a NULL value to PARMLST.      */
       /********************************************************/
  if (PARM_IND.PROCNM_IND<0 ||
      PARM_IND.SCHEMA_IND<0 || {
    *(int *) argv[3] = 9999;        /* set output return code  */
    PARM_IND.OUT_CODE_IND = 0;      /* value is not NULL       */
    PARM_IND.PARMLST_IND = -1;      /* PARMLST is NULL         */
   }
 
  else {
       /********************************************************/
       /* If the input parameters are not NULL, issue the SQL  */
       /* SELECT against the SYSIBM.SYSROUTINES catalog        */
       /* table.                                               */
       /********************************************************/
      strcpy(PARMLST, "");          /* Clear PARMLST           */
      EXEC SQL
        SELECT RUNOPTS INTO :PARMLST
            FROM SYSIBM.SYSROUTINES
            WHERE NAME=:PROCNM AND
                  SCHEMA=:SCHEMA;
       /********************************************************/
       /* Copy SQLCODE to the output parameter list.           */
       /********************************************************/
      *(int *) argv[3] = SQLCODE;
      PARM_IND.OUT_CODE_IND = 0;       /* OUT_CODE is not NULL */
    }
 
       /********************************************************/
       /* Copy the RUNOPTS value back to the output parameter  */
       /* area.                                                */
       /********************************************************/
   strcpy(argv[4], PARMLST);
 
       /********************************************************/
       /* Copy the null indicators back to the output parameter*/
       /* area.                                                */
       /********************************************************/
   memcpy((struct INDICATORS *) argv[5],&PARM_IND,
     sizeof(PARM_IND));
 
       /********************************************************/
       /* Open cursor C1 to cause DB2 to return a result set   */
       /* to the caller.                                       */
       /********************************************************/
  EXEC SQL OPEN C1;
  }