CREATE PROCEDURE statement

ESQL example 1

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

ESQL example 2

This example procedure shows the recursive use of an ESQL routine. It parses a tree, visiting all places at and below the specified starting point, and reports what it has found:

SET OutputRoot.MQMD = InputRoot.MQMD;

  DECLARE answer CHARACTER;
  SET     answer = '';

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


  CREATE PROCEDURE navigate (IN root REFERENCE, INOUT answer CHARACTER)
  BEGIN
    SET answer = answer || 'Reached Field... Type:' 
    || CAST(FIELDTYPE(root) AS CHAR)||
    ': 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

.NET routine example 1

CREATE PROCEDURE Swap ( 
       IN a INT NOT NULL, 
       OUT b INT NOT NULL, 
       INOUT c INT NOT NULL ) RETURNS CHARACTER NOT NULL
LANGUAGE .NET
EXTERNAL NAME "FunctionTests.SwapString"
ASSEMBLY "C:\coding\test projects\MyAssembly"
APPDOMAIN "MyDomain";

CALL Swap( intVar1, intVar2, intVar3 ) INTO ReturnVar;
-- or
SET ReturnVar = Swap ( intVar1, intVar2, intVar3);

.NET routine example 2

Defines a procedure representing a .NET Method that has no return value with three Nullable parameters of varying directions.

CREATE PROCEDURE SwapNullable ( 
       IN a INTEGER NULLABLE, 
       OUT b INTEGER NULLABLE, 
       INOUT c INTEGER NULLABLE )
LANGUAGE CLR
EXTERNAL NAME "FunctionTests.SwapStringNullable"
ASSEMBLY "MyAssembly2"
APPDOMAIN "MyDomain";

CALL SwapNullable(intVar1, intVar2, intVar3);

Example code for various .NET languages providing methods for both examples

C#

public class FunctionTests
{

  public static string Swap(int pIn, out int pOut, ref int pInout)
  {
    pOut = pInout;
    pInout = pIn;
    return "Finished";
  }

  public static void SwapNullable(long? pIn, out long? pOut, ref long? pInout)
  {
    pOut = pInout;
    pInout = pIn;
  }

}

VB

Public Class FunctionTests

  Shared Function Swap(ByVal pIn As Integer, <Out()> ByRef pOut As Integer, ByRef pInout As Integer) As String
    pOut = pInout
    pInout = pIn
    Return "Finished"
  End Function

  Shared Sub SwapNullable(ByVal pIn As Long?, ByRef pOut As Long?, ByRef pInout As Long?)
    pOut = pInout
    pInout = pIn
  End Sub

End Class

F#

module FunctionTests

  let Swap( pIn : int, [<Out>] pOut : byref<int> ,  pInOut : byref<int> ) = (
    pOut <- pInout
    pInout <- pIn
    let temp = "Finished"
    temp
  )

  let SwapNullable(  pIn : Nullable<int64>, [<Out>] pOut : byref<Nullable<int64>> ,  pInOut : byref<Nullable<int64>> ) = (
    pOut <- pInout
    pInout)
  )

C++ / CLi

public ref class FunctionTests
{
public:

  static String^ Swap(int pIn, [Out] int% pOut, int% pInout)
  {
    pOut = pInout;
    pInout = pIn;
    String^ temp = "Finished";
    return temp;
  }

  static void SwapNullable(Nullable<long long> pIn, [Out] Nullable<long long>% pOut, Nullable<long long>% pInout)
  {
    pOut = pInout;
    pInout = pIn;
  }
}

Java routine example 1

This routine contains three parameters of varying directions, and returns an integer, which maps to a Java return type of java.lang.Long.

CREATE FUNCTION  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);

Java routine example 2

This routine contains three parameters of varying directions and has a Java return type of void.

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 each of the preceding Java 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 might or might not be NULL (depending on the value of intVar1).
    P2[0] is always NULL (whatever the value of intVar2).
    P3[0] might or might not be NULL (depending on the value of intVar3).  
    This is the same as with LANGUAGE ESQL routines. 
    When these methods return:
         intVar1 is unchanged
         intVar2 might still be NULL or might have been changed
         intVar3 might contain the same value or might have been changed.
     This is the same as with LANGUAGE ESQL routines.
     
    When myMethod1 returns: intReturnVar3 is either  NULL (if the
    method returns NULL) or it contains the value returned by the 
    method.
 */
}

Java routine example 3

CREATE FUNCTION myMethod1 ( IN P1 INTEGER, IN P2 INTEGER )
  RETURNS INTEGER
  LANGUAGE JAVA
  EXTERNAL NAME "com.ibm.broker.test.MyClass.myMethod1"
  CLASSLOADER "myClassLoader";

Java routine example 4

CREATE PROCEDURE CallJava(IN VAL1 CHARACTER) 
  RETURNS CHARACTER 
  LANGUAGE JAVA 
  EXTERNAL NAME "com.ibm.test.esql.StaticJavaMethods.basicTransform" 
  CLASSLOADER "{MyJava}";
    

CREATE PROCEDURE CallJava(IN VAL1 CHARACTER) 
  RETURNS CHARACTER 
  LANGUAGE JAVA 
  EXTERNAL NAME "com.ibm.test.esql.StaticJavaMethods.basicTransform" 
  CLASSLOADER "MyJava";
    

Database routine example 1

The following example shows an ESQL definition of a stored procedure that returns a single scalar value and an OUT parameter:

CREATE PROCEDURE myProc1(IN P1 INT, OUT P2 INT)
RETURNS INTEGER
LANGUAGE DATABASE
EXTERNAL NAME "myschema.myproc";

Use this ESQL to invoke the myProc1 routine:

/*using CALL statement invocation syntax*/
CALL myProc1(intVar1, intVar2) INTO intReturnVar3;

/*or using function invocation syntax*/
SET intReturnVar3 = myProc1(intVar1, intVar2);

Database routine example 2

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
)
LANGUAGE DATABASE
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 @

db2 -td@ -vf test1.sql 

Database routine example 3

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
)
LANGUAGE DATABASE
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; 
/

sqlplus userID/password  @test1.sql

Database routine example 4

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
)
LANGUAGE DATABASE
EXTERNAL NAME dbSwapParms;

To register this stored procedure with SQL Server, 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 NOCOUNT ON
  SET @out_param   = @inout_param 
  SET @inout_param = @in_param 
go 

isql -UuserID -Ppassword -Sserver -ddatasource -itest1.sql

SQL Server considers OUTPUT parameters from stored procedures as INPUT/OUTPUT parameters. If you declare them 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.

Database routine example 5

The following ESQL code demonstrates how to define and call Sybase 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
)
LANGUAGE DATABASE
EXTERNAL NAME dbSwapParms;

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

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

isql -U<userID> -P<password> -S<server> -D<datasource> -itest1.sql

Sybase considers OUTPUT parameters from stored procedures as INPUT/OUTPUT parameters. If you declare them as OUT parameters in your ESQL, you encounter a type mismatch error at run time. To avoid that mismatch, declare Sybase OUTPUT parameters as INOUT in your ESQL.

Database routine example 6

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

ESQL Definition:
DECLARE inputParm CHARACTER 'Hello';
DECLARE outputParm CHARACTER;
DECLARE inputOutputParm CHARACTER 'World';
CALL swapParms( inputParm, outputParm, inputOutputParm );

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

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

DROP SPECIFIC PROCEDURE dbSwapParms;
CREATE PROCEDURE dbSwapParms
  (       inParm     CHAR(20),
   OUT    outParm    CHAR(20),
   INOUT  inoutParm  CHAR(20))

   SPECIFIC dbSwapParms

    LET outParm   = inoutParm;
    LET inoutParm = inParm;
END PROCEDURE; 

From the Informix server shell environment, enter:

dbaccess <dataBaseName> <fully qualified path/test1.sql>

The following restrictions apply to Informix stored procedures:

• Procedures that use the Informix INTERVAL datatype cannot be invoked from the integration node.
• Procedures can return only one result set.
• Procedures that return a result set must contain only IN parameters.
• Procedures cannot return CLOBs or BLOBs in result sets or as scalar return values.
• Procedures can return either a result set or a scalar value, but not both.

Database routine example 7

This example shows how to call a stored procedure that returns two result sets, in addition to an out parameter:

CREATE PROCEDURE myProc1 (IN P1 INT, OUT P2 INT)
  LANGUAGE DATABASE
  DYNAMIC RESULT SETS 2
  EXTERNAL NAME "myschema.myproc";

Use the following ESQL to invoke myProc1:

/* using a field reference */
CALL myProc1(intVar1, intVar2, Environment.RetVal[], OutputRoot.XMLNS.A[])
/* using a reference variable*/
CALL myProc1(intVar1, intVar2, myReferenceVariable.RetVal[], myRef2.B[])