CREATE PROCEDURE statement

The CREATE PROCEDURE statement defines a routine.

SYNTAX

Notes:
  1. When the NAMESPACE and NAME clauses are used the values are implicitly constant and of type CHARACTER (or CHAR).
The LANGUAGE clause specifies the language interface to which the procedure body is written. The options are:
  • ESQL - the procedure is called as an ESQL routine
  • DATABASE - the procedure is called as a database stored procedure
  • JAVA - the procedure is called as a static method in a Java class.
If you do not specify the LANGUAGE clause the default language is ESQL, unless you specify the EXTERNAL NAME clause, in which case the default language is DATABASE.
There are restrictions on the use of the LANGUAGE clause. Do not use the:
  • ESQL option with an EXTERNAL NAME clause.
  • DATABASE or JAVA options without an EXTERNAL NAME clause.
  • DATABASE option with a RETURNS clause.

For information on the use of CONSTANT statements, see the DECLARE statement.

Supply the procedure's name using ProcedureName and the procedure's parameters using ParameterList. In ESQL the procedure is implemented using the single ESQL statement Statement. This statement can also be a compound statement, for example BEGIN ... END.

Alternatively, you can use the EXTERNAL clause instead of Statement; (see External stored procedures, or External Java methods if you are using Java).

ESQL procedures, stored procedures, and Java methods (unlike ESQL functions) can have OUT and INOUT parameters in addition to IN parameters. This allows them to return several values to the caller, although they have no RETURN value as such.

Procedures defined in a module are local in scope to the current node only. To use the same procedure in more than one node, define it in a schema.

Using the RETURNS clause allows you to invoke a routine as a procedure or a function. A procedure cannot return values of the ESQL reference data types.

For the JAVA option, the ExternalRoutineName contains the class identifier and method identifier to invoke the declared procedure:
           >>--"-- className---.---methodName--"--------------><
where
className
Identifies the class identifier that contains the Java method. If the class is part of a package, the class identifier part must include the complete package prefix.
methodName
Identifies the name of the Java method to be invoked.

An example EXTERNAL NAME is "com.ibm.broker.test.MyClass.myMethod". The broker loads the Java class from the broker's CLASSPATH. If the specified class cannot be found an exception is thrown.

Note: Redeploy does not reload Java classes. See Deploying Java classes initially for further information.

Overloaded LANGUAGE ESQL routines are not supported. (An overloaded routine is one that has the same name as another routine in the same broker schema which has a different number of parameters, or parameters with different types.) If the broker detects that a routine has been overloaded, it raises an exception.

OUT parameters passed into a procedure (whether internal or external) always contain a NULL value of the correct type when they are received by the procedure. This happens irrespective of their value before the CALL.

IN and INOUT parameters can be NULL when received by the procedure. For example, the following passes NULL to the procedure testProc:

DECLARE nullChar CHARACTER; 
CALL testProc( nullChar ); 	 

This happens because nullChar was not assigned a value before the CALL.

External stored procedures

To call a stored procedure, you must define the procedure in the database and in the broker in ESQL. When writing stored procedures, in languages like C, you must use NULL indicators to ensure that your procedure can process the data correctly.

You can specify either a qualified or unqualified name in the EXTERNAL clause (the qualifier is the name of the database schema in which the procedure is defined). If you do not provide a schema name, the database connection user name is used as the default schema. If the required procedure does not exist in this schema, you must give an explicit schema name:

CREATE PROCEDURE ..... EXTERNAL NAME "mySchema.myProc";

If the procedure belongs to an Oracle package, you must provide an explicit qualified name of the form:

...EXTERNAL NAME "mySchema.myPackage.myProc"

You cannot use SQL wildcards for schema, package, or procedure names except to specify a dynamic schema name. The wildcards (percent, representing one or more characters, and underscore, representing a single character) are modified by the broker to include the database escape character immediately before each wildcard. The database therefore receives these as literal characters.

The clause below is modified by the broker and mySchema.Proc\_ is passed to the database (this assumes that the database escape character is a backslash);

...EXTERNAL NAME "mySchema.Proc_"

If you specify a database schema name as a single percent character when you create the procedure, the broker handles this as a special case and resolves the name at runtime. The clause below is valid and identifies a dynamic database schema:

...EXTERNAL NAME "%.myProc"

If you use this technique, you must include the EXTERNAL SCHEMA clause on the CALL statement that you use to invoke this procedure to identify the appropriate schema.

Database routine example 1 demonstrates how to define and call a stored procedure in Oracle and DB2. Although the database definitions vary between the databases, the ESQL does not. The names given to parameters in the ESQL do not have to match the names they are given on the database side. However, the external name of the procedure must match its defined name in the database, including any package or container specifications. Enclose ExternalRoutineName in quotation marks if it contains a character that is not allowed in an identifier.

All external procedures have the following restrictions:

Start of change

Database routine example 1

The following ESQL code demonstrates how to define and call DB2 stored procedures:

ESQL Definition:
DECLARE inputParm CHARACTER;
DECLARE outputParm CHARACTER;
DECLARE inputOutputParm CHARACTER;

SET inputParm = 'Hello';
SET inputOutputParm = 'World';
CALL swapParms( inputParm, outputParm, inputOutputParm );

CREATE PROCEDURE swapParms (
  IN parm1 CHARACTER,
  OUT parm2  CHARACTER,
  INOUT parm3 CHARACTER
) EXTERNAL NAME dbSwapParms;

To register this stored procedure with DB2, copy the following script to a file (for example, test1.sql)

-- DB2 Example Stored Procedure
DROP PROCEDURE dbSwapParms @                   
CREATE PROCEDURE dbSwapParms
( IN in_param CHAR(32), 
  OUT out_param CHAR(32),
  INOUT inout_param CHAR(32))
LANGUAGE SQL
BEGIN   
SET out_param = inout_param;  
    SET inout_param = in_param;
END @
and execute:
db2 -td@ -vf test1.sql 
from the DB2 command prompt.
Expect the following results from running this code:
  • The value of the IN parameter does not (and cannot, by definition) change.
  • The value of the OUT parameter becomes "World".
  • The value of the INOUT parameter changes to "Hello".
End of change
Start of change

Database routine example 2

The following ESQL code demonstrates how to define and call Oracle stored procedures:

ESQL Definition:
DECLARE inputParm CHARACTER;
DECLARE outputParm CHARACTER;
DECLARE inputOutputParm CHARACTER;

SET inputParm = 'Hello';
SET inputOutputParm = 'World';
CALL swapParms( inputParm, outputParm, inputOutputParm );

CREATE PROCEDURE swapParms (
  IN parm1 CHARACTER,
  OUT parm2  CHARACTER,
  INOUT parm3 CHARACTER
) EXTERNAL NAME dbSwapParms;

To register this stored procedure with Oracle, copy the following script to a file (for example, test1.sql)

CREATE OR REPLACE PROCEDURE dbSwapParms  
( in_param IN VARCHAR2,
  out_param OUT VARCHAR2,
  inout_param IN OUT VARCHAR2 ) 
AS 
BEGIN 
  out_param := inout_param;
  inout_param := in_param; 
END; 
/
and execute:
sqlplus <userid>/<password>  @test1.sql
Expect the following results from running this code:
  • The value of the IN parameter does not (and cannot, by definition) change.
  • The value of the OUT parameter becomes "World".
  • The value of the INOUT parameter changes to "Hello".
End of change
Start of change

Database routine example 3

The following ESQL code demonstrates how to define and call SQL Server stored procedures:

ESQL Definition:
DECLARE inputParm CHARACTER;
DECLARE outputParm CHARACTER;
DECLARE inputOutputParm CHARACTER;

SET inputParm = 'Hello';
SET inputOutputParm = 'World';
CALL swapParms( inputParm, outputParm, inputOutputParm );

CREATE PROCEDURE swapParms (
  IN parm1 CHARACTER,
  INOUT parm2  CHARACTER,
  INOUT parm3 CHARACTER
) EXTERNAL NAME dbSwapParms;

To register this stored procedure with SQLServer, copy the following script to a file (for example, test1.sql)

-- SQLServer Example Stored Procedure 
DROP PROCEDURE dbSwapParms 
go                                                   
CREATE PROCEDURE dbSwapParms 
 @in_param     CHAR(32), 
 @out_param    CHAR(32) OUT, 
 @inout_param  CHAR(32) OUT 
AS 
  SET @out_param   = @inout_param 
  SET @inout_param = @in_param 
go 
and execute:
isql -U<userid> -P<password> -S<server> -d<datasource> -itest1.sql
Note: SQL Server considers OUTPUT parameters from stored procedures as INPUT/OUTPUT parameters.

If you declare these as OUT parameters in your ESQL you encounter a type mismatch error at run time. To avoid that mismatch you must declare SQL Server OUTPUT parameters as INOUT in your ESQL.

Expect the following results from running this code:
  • The value of the IN parameter does not (and cannot, by definition) change.
  • The value of the OUT parameter becomes "World".
  • The value of the INOUT parameter changes to "Hello".
End of change

Example 4: an ESQL procedure

The following example shows the same procedure as in Database routine example 1, but implemented as an ESQL internal procedure. The CALL syntax for this procedure is the same, as well as the result.

CREATE PROCEDURE swapParms (
  IN parm1 CHARACTER,
  OUT parm2  CHARACTER,
  INOUT parm3 CHARACTER )
BEGIN
   SET parm2 = parm3;
   SET parm3 = parm1;
 END; 

Example 5: recursive use of CREATE PROCEDURE

The following example procedure parses a tree, visiting all places at and below the given starting point, and reports what it has found:

 SET OutputRoot.MQMD = InputRoot.MQMD;

  DECLARE answer CHARACTER;
  SET     answer = '';

  CALL navigate(InputRoot.XML, answer);
  SET OutputRoot.XML.Data.FieldNames = answer;


  CREATE PROCEDURE navigate (IN root REFERENCE, INOUT answer CHARACTER)
  BEGIN
    SET answer = answer || 'Reached Field... Type:' || CAST(FIELDTYPE(root) AS CHARACTER)||
    ': Name:' || FIELDNAME(root) || ': Value :' || root || ': ';

    DECLARE cursor REFERENCE TO root;
    MOVE cursor FIRSTCHILD;
    IF LASTMOVE(cursor) THEN
      SET answer = answer || 'Field has children... drilling down ';
    ELSE
      SET answer = answer || 'Listing siblings... ';
    END IF;

    WHILE LASTMOVE(cursor) DO
      CALL navigate(cursor, answer);
      MOVE cursor NEXTSIBLING;
    END WHILE;

    SET answer = answer || 'Finished siblings... Popping up ';
  END;

When given the following input message:

<Person><Name>John Smith</Name><Salary period='monthly' taxable='yes'>-1200</Salary></Person>

the procedure produces the following output, which has been manually formatted:

  Reached Field... Type:16777232: Name:XML: Value :: Field has children... drilling down 
  Reached Field... Type:16777216: Name:Person: Value :: Field has children... drilling down 
  Reached Field... Type:16777216: Name:Name: 
  Value :John Smith: Field has children... drilling down 
  Reached Field... Type:33554432: Name:: 
  Value :John Smith: Listing siblings... Finished siblings... Popping up
  Finished siblings... Popping up 
  Reached Field... Type:16777216: Name:Salary:
  Value :-1200: Field has children... drilling down 
  Reached Field... Type:50331648: Name:period: 
  Value :monthly: Listing siblings... Finished siblings... Popping up
  Reached Field... Type:50331648: Name:taxable: 
  Value :yes: Listing siblings... Finished siblings... Popping up 
  Reached Field... Type:33554432: Name:: 
  Value :-1200: Listing siblings... Finished siblings... Popping up 
  Finished siblings... Popping up 
  Finished siblings... Popping up 
  Finished siblings... Popping up

External Java methods

The Java method you use must have the following basic signature:
         public static <return-type> <method-Name>  (< 0 - N parameters>)
where:
Note:
  1. No exception throws clause is permitted on the Java method signature.
  2. If the Java return type is void, you cannot have a RETURNS clause on the CREATE PROCEDURE statement.
  3. The Java method signature must match the ESQL CREATE PROCEDURE declaration of the procedure. You do this as follows:
    • Ensure that the Java method name matches the procedure's EXTERNAL NAME, including the Class name and any package qualifiers.
    • Ensure that every parameter's type and direction matches the ESQL declaration, according to the rules listed in the table in ESQL-to-Java data-type mapping table.
    • Ensure that the methods return type matches the data type of the RETURNS clause.
  4. Enclose ExternalRoutineName in quotation marks as it must contain at least class.method.
  5. No WebSphere MQ or Java Messaging Service (JMS) work is permitted in the Java code.
  6. Only JDBC type 4 database connections are permitted.

Deploying Java classes initially

To deploy Java classes in your enterprise carry out the following procedure:
  1. Create your required ESQL LANGUAGE JAVA routine in the Toolkit.
  2. Create and compile a Java method with a matching method signature.
  3. Add the directory, in which you want to place the compiled Java code, to the system CLASSPATH on the machine on which the broker is running. There are four options, as follows, and the examples assume that you:
    • Are working on a Windows platform
    • Want to use the c:\mqsi directory to store the Java compiled code
    • The classfile you are deploying is called myTest.class.
    1. You want to deploy the compiled Java class file in a jar file, with the Java class not belonging to a Java package.
      1. Add c:\mqsi\myjar.jar; to the system CLASSPATH.
      2. Create the myjar.jar file containing myTest.class in the root of the jar file.
      3. Place the myjar.jar file in the c:\mqsi directory.
    2. You want to deploy the compiled Java class file without a jar, with the Java class not belonging to a Java package.
      1. Add c:\mqsi; to the system CLASSPATH.
      2. Place the myTest.class class file in the c:\mqsi directory.
    3. You want to place the compiled Java class file without a jar, with the Java class belonging to a package. In this example, the package is com.ibm.broker.test.
      1. Add c:\mqsi; to the system CLASSPATH.
      2. Place myTest.class in the c:\mqsi\com\ibm\broker\test; directory.
    4. You want to place the compiled Java class file in a jar, with the Java class belonging to a package. In this example, the package is com.ibm.broker.test.
      1. Add c:\mqsi\myjar.jar; to the system CLASSPATH.
      2. Create the myjar.jar file containing the myclass.class file in the com.ibm.broker.test directory inside the jar file.
      3. Place the myjar.jar file in the c:\mqsi directory.
    Note: On Windows machines only, you must restart the machine when you change the system CLASSPATH.
  4. Deploy your ESQL to the broker.
For every broker to which the Java routine is deployed, you must:
  • Add the required directory to the system CLASSPATH as detailed above, and reboot any Windows machines.
  • Place the class (or .jar) file in the correct location as detailed above.

Subsequent changes to class files

If you make any subsequent changes to the class (or jar) file that has previously been deployed, as described above, you must:
  1. Stop the broker.
  2. Replace the class (or jar) file.
  3. Restart the broker.
on every broker machine to which the Java routine is deployed

Example 1: Java Method that returns a java.lang.Long

This procedure contains three parameters of varying directions.
 CREATE PROCEDURE myProc1( IN P1 INTEGER, OUT P2 INTEGER, INOUT P3 INTEGER )
 RETURNS INTEGER
 LANGUAGE JAVA 
 EXTERNAL NAME "com.ibm.broker.test.MyClass.myMethod1";
You can use the following ESQL to invoke myProc1:
CALL myProc1( intVar1, intVar2, intVar3) INTO intReturnVar3;
-- or
SET intReturnVar3 = myProc1( intVar1, intVar2, intVar3);

Example 2: Java void return Method

This procedure contains three parameters of varying directions.
CREATE PROCEDURE myProc2( IN P1 INTEGER, OUT P2 INTEGER, INOUT P3 INTEGER )
 LANGUAGE JAVA 
 EXTERNAL NAME "com.ibm.broker.test.MyClass.myMethod2";
You must use the following ESQL to invoke myProc2:
CALL myProc2(intVar1, intVar2, intVar3);
The following Java class provides a method for both the preceding examples:
package com.ibm.broker.test;

class MyClass {
public static Long myMethod1( Long P1, Long[] P2 Long[] P3) { ... }
public static void myMethod2( Long P2, Long[] P2 Long[] P3) { ... }

  /*  When either of these methods is called:
         P1 may or may not be NULL (depending on the value of intVar1).
         P2[0] is always NULL (whatever the value of intVar2).
         P3[0] may or may not be NULL (depending on the value of intVar3).
      This is the same as with current ESQL procedures. 
      When these methods return:
         intVar1 is unchanged
         intVar2 may still be NULL or may have been changed
         intVar3 may contain the same value or may have been changed.
      This is the same as with current ESQL procedures. 
      When myMethod1 returns:
         intReturnVar3 is either  NULL (if the method returns NULL) or it 
         contains the value returned by the method.
  */
}

Any non-static objects created inside the Java routine are marked for garbage collection when the Java routine returns to ESQL, or earlier depending upon the form of reference. However, if the Java class that contains the Java routine has static data members, these exist across multiple invocations of the routine as there is only one copy of these static data members for each execution group.

Therefore, if you intend to call a Java routine that changes the static data from more than one message flow at once, or from a message flow that has an additional instance, you need to make use of the synchronization primitives within Java, in order to ensure data integrity.

Note, however, that static data is lost if you redeploy.

Related concepts
ESQL overview
Related tasks
Developing ESQL
Invoking stored procedures
Related reference
Syntax diagrams: available types
ESQL statements
CALL statement
ESQL-to-Java data-type mapping table