Example C stored procedure with a GENERAL linkage convention

You can call a stored procedure that uses the GENERAL 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 used for this stored procedure is GENERAL.

The output parameters from this stored procedure contain the SQLCODE from the SELECT statement and the value of the RUNOPTS column from SYSROUTINES.

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
  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

#pragma runopts(plist(os))
#include <stdlib.h>
 
EXEC SQL INCLUDE SQLCA;
 
/***************************************************************/
/* Declare C variables 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];
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 program for SQL processing.                      */
       /********************************************************/
  strcpy(PROCNM, argv[1]);
  strcpy(SCHEMA, argv[2]);
 
       /********************************************************/
       /* Issue the SQL SELECT against the SYSROUTINES         */
       /* DB2 catalog table.                                   */
       /********************************************************/
  strcpy(PARMLST, "");              /* Clear PARMLST           */
  EXEC SQL
    SELECT RUNOPTS INTO :PARMLST
        FROM SYSIBM.ROUTINES
        WHERE NAME=:PROCNM AND
              SCHEMA=:SCHEMA;

       /********************************************************/
       /* Copy SQLCODE to the output parameter list.           */
       /********************************************************/
  *(int *) argv[3] = SQLCODE;
 
       /********************************************************/
       /* Copy the PARMLST value returned by the SELECT back to*/
       /* the parameter list provided to this stored procedure.*/
       /********************************************************/
  strcpy(argv[4], PARMLST);
 
       /********************************************************/
       /* Open cursor C1 to cause DB2 to return a result set   */
       /* to the caller.                                       */
       /********************************************************/
  EXEC SQL OPEN C1;
 }