Parameter handling in PROGRAM TYPE MAIN or PROGRAM TYPE SUB procedures

Procedures can accept parameters in the style of main routines or subroutines. This is determined when you register your procedure with the CREATE PROCEDURE statement.

C or C++ procedures of PROGRAM TYPE SUB accept arguments in the same manner as C or C++ subroutines. Pass parameters as pointers. For example, the following C procedure signature accepts parameters of type INTEGER, SMALLINT, and CHAR(3):

   int storproc (sqlint32 *arg1, sqlint16 *arg2, char *arg3)

Java™ procedures can only accept arguments as subroutines. Pass IN parameters as simple arguments. Pass OUT and INOUT parameters as arrays with a single element. The following parameter-style Java procedure signature accepts an IN parameter of type INTEGER, an OUT parameter of type SMALLINT, and an INOUT parameter of type CHAR(3):

   int storproc (int arg1, short arg2[], String arg[])

To write a C procedure that accepts arguments like a main function in a C program, specify PROGRAM TYPE MAIN in the CREATE PROCEDURE statement. You must write procedures of PROGRAM TYPE MAIN to conform to the following specifications:

• The procedure accepts parameters through two arguments:
  • a parameter counter variable; for example, argc
  • an array of pointers to the parameters; for example, char **argv
• The procedure must be built as a shared library

In PROGRAM TYPE MAIN procedures, the database manager sets the value of the first element in the argv array, (argv[0]), to the name of the procedure. The remaining elements of the argv array correspond to the parameters as defined by the PARAMETER STYLE of the procedure. For example, the following embedded C procedure passes in one IN parameter as argv[1] and returns two OUT parameters as argv[2] and argv[3].

The CREATE PROCEDURE statement for the PROGRAM TYPE MAIN example is as follows:

   CREATE PROCEDURE MAIN_EXAMPLE (IN job CHAR(8),
      OUT salary DOUBLE, OUT errorcode INTEGER)
      DYNAMIC RESULT SETS 0
      LANGUAGE C 
      PARAMETER STYLE GENERAL
      NO DBINFO
      FENCED
      READS SQL DATA
      PROGRAM TYPE MAIN
      EXTERNAL NAME 'spserver!mainexample'

The following code for the procedure copies the value of argv[1] into the CHAR(8) host variable injob, then copies the value of the DOUBLE host variable outsalary into argv[2] and returns the SQLCODE as argv[3]:

  SQL_API_RC SQL_API_FN main_example (int argc, char **argv) 
  {
    EXEC SQL INCLUDE SQLCA;

    EXEC SQL BEGIN DECLARE SECTION;
      char injob[9];
      double outsalary;
    EXEC SQL END DECLARE SECTION;

    /* argv[0] contains the procedure name. */
    /* Parameters start at argv[1]         */
    strcpy (injob, (char *)argv[1]);

    EXEC SQL SELECT AVG(salary)
      INTO :outsalary
      FROM employee
      WHERE job = :injob;

    memcpy ((double *)argv[2], (double *)&outsalary, sizeof(double));

    memcpy ((sqlint32 *)argv[3], (sqlint32 *)&SQLCODE, sizeof(sqlint32));

    return (0);

  } /* end main_example function */